共计 4420 个字符，预计需要花费 12 分钟才能阅读完成。
原因：
原来每天都做数据备份，一般都是用的全量备份，现在数据库太大，全量非常影响性能，所以在网上找了一些办法，就找到了 XtraBackup 这个工具
前提：
MySQL数据库，InnoDB 引擎
使用：
增量备份：
[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/ #全备数据
[root@master ~]# mysql -uroot -p #在master上创建student库并创建testtb表插入若干数据
Enter password:
mysql> create database student;
Query OK, 1 row affected (0.03 sec)
mysql> use student;
Database changed
mysql> create table testtb(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into testtb values(1),(10),(99);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from testtb;
+------+
| id |
+------+
| 1 |
| 10 |
| 99 |
+------+
3 rows in set (0.00 sec)
mysql> quit;
Bye
#使用innobackupex进行增量备份
[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37/
......
180730 13:51:50 Executing UNLOCK TABLES
180730 13:51:50 All tables unlocked
180730 13:51:50 Backup created in directory '/backups/2018-07-30_13-51-47/'
MySQL binlog position: filename 'mysql-bin.000005', position '664'
180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/backup-my.cnf
180730 13:51:50 [00] ...done
180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/xtrabackup_info
180730 13:51:50 [00] ...done
xtrabackup: Transaction log of lsn (3158741) to (3158741) was copied.
180730 13:51:50 completed OK!
[root@master backups]# ll #查看备份数据
total 0
drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37 #全量备份数据目录
drwxr-x--- 8 root root 273 Jul 30 13:51 2018-07-30_13-51-47 #增量备份数据目录
[root@master 2018-07-30_11-01-37]# cat xtrabackup_checkpoints #查看全量备份的xtrabackup_checkpoints
backup_type = full-backuped #备份类型为全量备份
from_lsn = 0 #lsn从0开始
to_lsn = 3127097 #lsn到3127097结束
last_lsn = 3127097
compact = 0
recover_binlog_info = 0
[root@master 2018-07-30_13-51-47]# cat xtrabackup_checkpoints #查看增量备份的xtrabackup_checkpoints
backup_type = incremental #备份类型为增量备份
from_lsn = 3127097 #lsn从3127097开始
to_lsn = 3158741 #lsn到啊3158741结束
last_lsn = 3158741
compact = 0
recover_binlog_info = 0
增量恢复：
（1）模拟mysql故障，删除数据目录所有数据
[root@master ~]# /etc/init.d/mysqld stop #模拟mysql故障，停止mysql
Shutting down MySQL.. SUCCESS!
[root@master ~]# rm -rf /usr/local/mysql/data/* #删除数据目录中的所有数据
（2）合并全备数据目录，确保数据的一致性
[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/
180730 14:05:27 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /backups/2018-07-30_11-01-37/
......
......
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3127106
InnoDB: Number of pools: 1
180730 14:05:29 completed OK!
（3）将增量备份数据合并到全备数据目录当中
[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/ --incremental-dir=/backups/2018-07-30_13-51-47/
180730 14:06:42 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
......
......
180730 14:06:44 [00] ...done
180730 14:06:44 completed OK!
[root@master ~]# cat /backups/2018-07-30_11-01-37/xtrabackup_checkpoints
backup_type = log-applied #查看到数据备份类型是增加
from_lsn = 0 #lsn从0开始
to_lsn = 3158741 #lsn结束号为最新的lsn
last_lsn = 3158741
compact = 0
recover_binlog_info = 0
（4）恢复数据
[root@master ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/
180730 14:07:51 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!".
.......
.......
180730 14:08:17 [01] ...done
180730 14:08:17 completed OK!
[root@master ~]# ll /usr/local/mysql/data/
total 77844
-rw-r----- 1 root root 79691776 Jul 30 14:08 ibdata1
drwxr-x--- 2 root root 20 Jul 30 14:08 kim
drwxr-x--- 2 root root 4096 Jul 30 14:08 mysql
drwxr-x--- 2 root root 4096 Jul 30 14:08 performance_schema
drwxr-x--- 2 root root 20 Jul 30 14:08 repppp
drwxr-x--- 2 root root 56 Jul 30 14:08 student
drwxr-x--- 2 root root 4096 Jul 30 14:08 wordpress
-rw-r----- 1 root root 21 Jul 30 14:08 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 554 Jul 30 14:08 xtrabackup_info
[root@master ~]# chown -R mysql.mysql /usr/local/mysql/data #更改数据的属主属组
[root@master ~]# /etc/init.d/mysqld start #启动mysql
Starting MySQL.Logging to '/usr/local/mysql/data/master.err'.
.. SUCCESS!
[root@master ~]# mysql -uroot -p -e "show databases;" #查看数据是否恢复
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| kim |
| mysql |
| performance_schema |
| repppp |
| student |
| wordpress |
+--------------------+
正文完
