Xtrabackup Backup and Recovery
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
一、下载
[root@server ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm二、安装依赖包
http://mirror.centos.org/centos/7/extras/x86_64/Packages/libev-4.15-7.el7.x86_64.rpm
[root@server soft]# rpm -ivh libev-4.15-7.el7.x86_64.rpm
三、安装Xtrabackup
[root@server soft]# yum install -y percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
[root@server soft]# rpm -qa|grep percona-xtrabackup
percona-xtrabackup-24-2.4.23-1.el7.x86_64
[root@server soft]# rpm -ql percona-xtrabackup-24-2.4.23-1.el7.x86_64
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.23
/usr/share/doc/percona-xtrabackup-24-2.4.23/LICENSE
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
四、Innobackupex备份
1.全备
(1) 全备
[root@db01 ~]# innobackupex -uroot -p123456 /tmp/xtrabackup/full
如果不想生成日期目录，可以使用--no-timestamp参数，如:
[root@db01 ~]# innobackupex --login-path=root --no-timestamp /tmp/xtrabackup/full
如果不想使用明文密码，可以使用mysql_config_editor绕过密码输入:
[root@db01 ~]# mysql_config_editor set --login-path=root --user=root --password --host=localhost --socket=/tmp/mysql.sock
[root@db01 ~]# mysql_config_editor print --all
[client]
[root]
user = root
password = *****
host = localhost
socket = /tmp/mysql.sock
[root@db01 ~]# innobackupex --login-path=root /tmp/xtrabackup/full
(2) 查看备份信息
[root@db01 ~]# ll /tmp/xtrabackup/full/2021-07-14_09-23-46
total 77876
-rw-r-----. 1 root root 487 Jul 14 09:23 backup-my.cnf
drwxr-x---. 2 root root 48 Jul 14 09:23 binlog
drwxr-x---. 2 root root 46 Jul 14 09:23 gtid
-rw-r-----. 1 root root 359 Jul 14 09:23 ib_buffer_pool
-rw-r-----. 1 root root 79691776 Jul 14 09:23 ibdata1
drwxr-x---. 2 root root 118 Jul 14 09:23 mydb
drwxr-x---. 2 root root 4096 Jul 14 09:23 mysql
drwxr-x---. 2 root root 8192 Jul 14 09:23 performance_schema
drwxr-x---. 2 root root 8192 Jul 14 09:23 sys
drwxr-x---. 2 root root 48 Jul 14 09:23 test
-rw-r-----. 1 root root 63 Jul 14 09:23 xtrabackup_binlog_info
-rw-r-----. 1 root root 141 Jul 14 09:23 xtrabackup_checkpoints
-rw-r-----. 1 root root 538 Jul 14 09:23 xtrabackup_info
-rw-r-----. 1 root root 2560 Jul 14 09:23 xtrabackup_logfile
[root@db01 2021-07-14_09-23-46]# more xtrabackup_binlog_info
mysql-bin.000010 194 d5d29a37-ace6-11eb-9949-000c29bdd3e5:1-64
[root@db01 2021-07-14_09-23-46]# more xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 139317855
last_lsn = 139317864
compact = 0
recover_binlog_info = 0
flushed_lsn = 139317864
[root@db01 2021-07-14_09-23-46]# more xtrabackup_info
uuid = 24162a01-e442-11eb-b6a4-000c29bdd3e5
name =
tool_name = innobackupex
tool_command = -uroot -p123456 /tmp/xtrabackup/full
tool_version = 2.4.23
ibbackup_version = 2.4.23
server_version = 5.7.26-log
start_time = 2021-07-14 09:23:47
end_time = 2021-07-14 09:23:48
lock_time = 0
binlog_pos = filename 'mysql-bin.000010', position '194', GTID of the last change 'd5d29a37-ace6-11eb-9949-000c29bdd3e5:1-64'
innodb_from_lsn = 0
innodb_to_lsn = 139317855
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
(3) 备份报错处理
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp /tmp/xtrabackup/full
xtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=6 --log_bin=mysql-bin
xtrabackup: recognized client arguments:
210713 16:00:37 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!".
210713 16:00:37 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.
210713 16:00:37 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
解决办法:
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --host=127.0.0.1 /tmp/xtrabackup/full
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp -S /tmp/mysql.sock /tmp/xtrabackup/full
[root@db01 ~]# vi /etc/my.cnf
[client]
socket=/tmp/mysql.sock
2.增量备份
incremental backup是以full backup为基础，必须先有全备，使用--incremental定义增量备份参数。
(1) 全备
[root@db01 ~]# innobackupex -uroot -p123456 /tmp/xtrabackup/full
(2) 增量
[root@db01 ~]# innobackupex -uroot -p123456 --incremental /tmp/xtrabackup/inc --incremental-basedir=/tmp/xtrabackup/full/2021-07-14_13-00-57
如果多个增量，需重复执行步骤2，incremental-basedir需要承接上次增量目录，如:
[root@db01 inc]# innobackupex -uroot -p123456 --incremental /tmp/xtrabackup/inc --incremental-basedir=/tmp/xtrabackup/inc/2021-07-14_13-31-00
[root@db01 inc]# innobackupex -uroot -p123456 --incremental /tmp/xtrabackup/inc --incremental-basedir=/tmp/xtrabackup/inc/2021-07-14_13-40-473.单库/单表备份
单库备份前提: innodb_file_per_table开启
单库备份有三种方法，本次采用include备份:
(1) innobackupex --include
--include=REGEXP¶
This option is a regular expression to be matched against table names in databasename.tablename format. It is passed directly to xtrabackup’s xtrabackup --tables option. See the xtrabackup documentation for details.
---include=mydb 备份t库
--include=mydb.t 备份t表
--include=^imdb[.]t#P#p4 备份t表分区p4
[root@db01 ~]# innobackupex -uroot -p123456 --include=mydb /tmp/xtrabackup/mydb
[root@db01 ~]# ll /tmp/xtrabackup/mydb/2021-07-15_09-47-43/
total 77848
-rw-r-----. 1 root root 487 Jul 15 09:47 backup-my.cnf
-rw-r-----. 1 root root 474 Jul 15 09:47 ib_buffer_pool
-rw-r-----. 1 root root 79691776 Jul 15 09:47 ibdata1
drwxr-x---. 2 root root 74 Jul 15 09:47 mydb
-rw-r-----. 1 root root 146 Jul 15 09:47 xtrabackup_binlog_info
-rw-r-----. 1 root root 141 Jul 15 09:47 xtrabackup_checkpoints
-rw-r-----. 1 root root 636 Jul 15 09:47 xtrabackup_info
-rw-r-----. 1 root root 2560 Jul 15 09:47 xtrabackup_logfile
(2) innobackupex --tables-file
--tables-file=FILE
This option accepts a string argument that specifies the file in which there are a list of names of the form database.table, one per line. The option is passed directly to xtrabackup ‘s innobackupex --tables-file option.
cat /tmp/tables.txt
mydb.t
test.t
iypt.t1
innobackupex -uroot -p123456 --tables-file=/tmp/tables.txt /tmp/xtrabackup/mydb
(3) innobackupex --databases
--databases=LIST
This option specifies the list of databases that innobackupex should back up. The option accepts a string argument or path to file that contains the list of databases to back up. The list is of the form “databasename1[.table_name1] databasename2[.table_name2] . . .”. If this option is not specified, all databases containing MyISAM and InnoDB tables will be backed up. Please make sure that –databases contains all of the InnoDB databases and tables, so that all of the innodb.frm files are also backed up. In case the list is very long, this can be specified in a file, and the full path of the file can be specified instead of the list. (See option –tables-file.)
innobackupex -uroot -p123456 --databases=mydb,mysql /tmp/xtrabackup/db4.单表增量备份
(1) 全备t表
[root@db01 ~]# innobackupex -uroot -p123456 --include=mydb.t /tmp/xtrabackup/t
[root@db01 ~]# ll /tmp/xtrabackup/t/2021-07-15_12-56-58
total 77848
-rw-r-----. 1 root root 487 Jul 15 12:57 backup-my.cnf
-rw-r-----. 1 root root 474 Jul 15 12:57 ib_buffer_pool
-rw-r-----. 1 root root 79691776 Jul 15 12:56 ibdata1
drwxr-x---. 2 root root 60 Jul 15 12:57 mydb
-rw-r-----. 1 root root 147 Jul 15 12:57 xtrabackup_binlog_info
-rw-r-----. 1 root root 141 Jul 15 12:57 xtrabackup_checkpoints
-rw-r-----. 1 root root 636 Jul 15 12:57 xtrabackup_info
-rw-r-----. 1 root root 2560 Jul 15 12:57 xtrabackup_logfile
[root@db01 ~]# ll /tmp/xtrabackup/t/2021-07-15_12-56-58/mydb
total 216
-rw-r-----. 1 root root 8556 Jul 15 12:57 t1.frm
-rw-r-----. 1 root root 98304 Jul 15 12:56 t1.ibd
-rw-r-----. 1 root root 8556 Jul 15 12:57 t.frm
-rw-r-----. 1 root root 98304 Jul 15 12:56 t.ibd
(2) 增量备份t表
[root@db01 ~]# innobackupex -uroot -p123456 --include=mydb.t --incremental /tmp/xtrabackup/inc_t --incremental-basedir=/tmp/xtrabackup/t/2021-07-15_13-41-37
五、Innobackupex恢复
xtrabackupex恢复步骤:
1.停mysql
2.清空datadir目录数据
3.apply-log整理备份
4.copy-data恢复数据
5.修改datadir目录文件权限
6.开启mysql
1.全备恢复
(1) 全备
[root@db01 ~]# innobackupex -uroot -p123456 /tmp/xtrabackup/full
(2) 模拟数据丢失
mysql> use mydb
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
mysql> drop table t;
(3) 关闭数据库
[root@db01 ~]# mysqladmin -uroot -p shutdown
(4) 整理全备
[root@db01 ~]# innobackupex --apply-log --use-memory=4G /tmp/xtrabackup/full/2021-07-14_11-06-59
(5) 删除datadir数据
[root@db01 ~]# rm -rf /data/mysql/data/
(6) 恢复数据
[root@db01 ~]# innobackupex --copy-back /tmp/xtrabackup/full/2021-07-14_11-06-59
[root@db01 ~]# ll /data/mysql/data/
total 188460
drwxr-x---. 2 root root 48 Jul 14 11:16 binlog
drwxr-x---. 2 root root 46 Jul 14 11:16 gtid
-rw-r-----. 1 root root 474 Jul 14 11:16 ib_buffer_pool
-rw-r-----. 1 root root 79691776 Jul 14 11:16 ibdata1
-rw-r-----. 1 root root 50331648 Jul 14 11:16 ib_logfile0
-rw-r-----. 1 root root 50331648 Jul 14 11:16 ib_logfile1
-rw-r-----. 1 root root 12582912 Jul 14 11:16 ibtmp1
drwxr-x---. 2 root root 118 Jul 14 11:16 mydb
drwxr-x---. 2 root root 4096 Jul 14 11:16 mysql
drwxr-x---. 2 root root 8192 Jul 14 11:16 performance_schema
drwxr-x---. 2 root root 8192 Jul 14 11:16 sys
drwxr-x---. 2 root root 48 Jul 14 11:16 test
-rw-r-----. 1 root root 22 Jul 14 11:16 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root 538 Jul 14 11:16 xtrabackup_info
-rw-r-----. 1 root root 1 Jul 14 11:16 xtrabackup_master_key_id
(7) 重新分配权限
[root@db01 ~]# chown -R mysql:mysql /data/mysql/data
(8) 启动数据库
[root@db01 ~]# mysqld_safe --user=mysql &
(9) 验证数据
mysql> use mydb
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
2.增量恢复
replay the committed transactions on each backup:
1.full --apply-log --redo-only
2.incremental1 --apply-log --redo-only
3.incremental2 --apply-log --redo-only
4.full --apply-log
(1) 全备重演事务
[root@db01 inc]# innobackupex --apply-log --redo-only --use-memory=1G /tmp/xtrabackup/full/2021-07-14_13-00-57
(2) 增量重演事务
[root@db01 inc]# innobackupex --apply-log --redo-only --use-memory=1G /tmp/xtrabackup/full/2021-07-14_13-00-57 --incremental-dir=/tmp/xtrabackup/inc/2021-07-14_13-31-00
[root@db01 inc]# innobackupex --apply-log --redo-only --use-memory=1G /tmp/xtrabackup/full/2021-07-14_13-00-57 --incremental-dir=/tmp/xtrabackup/inc/2021-07-14_13-40-47
[root@db01 inc]# innobackupex --apply-log --use-memory=1G /tmp/xtrabackup/full/2021-07-14_13-00-57 --incremental-dir=/tmp/xtrabackup/inc/2021-07-14_13-42-39
注意: 最后一次增量合并不需要--redo-only参数。
(3) 整合全备与增量
[root@db01 inc]# innobackupex --apply-log --use-memory=1G /tmp/xtrabackup/full/2021-07-14_13-00-57
(4) 关闭数据库
[root@db01 inc]# mysqladmin -uroot -p shutdown
(5) 删除数据
[root@db01 inc]# rm -rf /data/mysql/data
(6) 恢复数据
innobackupex --copy-back /tmp/xtrabackup/full/2021-07-14_13-00-57
(7) 重新分配权限
[root@db01 ~]# chown -R mysql:mysql /data/mysql/data
(8) 启动数据库
[root@db01 ~]# mysqld_safe --user=mysql &
(9) 验证数据
mysql> use mydb
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
3.单库恢复
单库恢复前需使用--export参数prepare，不能使用--copy-backup恢复
(1) 模拟删除数据库
mysql> drop database mydb;
(2) 使用--export整理备份
[root@db01 ~]# innobackupex --apply-log --export /tmp/xtrabackup/mydb/2021-07-15_09-47-43
(3) 创建数据库
mysql> create database mydb charset utf8mb4;
(4) 创建表
[root@db01 ~]# mysqlfrm --diagnostic /tmp/xtrabackup/mydb/2021-07-15_09-47-43/mydb/ > /tmp/create_table.sql
mysql> use mydb
mysql> source /tmp/create_table.sql
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| t |
| t1 |
+----------------+
(5) discard tablespace
mysql> alter table t discard tablespace;
mysql> alter table t1 discard tablespace;
(6) 从备份中Copy表数据
[root@db01 ~]# cp -r /tmp/xtrabackup/mydb/2021-07-15_09-47-43/mydb/*.cfg /data/mysql/data/mydb/
[root@db01 ~]# cp -r /tmp/xtrabackup/mydb/2021-07-15_09-47-43/mydb/*.ibd /data/mysql/data/mydb/
(7) 分配权限
[root@db01 ~]# chown -R mysql.mysql /data/mysql/data/mydb/*
(8) import tablespace
mysql> alter table t import tablespace;
mysql> alter table t1 import tablespace;
(9) 验证数据
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.01 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
4.单表恢复
(1) 模拟数据丢失
mysql> use mydb
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
mysql> truncate table t;
(2) 使用--export整理备份
[root@db01 ~]# innobackupex --apply-log --export /tmp/xtrabackup/mydb/2021-07-15_09-47-43
(3) discard tablespace
mysql> alter table t discard tablespace;
(4) 从备份中Copy表数据
[root@db01 ~]# cp -r /tmp/xtrabackup/mydb/2021-07-15_09-47-43/mydb/t.cfg /data/mysql/data/mydb/
[root@db01 ~]# cp -r /tmp/xtrabackup/mydb/2021-07-15_09-47-43/mydb/t.ibd /data/mysql/data/mydb/
(7) 分配权限
[root@db01 ~]# chown -R mysql.mysql /data/mysql/data/mydb/*
(8) import tablespace
mysql> alter table t import tablespace;
(9) 验证数据
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
5.单表增量恢复
(2) 模拟数据丢失
mysql> truncate table t;
mysql> truncate table t1;
(1) 全备单表prepare
[root@db01 ~]# innobackupex --apply-log --redo-only --use-memory=1G --export /tmp/xtrabackup/t/2021-07-15_13-41-37
(3) 增量prepare
[root@db01 ~]# innobackupex --apply-log --use-memory=1G --export /tmp/xtrabackup/t/2021-07-15_13-41-37 --incremental-dir=/tmp/xtrabackup/inc_t/2021-07-15_13-44-47
注意: 最后一次增量合并不需要--redo-only参数。
(3) 整合全备与增量
[root@db01 inc]# innobackupex --apply-log --use-memory=1G --export /tmp/xtrabackup/t/2021-07-15_13-41-37
(4) discard tablespace
mysql> use mydb
mysql> alter table t discard tablespace;
mysql> alter table t1 discard tablespace;
(5) 从备份中Copy表数据
[root@db01 ~]# cp -r /tmp/xtrabackup/t/2021-07-15_13-41-37/mydb/t.cfg /data/mysql/data/mydb/
[root@db01 ~]# cp -r /tmp/xtrabackup/t/2021-07-15_13-41-37/mydb/t.ibd /data/mysql/data/mydb/
[root@db01 ~]# cp -r /tmp/xtrabackup/t/2021-07-15_13-41-37/mydb/t1.cfg /data/mysql/data/mydb/
[root@db01 ~]# cp -r /tmp/xtrabackup/t/2021-07-15_13-41-37/mydb/t1.ibd /data/mysql/data/mydb/
(6) 分配权限
[root@db01 ~]# chown -R mysql.mysql /data/mysql/data/mydb/*
(7) import tablespace
mysql> alter table t import tablespace;
mysql> alter table t1 import tablespace;
(8) 验证数据
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
7 rows in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
结论: --include=mydb.t 会将mydb库下所有的表也一同备份。
六、Xtrabackup完全恢复
xtrabackup与mysqlbinlog实现完全恢复
(1) 全库备份
[root@db01 ~]# innobackupex --uroot -p123456 /tmp/xtrabackup/full
(2) 模拟数据丢失
mysql> use mydb
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
mysql> insert into t values (7);
mysql> drop database mydb;
(3) 查看备份Position
[root@db01 ~]# cd /tmp/xtrabackup/full/2021-07-15_19-52-59/
[root@db01 2021-07-15_19-52-59]# more xtrabackup_binlog_info
mysql-bin.000002 431
[root@db01 2021-07-15_19-52-59]# more xtrabackup_info
uuid = 3488f5fb-e563-11eb-80c4-000c29bdd3e5
name =
tool_name = innobackupex
tool_command = --uroot -p123456 /tmp/xtrabackup/full
tool_version = 2.4.23
ibbackup_version = 2.4.23
server_version = 5.7.26-log
start_time = 2021-07-15 19:52:59
end_time = 2021-07-15 19:53:01
lock_time = 1
binlog_pos = filename 'mysql-bin.000002', position '431'
innodb_from_lsn = 0
innodb_to_lsn = 126574700
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
[root@db01 2021-07-15_19-52-59]# more xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 126574700
last_lsn = 126574709
compact = 0
recover_binlog_info = 0
flushed_lsn = 126574709
确定start-position:431
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 840 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> show binlog events in '/data/mysql/data/mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 6 | 291 | BEGIN |
| mysql-bin.000002 | 291 | Table_map | 6 | 335 | table_id: 108 (mydb.t) |
| mysql-bin.000002 | 335 | Delete_rows | 6 | 400 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 400 | Xid | 6 | 431 | COMMIT /* xid=46 */ |
| mysql-bin.000002 | 431 | Anonymous_Gtid | 6 | 496 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 496 | Query | 6 | 568 | BEGIN |
| mysql-bin.000002 | 568 | Table_map | 6 | 612 | table_id: 213 (mydb.t) |
| mysql-bin.000002 | 612 | Write_rows | 6 | 652 | table_id: 213 flags: STMT_END_F |
| mysql-bin.000002 | 652 | Xid | 6 | 683 | COMMIT /* xid=77 */ |
| mysql-bin.000002 | 683 | Anonymous_Gtid | 6 | 748 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 748 | Query | 6 | 840 | drop database mydb |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
14 rows in set (0.00 sec)
确定drop database mydb前的stop-position:683
(4) 使用mysqlbinlog截取binlog数据
mysqlbinlog --start-position=431 --stop-position=683 /data/mysql/data/mysql-bin.000002 > /tmp/mydb.sql
(5) 全库恢复
1.全库prepare
[root@db01 ~]# innobackupex --apply-log --use-memory=1G /tmp/xtrabackup/full/2021-07-15_19-52-59
2.关闭数据库
[root@db01 ~]# mysqladmin -uroot -p shutdown
3.删除数据
[root@db01 ~]# rm -rf /data/mysql/data
4.恢复
[root@db01 ~]# innobackupex --copy-back /tmp/xtrabackup/full/2021-07-15_19-52-59
5.授权
[root@db01 ~]# chown -R mysql:mysql /data/mysql/data
6.启动数据库
[root@db01 ~]# mysqld_safe --user=mysql &
7.恢复binlog数据
mysql> set sql_log_bin=0;
mysql> source /tmp/mydb.sql
mysql> set sql_log_bin=1;
8.验证数据
mysql> use mydb
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+------+
7 rows in set (0.00 sec)