实验环境：CentOS7
[root@~ localhost]#yum install percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm #创建备份目录： [root@~ localhost]#mkdir -pv /data/backup #创建可以进行备份的用户，只授予可备份的相关权限： MariaDB [(none)]> CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY '123456'; #一定要赋予PROCESS的权限 MariaDB [(none)]> grant RELOAD,RELOAD,REPLICATION CLIENT,PROCESS,LOCK TABLES on *.* to 'back'@'localhost'; MariaDB [(none)]> flush privileges; #全量备份： [root@~ localhost]#innobackupex --user back -p 123456 /data/backup/ #附：收回权限： MariaDB [(none)]> revoke PROCESS on *.* from 'back'@'localhost'; #附：查看用户所拥有的权限； MariaDB [(none)]> show grants for 'back'@'localhost'; #附：查看表的存储引擎 MariaDB [(none)]> show table statusG;
#全量恢复：
#备份恢复的时候最好关闭二进制日志：
MariaDB [hellodb]> set @@session.sql_log_bin=OFF;
#再另一台服务器上安装： [root@~ localhost]#yum -y install mariadb-server percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm #创建临时还原目录： [root@~ localhost]#mkdir -pv /data/backup #此时数据库的数据目录为空 [root@~ localhost]#cd /var/lib/mysql/ [root@mysql localhost]#ls #将备份数据文件发送给此台新服务器： [root@backup localhost]#ls 2017-07-14_12-09-21 [root@backup localhost]#scp -rp 2017-07-14_12-09-21/ root@172.16.254.47:/data/backup/ [root@~ localhost]#cd /data/backup/ [root@backup localhost]#ls 2017-07-14_12-09-21 [root@backup localhost]#cd 2017-07-14_12-09-21/ [root@2017-07-14_12-09-21 localhost]#ls backup-my.cnf ibdata1 performance_schema xtrabackup_binlog_info xtrabackup_info hellodb mysql test xtrabackup_checkpoints xtrabackup_logfile #在此目录下做数据事务的合并和回滚等操作 [root@2017-07-14_12-09-21 localhost]#innobackupex --apply-log ./ 170714 14:00:48 completed OK! [root@2017-07-14_12-09-21 localhost]#ls backup-my.cnf ib_logfile1 test xtrabackup_info hellodb ibtmp1 xtrabackup_binlog_info xtrabackup_logfile ibdata1 mysql xtrabackup_binlog_pos_innodb ib_logfile0 performance_schema xtrabackup_checkpoints #数据的还原 [root@2017-07-14_12-09-21 localhost]#innobackupex --copy-back ./ 170714 14:04:07 completed OK! [root@~ localhost]#cd /var/lib/mysql/ [root@mysql localhost]#ll total 40976 drwxr-x---. 2 root root 272 Jul 14 14:04 hellodb -rw-r-----. 1 root root 18874368 Jul 14 14:04 ibdata1 -rw-r-----. 1 root root 5242880 Jul 14 14:04 ib_logfile0 -rw-r-----. 1 root root 5242880 Jul 14 14:04 ib_logfile1 -rw-r-----. 1 root root 12582912 Jul 14 14:04 ibtmp1 drwxr-x---. 2 root root 4096 Jul 14 14:04 mysql drwxr-x---. 2 root root 4096 Jul 14 14:04 performance_schema drwxr-x---. 2 root root 20 Jul 14 14:04 test -rw-r-----. 1 root root 26 Jul 14 14:04 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 471 Jul 14 14:04 xtrabackup_info [root@mysql localhost]#chown -R mysql.mysql ./* #启动数据库，检查数据，并做全量备份 [root@2017-07-14_12-09-21 localhost]#systemctl start mariadb.service [root@2017-07-14_12-09-21 localhost]#innobackupex -u root /data/backup/
#经过一段时间的写操作，数据库发生数据变化，然后做增量备份：
#刚做完全量备份的源数据库的信息 MariaDB [hellodb]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000003 | 18667 #写操作后的二进制日志信息： MariaDB [(none)]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000003 | 19150 | root@mysql localhost]#cd /data/backup/ [root@backup localhost]#ls 2017-07-14_12-09-21 [root@backup localhost]#innobackupex -u back -p 123456 --incremental /data/backup/ --incremental-basedir=/data/backup/2017-07-14_12-09-21/ 170714 14:37:45 completed OK! #继续写操作，再次进行增量备份，这次增量是基于上一次的增量： [root@backup localhost]#ls 2017-07-14_12-09-21 2017-07-14_14-37-28 [root@backup localhost]#innobackupex -u back -p 123456 --incremental /data/backup/ --incremental-basedir=/data/backup/2017-07-14_14-37-28/ 170714 14:52:18 completed OK! [root@backup localhost]#ls 2017-07-14_12-09-21 2017-07-14_14-37-28 2017-07-14_14-52-08 MariaDB [hellodb]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000003 | 19261 | #加上二进制日志的备份，此处继续写操作，但不备份，使用二进制进行还原： MariaDB [hellodb]> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000003 | 19373 #通过下面的文件查看第二次增量备份后，是从哪个日志序列号开始的二进制日志： [root@backup localhost]#cd 2017-07-14_14-52-08/ [root@2017-07-14_14-52-08 localhost]#ls backup-my.cnf ibdata1.meta xtrabackup_binlog_info xtrabackup_logfile hellodb mysql xtrabackup_checkpoints ibdata1.delta performance_schema xtrabackup_info [root@2017-07-14_14-52-08 localhost]#less xtrabackup_binlog_info master-bin.000003 19261 #是从此开始 [root@2017-07-14_14-52-08 localhost]#cd /var/lib/mysql/ [root@mysql localhost]#ls aria_log.00000001 ibdata1 master-bin.000001 master-bin.index performance_schema aria_log_control ib_logfile0 master-bin.000002 mysql hellodb ib_logfile1 master-bin.000003 mysql.sock [root@mysql localhost]#mysqlbinlog -j 19261 master-bin.000003 > /data/backup/binlog-$(date +%F).sql [root@backup localhost]#ls 2017-07-14_12-09-21 2017-07-14_14-37-28 2017-07-14_14-52-08 binlog-2017-07-14.sql #接下来在另一数据库做增量恢复和二进制日志的恢复（恢复时数据库是不能启动的）： [root@backup localhost]#scp -rp 2017-07-14_14-37-28/ 2017-07-14_14-52-08/ binlog-2017-07-14.sql root@172.16.254.47:/data/backup/ #先全量做合并，然后其余进行合并；最后回滚 [root@backup localhost]#ls 2017-07-14_12-09-21 2017-07-14_14-37-28 2017-07-14_14-52-08 binlog-2017-07-14.sql [root@backup localhost]#innobackupex --apply-log --redo-only 2017-07-14_12-09-21 170714 15:14:19 completed OK! [root@backup localhost]#innobackupex --apply-log --redo-only 2017-07-14_12-09-21 --incremental-dir=2017-07-14_14-37-28 170714 15:19:30 completed OK! [root@backup localhost]#innobackupex --apply-log --redo-only 2017-07-14_12-09-21 --incremental-dir=2017-07-14_14-52-08 170714 15:20:02 completed OK! [root@backup localhost]#innobackupex --apply-log 2017-07-14_12-09-21 170714 15:25:12 completed OK! #恢复： [root@backup localhost]#innobackupex --copy-back 2017-07-14_12-09-21 [root@mysql localhost]#chown -R mysql.mysql * [root@mysql localhost]#systemctl start mariadb.service [root@backup localhost]#ls 2017-07-14_12-09-21 2017-07-14_14-37-28 2017-07-14_14-52-08 binlog-2017-07-14.sql [root@backup localhost]#mysql -p < binlog-2017-07-14.sql [root@backup localhost]#mysql -p #检查数据，保证恢复没有问题