Mysql主从搭建
Winserver下在线搭建Mysql主从复制的记录
背景
最近公司上线了一套运维系统，已经运行了一段时间，后端数据库仅部署了一个单机的MySQL，考虑到该系统的重要性，决定给这台服务器做个主从同步。
正好借此机会把Mysql的经典架构--主从复制，再简单的梳理一遍。 另，下文部署是在 Mysql InnoDB存储引擎前提下进行的。
基本环境
系统： Winserver2016
数据库： Mysql 5.7
主库： 192.168.1.77
备库： 192.168.1.74
主库配置
确认主库的bin-log日志是否已开启
此次部署为在线搭建，不能影响主库的业务，这里需要确认主库是开启了Bin-log日志的，否则是不能在线搭建的，因为binlog的开启/关闭需要重启数据库。
确认主库配置文件 my.ini ：
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days = 5
max_binlog_size = 200m
server-id=1
创建复制用户并授权slave
mysql> grant replication slave on *.* to 'repel'@'192.168.1.74' identified by 'Aa_123456';
这里根据实际要同步的数据库自定义。
主库备份
使用工具 mysqldump 的方式
1. 导出整个数据库：
mysqldump -uroot -p --single-transaction --no-autocommit --master-data=2 -A > alldata.sql
2. 只想导出其中一个业务库，假设叫做 aaimp_pro 数据库：
mysqldump -uroot -p --single-transaction --no-autocommit --master-data=2 --default-character-set=utf8 aaimp_pro > E:\mysqltempbk\aaimp_pro_dump.sql
这里我实际就需要同步aaimp_pro 一个库，且需要注意这3个参数：
--single-transaction 和 --master-data=2
在线备份的经典搭配,当master_data和 single_transaction 同时使用时，先加全局读锁，然后设置事务一致性和使用一致性快照开始事务，然后马上就取消锁，然后执行导出。过程如下:
FLUSH TABLES WITH READ LOCK
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
SHOW MASTER STATUS -- 这一步就是取出 binlog index and position
UNLOCK TABLES
...dump...
--default-character-set=utf8
指定导出备份文件的字符集
--no-autocommit
使用autocommit/commit 语句包裹表
至此我们得到了主库的备份文件，将其传输至备库待用。
从库配置
配置从库的参数文件
my.ini 关键参数示例：
# by master slave
server-id=229
relay-log=F:\MySQL\relay_log\mysql-relay-bin.log
relay-log-index=F:\MySQL\relay_log\mysql-relay-bin.index
relay_log_purge=on
relay_log_recovery=on
replicate_wild_do_table=aaimp_pro.%
这里使用参数 replicate_wild_do_table 进行了复制过滤
-------- 附- 复制过滤的配置方法： ---------------
复制过滤的配置方法一般有在线和修改配置文件（需重启）两种，这里介绍一下修改配置文件的这种方式：
-
在主库配置
replicate_do_db ="db1";#复制的库白名单 replicate_ingore_db ="db2"; #复制的库黑名单 replicate_do_table="db1.t1%";#复制的表白名单 relicate_ingore_table="db2.t2%";#复制的表黑名单 【注】模糊匹配可以使用通配符 -
在从库配置
replicate_wild_do_table=""; replicate_wild_ignore_table="";这里推荐使用replicate_wild_do_table和replicate_wild_ignore_table来代替 replicate_do_db 和 replicate_ingore_db ,.
因为使用replicate_do_db和replicate_ignore_db时有一个隐患，跨库更新时会出错：
如设置 replicate_do_db=test use mysql; update test.table1 set ...... 第二句将不会被执行 如设置 replicate_ignore_db=mysql use mysql; update test.table1 set ...... 第二句会被忽略执行 原因是设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句被忽略。 可以使用replicate_wild_do_table和replicate_wild_ignore_table来代替 如 replicate_wild_do_table=test.% 或 replicate_wild_ignore_table=mysql.% 这样就可以避免出现上述问题了
初始化备库
确保备库的软件已经正确安装，如需重新初始化可按如下步骤：
-
创建服务
mysqld install MySQL57 --defaults-file="F:\MySQL\my.ini" -
初始化
mysqld --initialize -
启动数据库服务
net start MySQL57 -
修改密码
alter user 'root'@'localhost' identified by 'xxxxxxx';
导入备份文件
导入数据库，在导入数据库前，要确保已经创建了跟主库一样字符编码的数据库，然后重置一下从库binlog，执行导入：
1. 获取主库的建库语句，然后在备库创建要同步的数据库
mysql> show create database aaimp_pro\G
*************************** 1. row ***************************
Database: aaimp_pro
Create Database: CREATE DATABASE `aaimp_pro` /*!40100 DEFAULT CHARACTER SET utf8 */
2. 重置从库bin-log
mysql -uroot -p -e 'reset master'
3. 执行数据导入
mysql -uroot -p --default-character-set=utf8 aaimp_pro < F:\dabackup\masterslave\aaimp_pro_dump.sql
配置主从同步
在备份数据库的时候，使用了一个关键的参数 --master-data=2，该参数可以让我们在备份文件中获取到 master_log_file 和 master_log_pos 两个参数。有了这两个参数，我们就不需要像以往那样停服锁表，去查看了。
因为导出的文件过大，win上的记事本无法打开备份文件，可以使用powershell命令行的方式查看：
powershell：
# 获取文件的前40行
Get-Content F:\dabackup\masterslave\aaimp_pro_dump.sql -Head 40
示例：
-- MySQL dump 10.13 Distrib 5.7.36, for Win64 (x86_64)
--
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=165914904;
得到我们需要的 master_log_file 和 master_log_pos 两个变量值。
这里要注意，在使用powershell查看文件内容时会遇到乱码的情况，可用如下招式破解：
PowerShell查 更改默认编码
从 PowerShell 5.1 开始，重定向运算符（> 和 >>）调用 Out-File cmdlet。 因此，可以使用 $PSDefaultParameterValues 首选项变量设置默认编码，如以下示例所示：
$PSDefaultParameterValues['Out-File:Encoding'] = 'utf8'
使用以下语句更改具有 Encoding 参数的所有 cmdlet 的默认编码。
$PSDefaultParameterValues['*:Encoding'] = 'utf8'
万事具备，接下来可以在从库配置slave了
# 先重置一下 slave
mysql> reset slave all;
#然后再执行操作
mysql> change master to master_host='192.168.1.77', master_user='repel', master_password='Aa_123456', master_port=3306,master_log_file='mysql-bin.000028',master_log_pos=165914904;
启动slave
mysql> start slave;
检查一下从库的状态：
show slave status\G
关注Slave_IO_Running 和 Slave_SQL_Running 状态值 是否均为 YES ，测试、检查数据同步情况。
至此Winserver下在线搭建Mysql主从复制的工作已经完成，后续可对主从同步情况进行实时监控。
