表迁移工具的选型-xtrabackup的使用
亿网1.1. 场景
有的时候test人员可能需要在测试库上比较新的数据，这时候只能是从生产库上面去那了。如果是小表还好实用mysqldump/mysqlpump就可以轻松的解决。但是，如果遇到了大表这将是一个很痛苦的过程。这时候最好的选择就是使用Percona公司的MySQL热备工具xtrabackup了。
1.2. 为什么不使用ibd文件拷贝方法
很简单，因为要锁表对生产环境影响比较大。
1.3. 扩展
当然如果他们数据的要求并不是那么高可以使用每天用xtrabackup备份的来做。但是，这往往会比现场直接备份生产库的某张表来的麻烦，因为往往我们使用的是增量备份，还要应用之前的所有日志。而且为了防止破坏备份数据，还需要拷贝一份。
1.4. 先决条件
前提必须开启innodb_file_per_table选项，并且使用InnoDB存储引擎。
| 1 | set global innodb_file_per_table = 1 ; |
由于我使用的是 Percona Server 5.7.10-3 所以需要使用的xtrabackup版本为2.4.1
1.5. 制造大表
下面我们制造表数据，下面模拟的数据比较小，主要是为了节省时间。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | USE test ; — 创建表 t1 DROP TABLE IF EXISTS t1 ; CREATE TABLE t1 ( id BIGINT unsigned NOT NULL AUTO_INCREMENT , x VARCHAR ( 500 ) NOT NULL , y VARCHAR ( 500 ) NOT NULL , PRIMARY KEY ( id ) ) ; — 创建添加数据存储过程 DROP PROCEDURE insert_batch ; DELIMITER // CREATE PROCEDURE insert_batch ( ) begin DECLARE num INT ; SET num = 1 ; WHILE num < 1000000 DO IF ( num % 10000 = 0 ) THEN COMMIT ; END IF ; INSERT INTO t1 VALUES ( NULL , REPEAT ( ‘X’ , 500 ) , REPEAT ( ‘Y’ , 500 ) ) ; SET num = num + 1 ; END WHILE ; COMMIT ; END // DELIMITER ; — 添加数据 CALL insert_batch ( ) ; DROP PROCEDURE insert_batch ; |
查看数据大小情况（磁盘上的数据大小）
| 1 2 3 | ls – lh / u02 / data / test / t1 . * – rw – r — — – 1 mysql mysql 8.5K Mar 10 13 : 54 / u02 / data / test / t1 . frm – rw – r — — – 1 mysql mysql 1.2G Mar 10 14 : 20 / u02 / data / test / t1 . ibd |
查看真实的大小情况
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | select count ( * ) from t1 ; + — — — — — + | count ( * ) | + — — — — — + | 999999 | + — — — — — + SELECT table_name , data_length / 1024 / 1024 AS ‘data_length(MB)’ , index_length / 1024 / 1024 AS ‘index_length(MB)’ , ( data_length + index_length ) / 1024 / 1024 AS ‘total(MB)’ FROM information_schema . tables WHERE table_schema = ‘test’ AND table_name = ‘t1’ ; + — — — — — — + — — — — — — — — – + — — — — — — — — — + — — — — — — — – + | table_name | data_length ( MB ) | index_length ( MB ) | total ( MB ) | + — — — — — — + — — — — — — — — – + — — — — — — — — — + — — — — — — — – + | t1 | 1048.00000000 | 0.00000000 | 1048.00000000 | + — — — — — — + — — — — — — — — – + — — — — — — — — — + — — — — — — — – + |
从上面可以看出在磁盘上的数据大小是1.2G，而实际的大小才1048MB(估计值)，实际情况会比上面的数据大很多。
1.6. 表迁移演示
1、使用xtrabackup备份test.t1表数据
| 1 2 3 4 5 6 7 8 | mkdir – p / tmp / backup / usr / local / percona – xtrabackup / bin / innobackupex \ — defaults – file = / etc / my . cnf \ — user = root \ — password = root \ — socket = / u02 / tmp / mysql . sock \ — include = ‘test.t1’ \ / tmp / backup |
2、查看备份集
| 1 2 3 4 5 6 7 8 9 10 11 12 | ll / tmp / backup drwxr – x — – 3 root root 4096 Mar 10 15 : 43 2016 – 03 – 10_15 – 43 – 35 ll / tmp / backup / 2016 – 03 – 10_15 – 43 – 35 total 1048600 – rw – r — — – 1 root root 412 Mar 10 15 : 43 backup – my . cnf – rw – r — — – 1 root root 453 Mar 10 15 : 43 ib_buffer_pool – rw – r — — – 1 root root 1073741824 Mar 10 15 : 43 ibdata1 drwxr – x — – 2 root root 32 Mar 10 15 : 43 test – rw – r — — – 1 root root 26 Mar 10 15 : 43 xtrabackup_binlog_info – rw – r — — – 1 root root 121 Mar 10 15 : 43 xtrabackup_checkpoints – rw – r — — – 1 root root 553 Mar 10 15 : 43 xtrabackup_info – rw – r — — – 1 root root 2560 Mar 10 15 : 43 xtrabackup_logfile |
3、应用日志并导出元数据
| 1 2 3 4 5 6 7 8 9 10 | / usr / local / percona – xtrabackup / bin / innobackupex \ — apply – log \ — export \ / tmp / backup / 2016 – 03 – 10_15 – 43 – 35 ll / tmp / backup / 2016 – 03 – 10_15 – 43 – 35 / test – rw – r — r — 1 root root 433 Mar 10 16 : 21 t1 . cfg – rw – r — — – 1 root root 16384 Mar 10 16 : 21 t1 . exp – rw – r — — – 1 root root 8604 Mar 10 15 : 43 t1 . frm – rw – r — — – 1 root root 1195376640 Mar 10 15 : 43 t1 . ibd |
4、test2库中创建和test.t1相同的表结构
| 1 2 3 | CREATE DATABASE test2 ; USE test2 ; CREATE TABLE t1 LIKE test . t1 ; |
5、废弃test2.t1表空间，等待新表空间导入
| 1 2 | USE test2 ; ALTER TABLE t1 DISCARD TABLESPACE ; |
6、将test.t1表*.ibd和*.cfg文件拷贝到test2库中
| 1 2 3 4 5 6 7 8 9 | cp / tmp / backup / 2016 – 03 – 10_15 – 43 – 35 / test / t1 . cfg / u02 / data / test2 / cp / tmp / backup / 2016 – 03 – 10_15 – 43 – 35 / test / t1 . ibd / u02 / data / test2 / cp / tmp / backup / 2016 – 03 – 10_15 – 43 – 35 / test / t1 . exp / u02 / data / test2 / ll – h / u02 / data / test2 / – rw – r — r — 1 root root 433 Mar 10 16 : 35 t1 . cfg – rw – r — — – 1 root root 16K Mar 10 16 : 35 t1 . exp – rw – r — — – 1 mysql mysql 8.5K Mar 10 14 : 33 t1 . frm – rw – r — — – 1 root root 1.2G Mar 10 16 : 35 t1 . ibd |
7、test2导入t1数据
| 1 2 3 | chown – R mysql : mysql / u02 / data / test2 USE test2 ; ALTER TABLE t1 IMPORT TABLESPACE ; |
8、查看test2.t1数据
| 1 2 3 4 5 6 7 | USE test2 ; SELECT COUNT ( * ) FROM t1 ; + — — — — — + | COUNT ( * ) | + — — — — — + | 999999 | + — — — — — + |
1.7. 总结
xtrabackup这种表迁移算是比较复杂的了，但是他对数据库的影响是比较小的。虽然说影响小，但是还是会有影响的。所以迁移还是避免高峰时期比较妥当。
文章转载来自：trustauth.cn