MySQL5.6如何实现数据库之间的相互迁移

下面讲讲关于MySQL5.6如何实现数据库之间的相互迁移，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL5.6如何实现数据库之间的相互迁移这篇文章你一定会有所受益。

###业务描述

1. 公司内部一个MySQL的master-slave架构上面跑有公司俩个项目的库
2. 俩个项目的有存在共用的table的情况
3. 俩个项目都可以对共用的table进行读写操作
4. 项目A的一个子业务在每天固定的时间段内都会有高并发的写操作,造成的后果就是在这个子业务工作的时间段内,俩个项目的读写操作会变得非常的慢,并且对外表现就是应用会很卡
5. 项目A的子业务使用的库a是独立的,和另外一个项目已经项目A下面的子业务并不存在关联关系,是这个子业务独立使用的

###需求:

1. 将库a独立剥离出来
2. 将业务A的关联的数据库剥离出来
3. 俩个共用的业务的table暂时还没有方案

###方案:

1. 新建一套master-slave的主从环境使用5.6的最新版本
2. 搭建级联环境
   • old master -> old slave -> new master -> old master
3. xtrabackup物理备份
4. pt-table-checksum 数据一致性校验
5. 项目A的子业务可以停掉一段时间,因为是阶段性的应用
6. 应用停掉的一段时间内,等待独立的database没有写入.针对库级别上面的表不做锁操作
7. 应用停掉一段时间之后直接修改子业务的MySQL的配置信息,将新的数据写入new master
8. 级联组织的架构不做任何的修改,只是在业务层次做写入的改动,这样子造成的后果就是老的master-slave和新的master-slave针对这个独立的database的数据是不一致的。
9. 在这里需要考虑的问题是旧的master-slave上面会不会因为部分业务忘记修改或者其他的原因,未完全将业务的读写迁移到新的master-slave架构上面,这样会造成数据的不一致,并且在databases中万一存在自增的建的话,可能会有建值冲突的情况。
10. 在完成业务MySQL的配置更改之后,四层的级联架构保持原样的目的是为了后续还需要继续做业务剥离。

###环境:

1. os system CentOS 6.9
2. MySQL 5.6.38 最新版本

###步骤：

1. 备份：

   • 使用xtrabackup 2.4.4版本在old slave 上面进行物理备份,不在old master上面备份的主要原因在于为了避免备份的时候消耗系统资源和数据库资源影响线上业务的正常使用,因为我们线上的业务所有的读写都是在主库上面,并没有做读写分离.所以针对于old slave的操作对于业务的影响几乎可以忽略.
   • 在备份的时候需要考虑一个因素就是你的备份所放置云服务器的磁盘空间是否足够,我第一次备份的时候并没有使用流备份,就直接备份到本地,在备份传输的时候网络IO和磁盘IO这块需要花费更多的时间。

   • 没有使用流备份:备份77G,花费9分钟
     innobackupex --host=127.0.0.1 --user=root --password=xxxxx --port=3306 /path/BACKUP_DIR/

   • 使用流备份进行压缩备份:备份12G,花费40分钟
     innobackupex --host=127.0.0.1 --port=3306 --user=root --password=xxxxx --stream=tar /tmpdir | gzip > /path/BACKUP_DIR/back_file.tar.gz

   • 因为是做4层的级联复制,所以只需要备份old slave上面的binlog的file_name和file_position这俩个信息,根据这俩个信息change master创建主从复制.不过其中需要注意的文件有:
     • xtrabackup_binlog_info：这个文件里面记录是在xtrabackup备份完非innodb数据之后,执行show master status获取得到的信息,做主传统主从复制的信息来自与这里.
     • xtrabackup_info：这个文件里面记录了备份完全,释放MySQL资源之后记录一些具体的详细信息,在这里也存在备份的binlog的信息,这个信息的主要来源是从redo log里面获取得到的,当这个信息和上面那个文件信息不一致的时候,主要是以这个文件为主,主要原因在与redo log里面记录的binlog信息打上commit标签之后就表明事务是已经完成了的。
   • 需要注意的参数有:
     • --slave-info：在从库上面执行备份,获取主库上面的二进制日志信息,并且会生成xtrabackup_slave_info文件记录这些信息
     • --safe-slave-backup：和slave-info结合使用,主要是在发起备份的时候会暂停slave的sql_thread进程确保备份的时候没有临时表打开,保证数据的一致性
     • --safe-slave-backup-timeout=SECONDS：指定safe-slave-backup应该等待多长时间

