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

文若1年前技术文章802


前言

本 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 可以避免该问题。


相关文章

Oozie web console is disabled 问题解决

Oozie web console is disabled 问题解决

一、问题一a、错误现象b、解决方案①、根据提示查看Oozie Quick Start 发现是缺少ExtJS 2.2库(必须是2.2版)下载ExtJS2.2下载地址:http://archive.clo...

python-序列化和反序列化

1、为什么要序列化内存中的字典、列表、集合以及各种对象,如何保存到一个文件中?如果是自己定义的类的实例,如何保存到一个文件中?如何从文件中读取数据,并让它们在内存中再次恢复成自己对应的类的实例?要设计...

Golang new and make

Go  语言中 new 方法和 make 方法,都是用于分配相应类型内存空间。1、makemake 内置函数分配并初始化(仅)slice、map 或 chan 类型的对象,返回值是所创建的类型本身。与...

Flume使用案例之实时读取本地文件到HDFS

Flume实时读取本地文件到HDFS1.  创建flume-hdfs.conf文件# 1 agenta2.sources = r2a2.sinks = k2a2.channels = c2 # 2 s...

MySQL运维实战(4.9) SQL_MODE之NO_UNSIGNED_SUBTRACTION

在mysql数据库中,unsigned表示不存负数,如果unsigned类型的字段作运算,得到的结果为负数,SQL会报错。mysql> create table t...

如何分析IIS占用CPU资源问题?看看这篇文章吧

如何分析IIS占用CPU资源问题?看看这篇文章吧

ProcDump捕获dump文件1、下载官方下载地址为:https://learn.microsoft.com/en-us/sysinternals/downloads/procdump#introd...

发表评论    

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