Mysql手动全量增量迁移实战_历史数据迁移怎么迁移增量数据

如果可以接受一定时间的停写，可以使用mysqldump+binlog的方式迁移数据

规划

8002库迁移全量加增量到8001库，增量迁移时8002库只读保证数据一致。

1 流程

（1）准备基础数据

sysbench oltp_common --mysql-host=127.0.0.1 --mysql-port=8002 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql  --tables=8 --table-size=100000 --report-interval=1 --threads=8 prepare
1

结果

Database changed
MySQL [server_234_db]> show tables;
+-------------------------+
| Tables_in_server_234_db |
+-------------------------+
| sbtest1                 |
| sbtest2                 |
| sbtest3                 |
| sbtest4                 |
| sbtest5                 |
| sbtest6                 |
| sbtest7                 |
| sbtest8                 |
+-------------------------+
8 rows in set (0.01 sec)

MySQL [server_234_db]> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

（2）8002库模拟业务流量，并全量迁出

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=8002 --mysql-user=server_234 --mysql-password=server_234 --mysql-db=server_234_db --db-driver=mysql  --tables=8 --table-size=100000 --rand-type=uniform --report-interval=1 --threads=1 --time=120000000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Report intermediate results every 1 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 1s ] thds: 1 tps: 186.67 qps: 3752.40 (r/w/o: 2627.38/750.68/374.34) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1 tps: 182.06 qps: 3633.29 (r/w/o: 2544.90/724.26/364.13) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1 tps: 178.01 qps: 3564.10 (r/w/o: 2496.07/712.02/356.01) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1 tps: 181.99 qps: 3643.86 (r/w/o: 2547.90/731.97/363.99) lat (ms,95%): 5.67 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1 tps: 176.01 qps: 3520.11 (r/w/o: 2464.08/704.02/352.01) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1 tps: 178.99 qps: 3563.85 (r/w/o: 2493.89/711.97/357.98) lat (ms,95%): 5.88 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1 tps: 173.01 qps: 3476.10 (r/w/o: 2434.07/696.02/346.01) lat (ms,95%): 6.32 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1 tps: 174.99 qps: 3499.89 (r/w/o: 2449.93/699.98/349.99) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1 tps: 173.00 qps: 3459.99 (r/w/o: 2421.99/692.00/346.00) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1 tps: 173.00 qps: 3460.04 (r/w/o: 2422.03/692.01/346.00) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

全量迁移出

mysqldump -h 127.0.0.1 -P8002 -userver_234 -pserver_234 --single-transaction --master-data=2 --flush-logs  server_234_db > server_234_db.sql

...
...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=194;
...
...
1
2
3
4
5
6
7

切换了binlog

（3）全量迁移结束，8002库继续写入一段时间，8001库全量迁入

mysql -uroot -proot -P8001 -h127.0.0.1 server_234_db < server_234_db.sql

（4）8002库只读，开始迁移binlog

flush tables with read lock;
set global read_only=1;
1
2

业务停写，确认binlog无增长

主库全量迁移位点：mysql-bin.000005 MASTER_LOG_POS=194

