此处默认为不可停服情况下的迁移，即不仅需要迁移数据，还需要主从同步，在相关服务全部更新到新 MySQL 上时才可停止原 MySQL。
环境准备
两台 IP 分别为192.168.137.151 和 192.168.137.152的 Ubuntu Server 22.04.1，分别为 A、B 服务器，都安装 MySQL：
# 安装 MySQL 服务端
$ sudo apt install mysql-server
# 登录 MySQL
$ sudo mysql
# 设置 root 用户密码（至少 8 位），如果没有先设置而是设置新密码会报这个错
# Re-enter new password:
# ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'newpassword';
Query OK, 0 rows affected (0.01 sec)
# 运行 MySQL 安全向导
$ sudo mysql_secure_installation
Securing the MySQL server deployment.
# 输入刚刚设置的 root 密码
Enter password for user root:
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.
Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
# 是否删除测试用的匿名用户，即 mysql 命令直接可以进入的情况
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
# 是否禁用 root 用户远程访问，因为是本地，为了方便我们就不禁用好了
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
# 是否删除测试用的“test”数据库
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
# 是否重新加载权限表
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
# 下载官方测试数据库
$ wget https://codeload.github.com/datacharmer/test_db/zip/refs/heads/master
# 安装解压 zip 的包
$ sudo apt-get install unzip
# 解压缩
$ unzip master
$ cd test_db-master/
# 导入 MySQL 实例（导入后就可以删除下载的数据库和解压后的目录了）
$ mysql -uroot -p -t < employees.sql
主从设置
假设 A 服务器为主，B 服务器为从。
主数据库（A 服务器）
# 先配置 A 服务器的 my.cnf 文件，在 [mysqld] 后添加
$ sudo vim /etc/mysql/my.cnf
[mysqld]
# 服务器 ID，唯一
server-id=151
# 启用二进制日志系统
log-bin=mysql-bin
# 需要同步的数据库，如果为多个库则逗号分隔，如果为所有库则注释此行（除了 ignore 的库）
binlog-do-db=xxx
# 忽略同步的库，如果为多个库则逗号分隔
binlog-ignore-db=mysql
# 确保 binlog 日志写入后与硬盘同步
sync_binlog=1
# 跳过现有才有 checksum 的事件，MySQL 5.6.5 以后值为 crc32，之前为 none
binlog_checksum=crc32
# bin-log 日志文件格式，设置为 mixed 可以防止主键重复
binlog_format=mixed
# 注释配置文件中的 IP 绑定以支持远程访问
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
……
#bind-address = 127.0.0.1
……
# 重启 MySQL
$ systemctl restart mysql
$ mysql -uroot -p
# 创建远程用户并授权，稍后用于从库连接
mysql > create user 'root'@'%' identified with mysql_native_password by 'newpassword';
mysql > grant all on *.* to 'root'@'%';
mysql > flush privileges;
# 数据库只读锁定，防止导出时有数据写入，解除锁定为 unlock tables;
mysql > flush tables with read lock;
# 查看主数据库状态，注意 File 和 Position 列，一会儿需要用到
mysql > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | xxx | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 主库导出数据
$ mysqldump -uroot bakdb -p > /tmp/bakdb.sql
# 将数据文件同步到 B 服务器上
$ rsync -e "ssh -p22" -avpgolr /tmp/bakdb.sql 192.168.137.152:/tmp
从数据库（B 服务器）
# 同样的需要先配置 my.cnf，在 [mysqld] 后添加
$ sudo vim /etc/mysql/my.cnf
[mysqld]
# 服务器 ID，唯一
server-id=152
# 启用二进制日志系统
log-bin=mysql-bin
# 需要同步的数据库，如果为多个库则逗号分隔，如果为所有库则注释此行（除了 ignore 的库）
binlog-do-db=xxx
# 忽略同步的库，如果为多个库则逗号分隔
binlog-ignore-db=mysql
# 跳过所有错误，继续执行复制操作
slave-skip-errors=all
# 同样的需要创建远程用户并配置，用于测试，此处同上略
# 重启 MySQL
$ systemctl restart mysql
$ mysql -uroot -p
# 在从库中创建数据库并导入原始数据
mysql > create database newdb character set utf8mb4 collate utf8mb4_general_ci;
mysql > use newdb;
mysql > source /tmp/bakdb.sql;
从数据库（B 服务器）启用主从
$ mysql -uroot -p
# 主从配置
mysql > change master to master_host='192.168.137.151',master_user='root',master_password='rootpwd',master_log_file='mysql-bin.000001',master_log_pos=157;
# 重启链路
mysql > stop slave;
mysql > start slave;
# 查看链路：Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 表示同步正常
mysql > show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to source
Master_Host: 192.168.137.151
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 157
Relay_Log_File: ubuntu-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 157
Relay_Log_Space: 337
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
# 重置绑定
# mysql > reset master;
# 重置同步日志，需要先执行 stop slave;
# mysql > reset slave;
测试
- 增删改查主数据库中的数据，查询从库中是否同步。✔️
最后再等和主数据库的相关服务的数据库地址都改成了从数据库，就可以关掉主从了。
评论区