1. 迁移前准备
在进行MySQL迁移之前，需要进行充分的准备工作，包括规划迁移时间、准备目标服务器、备份数据等。更多学习教程www.fgedu.net.cn
– 安排在业务低峰期进行迁移
– 提前通知相关业务方，做好迁移计划
– 确保目标服务器的硬件配置不低于源服务器
– 准备足够的网络带宽用于数据传输
– 制定回滚方案，以防迁移失败
2. 源服务器检查
检查源服务器的MySQL版本、数据大小、配置文件等信息。
# ssh root@192.168.1.51 “/mysql/current/bin/mysql –version”
mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using EditLine wrapper
# 检查源服务器MySQL状态
# ssh root@192.168.1.51 “systemctl status mysql”
● mysql.service – MySQL Server
Loaded: loaded (/etc/systemd/system/mysql.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2026-03-30 09:00:00 CST; 1 day ago
Main PID: 12345 (mysqld)
Tasks: 38
Memory: 2.0G
CGroup: /system.slice/mysql.service
└─12345 /mysql/current/bin/mysqld –defaults-file=/mysql/current/my.cnf
# 检查源服务器数据大小
# ssh root@192.168.1.51 “du -sh /mysql/data/”
100G /mysql/data/
# 检查源服务器配置文件
# ssh root@192.168.1.51 “cat /mysql/current/my.cnf”
3. 目标服务器准备
准备目标服务器，包括操作系统安装、网络配置、目录创建等。学习交流加群风哥微信: itpux-com
# ssh root@192.168.1.52 “free -h”
total used free shared buff/cache available
Mem: 64G 2.1G 60G 8.5M 1.8G 61G
Swap: 32G 0B 32G
# 检查目标服务器磁盘空间
# ssh root@192.168.1.52 “df -h”
Filesystem Size Used Avail Use% Mounted on
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 32G 8.5M 32G 1% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/sda1 50G 15G 36G 30% /
/dev/sdb1 1.0T 20G 980G 2% /mysql
# 在目标服务器创建目录
# ssh root@192.168.1.52 “mkdir -p /mysql/{current,conf,data,log,run}”
# ssh root@192.168.1.52 “chmod 755 /mysql/{current,conf,data,log,run}”
# ssh root@192.168.1.52 “chown -R mysql:mysql /mysql”
4. 数据备份
在迁移之前，对源服务器的MySQL数据进行备份。
# ssh root@192.168.1.51 “mkdir -p /backup/mysql/migration”
# 执行备份
# ssh root@192.168.1.51 “/mysql/current/bin/mysqldump –all-databases –single-transaction –master-data=2 –flush-logs –triggers –routines –events -u root -p”fgedudb123″ > /backup/mysql/migration/all_databases-$(date +”%Y%m%d%H%M%S”).sql”
# 检查备份文件
# ssh root@192.168.1.51 “ls -la /backup/mysql/migration/”
total 51200
-rw-r–r– 1 root root 26214400 Mar 31 10:00 all_databases-20260331100000.sql
# 压缩备份文件
# ssh root@192.168.1.51 “gzip /backup/mysql/migration/all_databases-20260331100000.sql”
5. 数据传输
将备份文件传输到目标服务器。
# scp root@192.168.1.51:/backup/mysql/migration/all_databases-20260331100000.sql.gz root@192.168.1.52:/backup/mysql/
# 传输配置文件到目标服务器
# scp root@192.168.1.51:/mysql/current/my.cnf root@192.168.1.52:/mysql/conf/
# 在目标服务器解压备份文件
# ssh root@192.168.1.52 “gunzip /backup/mysql/all_databases-20260331100000.sql.gz”
# 检查传输结果
# ssh root@192.168.1.52 “ls -la /backup/mysql/”
total 51200
-rw-r–r– 1 root root 26214400 Mar 31 10:10 all_databases-20260331100000.sql
6. 目标服务器安装MySQL
在目标服务器安装MySQL 5.7版本。
# ssh root@192.168.1.52 “cd /tmp && wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.36-el7-x86_64.tar.gz”
# 解压源码包
# ssh root@192.168.1.52 “cd /tmp && tar -zxvf mysql-5.7.36-el7-x86_64.tar.gz”
# 移动到安装目录
# ssh root@192.168.1.52 “mv /tmp/mysql-5.7.36-el7-x86_64 /mysql”
# 创建符号链接
# ssh root@192.168.1.52 “ln -s /mysql/mysql-5.7.36-el7-x86_64 /mysql/current”
# 检查安装结果
# ssh root@192.168.1.52 “ls -la /mysql/current/bin/”
total 102400
drwxr-xr-x 2 root root 4096 Mar 31 10:15 .
drwxr-xr-x 10 root root 4096 Mar 31 10:15 ..
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysql
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqladmin
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqlbinlog
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqldump
-rwxr-xr-x 1 root root 40960000 Mar 31 10:15 mysqld
-rwxr-xr-x 1 root root 2457600 Mar 31 10:15 mysqlimport
7. 数据恢复
在目标服务器恢复MySQL数据。学习交流加群风哥QQ113257174
# ssh root@192.168.1.52 “sed -i ‘s/bind-address=192.168.1.51/bind-address=192.168.1.52/g’ /mysql/conf/my.cnf”
# 检查配置文件
# ssh root@192.168.1.52 “grep bind-address /mysql/conf/my.cnf”
bind-address=192.168.1.52
# 初始化MySQL
# ssh root@192.168.1.52 “/mysql/current/bin/mysqld –initialize-insecure –user=mysql –basedir=/mysql/current –datadir=/mysql/data”
# 启动MySQL服务
# ssh root@192.168.1.52 “systemctl start mysql”
# 恢复数据
# ssh root@192.168.1.52 “/mysql/current/bin/mysql -u root -p”fgedudb123” < /backup/mysql/all_databases-20260331100000.sql"
8. 启动服务
在目标服务器启动MySQL服务。
# ssh root@192.168.1.52 “cat > /etc/systemd/system/mysql.service << EOF [Unit] Description=MySQL Server After=network.target [Service] Type=forking User=mysql Group=mysql PIDFile=/mysql/run/mysqld.pid ExecStart=/mysql/current/bin/mysqld --defaults-file=/mysql/conf/my.cnf ExecStop=/mysql/current/bin/mysqladmin -u root -p shutdown Restart=on-failure [Install] WantedBy=multi-user.target EOF" # 重新加载系统服务 # ssh root@192.168.1.52 "systemctl daemon-reload" # 启动MySQL服务 # ssh root@192.168.1.52 "systemctl start mysql" # 检查服务状态 # ssh root@192.168.1.52 "systemctl status mysql" ● mysql.service - MySQL Server Loaded: loaded (/etc/systemd/system/mysql.service; disabled; vendor preset: disabled) Active: active (running) since Mon 2026-03-31 10:20:00 CST; 1min ago Main PID: 67890 (mysqld) Tasks: 38 Memory: 2.0G CGroup: /system.slice/mysql.service └─67890 /mysql/current/bin/mysqld --defaults-file=/mysql/conf/my.cnf # 设置开机自启 # ssh root@192.168.1.52 "systemctl enable mysql" Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /etc/systemd/system/mysql.service.
9. 迁移验证
验证目标服务器的MySQL服务是否正常运行，数据是否完整。
# ssh root@192.168.1.52 “/mysql/current/bin/mysql -u root -p”
# 测试命令
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| fgedudb |
+——————–+
5 rows in set (0.00 sec)
mysql> use fgedudb;
Database changed
mysql> create table fgedu_users (
-> id int primary key auto_increment,
-> name varchar(50) not null,
-> age int,
-> email varchar(100)
-> ) engine=InnoDB default charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into fgedu_users (name, age, email) values (‘测试用户’, 25, ‘test@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from fgedu_users;
+—-+———–+—–+———————+
| id | name | age | email |
+—-+———–+—–+———————+
| 1 | 测试用户 | 25 | test@fgedu.net.cn |
+—-+———–+—–+———————+
1 row in set (0.00 sec)
mysql> status;
————–
/mysql/current/bin/mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using EditLine wrapper
Connection id: 8
Current database: fgedudb
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.7.36 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 360 sec
Threads: 2 Questions: 15 Slow queries: 0 Opens: 131 Flush tables: 3 Open tables: 48 Queries per second avg: 0.004
————–
mysql> exit
10. 业务切换
将业务流量从源服务器切换到目标服务器。更多学习教程公众号风哥教程itpux_com
# ssh root@192.168.1.51 “systemctl stop mysql”
# 更新应用配置，将MySQL地址指向目标服务器
# 例如，修改应用配置文件中的MySQL地址
# vi /app/config/application.yml
# spring:
# datasource:
# url: jdbc:mysql://192.168.1.52:3306/fgedudb?useSSL=false&serverTimezone=UTC
# username: fgedu
# password: fgedudb123
# 重启应用服务
# systemctl restart app
# 验证应用是否正常访问MySQL
# curl http://localhost:8080/mysql-test
{“status”:”success”,”message”:”MySQL connection successful”}
– 切换业务流量前，确保目标服务器的MySQL服务正常运行
– 切换过程中，密切监控应用的运行状态
– 保留源服务器的MySQL服务一段时间，以防需要回滚
– 切换完成后，验证所有业务功能是否正常
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