2. 恢复：在新的master-slave上面备份数据达到一致的状态,其目的是为了应用redo log:

   innobackupex --defaults-file=/etc/my.cnf --apply-log /path/BACKUP_DIR

   将数据恢复到datadir目录下面:

   innobackupex --defaults-file=/etc/my.cnf --copy-back /path/BACKUP_DIR

   修改datadir的数据权限

   chown mysql.mysql /datadir -R

3. 搭建new master-slave：在搭建old slave -> new master架构之前先搭建new master-slave 架构
   • 需要注意的是,在搭建之前需要注意事项有:
     • GTID是否开启,因为 old master-slave 是基于传统复制,所以new的master-slave不能开启GTID复制
     • server-id不能一致
     • binlog的日志格式,四个必须保持一致
     • 在配置文件my.cnf 添加参数 "replicate-ingore-db=mysql.* " 的目的是为了不复制系统库mysql的信息,目的是因为后续的用户权限管理;并且5.6并不支持在线修改这些复制过滤,只能在配置文件里面修改之后重启数据库。
   • 在new master-slave备份恢复之后,直接可以在new master上面show master status获取binlog信息,根据这些信息做主从同步.
4. old slave -> new master 搭建主从：从第一步备份获取得到slave的info信息搭建主从
5. 监测主从是否搭建成功,在old slave -> new master -> new slave 执行show slave status信息观察

6. pt-table-checksum检测数据的一致性

   • 命令：
     pt-table-checksum --replicate=percona.checksumss --nocheck-replication-filters --no-check-binlog-format h=x.x.x.x,u=rpl,p='xxxxx',P=3306 --databases-regex=database.* --recursion-method dsn=h=x.x.x.x,u=root,p='xxxxx',P=3306,D=zst_teach,t=dsns

   • 注意：
     • 命令第一个链接的主机是需要监测的master-slave中master实例信息
     • 命令中的dsn后续的链接信息是dsn存在信息的MySQL实例,这个dsn信息可以存放在master,也可以存放到第三方实例,这个时候pt-table-checksum执行所在的云服务器需要有root用户访问zst_teach.dsns表信息的权限,即select的权限
     • 命令在执行的时候会在master上面产生percona.checksumss信息,其中percona库是不会手动生成的,需要自己手动生成,但是checksums会自动生成,但是其中需要注意的是,由于是在master上面生成percona.checksumss信息表,所以rpl@command_host用户需要有percona.checksums的select,insert,update,delete,super,process,lock tables,craete的权限,而且还必须有针对所有表的select,lock tables,super,process权限。
     • 并且为了在slave进行检测,检测的用户也必须要有所以表的select,super,lock tables,process的权限
     • master:
       grant update,delete,insert,super,process,lock tables,create on *.* 'user'@'command_host'
   • slave:
     grant select,process,lock tabes,super on *.* to 'user'@'master_host';

7.pt-table-sync数据同步(在master和slave都可以执行)

• pt-table-sync --print --sync-to-master h=slave_host,P=3306,u=repl,p='xxxxx' --database=DB_name --tables=table_name1,table_name2

  --print 打印出在slave执行的SQL
  --sync-to-master 指定slave的IP地址,从show slave status获取master的信息

• 对于以上MySQL5.6如何实现数据库之间的相互迁移相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。