MySQL 复制-有数据环境搭建异步复制
前言
本 SOP 介绍的是已有数据的场景下如果部署主从复制，因为是生产环境而且有数据，我们就需要先将主库的数据同步到从库再建立复制关系，还需要根据数据量来选择更适合的备份工具。
一、步骤归纳
单实例安装：新初始化一台 MySQL 实例做为从库；
创建用户：在主库创建专用的复制用户；
全量备份：主库做一个全量备份
（8.0 可以用 clone 插件）；恢复从库：将主库完整数据快照还原到从库；
创建复制：从库与主库建立复制关系；
启动线程：从库启动复制线程；
验证复制：验证复制拓扑是否可以正常同步数据；
二、异步复制操作过程
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 的位置信息，使用下方命令即可：
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
第八步：主库和从库中，各自删除 /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
第三步：从库解压物理备份，并创建数据恢复目录：
第四步：因为 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 信息：
第九步：从库建立复制关系，启动复制线程 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;
第十步：检查复制线程状态：
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 信息，供我们参考确认，没有其它用途：
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;
第六步：从库查看复制线程状态，确认复制线程是否启动成功：
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
第三步：从库解压物理备份，并创建数据恢复目录：
第四步：因为 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 信息：
第九步：从库执行 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;
第十一步：检查复制线程状态：
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 可以避免该问题。
