MySQL通过 XtraBackup 备份恢复单个库

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

1.概述

本文通过 XtraBackup 备份单个数据库，然后恢复到另一个实例，用于快速迁移大数据量，使用的软件版本为：

软件名	版本
MySQL	mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz
XtraBackup	percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm

在2台主机分别安装一个MySQL实例：

主机	IP地址	端口号
MySQL-A	192.168.100.10	5508
MySQL-B	192.168.100.11	5508

2.在MySQL-A初始化数据

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

 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 库

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

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

 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
mysqldump: [Warning] Using a password on the command line interface can be insecure.

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

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

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

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

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

 mysql> source /tmp/discard.sql;

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

 shell> ll -h /mysql/dbdata/data5508/data/sysbench/
total 124K
-rw-r----- 1 mysql mysql   67 Dec 29 10:25 db.opt
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest10.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest1.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest2.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest3.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest4.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest5.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest6.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest7.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest8.frm
-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest9.frm

可以看到ibd文件已被丢弃

9.prepare 备份文件

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

 shell> innobackupex --apply-log --export /mysql/dbbackup/2022-12-29_10-11-07
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 
xtrabackup: recognized client arguments: 
221229 10:56:58 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!".
...
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1123519528
221229 10:57:13 completed OK!

10.查看备份文件目录

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

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

11.传输备份文件

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

 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.修改传输过来的文件的属主属组

 shell> chown -R mysql.mysql /mysql

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

 mysql> source /tmp/import.sql;

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

 mysql> select count(*) from sysbench.sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

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

 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
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
...
...
SQL statistics:
    queries performed:
        read:                            1702400
        write:                           486400
        other:                           243200
        total:                           2432000
    transactions:                        121600 (405.25 per sec.)
    queries:                             2432000 (8105.04 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
Throughput:
    events/s (eps):                      405.2522
    time elapsed:                        300.0601s
    total number of events:              121600
 
Latency (ms):
         min:                                    9.04
         avg:                                  123.36
         max:                                 1512.19
         95th percentile:                      590.56
         sum:                             15000942.35
 
Threads fairness:
    events (avg/stddev):           2432.0000/58.48
    execution time (avg/stddev):   300.0188/0.01

压测正常

16.重启MySQL-B并查询一张表

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

总结

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

Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交流群：

微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。

posted @ 2023-01-11 17:15 GreatSQL 阅读(459) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 技术分享 | 测试git上2500星的闪回小工具

· 技术分享 | MySQL数据误删除的总结

· 解密MySQL备份恢复的4种方法

· MySQL备份恢复xtrabackup

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~

历史上的今天：
2022-01-11 解析MySQL存储过程的游标执行过程
2022-01-11 在docker中出现的僵尸进程怎么处理

公告

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接：
GreatSQL社区 Gitee GitHub Bilibili

技术交流群：

> 微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。

昵称： GreatSQL
园龄： 3年4个月
粉丝： 11
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

GreatSQL

MySQL通过 XtraBackup 备份恢复单个库

1.概述

2.在MySQL-A初始化数据

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

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

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

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

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

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

9.prepare 备份文件

10.查看备份文件目录

11.传输备份文件

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

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

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

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

16.重启MySQL-B并查询一张表

总结

关于 GreatSQL

GreatSQL社区：

技术交流群：

公告

关于 GreatSQL

技术交流群：

搜索

常用链接

我的标签

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论

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

	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
	mysqldump: [Warning] Using a password on the command line interface can be insecure.

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

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

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

	shell> ll -h /mysql/dbdata/data5508/data/sysbench/
	total 124K
	-rw-r----- 1 mysql mysql 67 Dec 29 10:25 db.opt
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest10.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest1.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:25 sbtest2.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest3.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest4.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest5.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest6.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest7.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest8.frm
	-rw-r----- 1 mysql mysql 8.5K Dec 29 10:26 sbtest9.frm

	shell> innobackupex --apply-log --export /mysql/dbbackup/2022-12-29_10-11-07
	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
	xtrabackup: recognized client arguments:
	221229 10:56:58 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!".
	...
	...
	xtrabackup: starting shutdown with innodb_fast_shutdown = 0
	InnoDB: FTS optimize thread exiting.
	InnoDB: Starting shutdown...
	InnoDB: Shutdown completed; log sequence number 1123519528
	221229 10:57:13 completed OK!

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

	mysql> select count(*) from sysbench.sbtest1;
	+----------+
	\| count(*) \|
	+----------+
	\| 10000 \|
	+----------+
	1 row in set (0.01 sec)

	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
	sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
	...
	...
	SQL statistics:
	queries performed:
	read: 1702400
	write: 486400
	other: 243200
	total: 2432000
	transactions: 121600 (405.25 per sec.)
	queries: 2432000 (8105.04 per sec.)
	ignored errors: 0 (0.00 per sec.)
	reconnects: 0 (0.00 per sec.)

	Throughput:
	events/s (eps): 405.2522
	time elapsed: 300.0601s
	total number of events: 121600

	Latency (ms):
	min: 9.04
	avg: 123.36
	max: 1512.19
	95th percentile: 590.56
	sum: 15000942.35

	Threads fairness:
	events (avg/stddev): 2432.0000/58.48
	execution time (avg/stddev): 300.0188/0.01

	mysql> shutdown;
	shell> /mysql/svr/mysql/bin/mysqld_safe --defaults-file=/mysql/conf/my5508.cnf --user=mysql &

	mysql> select count(*) from sysbench.sbtest2;
	+----------+
	\| count(*) \|
	+----------+
	\| 10000 \|
	+----------+
	1 row in set (0.04 sec)