devbox
你好赵伟
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Mysql手动全量增量迁移实战_mysql-5.7.37全量迁移

作者：你好赵伟 | 2024-07-11 10:07:37

赞

踩

mysql-5.7.37全量迁移

如果可以接受一定时间的停写，可以使用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无增长
确认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
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/809620
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号