mysqldump和MySQL传输表空间方式归档表时间比较

2022-12-01 15:38:40

一、环境

2台阿里ECS 8c32g 500g SSD云盘. 系统为7.6.1810 X86_64位最小化安装
2台测试机器内网ip和主机名如下:
tidb06 172.16.0.247
tidb05 172.16.0.246
线上MySQL版本为5.7.22
2台阿里ECS上MySQL实例的版本为mysql5.7.22
3台MySQL实例表引擎都是innodb,而且都开器 innodb_file_per_table = 1 独立表空间

二、采用mysqldump方式

业务低峰期利用mysqldump方式备份线上大表t_assets_device_detail_20210522

[root@db-assets-pool backup_wjw]# time mysqldump   -uroot -h 127.0.0.1 -prt345  --single-transaction db_assets_pool t_assets_device_detail_20210522 --master-data=2 --skip-tz-utc > t_assets_device_detail_20210522.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
real    36m2.612s
user    23m3.842s
sys     4m2.889s

[root@db-assets-pool backup_wjw]# du -sh t_assets_device_detail_20210522.sql
114G    t_assets_device_detail_20210522.sql

[root@db-assets-pool db_assets_pool]# time scp t_assets_device_detail_20210522.sql root@172.16.0.247:/data1/pump
root@172.16.0.246's password: 
t_assets_device_detail_20210522.ibd                                                                                                                       100%  140GB 119.5MB/s   20:01    

real    15m38.756s
user    12m22.409s
sys 9m5.177s

[root@tidb06 pump]#  time mysql test003 < t_assets_device_detail_20210522.sql
real    412m36.992s
user    24m9.813s
sys     1m32.963s

root@tidb06 11:00:  [test003]> select count(*) from t_assets_device_detail_20210522;
+-----------+
| count(*)  |
+-----------+
| 225999783 |
+-----------+
1 row in set (53.83 sec)

mysqldump方式整体耗时:463分钟完成。

三、MySQL传输表空间的方式

导出表建表语句:
线上生产库导出表结构

mysqldump -uroot -p -d test003 t_assets_device_detail_20210522 >1.sql

导入建表sql到tidb05测试库上:
root@tidb05 11:30: [test003]>soure /root/1.sql

关闭掉刚才新建表的表空间:

root@tidb05 11:30:  [test003]> ALTER TABLE test003.t_assets_device_detail_20210522 DISCARD TABLESPACE;

传输线上的表t_assets_device_detail_20210522.ibd 表空间文件到tidb06服务器目录/data1/mysql/data/test003/ 下：
tps:传输时，线上的表t_assets_device_detail_20210522已经是备份表了，没有任何数据写入的。

[root@db-assets-pool db_pool]# time scp t_assets_device_detail_20210522.ibd root@172.16.0.246:/data1/mysql/data/test003/
root@172.16.0.246's password: 
Permission denied, please try again.
root@172.16.0.246's password: 
t_assets_device_detail_20210522.ibd                                                                                                                       100%  140GB 119.5MB/s   20:01    

real    20m38.756s
user    12m22.409s
sys 9m5.177s

给表空间文件授权MySQL权限：

[root@tidb05 test003]# chown mysql.mysql /data1/mysql/data/test003/t_assets_device_detail_20210522.ibd
[root@tidb05 test003]# du -sh /data1/mysql/data/test003/t_assets_device_detail_20210522.ibd 
141G    /data1/mysql/data/test003/t_assets_device_detail_20210522.ibd

再次验证一开始新建表的表空间是否已经关闭：

root@tidb05 17:14:  [(none)]> ALTER TABLE test003.t_assets_device_detail_20210522 DISCARD TABLESPACE;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

是已经关闭的：

root@tidb05 17:14:  [(none)]> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1814 | InnoDB: Tablespace has been discarded for table 't_assets_device_detail_20210522' |
| Warning | 1812 | InnoDB: Tablespace is missing for table test003/t_assets_device_detail_20210522.  |
+---------+------+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

导入t_assets_device_detail_20210522表的表空间:

[root@tidb05 test003]# time mysql -e "ALTER TABLE test003.t_assets_device_detail_20210522 import TABLESPACE"
real    39m5.374s
user    0m0.002s
sys     0m0.004s

查看此时MySQL得错误日志提示已经导入完成:

cat /data1/mysql/logs/error.log
2021-05-23T00:12:17.947853+08:00 42911 [Note] InnoDB: Sync to disk
2021-05-23T00:12:22.524317+08:00 42911 [Note] InnoDB: Sync to disk - done!
2021-05-23T00:12:22.524433+08:00 42911 [Note] InnoDB: Phase I - Update all pages
2021-05-23T00:31:50.562602+08:00 42911 [Note] InnoDB: Sync to disk
2021-05-23T00:31:55.174899+08:00 42911 [Note] InnoDB: Sync to disk - done!
2021-05-23T00:31:55.784471+08:00 42911 [Note] InnoDB: Phase III - Flush changes to disk
2021-05-23T00:31:55.797833+08:00 42911 [Note] InnoDB: Phase IV - Flush complete
2021-05-23T00:31:55.798195+08:00 42911 [Note] InnoDB: `test003`.`t_assets_device_detail_20210522` autoinc value set to 0
2021-05-23T00:31:55.814213+08:00 42911 [Note] InnoDB: AUTOINC next value generation is disabled for '`test003`.`t_assets_device_detail_20210522`'

验证:

root@tidb05 01:07:  [test003]> select count(*) from t_assets_device_detail_20210522;
+-----------+
| count(*)  |
+-----------+
| 225999783 |
+-----------+
1 row in set (54.19 sec)

总体耗时是59分钟. 比MySQLdump方式缩短了7个小时的时间。再一次验证了利用表MySQL表空间方式复制表数据还是相当的快的。
此次演示利用的是阿里的SSD云盘。

因为mysqldump方式是逻辑备份方式，需要备份文件中的sql一条条的进行顺序写入表。一定程度上可以说对磁盘的IO性能依赖性不大。这种方式虽然安全，但是时间上不高效。
然而MySQL的表空间传输方式利用了物理复制文件的方式。这个更加依赖于底层磁盘的IO读写性能。如果采用的是阿里云的高I/O型本地盘的话，速度会更加的快。更能体现出MySQL表空间传输功能的便捷性。

四、遇到的问题

在执行ALTER TABLE test003.t_assets_device_detail_20210522 import TABLESPACE命令是由于没有采用后台方式运行。导致shell会话窗口字段断开。导致执行的命令中断失败
。再次登录服务器尝试操作了几次，一直报错如下：

[root@tidb05 logs]# time mysql -e "ALTER TABLE test003.t_assets_device_detail_20210522 import TABLESPACE"

ERROR 1815 (HY000) at line 1: Internal error: Cannot reset LSNs in table `test003`.`t_assets_device_detail_20210522` : Data structure corruption

real    27m31.130s
user    0m0.002s
sys     0m0.004s

[ERROR] Got error 155 when reading table './test003/t_assets_device_detail_20210522'
[ERROR] InnoDB: Cannot delete tablespace 283 because it is not found in the tablespace memory cache.
[Warning] InnoDB: Cannot delete tablespace 283 in DISCARD TABLESPACE: Tablespace not found

只能是删除掉tidb05 服务器上的t_assets_device_detail_20210522.ibd文件，重新把线上的t_assets_device_detail_20210522.ibd再传输一份到tidb005服务器上，重新导入表空间。

码农公寓

一、环境

二、采用mysqldump方式

三、MySQL传输表空间的方式

四、遇到的问题

相关文章