1.1. 场景
有两种场景适合使用逻辑迁移：伪大表、跨产品迁移(MySQL 到 MSSQL)。
下面我们模拟伪大表的进行表迁移的情况。
1.2. 使用工具
mysqlpump
1.3. 为什么选mysqlpump
没有什么别的原因就是因为图新，mysqlpump这个工具是在MySQL5.7才才出来的，在备份库的时候能做到多线程备份。其实，备份单表我更加推荐使用mydumper。当是个人认为mysqlpump以后也会有很多人选择的。就先Oralce的dump代替exp一样。
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. 总结
很明显在上面的情况如果使用物理迁移那将会需要比较长的时间。当然如果你遇到了伪大表的情况，这时候就应该向老大申请时间，重新创建这张表的数据了。在实际工作当中增删改平凡的表就容易出现这样的情况。
文章转载来自：trustauth.cn
领取优惠
提交成功！