MySQL【进阶篇】06：MySQL数据迁移【从A机房迁移到B机房】

MySQL数据迁移【从A机房迁移到B机房】

做一个有趣的人Zz

773人浏览 · 2025-05-13 09:05:35

做一个有趣的人Zz · 2025-05-13 09:05:35 发布

一、迁移流程介绍

1、MySQL迁移流程

在这里插入图片描述

二、MySQL迁移前准备

1、环境准备

将A机房的主从迁移到B机房

（1）环境信息

A机房：
	192.168.201.161  主
	192.168.201.162  从

在这里插入图片描述

B机房：
	192.168.201.163  主
	192.168.201.164  从

在这里插入图片描述

（2）环境搭建

首先在A机房搭建主从环境：
基于GTID主从复制的自动部署

在这里插入图片描述

（3）写入数据

在A环境写入测试数据，在161节点执行：

-- 创建数据库 
CREATE DATABASE IF NOT EXISTS `online`;
USE `online`;
 
-- 创建产品表
CREATE TABLE IF NOT EXISTS `product` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `stock` INT UNSIGNED DEFAULT 0 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 创建用户表
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) UNIQUE NOT NULL,
  `email` VARCHAR(100) UNIQUE NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 创建订单表（使用反引号包裹保留字）
CREATE TABLE IF NOT EXISTS `order` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  `quantity` INT UNSIGNED NOT NULL,
  `order_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`),
  FOREIGN KEY (`product_id`) REFERENCES `product`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 插入产品数据 
INSERT INTO `product` (`name`, `price`, `stock`) VALUES 
('Laptop', 999.99, 50),
('Smartphone', 599.50, 100),
('Headphones', 89.99, 200),
('Keyboard', 45.75, 150),
('Mouse', 25.00, 300),
('Monitor', 199.99, 75),
('Tablet', 299.95, 60),
('Printer', 150.00, 40),
('Router', 79.99, 120),
('Speaker', 120.00, 90);
 
-- 插入用户数据 
INSERT INTO `user` (`username`, `email`) VALUES 
('john_doe', 'john@example.com'), 
('jane_smith', 'jane@example.com'), 
('mike_jones', 'mike@example.com'), 
('sara_wilson', 'sara@example.com'), 
('alex_brown', 'alex@example.com'), 
('emily_davis', 'emily@example.com'), 
('david_miller', 'david@example.com'), 
('lisa_taylor', 'lisa@example.com'), 
('peter_clark', 'peter@example.com'), 
('anna_white', 'anna@example.com'); 
 
-- 插入订单数据 
INSERT INTO `order` (`user_id`, `product_id`, `quantity`) VALUES 
(1, 2, 1),
(2, 1, 2),
(3, 3, 3),
(4, 5, 1),
(5, 4, 2),
(6, 6, 1),
(7, 7, 1),
(8, 8, 1),
(9, 9, 2),
(10, 10, 1);

在这里插入图片描述

2、B机房安装mysql

（1）将A机房的配置文件拷贝到B机房

161节点–>163节点
162节点–>164节点

161节点执行

scp /data/mysql/conf/my.cnf 192.168.184.163:/tmp

162节点执行

scp /data/mysql/conf/my.cnf 192.168.184.164:/tmp

（2）修改配置文件的server-id

一般修改为网段的后两位
163节点执行

cd /tmp
vim my.cnf

在这里插入图片描述
164节点执行

cd /tmp
vim my.cnf

在这里插入图片描述

（3）B机房安装mysql

在163、164节点安装单机的mysql，注意，版本要一致，可参考：
使用脚本自动安装MySQL8.0.25

注意!!! 这里的配置文件使用/tmp目录下的，就是上一步传输过来的配置文件。

163节点执行

cd /tmp/
mv my.cnf /data/script/install_mysql8/

164节点执行

cd /tmp/
mv my.cnf /data/script/install_mysql8/

3、拷贝A机房数据到B机房

（1）A机房数据进行全备

在161节点使用xtraback进行全备：

mkdir -p /data/backup
cd /data/backup
xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uroot -p'admin' --backup --stream=xbstream --target-dir=./ > /data/backup/xtrabackup.xbstream

