MySQL通过 XtraBackup 备份恢复单个库

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
  
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
  
• 作者：王权富贵
  
• 文章来源：GreatSQL社区原创
  

1.概述

本文通过 XtraBackup 备份单个数据库，然后恢复到另一个实例，用于快速迁移大数据量，使用的软件版本为：
软件名版本MySQLmysql-5.7.39-linux-glibc2.12-x86_64.tar.gzXtraBackuppercona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm在2台主机分别安装一个MySQL实例：
主机IP地址端口号MySQL-A192.168.100.105508MySQL-B192.168.100.1155082.在MySQL-A初始化数据

通过sysbench 初始化数据，10张表，每张表10000条记录

1. shell> sysbench src/lua/oltp_read_write.lua --mysql-db=sysbench --mysql-host=192.168.100.10 --mysql-port=5508 --mysql-user=greatsql --mysql-password='greatsql' --tables=10 --table_size=10000 --report-interval=2 --threads=50 --time=1000  --mysql_storage_engine=innodb --partitions=3 --skip_trx=0 --mysql-ignore-errors=all prepare

复制代码

3.通过 xtrabackup 备份 MySQL-A 的 sysbench 库

1. shell> innobackupex --defaults-file=/mysql/conf/my5508.cnf  --user greatsql --password greatsql -H127.0.0.1 -P5508  --databases sysbench  /mysql/dbbackup
2. ...
3. ...
4. 221229 10:11:17 Executing UNLOCK TABLES
5. 221229 10:11:17 All tables unlocked
6. 221229 10:11:17 [00] Copying ib_buffer_pool to /mysql/dbbackup/2022-12-29_10-11-07/ib_buffer_pool
7. 221229 10:11:17 [00]        ...done
8. 221229 10:11:17 Backup created in directory '/mysql/dbbackup/2022-12-29_10-11-07/'
9. MySQL binlog position: filename 'binlog.000005', position '190', GTID of the last change '3e362a47-8683-11ed-92e1-00163ed294ac:1-87'
10. 221229 10:11:17 [00] Writing /mysql/dbbackup/2022-12-29_10-11-07/backup-my.cnf
11. 221229 10:11:17 [00]        ...done
12. 221229 10:11:17 [00] Writing /mysql/dbbackup/2022-12-29_10-11-07/xtrabackup_info
13. 221229 10:11:17 [00]        ...done
14. xtrabackup: Transaction log of lsn (1123405728) to (1123405737) was copied.
15. 221229 10:11:17 completed OK!

复制代码

4.备份 MySQL-A sysbench 库的表结构

1. shell> /mysql/svr/mysql/bin/mysqldump -ugreatsql -pgreatsql -h127.0.0.1 -P5508 --single-transaction --set-gtid-purged=OFF --master-data=2  -d  -B sysbench > sysbench.sql
2. mysqldump: [Warning] Using a password on the command line interface can be insecure.

复制代码

5.将备份的 sysbench 库的表结构导入 MySQL-B

1. shell> /mysql/svr/mysql/bin/mysql -ugreatsql -pgreatsql -h192.168.100.11 -P5508 < sysbench.sql
2. mysql: [Warning] Using a password on the command line interface can be insecure.

复制代码

6.登录 MySQL-B 生成丢弃/导入表空间的 SQL 语句

1. # 生成丢弃表空间的SQL
2. mysql> select concat('alter table ',table_schema,'.',TABLE_NAME , ' discard tablespace', ';') from  information_schema.tables where TABLE_SCHEMA = 'sysbench' into outfile '/tmp/discard.sql';
3. Query OK, 10 rows affected (0.00 sec)
5. # 生成导入表空间的SQL
6. mysql> select concat('alter table ',table_schema,'.',TABLE_NAME , ' import tablespace', ';') from  information_schema.tables where TABLE_SCHEMA = 'sysbench' into outfile '/tmp/import.sql';
7. Query OK, 10 rows affected (0.01 sec)

复制代码

7.登录 MySQL-B 执行丢弃表空间的 SQL 语句

1. mysql> source /tmp/discard.sql;  

复制代码

8.查看 MySQL-B 底层数据文件

1. shell> ll -h /mysql/dbdata/data5508/data/sysbench/
2. total 124K
3. -rw-r----- 1 mysql mysql   67 Dec 29 10:25 db.opt
4. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest10.frm
5. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest1.frm
6. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest2.frm
7. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest3.frm
8. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest4.frm
9. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest5.frm
10. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest6.frm
11. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest7.frm
12. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest8.frm
13. -rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest9.frm

复制代码

可以看到ibd文件已被丢弃
9.prepare 备份文件

注意这里需要加 --export 选项，它允许导出单个表以进行导入到另一个服务器

