- A+
1.1. 场景
有两种场景适合使用逻辑迁移：伪大表、跨产品迁移(MySQL 到 MSSQL)。
下面我们模拟伪大表的进行表迁移的情况。
1.2. 使用工具
mysqlpump
1.3. 为什么选mysqlpump
没有什么别的原因就是因为图新，mysqlpump这个工具是在MySQL5.7才才出来的，在备份库的时候能做到多线程备份。其实，备份单表我更加推荐使用mydumper。当是个人认为mysqlpump以后也会有很多人选择的。就先Oralce的dump代替exp一样。
官网：http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
1.4. 制造出大表假象
下面制造的数据量点大，可能要花费你一点时间，你可以更具磁盘情况来减少一些数据：
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
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 < 10000000 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(); -- 由于之前造的数据有 10多G,因此删除的时候最好要分次删除。 -- 如果一次性删除，会生成很大的undo 和redo log 这样会占用很大的临时空间。 -- 当然，也可以少创建点数据 DELETE FROM t1 WHERE id < 1000000; DELETE FROM t1 WHERE id < 2000000; DELETE FROM t1 WHERE id < 3000000; DELETE FROM t1 WHERE id < 4000000; DELETE FROM t1 WHERE id < 5000000; DELETE FROM t1 WHERE id < 6000000; DELETE FROM t1 WHERE id < 7000000; DELETE FROM t1 WHERE id < 8000000; DELETE FROM t1 WHERE id < 9000000; DELETE FROM t1 WHERE id < 10000000; INSERT INTO t1 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500)); INSERT INTO t1 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500)); INSERT INTO t1 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500)); INSERT INTO t1 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500)); INSERT INTO t1 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500)); DROP PROCEDURE insert_batch; |
查看数据大小情况（磁盘上的数据大小）
|
1 2 3 |
ls -lh /u02/data/test/t1.* -rw-r----- 1 mysql mysql 8.5K Mar 9 18:21 /u02/data/test/t1.frm -rw-r----- 1 mysql mysql 12G Mar 10 10:47 /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(*) | +----------+ | 5 | +----------+ 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 | 0.01562500 | 0.00000000 | 0.01562500 | +------------+-----------------+------------------+------------+ |
从上面可以看出在磁盘上的数据大小是12G，而实际的大小才0.01562500MB(估计值)，在种情况使用逻辑迁移是再好不过的了。
1.5. mysqlpump的使用
|
1 2 3 4 5 6 |
/usr/local/mysql/bin/mysqlpump \ -uroot \ -proot \ --exclude-databases=insert_batch \ --result-file=/tmp/t1.sql \ test t1 |
上面会生成 t1.sql 表数据文件
1.6. 将数据迁移到 test2库中
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql -uroot -proot CREATE DATABASE test2; USE test2; source /tmp/t1.sql SELECT id FROM t1; +----------+ | id | +----------+ | 10000000 | | 10000001 | | 10000002 | | 10000003 | | 10000004 | +----------+ |
1.7. 总结
很明显在上面的情况如果使用物理迁移那将会需要比较长的时间。当然如果你遇到了伪大表的情况，这时候就应该向老大申请时间，重新创建这张表的数据了。在实际工作当中增删改平凡的表就容易出现这样的情况。
昵称：HH
QQ：275258836
ttlsa群交流沟通(QQ群②：6690706 QQ群③：168085569 QQ群④：415230207（新） 微信公众号：ttlsacom)
感觉本文内容不错，读后有收获？
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
