MySQL 运维篇 -- 备份/恢复

L小Ray想有腮

已于 2022-04-04 16:28:43 修改

阅读量839

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

于 2020-09-26 17:31:45 首次发布

本文链接：https://blog.csdn.net/weixin_42480750/article/details/108789321

版权

MySQL 专栏收录该内容

28 篇文章 10 订阅

订阅专栏

文章目录

概述
mysqldump
- 数据备份（全备）
- 数据恢复（全备）
xtrabackup
- 软件安装
- 备份及恢复（增量）

概述

备份策略的设计

备份周期
备份工具：myslqdump（MDP）、XBK（Percona Xtrabackup）、MEB（MySQL Enterprise Backup）及mysqlbinlog等
逻辑备份方式：全备（mysqldump）、增量（binlog）
物理备份方式：全备（XBK）、增量（XBK）

备份类型

热备：对业务影响最小（InnoDB）
温备：长时间锁表（MyISAM）
冷备：业务关闭

检查备份的可用性
定期备份恢复演练
数据恢复
数据迁移

mysql -> mysql
other -> mysql
mysql -> other
不同操作系统间

mysqldump

数据备份（全备）

备份范围

全备

[root@mysql01 ~]# mysqldump -uroot -p -A > /tmp/full.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@mysql01 ~]#

       ·   --all-databases, -A

           Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.

           Prior to MySQL 8.0, the --routines and --events options for mysqldump and mysqlpump were not required to include stored routines and events when using the --all-databases option: The dump
           included the mysql system database, and therefore also the mysql.proc and mysql.event tables containing stored routine and event definitions. As of MySQL 8.0, the mysql.event and mysql.proc
           tables are not used. Definitions for the corresponding objects are stored in data dictionary tables, but those tables are not dumped. To include stored routines and events in a dump made using
           --all-databases, use the --routines and --events options explicitly.
           
       ·   --triggers

           Include triggers for each dumped table in the output. This option is enabled by default; disable it with --skip-triggers.

单库备

[root@mysql01 ~]# # mysqldump -uroot -p -B [db1] [db2] [..]
[root@mysql01 ~]# mysqldump -uroot -p -B world school > /tmp/db.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@mysql01 ~]#

单表备

[root@mysql01 ~]# # mysqldump -uroot -p [db] [tab1] [tab2] [..]
[root@mysql01 ~]# mysqldump -uroot -p world city country > /tmp/table.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@mysql01 ~]#

特殊备份参数

-R --routines：函数
-E --events：事件
–triggers：触发器
–master-data=2
（1）记录备份时刻的binlog信息
（2）不加–single-transaction，锁表问备份；加–single-transaction，对于InnoDB表不锁表备份。

       ·   --master-data[=value]

           Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER
           TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after
           you load the dump file into the slave.

           If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the
           statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

           This option requires the RELOAD privilege and the binary log must be enabled.

           The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired
           only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

           It is also possible to set up a slave by dumping an existing slave of the master, using the --dump-slave option, which overrides --master-data and causes it to be ignored if both options are
           used.

–single-transaction

对于InnoDB的表，进行一致性快照备份，不锁表

       ·   --single-transaction

           This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such
           as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

           When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change
           state.

           While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER
           TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is
           performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

           The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

           To dump large tables, combine the --single-transaction option with the --quick option.

–set-gtid-purged
（1）=AUTO/ON
（2）=OFF：仅是做普通的本机备份恢复时，可以添加
–max-allowed-packet=[size]：控制的是备份时传输数据包的大小，导出较大的数据表时可能需要调整。

数据恢复（全备）

环境准备

模拟数据

mysql> create database backup;
Query OK, 1 row affected (0.01 sec)

mysql> use backup;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

全备

[root@mysql01 ~]# mkdir /backup
[root@mysql01 ~]# mysqldump -uroot -p -A -R -E --set-gtid-purged=OFF --master-data=2 --single-transaction | gzip > /backup/full_`date +%F_%T`.sql.gz
Enter password: 
[root@mysql01 ~]# ll -h /backup
total 13M
-rw-r--r--. 1 root root 13M Sep 25 10:42 full_2020-09-25_10:42:51.sql.gz
[root@mysql01 ~]#

