表迁移工具的选型-mysqlpump-成长的对话

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. 制造出大表假象

下面制造的数据量点大，可能要花费你一点时间，你可以更具磁盘情况来减少一些数据：

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;

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 ) ) ;

DROP PROCEDURE insert_batch ;

查看数据大小情况（磁盘上的数据大小）

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

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

查看真实的大小情况

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 |
+------------+-----------------+------------------+------------+

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' ;

+ -- -- -- -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- +

+ -- -- -- -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- +

| t1 | 0.01562500 | 0.00000000 | 0.01562500 |

+ -- -- -- -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- +

从上面可以看出在磁盘上的数据大小是12G，而实际的大小才0.01562500MB(估计值)，在种情况使用逻辑迁移是再好不过的了。

1.5. mysqlpump的使用

/usr/local/mysql/bin/mysqlpump \
  -uroot \
  -proot \
  --exclude-databases=insert_batch \
  --result-file=/tmp/t1.sql \
  test t1

/ usr / local / mysql / bin / mysqlpump \

- uroot \

- proot \

-- exclude - databases = insert _batch \

-- result - file = / tmp / t1 . sql \

test t1

上面会生成 t1.sql 表数据文件

1.6. 将数据迁移到 test2库中

mysql -uroot -proot
CREATE DATABASE test2;
USE test2;
source /tmp/t1.sql
SELECT id FROM t1;
+----------+
| id       |
+----------+
| 10000000 |
| 10000001 |
| 10000002 |
| 10000003 |
| 10000004 |
+----------+

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)

感觉本文内容不错，读后有收获？

逛逛衣服店，鼓励作者写出更好文章。

转载请注明：成长的对话 » 表迁移工具的选型-mysqlpump

1.1. 场景

1.2. 使用工具

1.3. 为什么选mysqlpump

1.4. 制造出大表假象

1.5. mysqlpump的使用

1.6. 将数据迁移到 test2库中

1.7. 总结

与本文相关的文章