随着业务发展, 混合部署在同一个实例的不同业务库需要拆分 …

MySQL数据库拆分操作步骤

背景:

随着业务发展, 混合部署在同一个实例的不同业务库需要拆分, 假设是user_db和 order_db, 需要迁移的库为 order_db.

实例环境:

开启gtid, 开启半同步复制 现有数据库主从关系:

拆分后数据库主从关系:

原则:

1. 不能出现有两个地方同时写入同一个数据库.
2. 不能丢失数据. 如果回滚, 业务切换过程中产生的数据要重新写入原先的主库.

一 准备阶段

1. 安装新的数据库, 执行mysql_auto_install.sh不会执行任何事务(不会初始化用户). 在192.168.1.17和192.168.2.17上分别执行

   bash mysql_auto_install.sh -p 3306
   

   1. 从192.168.2.16 上用mydumper导出整个数据库,同步到192.168.1.17, 192.168.2.17. 之所以导出全部数据库 是为了同时导出mysql库, 可以不用再次进行授权

   mydumper -u xxx -p xxx -S /path/to/mysql.sock -t 8 -o /tmp/backup -c
   rsync -avzP -e ssh /tmp/backup root@192.168.1.17:/tmp
   rsync -avzP -e ssh /tmp/backup root@192.168.2.17:/tmp
   

2. 在192.168.1.17和192.168.2.17上进行myloader导入(不记录binlog),导入完成之后在 ,根据mydumper导出的metedata文件设置gtid_purged, 并执行 flush privileges. tips: 在导入时可以调整 innodb_flush_log_at_trx_commit=0, slow_query_log=0 加快导入速度, 在导入完成之后恢复

   myloader -u root -S /path/to/mysql.sock -t 8 -o -d /tmp/backup
   mysql> set global gtid_purged='5696461f-3bc5-11e8-8529-00163e0261b0:xxx':
   mysql> flush privileges;
   

3. 在 192.168.1.17:3306(新maser)上执行change master

   mysql> change master to 
   -> MASTER_HOST='192.168.1.16',
   -> master_user='xxx',
   -> master_password='yyy',
   -> MASTER_PORT = 3306,
   -> master_auto_position=1;
   

4. 在 192.168.2.17:3306(新slave)上执行change master

   mysql> change master to 
   -> MASTER_HOST='192.168.1.17',
   -> master_user='xxx',
   -> master_password='yyy',
   -> MASTER_PORT = 3306,
   -> master_auto_position=1;
   

二 切换操作阶段

1. 业务需要连到新的主库 192.168.1.17:3306. 此时 192.168.1.17:3306 只读, 192.168.1.16:3306可写

2. 在与研发确定的切换时间, 在 192.168.1.16:3306和 192.168.2.16:3306执行rename table操作, 确保没有数据可以写入

   mysql>
   -> select concat( "rename table order_db.",table_name,' to order_db.',table_name,'_rename;'   ) from information_schema.tables where table_schema='order_db'; # 生成rename sql
   -> select concat( "rename table order_db.",table_name,'_rename',' to order_db.',table_name,';') from information_schema.tables where table_schema='order_db'; # 生成回滚rename sql
   -> rename table order_db.order_db to order_db.order_db_rename; #执行rename 操作
   

3. 在192.168.1.17:3306(新maser)上执行 stop slave 操作,使用 第2步 生成的sql将表rename回来,并关掉read_only, 使业务可写. 此时 192.168.1.17:3306, 192.168.1.16:3306同时可写, 但是192.168.1.16:3306上 order_db中的表已经rename, 所以无法写入成功, 可以保证只有一个实例可以写入 order_db数据

   mysql> stop slave;
   mysql> rename table order_db.test_rename to order_db.test; #执行rename 操作
   mysql> set global read_only=0;
   

此时需要验证业务是否正常, 如果业务正常, 进行收尾阶段; 如果业务异常, 需要回滚, 按下列步骤进行回滚

1. 业务需要连到原来的主库 192.168.1.16:3306

2. 在192.168.1.17:3306上开启只读

   mysql> set global read_only=1;
   

3. 在原来的主库192.168.1.16:3306上, 将order_db的所有可写用户锁定, 并kill掉order_db所有的连接, 确保下一步change master之后, 主从同步完成之前没有连接可以写入数据

   mysql> alter user 'xxx_user'@'192.168.%' account lock;
   mysql> kill pid;
   

4. 在 192.168.1.16:3306上, 执行change master, 从192.168.1.17:3306(新主库)同步变更到192.168.1.16:3306(老主库)

   mysql> change master to 
   -> MASTER_HOST='192.168.1.17',
   -> master_user='xxx',
   -> master_password='yyy',
   -> MASTER_PORT = 3306,
   -> master_auto_position=1;    
   

5. 同步完成后, 在 192.168.1.16:3306 上stop slave, 并reset slave; 然后解锁用户

   mysql> stop slave;
   mysql> reset slave all;
   mysql> alter user 'xxx_user'@'192.168.%' account unlock;
   

6. 在192.168.1.17:3306上重新开启slave, 重新同步数据, 方便下次操作切换

   mysql> start slave;
   

此时回滚完成, 并且没有丢失切换过程中产生的数据, 需要再次验证业务

三 收尾工作

业务切换之后, 测试ok, 需要进行收尾工作

1. 在新主库 192.168.1.17:3306 上执行 reset slave all;

   mysql> reset slave all;
   

2. 在老主库和从库上 192.168.1.16:3306,192.168.2.16:3306 删除已经迁移的库, 谨慎操作

   mysql> set sql_log_bin = off;
   mysql> drop database order_db ;
   

3. 在新主库和从库 192.168.1.17:3306,192.168.2.17:3306 上将 不需要的数据库删除, 谨慎操作

   mysql> set sql_log_bin = off;
   mysql> drop database user_db ;
   

四 总结

1. 数据库拆分对比数据库迁移的难度在于, 在切换过程操作中, 原主库需要保持一直可写. 而在数据库迁移的过程中, 为确保数据一致性, 可以将原主库置为只读.
2. 在数据库拆分收尾阶段需要drop database.drop database是没法回滚的. 建议平时做好冷备, 并且谨慎操作. 否则就要准备删库跑路了