由于 centos7 停止维护，公司需要把原先运行在 centos7 上的 mysql 服务迁移到 ubuntu。运行在 centos7 上的 mysql 服务有多套，有些是单实例，有些是主从方式。评估公司数据量，实际可以替换为一套主从 mysql。即我们需要把多套运行在 centos7 上的 mysql 数据迁移到一套运行在 ubuntu 上的 mysql。

备注：下文中 {{}} 括起来的变量是我们实际执行时需要替换的变量。

迁移策略

1. 把 centos mysql 数据导出，然后导入 ubuntu mysql。
2. 建立 mysql 多源复制，master 节点是 centos mysql，slave 节点是 unbuntu mysql
3. 把 mysql 域名指向 ubuntu mysql，重启连接 mysql 的服务使其指向 ubuntu mysql。

执行前两步时不影响 centos mysql 和 连接 mysql 服务的正常执行，第三步会造成连接mysql 服务短暂不可用。

迁移数据前准备

• 确认 centos mysql 开启了二进制日志
• ubuntu mysql server_id 要与 centos mysql server_id 不同

server_id 作用: 在主从复制拓扑中，server_id 用于标记二进制日志事件的源实例。当从库发现日志中的 server_id 与自身相同时，会跳过该事件，从而避免循环复制。

• 修改或添加 ubuntu mysql 配置文件 my.cnf，并重启 ubuntu mysql。

# max_allowed_packet 参数是指mysql服务器端和客户端在一次传送数据包的过程当中接收的最大允许数据包大小
# 详见 https://louishust.gitbooks.io/understand-mysql/content/max_allowed_packet.html
max_allowed_packet = 1024m                      

# slave-skip-errors 参数用于指定在从库上遇到特定错误码时，复制线程不应停止，而是应该跳过这些错误继续执行
slave-skip-errors=1062,1032          

# replicate-ignore-db 参数作用是指定一些数据库，使得这些数据库的数据将不会被复制到从服务器
replicate-ignore-db = mysql                             
replicate-ignore-db = information_schema      
replicate-ignore-db = performance_schema    
replicate-ignore-db = sys               


master_info_repository = TABLE     # 把主服务器连接信息、复制状态记录于表 mysql.slave_master_info                     
relay_log_info_repository = TABLE  # 把中继日志信息记录于表 mysql.slave_relay_info

• 给 centos mysql 添加 dump 和主从复制用户。

# 添加 mysql_dump_user 用户用于 mysql dump
mysql -h127.0.0.1 -uroot -p"{{ root_user_password }}" -e "grant all on *.* to 'mysql_dump_user'@'%' identified by '{{ dump_user_password }}';flush privileges;"
# 添加 replica_user 用户用于主从复制
mysql -h127.0.0.1 -uroot -p"{{ root_user_password }}" -e "GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%' IDENTIFIED BY '{{ replica_user_password }}';flush privileges;"

使用 mysqldump 导出 centos mysql 数据并导入 ubuntu mysql

使用 mysqldump 可以把 centos mysql 中当前数据导出并导入到 ubuntu mysql，可以解决直接使用 mysql 多源复制遇到的问题：

• 若 centos mysql 之前没有开启二进制日志或二进制日志有删除或丢失，则不能使用 mysql 多源复制同步所有数据
• 若centos mysql 之前开启了二进制日志，但是已经运行时间太长，也会导致 mysql 多源复制耗费时间非常长

在 ubuntu mysql 主机上执行命令将 centos mysql 数据库导出到一个 sql 文件。

# 获取需要 dump 的库
DATABASES=$(mysql -h{{ centos_mysql }} -umysql_dump_user -p{{ dump_user_password }} -e "show databases;" | grep -v "Database\|information_schema\|mysql\|performance_schema\|sys\|undolog" | awk '{ print $1}')

# --max_allowed_packet=1024M 设置 mysqldump 接收的 packet 最大为1G。
# --single-transaction 导出数据库时避免锁表，启动一个单一的事务，以确保数据的一致性和完整性。
# --master-data=2 导出数据库时将主服务器的二进制日志信息（文件名和位置）写入到导出文件中，但以注释的形式记录。
# --triggers 导出数据库时包含所有的触发器定义
# --events 导出数据库时包含所有的事件调度器定义
# --routines 导出数据库时包含所有的存储过程和存储函数定义
/opt/mysqldump -h{{ centos_mysql }} -umysql_dump_user -p{{ dump_user_password }} --max_allowed_packet=1024M --single-transaction --master-data=2  --triggers --events --routines --databases $DATABASES > /data1/mysql/dump/full_backup-{{ centos_mysql }}.sql

检查 dump sql 文件末尾是否有 "Dump completed" 关键字，若包含此字段，表示 dump 成功。"Dump completed" 关键字举例如下。

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-10-15 22:34:38

在 ubuntu mysql 主机导入 dump sql 文件。

mysql -h127.0.0.1 -uroot -p{{ root_user_password }} -e "source /data1/mysql/dump/full_backup-{{ centos_mysql }}.sql

设置多源复制

