Mysql备份工具Xtrabackup

[root@youxi1 ~]# systemctl stop mysqld
[root@youxi1 ~]# rm -rf /var/lib/mysql/*　　//危险操作，请在测试环境测试
[root@youxi1 ~]# innobackupex --copy-back ./db_backup/2019-06-16_15-49-44/ 2>>./db_backup/copyback.log
[root@youxi1 ~]# ll /var/lib/mysql
总用量 12324
-rw-r----- 1 root root      292 6月  16 17:08 ib_buffer_pool
-rw-r----- 1 root root 12582912 6月  16 17:08 ibdata1
drwxr-x--- 2 root root     4096 6月  16 17:08 mysql
drwxr-x--- 2 root root     8192 6月  16 17:08 performance_schema
drwxr-x--- 2 root root     8192 6月  16 17:08 sys
-rw-r----- 1 root root      423 6月  16 17:08 xtrabackup_info
[root@youxi1 ~]# chown -R mysql:mysql /var/lib/mysql　　//重新授权，否则mysqld无法启动
[root@youxi1 ~]# systemctl start mysqld
[root@youxi1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

4）增量备份和还原

　　增量备份的实现，依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。

　　查看完整备份的LSN

[root@youxi1 ~]# cat ./db_backup/2019-06-16_15-49-44/xtrabackup_checkpoints 
backup_type = full-backuped　　//代表完整备份
from_lsn = 0
to_lsn = 2525919
last_lsn = 2525928
compact = 0
recover_binlog_info = 0
flushed_lsn = 2525928

　　创建一些数据，然后以2019-06-16_15-49-44时间戳创建第一个增量备份，并查看LSN

[root@youxi1 ~]# mysql -uroot -p123456
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> use test_db;
Database changed
mysql> create table user_tb(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user_tb values(1,‘zhangsan‘);
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[root@youxi1 ~]# innobackupex -uroot -p123456 --incremental --incremental-basedir=./db_backup/2019-06-16_15-49-44/ ./db_backup/ 2>>./db_backup/backup.log
[root@youxi1 ~]# ls db_backup/
2019-06-16_15-49-44  2019-06-16_15-51-23  2019-06-16_17-26-54  backup.log  copyback.log  test
[root@youxi1 ~]# cat db_backup/2019-06-16_17-26-54/xtrabackup_checkpoints 
backup_type = incremental　　//表示增量备份
from_lsn = 2525919
to_lsn = 2530689
last_lsn = 2530698
compact = 0
recover_binlog_info = 0
flushed_lsn = 2530698

　　再创建一些数据，以上一个增量备份创建新的增量备份，并查看LSN

[root@youxi1 ~]# mysql -u root -p123456
mysql> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into user_tb values(2,‘lisi‘);
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[root@youxi1 ~]# innobackupex --user=root --password=123456 --incremental --incremental-basedir=db_backup/2019-06-16_17-26-54/ ./db_backup/ 2>>./db_backup/backup.log
[root@youxi1 ~]# ls db_backup/                        
2019-06-16_15-49-44  2019-06-16_17-26-54  backup.log    test
2019-06-16_15-51-23  2019-06-16_17-40-13  copyback.log
[root@youxi1 ~]# cat db_backup/2019-06-16_17-40-13/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2530689
to_lsn = 2530892
last_lsn = 2530901
compact = 0
recover_binlog_info = 0
flushed_lsn = 2530901

　　增量备份的还原就比较麻烦了：

　　　　第一步，从完整备份一步步升上来。首先是完整备份，innobackupex --apply-log --redo-only [完整备份的地址]。接着是除了想要还原到的增量备份，都需要innobackupex --apply-log --redo-only [完整备份的地址] --incremental-dir=[第1次到第n-1次增量备份地址]，一个个执行过去。然后想要还原的增量备份innobackupex --apply-log [完整备份的地址] --incremental-dir=[第n次增量备份地址]。这时所有需要的日志就到了完整备份目录下，最后执行innobackupex --apply-log [完整备份的地址]，将未完成的日志执行。

　　　　第二步、停止mysqld，恢复数据，恢复目录及文件权限，启动mysqld，检查是否正常。

　　还原实例：

[root@youxi1 ~]# innobackupex --apply-log --redo-only db_backup/2019-06-16_15-49-44/ 2>>db_backup/copyback.log
[root@youxi1 ~]# innobackupex --apply-log --redo-only db_backup/2019-06-16_15-49-44/ --incremental-dir=db_backup/2019-06-16_17-26-54/ 2>>db_backup/copyback.log
[root@youxi1 ~]# innobackupex --apply-log db_backup/2019-06-16_15-49-44/ --incremental-dir=db_backup/2019-06-16_17-40-13/ 2>>db_backup/copyback.log
[root@youxi1 ~]# innobackupex --apply-log db_backup/2019-06-16_15-49-44/ 2>>db_backup/copyback.log
[root@youxi1 ~]# systemctl stop mysqld
[root@youxi1 ~]# rm -rf /var/lib/mysql/*　　//危险操作
[root@youxi1 ~]# innobackupex --copy-back db_backup/2019-06-16_15-49-44/ 2>db_backup/copyback.log
[root@youxi1 ~]# chown -R mysql:mysql /var/lib/mysql/
[root@youxi1 ~]# systemctl start mysqld
[root@youxi1 ~]# mysql -uroot -p123456
mysql> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user_tb;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+
2 rows in set (0.00 sec)

Mysql备份工具Xtrabackup

标签：adb   密钥   zhang   orm   info   参数   pass   存在   off