1. shell> innobackupex --apply-log --export /mysql/dbbackup/2022-12-29_10-11-07
2. xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=4 --innodb_log_file_size=33554432 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=/mysql/dbdata/data5508/log --innodb_undo_tablespaces=0 --server-id=2 --redo-log-version=1
3. xtrabackup: recognized client arguments:
4. 221229 10:56:58 innobackupex: Starting the apply-log operation
6. IMPORTANT: Please check that the apply-log run completes successfully.
7.            At the end of a successful apply-log run innobackupex
8.            prints "completed OK!".
9. ...
10. ...
11. xtrabackup: starting shutdown with innodb_fast_shutdown = 0
12. InnoDB: FTS optimize thread exiting.
13. InnoDB: Starting shutdown...
14. InnoDB: Shutdown completed; log sequence number 1123519528
15. 221229 10:57:13 completed OK!

复制代码

10.查看备份文件目录

1. shell> ll -h /mysql/dbbackup/2022-12-29_10-11-07/sysbench/
2. total 271M
3. -rw-r----- 1 root root   67 Dec 29 10:11 db.opt
4. -rw-r----- 1 root root 8.5K Dec 29 10:11 sbtest10.frm
5. -rw-r--r-- 1 root root  578 Dec 29 10:57 sbtest10#P#p0.cfg
6. -rw-r----- 1 root root  16K Dec 29 10:57 sbtest10#P#p0.exp
7. -rw-r----- 1 root root 9.0M Dec 29 10:11 sbtest10#P#p0.ibd
8. -rw-r--r-- 1 root root  578 Dec 29 10:57 sbtest10#P#p1.cfg
9. -rw-r----- 1 root root  16K Dec 29 10:57 sbtest10#P#p1.exp
10. -rw-r----- 1 root root 9.0M Dec 29 10:11 sbtest10#P#p1.ibd
11. -rw-r--r-- 1 root root  578 Dec 29 10:57 sbtest10#P#p2.cfg
12. ...

复制代码

可以看到prepare备份文件后，多了 cfg,exp结尾的文件
11.传输备份文件

将准备好的备份文件中后缀名为cfg，ibd，exp的文件传输到MySQL-B实例的sysbench库下

1. shell> scp -r /mysql/dbbackup/2022-12-29_10-11-07/sysbench/*.ibd /mysql/dbbackup/2022-12-29_10-11-07/sysbench/*.cfg /mysql/dbbackup/2022-12-29_10-11-07/sysbench/*.exp root@192.168.100.11:/mysql/dbdata/data5508/data/sysbench/

复制代码

12.修改传输过来的文件的属主属组

1. shell> chown -R mysql.mysql /mysql  

复制代码

13.登录 MySQL-B 执行导入表空间的 SQL 语句

1. mysql> source /tmp/import.sql;  

复制代码

14.登录 MySQL-B 检查一张表是否正常

1. mysql> select count(*) from sysbench.sbtest1;
2. +----------+
3. | count(*) |
4. +----------+
5. |    10000 |
6. +----------+
7. 1 row in set (0.01 sec)

复制代码

15.使用 sysbench 进行压测 MySQL-B

1. shell> sysbench src/lua/oltp_read_write.lua --mysql-db=sysbench --mysql-host=192.168.100.11 --mysql-port=5508 --mysql-user=greatsql --mysql-password='greatsql' --tables=10 --table_size=10000 --report-interval=2 --threads=50 --time=300  --mysql_storage_engine=innodb --partitions=3 --skip_trx=0 --mysql-ignore-errors=all run
2. sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
3. ...
4. ...
5. SQL statistics:
6.     queries performed:
7.         read:                            1702400
8.         write:                           486400
9.         other:                           243200
10.         total:                           2432000
11.     transactions:                        121600 (405.25 per sec.)
12.     queries:                             2432000 (8105.04 per sec.)
13.     ignored errors:                      0      (0.00 per sec.)
14.     reconnects:                          0      (0.00 per sec.)
16. Throughput:
17.     events/s (eps):                      405.2522
18.     time elapsed:                        300.0601s
19.     total number of events:              121600
21. Latency (ms):
22.          min:                                    9.04
23.          avg:                                  123.36
24.          max:                                 1512.19
25.          95th percentile:                      590.56
26.          sum:                             15000942.35
28. Threads fairness:
29.     events (avg/stddev):           2432.0000/58.48
30.     execution time (avg/stddev):   300.0188/0.01

复制代码

压测正常
16.重启MySQL-B并查询一张表

1. mysql> shutdown;
2. shell> /mysql/svr/mysql/bin/mysqld_safe --defaults-file=/mysql/conf/my5508.cnf --user=mysql &
4. mysql> select count(*) from sysbench.sbtest2;
5. +----------+
6. | count(*) |
7. +----------+
8. |    10000 |
9. +----------+
10. 1 row in set (0.04 sec)

复制代码

总结

通过上述方法可以快速备份恢复大数据量的库，也可以同时指定多个库，单张表或多张表进行恢复。可参考官方文档 Partial Backups - Percona XtraBackup

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！