MySql Replication主从环境搭建
1、主库
(1)、校验主库是否符合Replication主从的条件
执行以下查询:
SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW VARIABLES LIKE 'server_id'; SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; SHOW VARIABLES LIKE 'sync_binlog'; SHOW VARIABLES LIKE 'skip_networking';
结果如下则符合建立Replication主从的条件:
log_bin ON binlog_format ROW server_id 1 innodb_flush_log_at_trx_commit 1 sync_binlog 1 skip_networking OFF
log_bin ON 代表当前开启了binlog
binlog_format代表binlog记录模式为Row
server_id 服务器id不能和从服务器相同
innodb_flush_log_at_trx_commit 有三个值：0、1、2，默认是1。
0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
(2)、酌情配置my.ini文件
binlog_do_db=需要进行binlog的库
注意:这个配置如果添加,那么只会对配置值的库进行binlog的写入.慎用
2、从库
（1）、修改my.ini配置文件(windows下在ProgramData文件夹的MySql文件夹下)
server-id=101
replicate-do-db=2
slave-skip-errors=all
server_id 注意此id不能和主库id相同
replicate-do-db 需要从主库同步哪个库
注：
replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
replicate-ignore-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
slave-skip-errors 同步过程中出现错误,默认同步会停止 如果将值设为all,则跳过所有错误,如果指定具体的错误类型如下:
slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
或者在mysql执行如下语句
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务
mysql>slave start
注：如果从库不需要binlog功能,在配置文件中加上skip-log-bin=ON 关闭从库binlog,如果使用了canal等同步工具订阅了从库binlog则不能加上此配置.
(2)、检查核心配置是否生效
执行如下代码检查配置是否生效:
SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'server_id'; SHOW VARIABLES LIKE '%relay%';
正常结果如下:
log_bin ON server_id 101
中继日志相关配置如下图:
3、去主库创建从库用户
CREATE USER 'replication' IDENTIFIED WITH mysql_native_password BY 'replication'; GRANT REPLICATION SLAVE ON *.* TO 'replication'; FLUSH PRIVILEGES;
执行以下语句验证创建是否成功
SHOW CREATE USER 'replication'; SHOW GRANTS FOR 'replication';
4、锁定主库的相关表,并查看状态
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
当前停止在那个binlog文件,位置在哪里.
5、让从库指向主库
CHANGE MASTER TO
MASTER_HOST='172.18.100.231',
MASTER_USER='replication',
MASTER_PASSWORD='replication',
MASTER_LOG_FILE='binlog.000067',
MASTER_LOG_POS=155;
MASTER_LOG_FILE、MASTER_LOG_POS为4中锁定所有表之后的binlog文件和其position位置
6、从库和主库相差非常大,主库存在已久,如何查询binlogfile和position,并指定5中的相关参数
7、启动Slave
START SLAVE;
8、去主库查看从库Slave是否建立连接
SHOW PROCESSLIST;
结果如下
30 replication 172.18.10.254:54983 Binlog Dump 641 Master has sent all binlog to slave; waiting for more updates
说明连接已建立
9、主库解锁所有表
UNLOCK TABLES;
10、测试Replication主从是否建立成功
从库执行一下语句,mysql 8.0
Show SLAVE STATUS
可以根据 Seconds_Behind_Master这个值判断主从延迟,如果是0代表同步良好,如果是正值,说明存在延迟.也可以通过Relay_Log为前缀的几个参数进行判断,查看重放进度.
12、主库、从库 相差过大,导致从库追不上优化措施如下
(1)、sync_binlog=1
MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。
默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。
但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。
虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，
“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是2或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。
默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。
操作命令行如下:
show VARIABLES like '%sync_binlog%'; set global sync_binlog=0;
(2)、innodb_flush_log_at_trx_commit
13、如果需要通过canal同步,这时候如果先通过mysqldump导出、在通过mysql导入的方式,必须先重放mysql导入的binlog,然后在执行如下操作
(1)、执行如下代码
show master status;
记录下当前的binlog和position
(2)、执行如下代码
Flush Logs;
记录下当前的binlog和position
以防故障的情况发生
开启slave,之后在通过上面记录的点位,还原开启slave产生的binlog.