从 dump sql 文件获取主服务器二进制日志文件名和位置信息，举例如下图所示，文件名为mysql-bin.000305，位置34905648。

。。。省略其他不相关信息。。。

-- Position to start replication or point-in-time recovery from 
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000305', MASTER_LOG_POS=34905648;

--
-- Current Database: `activemq`
--
。。。省略其他不相关信息。。。

在 ubuntu mysql 主机设置多源复制

# {{ master_log_file }}   主服务器二进制日志文件名
# {{ master_log_pos }}  主服务器二进制日志文件位置
mysql -h 127.0.0.1 -uroot -p{{ root_user_password }} -e "change master to master_host='{{ centos_mysql }}',master_user='replica_user',master_password='{{ replica_user_password }}',master_log_file='{{ master_log_file }}',master_log_pos={{ master_log_pos }} for channel 'channel-{{ centos_mysql }}';"
mysql -h 127.0.0.1 -uroot -p{{ root_user_password }} -e "start slave;"

重复执行”使用 mysqldump 导出 centos mysql 数据并导入 ubuntu mysql”和”设置多源复制“ 步骤，把多个 centos mysql 都设置为 ubuntu mysql 的 master 。在 ubuntu mysql 主机查看多源复制状态，举例如下（这里把部分非重要信息省略了）。

*************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event
                          Master_Host: 10.66.10.152
                          Master_User: replica_user
                           Master_Port: 3306
                       Connect_Retry: 60
                    Master_Log_File: mysql-bin.000009
           Read_Master_Log_Pos: 1568
                       Relay_Log_File: mysql-relay-bin-channel@002d10@002e66@002e10@002e152.000002
                       Relay_Log_Pos: 620
           Relay_Master_Log_File: mysql-bin.000009
                  Slave_IO_Running: Yes
               Slave_SQL_Running: Yes
              Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
             Exec_Master_Log_Pos: 1568
                    Relay_Log_Space: 864
                          Last_IO_Error:
                        Last_SQL_Error:
                    Master_Server_Id: 101
                          Master_UUID: 74c1cfc1-884b-11ef-88e4-0242ac120002
                     Master_Info_File: mysql.slave_master_info
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                       Channel_Name: channel-10.66.10.152
*************************** 2. row ***************************
                      Slave_IO_State: Waiting for master to send event
                         Master_Host: 10.66.10.153
                         Master_User: replica_user
                          Master_Port: 3306
                      Connect_Retry: 60
                   Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 4424
                      Relay_Log_File: mysql-relay-bin-channel@002d10@002e66@002e10@002e153.000002
                      Relay_Log_Pos: 1232
          Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
             Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
            Exec_Master_Log_Pos: 4424
                   Relay_Log_Space: 1476
                          Last_IO_Error:
                        Last_SQL_Error:
                   Master_Server_Id: 101
                         Master_UUID: 6821082c-8a9f-11ef-98bc-0242ac120002
                    Master_Info_File: mysql.slave_master_info
     Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Master_Retry_Count: 86400
                      Channel_Name: channel-10.66.10.153

其中 Slave_IO_Running 值为 Yes 时表示slave mysql I/O 线程正在正常执行，并且已经或正在将 master mysql 的二进制日志写入到中继日志中。Slave_SQL_Running 值为 Yes 时表示 slave mysql SQL 线程正在正常执行，并且已经或正在读取中继日志中的事件并执行。

若 Slave_IO_Running 或 Slave_SQL_Running 值不是 Yes，查看 Last_IO_Error 或 Last_SQL_Error 显示的问题，解决完问题后，重新执行设置主从复制。

重启连接 mysql 服务

设置 centos mysql 为只读，连接 centos mysql 服务不能写入数据，通过多源复制，最新的 centos mysql 数据也已经同步到 ubuntu mysql。在 centos mysql 执行下面指令设置只读。

mysql -h127.0.0.1 -uroot -p{{ root_user_password }} -e "SET GLOBAL read_only = ON;"

切换 mysql 域名指向 ubuntu mysql 并且重启连接 mysql 的服务后，查看 centos mysql 是否还有服务链接？若有的话，需要单独处理。可能的原因: 服务连接 centos mysql 时使用了 ip，而不是域名。譬如下面例子在 centos mysql 执行 "show processlist;" 显示，已经没有服务连接此 mysql。

 # mysql -h 127.0.0.1 -uroot -p{{ root_user_password }} -e "show processlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
| 26 | root | localhost:37488 | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+

最终在 ubuntu mysql 清除主从复制，因为此时 centos mysql 数据已经同步到 ubuntu mysql，连接 mysql 的服务也已经指向 ubuntu mysql。在 ubuntu mysql 执行下面命令清除主从复制

mysql -h 127.0.0.1 -uroot -p{{ root_user_password }} -e "stop slave;"
mysql -h 127.0.0.1 -uroot -p{{ root_user_password }} -e "reset slave all;"

本文使用 mysql 多源复制实践了把多套 mysql 迁移到一套方案，另外也介绍了 mysqldump 的使用方式，希望对大家有所帮助。