使用GTID，将阿里云RDS数据库，同步到本地MySQL

最新推荐文章于 2024-10-04 00:00:08 发布

老陈头7

最新推荐文章于 2024-10-04 00:00:08 发布

阅读量1.5k

点赞数

分类专栏： Linux 数据库文章标签： mysql linux 运维数据库 rds

本文链接：https://blog.youkuaiyun.com/baiuterfkcyj/article/details/116228215

版权

数据库同时被 2 个专栏收录

4 篇文章

订阅专栏

Linux

2 篇文章

订阅专栏

目标：

将阿里云RDS数据库，同步到本地MySQL。实现RDS为Master，本地MySQL为Slave

核心技术：

GTID复制。

核心步骤：

下载线上物理文件，部署到本地，设置主从关系，启动，纠错。

将阿里云RDS物理备份文件到本地MySQL Data文件夹
设置本地为Slave。My.cnf
设置Change Master；start slave;
处理1236错误，纠偏：同步数据、跳过事务。

参考文件：

本地安装MySQL：https://www.cnblogs.com/nickchou/p/12093763.html
物理备份恢复至本地：https://help.aliyun.com/document_detail/41817.htm?spm=a2c4g.11186623.2.6.71624d4e3sGtj1
安装XtraBackup 8.0：https://www.percona.com/doc/percona-xtrabackup/8.0/installation.html?spm=a2c4g.11186623.2.14.49d12df0gWtYFW
修改MySQL的Root密码：https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html?spm=a2c4g.11186623.2.27.49d12df0bqzE43
配置GTID，绑定主从：https://dudashuang.com/rds-sync-to-local/
绑定主从：https://my.oschina.net/azaz/blog/3119700
处理1236：https://blog.51cto.com/hnr520/1883282
跳过事务：https://note.t4x.org/database/mysql-gtid-purged/

本地MySQL安装配置

一、安装配置：

1.删除mariadb

# 检查mariadb
rpm -qa | grep mariadb
# 删除mariadb
yum -y remove mariadb*

2.删除Mysql

# 检查mysql
rpm -qa | grep mysql
# 删除mysql
yum -y remove mysql*
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql

3. 安装MySQL

# 配置安装源
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# 安装MySQL
sudo yum --enablerepo=mysql80-community install mysql-community-server

4. 操作MySQL服务

systemctl start mysqld
systemctl status mysqld
systemctl stop mysqld
systemctl restart mysqld
# 查看端口运行情况
netstat -nlpt

二、查看并修改密码

mysql安装后会生成一个默认密码，此密码登录后要修改密码，不能用于其他操作

cat /var/log/mysqld.log

登录root账号修改初始密码，复制上面的密码登录

mysql -u root -p

修改密码，注意密码要稍微复杂点(大小写+符号+数字)

mysql> ALTER USER USER() IDENTIFIED BY 'Xinmima@2020';
mysql> update mysql.user set host='%' where user='root';
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
mysql> FLUSH PRIVILEGES;

可能会用到新建账号、赋权命令：

create user 'test'@'%' identified by 'Xinmima@2020';
grant all privileges on testdb.* to 'test'@'%';
# grant select on testdb.* to 'test'@'%';
# grant insert,delete,update on testdb.* to 'test'@'%';
flush privileges;
# 查看权限
show grants for 'test'@'%';
# 删除权限
revoke update on testdb.* from 'test'@'%';

阿里RDS数据库-还原到本地MySQL

三、下载RDS备份

下载物理备份，步骤如下：

登录RDS管理控制台。
在左侧单击实例列表，然后在上方选择实例所在地域。
找到目标实例，单击实例ID。
在左侧导航栏中单击备份恢复。
选择数据备份标签页。
选择查询的时间范围。
在数据备份列表中，找到要下载的数据备份，并单击其右侧的下载。

下载时要注意，链接里用来验证的参数太多，需要把文件名指定出来，否则下载会出错。下载命令：

wget -c 'http://.../hinsxxxx_data_20201123215531_xb.qp?...' -O hins_20201123215531_xb. qp

四、解压缩包

RDS-MySQL目前物理备份集文件有几种格式：

tar 压缩包（.tar.gz 后缀）
xbstream 压缩包（.xb.gz 后缀）
xbstream 文件包（_qp.xb 后缀）
xbstream qpress压缩包（_xb.qp后缀）

根据不同的MySQL版本，需要用到的数据恢复工具也不一样：

MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3
MySQL 5.7版本需要安装 Percona XtraBackup 2.4
MySQL 8.0版本需要安装 Percona XtraBackup 8.0

