MySQL 复制-有数据环境搭建异步复制

文若2年前技术文章1189


前言

本 SOP 介绍的是已有数据的场景下如果部署主从复制，因为是生产环境而且有数据，我们就需要先将主库的数据同步到从库再建立复制关系，还需要根据数据量来选择更适合的备份工具。

一、步骤归纳

  1. 单实例安装：新初始化一台 MySQL 实例做为从库；

  2. 创建用户：在主库创建专用的复制用户；

  3. 全量备份：主库做一个全量备份 （8.0 可以用 clone 插件）；

  4. 恢复从库：将主库完整数据快照还原到从库；

  5. 创建复制：从库与主库建立复制关系；

  6. 启动线程：从库启动复制线程；

  7. 验证复制：验证复制拓扑是否可以正常同步数据；

二、异步复制操作过程

1. 单实例安装

忽略

2. 创建用户

主库创建专用的复制用户（已有请忽略）创建用户请参考下方（SQL 代码）：

-- 密码安装需求修改
grant replication slave on *.* to repl@'%' identified by 'YouPassword';

3. 全量备份 & 恢复 & 主从搭建

提示：MySQL 备份分为逻辑备份和物理备份，常用的备份工具分别是 MySQL 自带的 mysqldump 和 Percona 的 XtraBackUp 今天我们分别介绍用两种备份工具如果完成异步复制部署。

3.1 mysqldump 工具

第一步：使用👇下方命令对整个主实例进行逻辑全量备份：

mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --all-databases > /bakup/bakup_`date +"%F_%H_%M_%S"`.sql

第二步：主库使用 scp 命令将全备文件上传到从服务器：

scp /bakup/bakup_2021-03-11_11_06_58.sql root@172.16.104.57:/backup

第三步：从库导入主库的逻辑备份文件：

mysql -uroot -pYouPassword -e'source /backup/bakup_2021-03-11_11_06_58.sql'

第四步：需要从备份文件中找到 binlog 的位置信息，使用下方命令即可：
截屏2023-02-20 上午10.42.12.png

head -100 /backup/bakup_2021-03-11_11_06_58.sql |grep -i 'change master'

第五步：从库建立复制关系，根据上一步取出的语句，补充 IP 地址及主库复制账户密码即可：

CHANGE MASTER TO
	MASTER_HOST='主库 id 地址',
	MASTER_USER='复制用户',
	MASTER_PASSWORD='password',
	MASTER_PORT=3306,
	MASTER_LOG_FILE='mysql-bin.000003',
	MASTER_LOG_POS=118286302,
 	MASTER_CONNECT_RETRY=10;

第六步：从库启动复制线程：

start slave;

第七步：查询复制线程状态：

show slave status\G

截屏2023-02-20 上午10.42.32.png
第八步：主库和从库中，各自删除 /backup/bakup_2021-03-11_11_06_58.sql 备份文件：

rm -rf /backup/bakup_2021-03-11_11_06_58.sql

此时使用 mysqldump 搭建复制关系已经完成了，接下来可尝试创建数据验证主从是否可以同步即可。接下来我们来介绍使用 XtraBackUp 完成以上步骤～

3.2 XtraBackUp 工具

第一步：使用👇下方命令对整个主实例进行物理全量备份：

innobackupex --defaults-file=/etc/my.cnf --user=root --password=YouPassword --no-timestamp --stream=tar ./ | cat - > /bakup/bakup_`date +"%F_%H_%M_%S"`.tar.gz

第二步：主库使用 scp 命令将备份文件上传到从服务器：

scp /bakup/bakup_2021-03-11_14_55_44.tar.gz root@172.16.104.57:/backup

第三步：从库解压物理备份，并创建数据恢复目录：
截屏2023-02-20 上午10.42.53.png
第四步：因为 XtraBackUp 支持热备，是基于 MySQL 自动故障恢复机制来做的，在还原数据之前需要执行一下 redo log 应用：

innobackupex --apply-log --use-memory=1G ./

第五步：关闭从库，清理数据文件，必须清理的文件有：共享表空间文件、数据表空间文件、undo log、redo log、binlog、relary log 等文件（删除时请根据自己的配置文件信息）

