服务器运维

记一次MySQL5.7到MySQL8.0，利用XtraBackup同步数据，主从复制

September 28, 2023 - By janusa

MySQL5.7 到 MySQL8.0，不同版本主从复制，这个方案的优点是主服务器不用停机。

主服 CentOS 7 / MySQL 5.7.34 / IP: 45.45.45.45
从服 Debian 11 / MySQL 8.0.33 / IP: 72.72.72.72

步骤：

主服务器、副本服务器分别安装xtrabackup，rsync，分别创建同步目录
主服务器创建MYSQL 主从复制账号，授权副本服务器访问
主服务器创建防火墙规则，允许副本服务器访问3306端口
主服务器备份并准备文件
主服务器使用rsync复制备份文件至副本服务器同步目录下
副本服务器测试与主服务器的连接
副本服务器停止MYSQL服务，并备份原文件
副本服务器使用xtrabackup还原来自主服务器的备份文件
副本服务器查看还原备份文件的SOURCE_LOG_FILE和SOURCE_LOG_POS数字并记录
副本服务器修改my.cnf文件，设置server-id和replicate_do_db
副本服务器启动 mysql service
副本服务器设置并启动REPLICATION
副本服务器检查REPLICATION状态
删除主服务器和副本服务器的同步文件目录

1. 主、副本服务器分别安装xtrabackup，rsync，创建备份同步目录

主服务器 CentOS 7

# 安装xtrabackup
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
rpm -ivH percona-release-latest.noarch.rpm
yum update
percona-release enable-only tools
yum install percona-xtrabackup-24 -y

# 安装rsync
yum install rsync

# 创建备份同步目录
mkdir -p /opt/perconaXtraBackup

副本服务器 Debian 11

# 安装xtrabackup
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
apt-get update
percona-release enable-only tools
apt-get update
apt install percona-xtrabackup-24 -y

# 安装rsync
apt install rsync

# 创建备份同步目录
mkdir -p /opt/perconaXtraBackup

关于安装percona，详细：在 Debian 和 Ubuntu 上安装 Percona XtraBackup

2. 在主服务器上创建复制用户，配置权限

主服务器 CentOS 7

[root]# mysql -uroot -p
mysql> CREATE USER 'repl'@'72.72.72.72' IDENTIFIED BY 'REPLICA_PASSWD';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'72.72.72.72';
mysql> FLUSH PRIVILEGES;

3. 主服务器创建防火墙规则，允许副本服务器访问3306端口

# CentOS 列出防火墙规则
iptables -L --line-numbers

# 删除指定内容的防火墙规则 <chain> 是要删除规则的链的名称，如 INPUT、OUTPUT 或 FORWARD 等
iptables -D <chain> <rule-number>

# CentOS 允许来自72.72.72.72对3306端口的TCP连接
iptables -I INPUT 1 -p tcp --dport 3306 -s 72.72.72.72 -j ACCEPT

4. 主服务器备份并准备文件

主服务器 CentOS 7

# 备份数据文件并准备
xtrabackup --backup --user=root --password=PASSWORD --socket=/tmp/mysql.sock --target-dir=/opt/perconaXtraBackup/
xtrabackup --user=root --password=PASSWORD --prepare --target-dir=/opt/perconaXtraBackup/

5. 主服务器使用rsync复制备份文件至副本服务器同步目录下

主服务器 CentOS 7

# 复制数据备份文件到从服务器
rsync -avpP -e ssh /opt/perconaXtraBackup/ root@72.72.72.72:/opt/perconaXtraBackup/

6. 副本服务器测试与主服务器的连接

从服务器 Debian 11

# 测试连接成功并退出
mysql --host=45.45.45.45 --user=repl --password=REPLICA_PASSWD

7. 副本服务器停止MYSQL服务，并备份原文件

从服务器 Debian 11

# 备份原数据
systemctl stop mysql.service
mv /var/lib/mysql /var/lib/mysql_bak

8. 副本服务器使用xtrabackup还原来自主服务器的备份文件

从服务器 Debian 11

# 还原来自主服务器的数据
xtrabackup --move-back --target-dir=/opt/perconaXtraBackup --datadir=/var/lib/mysql
mkdir '/var/lib/mysql/#innodb_redo'
chown -R mysql:mysql /var/lib/mysql

9.副本服务器查看还原备份文件的SOURCE_LOG_FILE和SOURCE_LOG_POS数字并记录

从服务器 Debian 11

cat /var/lib/mysql/xtrabackup_binlog_pos_innodb
mysql-bin.000130	907352573

10. 副本服务器修改my.cnf文件，设置server-id和replicate_do_db

my.cnf 文件默认位置/etc/mysql/my.cnf

[mysqld]
server-id = 2
replicate_do_db = DB_NAME

11. 副本服务器启动 mysql service

从服务器 Debian 11

# 启动mysql
systemctl start mysql.service && systemctl status mysql.service

12. 副本服务器设置并启动REPLICATION

13. 副本服务器检查REPLICATION状态

从服务器 Debian 11

# 进入mysql命令行
mysql -uroot -p

# 配置从服务器从主服务器复制
mysql> STOP REPLICA;
mysql> CHANGE REPLICATION SOURCE TO
  SOURCE_HOST ='45.45.45.45',
  SOURCE_USER ='repl',
  SOURCE_PASSWORD ='REPLICA_PASSWD',
  SOURCE_LOG_FILE = 'mysql-bin.000130',
  SOURCE_LOG_POS = 907352573;
mysql> START REPLICA;

# 检查REPLICATION状态
mysql> SHOW REPLICA STATUS \G
        ...
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
        ...

14. 删除主服务器和副本服务器的同步文件目录

rm -rf /opt/perconaXtraBackup/*

如果启动MySQL失败，详细看看 /usr/local/mysql/var/ 目录下的错误日志。

还原来自主服务器的数据后，从服务器的MySQL的用户名和密码也会被一并覆盖为主服务器的用户名密码

一些相关的命令

# CentOS 列出防火墙规则
iptables -L --line-numbers

# 删除指定内容的防火墙规则 <chain> 是要删除规则的链的名称，如 INPUT、OUTPUT 或 FORWARD 等
iptables -D <chain> <rule-number>

# CentOS 允许来自72.72.72.72对3306端口的TCP连接
iptables -I INPUT 1 -p tcp --dport 3306 -s 72.72.72.72 -j ACCEPT

#mysql 查看系统变量
show variables like '%relay%';
show variables like '%bin%';

# mysql 查看命令warnings
SHOW WARNINGS;

# mysql 重置REPLICA
STOP REPLICA;