我的是MySQL8.0.18，备份文件的格式是_xb.qp。其余版本，参考链接里找对应方法。

1.安装qpress

wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"
tar xvf qpress-11-linux-x64.tar
chmod 775 qpress
cp qpress /usr/bin

2. 安装XtraBackup：

Installing Percona XtraBackup from Percona yum repository

Install the Percona yum repository by running the following command as the root user or with sudo: yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Enable the repository: percona-release enable-only tools release

If Percona XtraBackup is intented to be used in combination with the upstream MySQL Server, you only need to enable the tools repository: percona-release enable-only tools.

Install Percona XtraBackup by running: yum install percona-xtrabackup-80

3. 解压数据备份：

qpress -do hins_20201123215531_xb.qp | xbstream -x -v -C /home/mysql/data

若解压成功：

ls -l /home/mysql/data

4*. 恢复解压好的备份文件，记得配归属

## MySQL 8.0
xtrabackup --prepare --target-dir=/home/mysql/data
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data

# 我继续做了一步：哈哈哈哈，我决定保留这份小青涩# cp -rf /home/mysql/data/* /var/lib/mysql/

耐心等待，若系统返回如下类似结果，则说明备份文件已成功恢复到自建数据库。

若系统返回如下报错，可以用rm -rf /var/lib/mysql命令清空文件夹内文件，然后用chown -R mysql:mysql /var/lib/mysql修改归属。

5*. 需修改backup-my.cnf参数

# vi /home/mysql/data/backup-my.cnf# 修改MySQL配置文件：
Vim /etc/my.cnf

添加如下参数：

lower_case_table_names=1

注释掉如下自建数据库不支持的参数：

#innodb_log_checksum_algorithm

#innodb_fast_checksum

#innodb_log_block_size

#innodb_doublewrite_file

#innodb_encrypt_algorithm

#rds_encrypt_data

#redo_log_version

#master_key_id

#server_uuid

6. 启动MySQL，修改用户密码：

# mysqld --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &# 通过上面我自己的配置，直接启是可以成功的。但是还是保留一下，以后好查systemctl start mysqld

执行如下命令，登录MySQL数据库以验证进程启动成功。

mysql -u<源RDS实例账号> -p<对应密码>

使用show databases;查看数据库，确认是否恢复成功

建议您参考官方文档重置root账户的密码。

User表里面有一个aliyun_root，那就是root账户，update、identified、grant、flush参见上文。

配GTID同步

1. 配/etc/my.cnf

我的my.cnf文件内容

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

default_authentication_plugin = 'mysql_native_password'

lower_case_table_names = 1



#GTID:

gtid_mode=on

enforce_gtid_consistency=on

server_id=87

binlog_format=row

log-slave-updates=1

2. 设置GDIT，绑定主从

reset slave;

# 若备份文件中包含了 RDS 的主从复制关系，需要把这些主从复制关系清理掉

use mysql;

truncate table slave_relay_log_info;

truncate table mysql.slave_master_info;

truncate table mysql.slave_worker_info;

reset master;

change master to master_host = 'rdsExampleUrl.mysql.rds.aliyuncs.com', master_port = 3306, master_user = 'username', master_password='password', master_auto_position = 1;

# 启动

start slave;

# 查看状态

show slave status\G;

处理1236报错

报错：

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

从库报错：主库清理（purge）掉了从库还没有接受的事物。（https://dudashuang.com/rds-sync-to-local/）

解决：

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

mysql> quit

/etc/init.d/mysqld stop

mysql> reset slave;

mysql> reset master;

# 下文加粗部分到“/var/lib/mysql/xtrabackup_info”文件里找

mysql> set @@global.gtid_purged='78488ade-0dfc-11e7-ad71-00163e00dadd:1-18125360,b2723b81-468b-11e7-8636-00163e1ab1b4:1-3,78488ade-0dfc-11e7-ad71-00163e00dadd:1-18367652,b2723b81-468b-11e7-8636-00131ab1b4:1-4';

Query OK, 0 rows affected (0.00 sec)

找这里：

binlog_pos = filename 'mysql-bin.000206', position 195, GTID of the last change '02XXXXX-636a-11ea-ad5c-98039XXXXX:1-261417,030d8530-636a-11ea-aa21-ecXXXXXa7c34:1-1418028,83d2116b-d431-11ea-99c6-1c349XXXXXc:1-1411391,fb421df5-e902-11ea-81f2-b9XXXXX a0:1-512624'

再start slave就成功了。如图：