模拟数据变化

mysql> use backup;
Database changed
mysql> insert t1 values(11),(22),(33);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t2 values(11),(22),(33);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

模拟故障

mysql> drop database backup;
Query OK, 2 rows affected (0.01 sec)

mysql>

恢复过程

准备临时数据库

[root@mysql01 ~]# systemctl start mysqld61
[root@mysql01 ~]# mysql -S /tmp/mysql33061.sock
....
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          61 |
+-------------+
1 row in set (0.00 sec)

mysql>

查找备份时binglog号（866）

[root@mysql01 ~]# zcat /backup/full_2020-09-25_10\:42\:51.sql.gz | head -50 | grep MASTER_LOG_POS
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=866;
[root@mysql01 ~]#

查找事故前binlog号（1709）

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000010 |     1819 |              |                  | 95a35387-fa2c-11ea-8932-000c29b46d5f:1-19 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000010';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000010 |    4 | Format_desc    |         6 |         125 | Server ver: 8.0.20, Binlog ver: 4                                  |
| mysql-bin.000010 |  125 | Previous_gtids |         6 |         196 | 95a35387-fa2c-11ea-8932-000c29b46d5f:1-12                          |
| mysql-bin.000010 |  196 | Gtid           |         6 |         273 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:13' |
| mysql-bin.000010 |  273 | Query          |         6 |         387 | create database backup /* xid=2867 */                              |
| mysql-bin.000010 |  387 | Gtid           |         6 |         464 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:14' |
| mysql-bin.000010 |  464 | Query          |         6 |         579 | use `backup`; create table t1(id int) /* xid=2872 */               |
| mysql-bin.000010 |  579 | Gtid           |         6 |         658 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:15' |
| mysql-bin.000010 |  658 | Query          |         6 |         735 | BEGIN                                                              |
| mysql-bin.000010 |  735 | Table_map      |         6 |         785 | table_id: 121 (backup.t1)                                          |
| mysql-bin.000010 |  785 | Write_rows     |         6 |         835 | table_id: 121 flags: STMT_END_F                                    |
| mysql-bin.000010 |  835 | Xid            |         6 |         866 | COMMIT /* xid=2873 */                                              |
| mysql-bin.000010 |  866 | Gtid           |         6 |         945 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:16' |
| mysql-bin.000010 |  945 | Query          |         6 |        1022 | BEGIN                                                              |
| mysql-bin.000010 | 1022 | Table_map      |         6 |        1072 | table_id: 200 (backup.t1)                                          |
| mysql-bin.000010 | 1072 | Write_rows     |         6 |        1122 | table_id: 200 flags: STMT_END_F                                    |
| mysql-bin.000010 | 1122 | Xid            |         6 |        1153 | COMMIT /* xid=4433 */                                              |
| mysql-bin.000010 | 1153 | Gtid           |         6 |        1230 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:17' |
| mysql-bin.000010 | 1230 | Query          |         6 |        1345 | use `backup`; create table t2(id int) /* xid=4435 */               |
| mysql-bin.000010 | 1345 | Gtid           |         6 |        1424 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:18' |
| mysql-bin.000010 | 1424 | Query          |         6 |        1501 | BEGIN                                                              |
| mysql-bin.000010 | 1501 | Table_map      |         6 |        1551 | table_id: 263 (backup.t2)                                          |
| mysql-bin.000010 | 1551 | Write_rows     |         6 |        1601 | table_id: 263 flags: STMT_END_F                                    |
| mysql-bin.000010 | 1601 | Xid            |         6 |        1632 | COMMIT /* xid=4436 */                                              |
| mysql-bin.000010 | 1632 | Gtid           |         6 |        1709 | SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:19' |
| mysql-bin.000010 | 1709 | Query          |         6 |        1819 | drop database backup /* xid=4438 */                                |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
25 rows in set (0.01 sec)

mysql>

二进制日志截取（–skip-gtids）

