Percona XtraBackup热备份实践
实验环境及操作步骤:一、操作系统
# cat /etc/redhat-release
CentOS release 6.8 (Final)
# uname -a
Linux DB-SERVER 2.6.32-642.el6.x86_64 #1 SMP Tue May 10 17:27:01 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
#
二、MySQL和percona版本及安装
# ll
total 5
-rw-r--r--1 root root 5691656 Apr52015 cmake-2.8.8.tar.gz
-rw-r--r--1 root root 24596474 Apr52015 mysql-5.5.32.tar.gz
-rw-r--r--1 root root 5664452 Oct 172015 percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
#
①MySQL安装
# cd /opt/tools/
# tar xf cmake-2.8.8.tar.gz
# ./configure
# gmake
# gmake install
# cd ..
# yum install ncurses-devel -y
#groupadd mysql
#useradd mysql -s /sbin/nologin -M -g mysql
#tar zxf mysql-5.5.32.tar.gz
#cd mysql-5.5.32
#
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
#make && make install
#cd ..
#cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf
#echo 'export PATH=/application/mysql/bin:$PATH'>>/etc/profile
#chown -R mysql:mysql /application/mysql/data/
#cd /application/mysql/scripts/
#./mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql
#cd /opt/tools/mysql-5.5.32
#cp support-files/mysql.server /etc/init.d/mysqld
#chmod +x /etc/init.d/mysqld
#netstat -lntup|grep 3306tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 18591/mysqld #
MySQL配置文件为:
port = 3306
socket = /application/mysql-5.5.32/tmp/mysql.sock
port = 3306
socket = /application/mysql-5.5.32/tmp/mysql.sock
datadir = /application/mysql-5.5.32/data
basedir = /application/mysql-5.5.32
tmpdir = /application/mysql-5.5.32/tmp
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
innodb_log_file_size = 256M #没有这个参数恢复后启动会报错，所以这里设置下innodb_log_file_size=256
server-id = 1
quick
max_allowed_packet = 16M
no-auto-rehash
key_buffer_size = 8M
sort_buffer_size = 8M
interactive-timeout
鉴于没有设置innodb_log_file_size出现错误为:
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
解决方法:
对于使用了默认 my.cnf（一般教程都会教你使用support-files/my-medium.cnf）的Mysql服务来说
如果中间使用了innodb的话，innodb默认的log file大小是56M
如果你的配置文件使用了类似my-innodb-heavy-4G.cnf作为配置文件的话。
Mysql可以正常启动，但innodb的表无法使用
在错误日志里你会看到如下输出：
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
现在需要做的事情就是把原来的 innodb 的ib_logfile×备份到一个目录下，然后删除掉原来的文件，重启 mysql。
你会看到ib_logfile*大小变成了你配置文件中指定的大小。
my-innodb-heavy-4G.cnf的话（log file 的大小是256M：innodb_log_file_size = 256M）
你会看到很多个268435456大小的文件。
所以我在MySQL的配置文件中使用了该参数，并设置为256M
修改root登陆了密码:
#/application/mysql/bin/mysqladmin -u root password 'new-password'
#
②percona的安装
# cd /opt/tools/
# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/redhat/6/x86_64/percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
#
安装依赖库
#yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
#rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
#rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
#mkdir -p /databackup/xtrabackup
#mkdir -p /databackup/xtrabackuplog
③创建用于实践的数据库并插入数据
CREATE DATABASE opark;
CREATE TABLE `person` (
`number` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO person (number,name,birthday) VALUES ("0001", "John Poul", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0002", "John Hock", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0003", "Rick Hock", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0004", "Rick stone", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0005", "John Green", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0006", "John Halk", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0007", "Rick rose", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0008", "Rick kate", NOW());
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| opark |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use opark;
Database changed
mysql> select * from person;
+--------+------------+------------+
| number | name | birthday |
+--------+------------+------------+
| 1 | John Poul| 2016-09-18 |
| 2 | John Hock| 2016-09-18 |
| 3 | Rick Hock| 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk| 2016-09-18 |
| 7 | Rick rose| 2016-09-18 |
| 8 | Rick kate| 2016-09-18 |
+--------+------------+------------+
8 rows in set (0.01 sec)
mysql>
④创建备份用户和授权
mysql>grant SELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,FILE on *.* to backup@'localhost' identified by 'MANAGER';
三、实战备份操作
（1）全备与恢复
①.全备操作
# cd /databackup/
# ll
total 4
drwx------ 6 root root 4096 Sep 18 01:40 2016-09-18_01-40-28
#
# innobackupex --user=backup --password='MANAGER' /databackup/
160918 02:48:10 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
160918 02:48:13 Backup created in directory '/databackup//2016-09-18_02-48-10'
160918 02:48:13 Writing backup-my.cnf
160918 02:48:13 ...done
160918 02:48:13 Writing xtrabackup_info
160918 02:48:13 ...done
xtrabackup: Transaction log of lsn (1609238) to (1609238) was copied.
160918 02:48:13 completed OK!
#
# ll
total 8
drwx------ 6 root root 4096 Sep 18 01:40 2016-09-18_01-40-28
drwx------ 6 root root 4096 Sep 18 02:48 2016-09-18_02-48-10
#
②.全备恢复操作
对于一般恢复，都是直接用备份文件还原，如果我们这里也是直接运用该备份文件，则可能会导致一些意想不到的问题，比如:备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件处于不一致的状态，我们现在就是要通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。所以我们要用如下命令进行检查
# innobackupex --apply-log /databackup/2016-09-18_02-48-10/
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1609740
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1609750
160918 02:53:59 completed OK!
#
关闭数据库，模拟数据丢失，可以将MySQL的数据目录删除，我们这里为了演示方便，直接把数据目录改名
# /etc/init.d/mysld stop
Shutting down MySQL. SUCCESS!
# cd /application/mysql-5.5.32/
# ll
total 84
drwxr-xr-x2 mysql mysql4096 Sep 17 23:36 bin
-rw-r--r--1 mysql mysql 17987 Jul22013 COPYING
drwx------6 mysql mysql4096 Sep 18 02:54 data
drwxr-xr-x6 mysql mysql4096 Sep 18 01:40 data.backup
drwxr-xr-x2 mysql mysql4096 Sep 17 23:36 docs
drwxr-xr-x3 mysql mysql4096 Sep 17 23:36 include
-rw-r--r--1 mysql mysql7470 Jul22013 INSTALL-BINARY
drwxr-xr-x3 mysql mysql4096 Sep 17 23:36 lib
drwxr-xr-x4 mysql mysql4096 Sep 17 23:36 man
drwxr-xr-x 10 mysql mysql4096 Sep 17 23:36 mysql-test
-rw-r--r--1 mysql mysql2496 Jul22013 README
drwxr-xr-x2 mysql mysql4096 Sep 17 23:36 scripts
drwxr-xr-x 27 mysql mysql4096 Sep 17 23:36 share
drwxr-xr-x4 mysql mysql4096 Sep 17 23:36 sql-bench
drwxr-xr-x3 mysql mysql4096 Sep 17 23:36 support-files
drwxr-xr-x2 mysql root 4096 Sep 18 02:54 tmp
# mv data data.backup
# mkdir data
#
# ll data
total 2
#
目录data下没有数据，接下来执行恢复操作
# innobackupex --copy-back /databackup/2016-09-18_02-48-10/
160918 02:57:41 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 306a2e0)
160918 02:57:41 Copying ib_logfile0 to /application/mysql-5.5.32/data/ib_logfile0
160918 02:57:41 ...done
160918 02:57:42 Copying ./opark/db.opt to /application/mysql-5.5.32/data/opark/db.opt
160918 02:57:42 ...done
160918 02:57:42 Copying ./test/db.opt to /application/mysql-5.5.32/data/test/db.opt
160918 02:57:42 ...done
160918 02:57:42 completed OK!
#
再看MySQL数据目录，数据目录data下数据已经过来了
# ll data
total 116756
-rw-r----- 1 root root 18874368 Sep 18 02:57 ibdata1
-rw-r----- 1 root root 50331648 Sep 18 02:57 ib_logfile0
-rw-r----- 1 root root 50331648 Sep 18 02:57 ib_logfile1
drwx------ 2 root root 4096 Sep 18 02:57 mysql
drwx------ 2 root root 4096 Sep 18 02:57 opark
drwx------ 2 root root 4096 Sep 18 02:57 performance_schema
drwx------ 2 root root 4096 Sep 18 02:57 test
-rw-r----- 1 root root 429 Sep 18 02:57 xtrabackup_info
#
启动数据库
# /etc/init.d/mysld start
Starting MySQL. ERROR! The server quit without updating PID file (/application/mysql-5.5.32/data/DB-SERVER.pid).
进一步查看错误日志:
# cat DB-SERVER.err
160918 18:04:18 mysqld_safe Starting mysqld daemon with databases from /application/mysql-5.5.32/data
160918 18:04:18 Plugin 'FEDERATED' is disabled.
/application/mysql-5.5.32/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
160918 18:04:18 Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
160918 18:04:18 InnoDB: The InnoDB memory heap is disabled
160918 18:04:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160918 18:04:18 InnoDB: Compressed tables use zlib 1.2.3
160918 18:04:18 InnoDB: Initializing buffer pool, size = 128.0M
160918 18:04:18 InnoDB: Completed initialization of buffer pool
160918 18:04:18InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
160918 18:04:18 mysqld_safe mysqld from pid file /application/mysql-5.5.32/data/DB-SERVER.pid ended
#
出现这样的错误一般就是data目录下的数据用户名和和属组不是mysql，所以这里修改下:
# chown -R mysql.mysql data
# ll data
total 116760
-rw-r----- 1 mysql mysql 1025 Sep 18 03:00 DB-SERVER.err
-rw-r----- 1 mysql mysql 18874368 Sep 18 02:57 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep 18 02:57 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep 18 02:57 ib_logfile1
drwx------ 2 mysql mysql 4096 Sep 18 02:57 mysql
drwx------ 2 mysql mysql 4096 Sep 18 02:57 opark
drwx------ 2 mysql mysql 4096 Sep 18 02:57 performance_schema
drwx------ 2 mysql mysql 4096 Sep 18 02:57 test
-rw-r----- 1 mysql mysql 429 Sep 18 02:57 xtrabackup_info
#
再启动运行/etc/init.d/mysqld start
# /etc/init.d/mysld start
Starting MySQL.. SUCCESS!
# mysql -uroot -proot -hlocalhost
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| opark |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use opark;
Database changed
mysql> select * from person;
+--------+------------+------------+
| number | name | birthday |
+--------+------------+------------+
| 1 | John Poul| 2016-09-18 |
| 2 | John Hock| 2016-09-18 |
| 3 | Rick Hock| 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk| 2016-09-18 |
| 7 | Rick rose| 2016-09-18 |
| 8 | Rick kate| 2016-09-18 |
+--------+------------+------------+
8 rows in set (0.00 sec)
mysql>
说明:
innobackup的--copy-back选项用于执行恢复操作，它是通过复制所有数据相关文件至MySQL数据目录，因此，需要清空数据目录。我这里是将其重命名，然后再重建目录。最主要最后一步是将其权限更改
（2）.增量备份
innobackupex --user=backup --password='MANAGER' --incremental /databackup/incrementdir --incremental-basedir=/databackup/2016-09-18_03-35-06/
①.向数据库中添加数据:
INSERT INTO person (number,name,birthday) VALUES ("0009", "Reh Hat", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0010", "pyhton study", NOW());
INSERT INTO person (number,name,birthday) VALUES ("0008", "Linux system", NOW());
mysql> select * from person;
+--------+--------------+------------+
| number | name | birthday |
+--------+--------------+------------+
| 1 | John Poul | 2016-09-18 |
| 2 | John Hock | 2016-09-18 |
| 3 | Rick Hock | 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk | 2016-09-18 |
| 7 | Rick rose | 2016-09-18 |
| 8 | Rick kate | 2016-09-18 |
| 9 | Reh Hat | 2016-09-18 |
| 10 | pyhton study | 2016-09-18 |
| 8 | Linux system | 2016-09-18 |
+--------+--------------+------------+
11 rows in set (0.00 sec)
②.删除数据
mysql> delete from opark.person where name="Reh Hat";
Query OK, 1 row affected (0.01 sec)
mysql> select * from person;
+--------+--------------+------------+
| number | name | birthday |
+--------+--------------+------------+
| 1 | John Poul | 2016-09-18 |
| 2 | John Hock | 2016-09-18 |
| 3 | Rick Hock | 2016-09-18 |
| 4 | Rick stone | 2016-09-18 |
| 5 | John Green | 2016-09-18 |
| 6 | John Halk | 2016-09-18 |
| 7 | Rick rose | 2016-09-18 |
| 8 | Rick kate | 2016-09-18 |
| 10 | pyhton study | 2016-09-18 |
| 8 | Linux system | 2016-09-18 |
+--------+--------------+------------+
10 rows in set (0.00 sec)
mysql>
# innobackupex --user=backup --password='MANAGER' --incremental /databackup/incrementdir --incremental-basedir=/databackup/2016-09-18_03-09-33
160918 03:23:25 Executing UNLOCK TABLES
160918 03:23:25 All tables unlocked
160918 03:23:25 Backup created in directory '/databackup/incrementdir/2016-09-18_03-23-22'
160918 03:23:25 Writing backup-my.cnf
160918 03:23:25 ...done
160918 03:23:25 Writing xtrabackup_info
160918 03:23:25 ...done
xtrabackup: Transaction log of lsn (1611092) to (1611092) was copied.
160918 03:23:25 completed OK!
#
# ll incrementdir/
total 4
drwx------ 6 root root 4096 Sep 18 03:23 2016-09-18_03-23-22
# ll
total 24
drwx------ 6 root root 4096 Sep 18 01:40 2016-09-18_01-40-28
drwx------ 6 root root 4096 Sep 18 02:53 2016-09-18_02-48-10
drwx------ 6 root root 4096 Sep 18 03:05 2016-09-18_03-05-15
drwx------ 6 root root 4096 Sep 18 03:09 2016-09-18_03-09-33
drwxr-xr-x 3 root root 4096 Sep 18 03:23 incrementdir
drwxr-xr-x 2 root root 4096 Sep 18 03:16 xtrabackuplog
#
其中，--incremental-basedir指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/data/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
③.增量备份的恢复，如果需要恢复的话需要做如下操作
# innobackupex --apply-log --redo-only /databackup/2016-09-18_03-35-06/
# innobackupex --apply-log --redo-only /databackup/2016-09-18_03-35-06/ --incremental-dir=/databackup/incrementdir/2016-09-18_03-38-06/ 如果存在多次增量备份的话，就需要多次执行.如
#innobackupex --apply-log --redo-only BACKUPDIR
#innobackupex --apply-log --redo-only BACKUPDIR --incremental-dir=INCREMENTDIR-1
#innobackupex --apply-log --redo-only BACKUPDIR --incremental-dir=INCREMENTDIR-2 BACKUP是全备目录，INCREMENTDIR是增量备份目录，上面是有2次增量备份，如果存在多次增量备份，则需要多次运行如上的命令
另外一种增量恢复方式为:分别将多次的增量备份依次合并到全量备份中，最后执行全量恢复，比如:
#innobackupex --apply-log --redo-only --incremental /databackup/2016-09-18_03-35-06/ --incremental-dir=/databackup/incrementdir/2016-09-18_03-55-12/
#innobackupex --apply-log --redo-only --incremental /databackup/2016-09-18_03-35-06/ --incremental-dir=/databackup/incrementdir/2016-09-18_04-11-30/
#innobackupex --apply-log --redo-only --incremental /databackup/2016-09-18_03-35-06/ --incremental-dir=/databackup/incrementdir/2016-09-18_04-17-11/
#innobackupex --apply-log --redo-only /databackup/2016-09-18_03-35-06/
其中:2016-09-18_03-35-06是全备，后面的2016-09-18_03-55-12、2016-09-18_04-11-30、2016-09-18_04-17-11为增量备份
（3）.Xtrabackup的备份压缩
Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可。如：
1
innobackupex --user=backup --password='MANAGER' --stream=tar/databackup/ | gzip > /databackup/`date +%F_%H-%M-%S`.tar.gz
页:
[1]