# 关闭 MySQL 数据库
mysqladmin -uroot -p shutdown
# 删除数据文件
rm -rf /data/mysql_57/data/*
# 删除 binlog
rm -rf /data/mysql_57/logs/mysql-bin.*
# 删除 relary log
rm -rf /data/mysql_57/logs/mysql-relary.*

第六步：删除临时文件假下的压缩包，然后还原好的文件恢复到从库数据目录：

# 删除临时文件夹下的备份文件，已经不需要它了
rm -ef bakup_2021-03-11_14_55_44.tar.gz
# 恢复数据
innobackupex --defaults-file=/etc/my.cnf --copy-back ./

第七步：修改文件宿主用户，启动 MySQL

-- 修改文件宿主用户
chown -R mysql:mysql /data/mysql_57
-- 启动 MySQL
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

第八步：从 xtrabackup_binlog_pos_innodb 文件中查询备份 binlog 信息：
截屏2023-02-20 上午10.37.32.png
第九步：从库建立复制关系，启动复制线程 MASTER_LOG_FILE 和 MASTER_LOG_POS 就填写上一步从 binlog pos 文件中查询到的数据，然后补充主库 ip 地址用户密码即可：

-- 建立复制关系
CHANGE MASTER TO
	MASTER_HOST='主库 id 地址',
	MASTER_USER='复制用户',
	MASTER_PASSWORD='password',
	MASTER_PORT=3306,
	MASTER_LOG_FILE='mysql-bin.000003',
	MASTER_LOG_POS=118286302,
 	MASTER_CONNECT_RETRY=10;
-- 启动复制线程
start slave;

第十步：检查复制线程状态：
截屏2023-02-20 上午10.38.22.png

4. 创建测试数据验证

此时我们的复制已经搭建成功了，建议可以创建一些测试数据，验证数据是否可以同步。

create database rep_test;
use rep_test;
create table rep_test_table(id int);
insert into rep_test_table values(1),(2),(3);

三、GTID 复制操作过程

1. 单实例安装

忽略

2. 创建用户

主库创建专用的复制用户（已有请忽略）创建用户请参考下方（SQL 代码）：

-- 密码安装需求修改
grant replication slave on *.* to repl@'%' identified by 'YouPassword';

3. 全量备份 & 恢复 & 主从搭建

提示：MySQL 备份分为逻辑备份和物理备份，常用的备份工具分别是 MySQL 自带的 mysqldump 和 Percona 的 XtraBackUp 今天我们分别介绍用两种备份工具如果完成异步复制部署。

3.1  mysqldump 工具

第一步：使用👇下方命令对整个主实例进行逻辑全量备份：

mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --all-databases > /bakup/bakup_`date +"%F_%H_%M_%S"`.sql

第二步：主库使用 scp 命令将全备文件上传到从服务器：

scp /bakup/bakup_2021-03-11_11_06_58.sql root@172.16.104.57:/backup

第三步：从库导入主库的逻辑备份文件：

mysql -uroot -pYouPassword -e'source /backup/bakup_2021-03-11_11_06_58.sql'

第四步：从逻辑备份文件中找出 GTID 信息，供我们参考确认，没有其它用途：
截屏2023-02-20 上午10.38.54.png

head -100 /backup/bakup_2021-03-11_16_51_11.sql | grep -i gtid

第五步：建立复制关系，启动复制线程。GTID 复制 不需要添加 binlog 文件和 position 位置：

-- 建立复制关系
CHANGE MASTER TO
	MASTER_HOST='主库 id 地址',
	MASTER_USER='复制用户',
	MASTER_PASSWORD='password',
	MASTER_PORT=3306,
	MASTER_AUTO_POSITION=1;
-- 启动复制线程
start slave;

第六步：从库查看复制线程状态，确认复制线程是否启动成功：
截屏2023-02-20 上午10.39.19.png

3.2   XtraBackUp 工具

第一步：使用👇下方命令对整个主实例进行物理全量备份：

innobackupex --defaults-file=/etc/my.cnf --user=root --password=YouPassword --no-timestamp --stream=tar ./ | cat - > /bakup/bakup_`date +"%F_%H_%M_%S"`.tar.gz

第二步：主库使用 scp 命令将备份文件上传到从服务器：

scp /bakup/bakup_2021-03-11_14_55_44.tar.gz root@172.16.104.57:/backup

第三步：从库解压物理备份，并创建数据恢复目录：

截屏2023-02-20 上午10.39.43.png第四步：因为 XtraBackUp 支持热备，是基于 MySQL 自动故障恢复机制来做的，在还原数据之前需要执行一下 redo log 应用：

innobackupex --apply-log --use-memory=1G ./

第五步：关闭从库，清理数据文件，必须清理的文件有：共享表空间文件、数据表空间文件、undo log、redo log、binlog、relary log 等文件（删除时请根据自己的配置文件信息）

# 关闭 MySQL 数据库
mysqladmin -uroot -p shutdown
# 删除数据文件
rm -rf /data/mysql_57/data/*
# 删除 binlog
rm -rf /data/mysql_57/logs/mysql-bin.*
# 删除 relary log
rm -rf /data/mysql_57/logs/mysql-relary.*

第六步：删除临时文件假下的压缩包，然后还原好的文件恢复到从库数据目录：

# 删除临时文件夹下的备份文件，已经不需要它了
rm -ef bakup_2021-03-11_14_55_44.tar.gz
# 恢复数据
innobackupex --defaults-file=/etc/my.cnf --copy-back ./

第七步：修改文件宿主用户，启动 MySQL

-- 修改文件宿主用户
chown -R mysql:mysql /data/mysql_57
-- 启动 MySQL
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

第八步：从 xtrabackup_info 中读取 GTID 信息：
截屏2023-02-20 上午10.40.28.png
第九步：从库执行 reset master 避免初始化环境问题，SET GTID：

-- 重置 binlog 
reset master;
-- 使用上一步查询的 GTID 信息组成下方 SQL
set GLOBAL GTID_PURGED='258946b5-8178-11eb-bf99-faec3ea1f200:1-6007';

第十步：建立复制关系，启动复制线程：

-- 建立复制关系
CHANGE MASTER TO
	MASTER_HOST='主库 id 地址',
	MASTER_USER='复制用户',
	MASTER_PASSWORD='password',
	MASTER_PORT=3306,
	MASTER_AUTO_POSITION=1;
-- 启动复制线程
start slave;

第十一步：检查复制线程状态：
截屏2023-02-20 上午10.41.16.png

4. 创建测试数据验证

此时我们的 GTID 复制已经搭建成功了，建议可以创建一些测试数据，验证数据是否可以同步。

create database rep_test;
use rep_test;
create table rep_test_table(id int);
insert into rep_test_table values(1),(2),(3);

总结

1. 备份权限问题

备份还原过程中，需要有 super 权限，避免因为权限不足导致数据无法写入。

2. 从库 SQL 线程无法启动问题

从库的话，可以 rest master 避免因为初始化过程中有自己的日志写入。

3. 如何选择备份工具？

从主库实例的数据量来考虑：数据量大的话推荐使用物理备份，如果数据量比较小，二三十个 G 那么可以使用 mysqldump 操作也比较简单；

从对业务的影响来考虑：如果业务使用了非事务存储引擎，则在备份期间不能写入，否则可能会破坏备份的一致性，那么使用 mysqldump 备份的话，就需要去掉 --single-transaction 在备份过程中会全程锁表，堵塞 DML 操作，而使用 XtraBackUp 可以避免该问题。


返回列表

上一篇：MySQL 复制-无数据环境搭建异步复制

下一篇：MySQL 复制-半同步搭建及原理

相关文章

doris高可用架构图

doris高可用架构图

...

DBeaver连接Trino

DBeaver连接Trino

1、背景trino 开启https，需要通过dbeaver进行连接DBeaver版本：21.2.02、解决办法下载安装dbeaver打开选择选择trino填写主机如果trino开启https，则可以使...

trino容器对接ldap（二）

trino容器对接ldap（二）

前提：本文前提是在trino容器已经对接上hive组件，并且ldap已经部署完成的基础上进行的对接。前提文章见：helm安装部署trino对接hive（一）安装部署1、设置证书因为不确定是在哪台机器中...

sql_mode参数

sql_mode参数

一、参数含义sql_mode是个很容易被忽视的变量，其本身主要就是一种语法校验规则 ，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。二、常用值含义1、ONLY_FUL...

阿里金融云经典网络和线下某银行实现网络互通

阿里金融云经典网络和线下某银行实现网络互通

需求某银行需要和某阿里金融云账号下的经典网络实例内网打通。已知不考虑将该服务器从经典网络类型迁移至VPC类型。阿里金融云环境下，之前是支持拉线下到经典网络专线的，但是目前和阿里侧核查，确认已不支持，仅...

keycloak高可用部署

keycloak高可用部署

添加keycloak应用rancher应用商店模式添加keycloak仓库地址rancher应用商店添加bitnami的helm仓库地址https://charts.bitnami.com/bitna...

发表评论    

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号