[root@mysql01 ~]# mysqlbinlog --skip-gtids --start-position=866 --stop-position=1709 /data/mysql/binlog/mysql-bin.000010 > /backup/binlog.sql
[root@mysql01 ~]#

恢复备份到临时库

[root@mysql01 ~]# gunzip -k /backup/full_2020-09-25_10\:42\:51.sql.gz 
[root@mysql01 ~]# mv /backup/full_2020-09-25_10\:42\:51.sql /backup/full.sql
[root@mysql01 ~]# mysql -S /tmp/mysql33061.sock
....
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /backup/full.sql
....
Query OK, 0 rows affected (0.00 sec)
....
mysql> source /backup/binlog.sql
....
Query OK, 0 rows affected (0.00 sec)
....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from backup.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
+------+
6 rows in set (0.00 sec)

mysql> select * from backup.t2;
+------+
| id   |
+------+
|   11 |
|   22 |
|   33 |
+------+
3 rows in set (0.00 sec)

mysql>

将故障表导出并恢复到生产

[root@mysql01 ~]# mysqldump -S /tmp/mysql33061.sock -B backup > /backup/restore.sql
[root@mysql01 ~]# mysql -S /tmp/mysql.sock -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /backup/restore.sql
....
Query OK, 0 rows affected (0.00 sec)
....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from backup.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
+------+
6 rows in set (0.00 sec)

mysql> select * from backup.t2;
+------+
| id   |
+------+
|   11 |
|   22 |
|   33 |
+------+
3 rows in set (0.00 sec)

mysql>

xtrabackup

XtraBackup备份原理

针对非InnoDB，进行锁表备份，copy所有的非InnoDB表文件
而针对InnoDB，立即触发CKPT，copy所有的InnoDB相关的文件（ibdata1,idb等），并将备份过程中产生的新数据变化部分，即redo日志文件一起备份走
在恢复时，xtrabackup会调用InnoDB引擎的CSR过程，将数据和redo日志的LSN追平，然后进行一致性恢复。

软件安装

下载软件（这儿有问题，先看完整个安装步骤）

https://www.percona.com/downloads/Percona-XtraBackup-8.0/LATEST/

在这里插入图片描述
2. 安装软件

[root@mysql01 ~]# tar -xf Percona-XtraBackup-8.0.14-r113f3d7-el8-x86_64-bundle.tar 
[root@mysql01 ~]# ls
Percona-XtraBackup-8.0.14-r113f3d7-el8-x86_64-bundle.tar  percona-xtrabackup-80-debuginfo-8.0.14-1.el8.x86_64.rpm    percona-xtrabackup-test-80-8.0.14-1.el8.x86_64.rpm
percona-xtrabackup-80-8.0.14-1.el8.x86_64.rpm             percona-xtrabackup-80-debugsource-8.0.14-1.el8.x86_64.rpm
[root@mysql01 ~]# dnf localinstall ./*.rpm
Last metadata expiration check: 2:57:17 ago on Fri 25 Sep 2020 09:20:21 AM CST.
Package percona-xtrabackup-80-8.0.14-1.el8.x86_64 is already installed.
Dependencies resolved.
====================================================================================================================================================================================================================
 Package                                                      Architecture                      Version                                                               Repository                               Size
====================================================================================================================================================================================================================
Installing:
 percona-xtrabackup-80-debuginfo                              x86_64                            8.0.14-1.el8                                                          @commandline                            201 M
 percona-xtrabackup-80-debugsource                            x86_64                            8.0.14-1.el8                                                          @commandline                             15 M
 percona-xtrabackup-test-80                                   x86_64                            8.0.14-1.el8                                                          @commandline                            210 M
Installing dependencies:
 mariadb                                                      x86_64                            3:10.3.17-1.module_el8.1.0+257+48736ea6                               AppStream                               6.1 M
 mariadb-common                                               x86_64                            3:10.3.17-1.module_el8.1.0+257+48736ea6                               AppStream                                62 k
Enabling module streams:
 mariadb                                                                                        10.3                                                                                                               

Transaction Summary
====================================================================================================================================================================================================================
Install  5 Packages