因为加了flush-logs，所以mysqldump总会切一个binlog在迁：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200617  9:50:52 server id 6700002  end_log_pos 123 CRC32 0xe4f0c734 	Start: binlog v 4, server v 5.7.30-log created 200617  9:50:52
BINLOG '
fOfpXg/iO2YAdwAAAHsAAAAAAAQANS43LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATTH8OQ=
'/*!*/;
# at 123
#200617  9:50:52 server id 6700002  end_log_pos 194 CRC32 0x27160219 	Previous-GTIDs
# 20f5cc1e-b07b-11ea-9d7d-0242ac110006:1-63446
# at 194
#200617  9:50:52 server id 6700002  end_log_pos 259 CRC32 0x3ca23e0f 	GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '20f5cc1e-b07b-11ea-9d7d-0242ac110006:63447'/*!*/;
# at 259
#200617  9:50:52 server id 6700002  end_log_pos 340 CRC32 0x1c0d3beb 	Query	thread_id=36	exec_time=0	error_code=0
SET TIMESTAMP=1592387452/*!*/;
SET @@session.pseudo_thread_id=36/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 340
#200617  9:50:52 server id 6700002  end_log_pos 406 CRC32 0xb65247f7 	Table_map: `server_234_db`.`sbtest5` mapped to number 151
# at 406
#200617  9:50:52 server id 6700002  end_log_pos 820 CRC32 0xb3280631 	Update_rows: table id 151 flags: STMT_END_F
BINLOG '
fOfpXhPiO2YAQgAAAJYBAAAAAJcAAAAAAAEADXNlcnZlcl8yMzRfZGIAB3NidGVzdDUABAMD/v4E
/nj+PAD3R1K2
fOfpXh/iO2YAngEAADQDAAAAAJcAAAAAAAEAAgAE///wNlUAAB3FAAB3Mzg4NzMyODQ5MDYtMDgw
MjA0MDA0ODAtNDE2MjgxMDEyNDAtODg5NjAyMDAzOTMtNjA4MTU4MzY3NTAtNTc5ODc4NzA2NTIt
NjY4ODc5MjM4MTYtODY5MDIzMTExNjItOTQwMDAyNTI5MTUtNzQ1MzczMzk2Mzg7NjI0NDQxMDY4
NzEtMTYwNDgxMDcwOTAtNTY0ODM4OTc1MjEtNzExNTA1NTY3NTUtMjU2MjkwMzE1NTjwNlUAAB7F
AAB3Mzg4NzMyODQ5MDYtMDgwMjA0MDA0ODAtNDE2MjgxMDEyNDAtODg5NjAyMDAzOTMtNjA4MTU4
MzY3NTAtNTc5ODc4NzA2NTItNjY4ODc5MjM4MTYtODY5MDIzMTExNjItOTQwMDAyNTI5MTUtNzQ1
MzczMzk2Mzg7NjI0NDQxMDY4NzEtMTYwNDgxMDcwOTAtNTY0ODM4OTc1MjEtNzExNTA1NTY3NTUt
MjU2MjkwMzE1NTgxBiiz
'/*!*/;
### UPDATE `server_234_db`.`sbtest5`
### WHERE
###   @1=21814
###   @2=50461
###   @3='38873284906-08020400480-41628101240-88960200393-60815836750-57987870652-66887923816-86902311162-94000252915-74537339638'
###   @4='62444106871-16048107090-56483897521-71150556755-25629031558'
### SET
###   @1=21814
###   @2=50462
###   @3='38873284906-08020400480-41628101240-88960200393-60815836750-57987870652-66887923816-86902311162-94000252915-74537339638'
###   @4='62444106871-16048107090-56483897521-71150556755-25629031558'
# at 820
#200617  9:50:52 server id 6700002  end_log_pos 886 CRC32 0x10fed04e 	Table_map: `server_234_db`.`sbtest2` mapped to number 152
# at 886
#200617  9:50:52 server id 6700002  end_log_pos 1300 CRC32 0x56cc558e 	Update_rows: table id 152 flags: STMT_END_F

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
51
52
53
54
55
56
57
58
59
60
61
62

194后面可以看到有一次更新

### UPDATE `server_234_db`.`sbtest5`
### WHERE
###   @1=21814
###   @2=50461
###   @3='38873284906-08020400480-41628101240-88960200393-60815836750-57987870652-66887923816-86902311162-94000252915-74537339638'
###   @4='62444106871-16048107090-56483897521-71150556755-25629031558'
### SET
###   @1=21814
###   @2=50462
###   @3='38873284906-08020400480-41628101240-88960200393-60815836750-57987870652-66887923816-86902311162-94000252915-74537339638'
###   @4='62444106871-16048107090-56483897521-71150556755-25629031558'
# at 820
#200617  9:50:52 server id 6700002  end_log_pos 886 CRC32 0x10fed04e 	Table_map: `server_234_db`.`sbtest2` mapped to number 152
# at 886
#200617  9:50:52 server id 6700002  end_log_pos 1300 CRC32 0x56cc558e 	Update_rows: table id 152 flags: STMT_END_F
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

8002有这条数据

8001无这条数据

导出数据
mysqlbinlog mysql-bin.000005 --start-position=194 > b5.sql
mysqlbinlog mysql-bin.000006 --start-position=4 > b6.sql

导入新库
mysql -uroot -proot -P8001 -h127.0.0.1 server_234_db < b5.sql
mysql -uroot -proot -P8001 -h127.0.0.1 server_234_db < b6.sql

验证8001存在数据

2 命令

恢复语法格式

# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
 常用选项：
  --start-position=953                   起始pos点
  --stop-position=1437                   结束pos点
  --start-datetime="2013-11-29 13:18:54" 起始时间点
  --stop-datetime="2013-11-29 13:21:53"  结束时间点
  --database=zyyshop                     指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
    
1
2
3
4
5
6
7
8

在mysql中查看binlog日志

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

 选项解析：
   IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)
   FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
   LIMIT [offset,] 偏移量(不指定就是0)
   row_count       查询总条数(不指定就是所有行)

 截取部分查询结果：
 *************************** 20. row ***************************
    Log_name: mysql-bin.000021  ----------------------------------------------> 查询的binlog日志文件名
         Pos: 11197 ----------------------------------------------------------> pos起始点:
  Event_type: Query ----------------------------------------------------------> 事件类型：Query
   Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
 End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)
        Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句
 *************************** 21. row ***************************
    Log_name: mysql-bin.000021
         Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)
  Event_type: Query
   Server_id: 1
 End_log_pos: 11417
        Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
 *************************** 22. row ***************************
    Log_name: mysql-bin.000021
         Pos: 11417
  Event_type: Query
   Server_id: 1
 End_log_pos: 11510
        Info: use `zyyshop`; DROP TABLE IF EXISTS `type`
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