在这里插入图片描述
xtraback安装可参考：MySQL的备份和恢复

（2）将A机房数据拷贝到B机房

在B机房163、164创建恢复目录

163节点执行

mkdir -p /data/backup/recover

在这里插入图片描述
164节点执行

mkdir -p /data/backup/recover

在这里插入图片描述

在161节点拷贝数据到163、164节点

cd /data/backup
scp xtrabackup.xbstream 192.168.184.163:/data/backup/recover
scp xtrabackup.xbstream 192.168.184.164:/data/backup/recover

在这里插入图片描述

4、把全备导入到B机房

（1）清空B机房的数据库

163节点执行

/etc/init.d/mysql.server stop
rm -rf /data/mysql/data/*
rm -rf /data/mysql/binlog/*

在这里插入图片描述

164节点执行

/etc/init.d/mysql.server stop
rm -rf /data/mysql/data/*
rm -rf /data/mysql/binlog/*

在这里插入图片描述

（2）全备导入到B机房

xtraback安装可参考：MySQL的备份和恢复
在这里插入图片描述

163节点执行

cd /data/backup/recover
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./

xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./

在这里插入图片描述

164节点执行

cd /data/backup/recover
xbstream -x < xtrabackup.xbstream
xtrabackup --prepare --target-dir=./

xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./

在这里插入图片描述

（3）重启B机房的MySQL

163节点执行

chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start

在这里插入图片描述

164节点执行

chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start

在这里插入图片描述

5、建立A机房到B机房的复制关系

（1）先建立B机房中164到163的复制关系

164节点执行

stop slave;
reset slave all;

change master to 
master_host='192.168.184.163',
master_user='repl',
master_password='admin',
MASTER_AUTO_POSITION=1;

start slave;

在这里插入图片描述

查看复制关系

show slave status\G;

在这里插入图片描述

（2）建立A机房到B机房的复制关系

就是建立161节点到163节点的复制关系，将163节点配置成161的从库。

163节点执行

stop slave;
reset slave all;

change master to 
master_host='192.168.184.161',
master_user='repl',
master_password='admin',
MASTER_AUTO_POSITION=1;

start slave;

在这里插入图片描述

查看复制关系

show slave status\G;

在这里插入图片描述

6、做好上线前准备

（1）增加监控和备份

可参考：
Promethets监控MySQL以及通过企业微信发送告警信息
 设置MySQL自动备份

（2）数据一致性校验

161节点执行

CREATE USER 'dba'@'192.168.%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'admin';
GRANT all ON *.* TO 'dba'@'192.168.%';

在这里插入图片描述
进行数据一致性校验

pt-table-checksum --no-check-binlog-format --host=192.168.184.161 --user=dba --password='admin' --socket=/tmp/mysql.sock

在这里插入图片描述

pt工具安装可参考：
使用pt-table-checksum进行主从数据一致性排查

7、给B机房的客户端增加白名单

（1）添加白名单

注意，如果防火墙是关闭的，不需要执行
在163节点执行，例如：mysql的客户端是165，想把165节点加到白名单，可执行：

iptables -L
iptables -A INPUT -s 192.168.184.165 -p tcp --dport 3306 -j ACCEPT
iptables -L

（2）加到开机启动中

vim /etc/rc.local

添加如下内容：

iptables -A INPUT -s 192.168.184.165 -p tcp --dport 3306 -j ACCEPT

在这里插入图片描述

三、迁移过程

1、查看A机房MySQL数据库是否有连接

161主库执行

show processlist;

在这里插入图片描述

2、确定MySQL是否有同步延迟

B机房主库查询

mysql -uroot -padmin

show slave status\G;

在这里插入图片描述

3、配置反向同步

这里配置反向同步是为了回滚，万一要回滚的话，新插入B机房的数据也能进行回滚。

在A机房MySQL主库执行，我这里是161节点，将B机房主库，也就是163节点配置为A机房161节点的主节点。

stop slave;
reset slave;

change master to 
master_host='192.168.184.163',
master_user='repl',
master_password='admin',
MASTER_AUTO_POSITION=1;

start slave;