Total size: 432 M
Total download size: 6.1 M
Installed size: 1.6 G
Is this ok [y/N]:

安装完成，偶然重启系统，发现mysql不能登录

[root@mysql01 ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib64/mariadb/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
[root@mysql01 ~]#

在官网文档找到报错原因：8.0以后默认启用“caching_sha2_password”进行认证，如果客户端或连接器不支持此种方式的话，就会报诸如以下的错误。

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

在这里插入图片描述

但是手工安装的mysql-server是支持“caching_sha2_password”：

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatible-connectors

在这里插入图片描述

反复检查后发现，mysql命令优先使用的安装XtraBackup时的依赖mariadb，而手工安装的mysql-server中的命令是可以正常登录的。

[root@mysql01 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/mysql/bin:/root/bin
[root@mysql01 ~]# which mysql
/usr/bin/mysql
[root@mysql01 ~]# rpm -ql mariadb | grep /usr/bin/mysql$
/usr/bin/mysql
[root@mysql01 ~]# /usr/local/mysql/bin/mysql -uroot -p
....
mysql> select @@default_authentication_plugin;
+---------------------------------+
| @@default_authentication_plugin |
+---------------------------------+
| caching_sha2_password           |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

解决方案：查看XtraBackup的安装文档，发现其实只安装percona-xtrabackup-80-8.0.14-1.el8.x86_64.rpm即可。

https://www.percona.com/doc/percona-xtrabackup/8.0/installation/yum_repo.html

在这里插入图片描述

在其它虚拟机上试验了下多余的三个XtraBackup单独安装

[root@mysql01 ~]# dnf localinstall ./*.rpm
Last metadata expiration check: 0:02:14 ago on Fri 25 Sep 2020 05:03:05 PM CST.
Package percona-xtrabackup-80-8.0.14-1.el8.x86_64 is already installed.
Dependencies resolved.
====================================================================================================================================================================================================================
 Package                                                      Architecture                      Version                                                               Repository                               Size
====================================================================================================================================================================================================================
Installing:
 percona-xtrabackup-80-debuginfo                              x86_64                            8.0.14-1.el8                                                          @commandline                            201 M
 percona-xtrabackup-80-debugsource                            x86_64                            8.0.14-1.el8                                                          @commandline                             15 M
 percona-xtrabackup-test-80                                   x86_64                            8.0.14-1.el8                                                          @commandline                            210 M
Installing dependencies:
 mariadb                                                      x86_64                            3:10.3.17-1.module_el8.1.0+257+48736ea6                               AppStream                               6.1 M
 mariadb-common                                               x86_64                            3:10.3.17-1.module_el8.1.0+257+48736ea6                               AppStream                                62 k
Enabling module streams:
 mariadb                                                                                        10.3                                                                                                               

Transaction Summary
====================================================================================================================================================================================================================
Install  5 Packages

Total size: 432 M
Total download size: 6.1 M
Installed size: 1.6 G
Is this ok [y/N]:

卸载掉吧

[root@mysql01 ~]# dnf search percona
Last metadata expiration check: 1:39:37 ago on Fri 25 Sep 2020 04:14:54 PM CST.
========================================================================================= Name & Summary Matched: percona ==========================================================================================
percona-xtrabackup-test-80.x86_64 : Test suite for Percona XtraBackup
percona-xtrabackup-80-debugsource.x86_64 : Debug sources for package percona-xtrabackup-80
percona-xtrabackup-80-debuginfo.x86_64 : Debug information for package percona-xtrabackup-80
============================================================================================== Name Matched: percona ===============================================================================================
percona-xtrabackup-80.x86_64 : XtraBackup online backup for MySQL / InnoDB
[root@mysql01 ~]# dnf remove percona-xtrabackup-test-80.x86_64 percona-xtrabackup-80-debugsource.x86_64 percona-xtrabackup-80-debuginfo.x86_64
Dependencies resolved.
====================================================================================================================================================================================================================
 Package                                                      Architecture                      Version                                                              Repository                                Size
====================================================================================================================================================================================================================
Removing:
 percona-xtrabackup-80-debuginfo                              x86_64                            8.0.14-1.el8                                                         @@commandline                            1.0 G
 percona-xtrabackup-80-debugsource                            x86_64                            8.0.14-1.el8                                                         @@commandline                            117 M
 percona-xtrabackup-test-80                                   x86_64                            8.0.14-1.el8                                                         @@commandline                            505 M
Removing unused dependencies:
 mariadb                                                      x86_64                            3:10.3.17-1.module_el8.1.0+257+48736ea6                              @AppStream                                40 M
 mariadb-common                                               x86_64                            3:10.3.17-1.module_el8.1.0+257+48736ea6                              @AppStream                               179 k

Transaction Summary
====================================================================================================================================================================================================================
Remove  5 Packages

Freed space: 1.6 G
Is this ok [y/N]:

刷新下环境变量文件登录成功

[root@mysql01 ~]# mysql -uroot -p
-bash: /usr/bin/mysql: No such file or directory
[root@mysql01 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/mysql/bin:/root/bin
[root@mysql01 ~]# tail -2 /etc/profile
# User defined 2020/9/19
export PATH=$PATH:/usr/local/mysql/bin
[root@mysql01 ~]# source /etc/profile
[root@mysql01 ~]# mysql -uroot -p
....
mysql> quit
Bye
[root@mysql01 ~]#

备份及恢复（增量）

模拟数据

mysql> create database full;
Query OK, 1 row affected (0.01 sec)

mysql> use full;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

全备

[root@mysql01 ~]# xtrabackup --backup --target-dir=/data/backup/mysql/full -uroot -S /tmp/mysql.sock -p
xtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=6 --log_bin=/data/mysql/binlog/mysql-bin 
xtrabackup: recognized client arguments: --backup=1 --target-dir=/data/backup/mysql/full --user=root --socket=/tmp/mysql.sock --password 
....
xtrabackup: Transaction log of lsn (17841309) to (17846139) was copied.
200926 14:59:14 completed OK!
[root@mysql01 ~]#

模拟第一次数据变化

mysql> create database inc1;
Query OK, 1 row affected (0.01 sec)

mysql> use inc1;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

第一次增量备份

[root@mysql01 ~]# mkdir /data/backup/mysql/inc1
[root@mysql01 ~]# xtrabackup --backup --target-dir=/data/backup/mysql/inc1 --incremental-basedir=/data/backup/mysql/full -uroot -S /tmp/mysql.sock -p
xtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=6 --log_bin=/data/mysql/binlog/mysql-bin 
xtrabackup: recognized client arguments: --backup=1 --target-dir=/data/backup/mysql/inc1 --incremental-basedir=/data/backup/mysql/full --user=root --socket=/tmp/mysql.sock --password

模拟第二次数据变化

mysql> create database inc2;
Query OK, 1 row affected (0.01 sec)

mysql> use inc2;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

第二次增量备份

[root@mysql01 ~]# mkdir /data/backup/mysql/inc2
[root@mysql01 ~]# xtrabackup --backup --target-dir=/data/backup/mysql/inc2 --incremental-basedir=/data/backup/mysql/inc1 -uroot -S /tmp/mysql.sock -p
xtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=6 --log_bin=/data/mysql/binlog/mysql-bin 
xtrabackup: recognized client arguments: --backup=1 --target-dir=/data/backup/mysql/inc2 --incremental-basedir=/data/backup/mysql/inc1 --user=root --socket=/tmp/mysql.sock --password 
....
xtrabackup: Transaction log of lsn (17895755) to (17900405) was copied.
200926 15:26:04 completed OK!
[root@mysql01 ~]#

模拟第三次数据变化

mysql> create database inc3;
Query OK, 1 row affected (0.01 sec)

mysql> use inc3;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

模拟故障

[root@mysql01 ~]# ps -ef | grep mysql
mysql       1752       1  0 14:22 ?        00:00:24 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root        2299    1621  0 14:56 pts/0    00:00:00 mysql -uroot -p
root        2384    1724  0 15:41 pts/1    00:00:00 grep --color=auto mysql
[root@mysql01 ~]# pkill mysqld
[root@mysql01 ~]# mkdir /backup/
[root@mysql01 ~]# rsync -az /data/mysql/data/ /backup
[root@mysql01 ~]# rm -rf /data/mysql/data/*
[root@mysql01 ~]# systemctl start mysqld
[root@mysql01 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Sat 2020-09-26 15:43:57 CST; 5s ago
  Process: 2423 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf (code=exited, status=1/FAILURE)
 Main PID: 2423 (code=exited, status=1/FAILURE)

Sep 26 15:43:56 mysql01 systemd[1]: Started MySQL Server.
Sep 26 15:43:57 mysql01 systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
Sep 26 15:43:57 mysql01 systemd[1]: mysqld.service: Failed with result 'exit-code'.
[root@mysql01 ~]#

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
ERROR: 
Can't connect to the server

mysql>

Prepare上一次全备

[root@mysql01 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full
....
Shutdown completed; log sequence number 17846139
Number of pools: 1
200926 15:47:47 completed OK!
[root@mysql01 ~]#

整合第一次增量到上一次全备

[root@mysql01 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full --incremental-dir=/data/backup/mysql/inc1
....
incremental backup from 17841309 is enabled.
xtrabackup: cd to /data/backup/mysql/full/
xtrabackup: This target seems to be already prepared with --apply-log-only.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(17868628)
....
xtrabackup: page size for /data/backup/mysql/inc1//ibdata1.delta is 16384 bytes
Applying /data/backup/mysql/inc1//ibdata1.delta to ./ibdata1...
xtrabackup: page size for /data/backup/mysql/inc1//sys/sys_config.ibd.delta is 16384 bytes
Applying /data/backup/mysql/inc1//sys/sys_config.ibd.delta to ./sys/sys_config.ibd...
....
200926 15:49:51 completed OK!
[root@mysql01 ~]#

再整合第二次增量到全备

[root@mysql01 ~]# xtrabackup --prepare --target-dir=/data/backup/mysql/full --incremental-dir=/data/backup/mysql/inc2
....
200926 15:55:18 completed OK!
[root@mysql01 ~]#

提取binlog的起始ID

[root@mysql01 ~]# cat /data/backup/mysql/inc2/xtrabackup_binlog_info 
mysql-bin.000015	196	95a35387-fa2c-11ea-8932-000c29b46d5f:1-10
[root@mysql01 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000015 | grep SET | tail -10
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
/*!80001 SET @@session.original_commit_timestamp=1601105424244392*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= '95a35387-fa2c-11ea-8932-000c29b46d5f:13'/*!*/;
SET TIMESTAMP=1601105424/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysql01 ~]#

xtrabackup_binlog_info：记录的是最后一次增量备份时GTID号
mysql-bin.000015：记录的是事故发生前的最后一次事务提交后的GTID号

提取binlog

[root@mysql01 ~]# mysqlbinlog --skip-gtids --include-gtids='95a35387-fa2c-11ea-8932-000c29b46d5f:11-13' /data/mysql/binlog/mysql-bin.000015 > /data/backup/mysql/binlog.sql
[root@mysql01 ~]#

恢复全备

[root@mysql01 ~]# rsync -az /data/backup/mysql/full/ /data/mysql/data/
[root@mysql01 ~]# chown -R mysql.mysql /data/mysql/data/
[root@mysql01 ~]#

恢复binlog

[root@mysql01 ~]# systemctl start mysqld
[root@mysql01 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-09-26 16:15:08 CST; 3s ago
 Main PID: 2685 (mysqld)
    Tasks: 19 (limit: 11310)
   Memory: 283.9M
   CGroup: /system.slice/mysqld.service
           └─2685 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

Sep 26 16:15:08 mysql01 systemd[1]: Started MySQL Server.
[root@mysql01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> set sql_log_bin=0
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /data/backup/mysql/binlog.sql;
....
Query OK, 0 rows affected (0.00 sec)
....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

检查数据

mysql> select * from full.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql> select * from inc1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from inc2.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from inc3.t1;
Empty set (0.00 sec)

mysql>