2019独角兽企业重金招聘Python工程师标准>>> 
0、引言
MySQL主从复制的原理及搭建，故障分析
一、MySQL主从同步的架构及原理
原理：
1）Slave连接到master，主从数据一致，开启同步，开始同步数据
2） 用户在主上写入数据，日志储存到binlog，master上IO thread读取主库binlog，然后把信息传递给从库上的IO thread
3）从库上的IO thread再把部分信息存储在master.info（存储连接位置信息）和relay log里（存储binlog信息）
4）从库上的SQL thread监测到relay log有更新，把更新的信息恢复到从库，然后把位置点信息记录下来
二、MySQL实现主从同步的几种方式及使用价值
1、MySQL主从复制的几种实现方式
l 基于binlog+position的传统复制
l 基于事务的GTID复制
l 多源复制
l Group replication（5.7新特性）
l 半同步
2、MySQL主从复制的使用价值
l 利用从库做高可用
l 利用从库做读写分离
l 利用从库做升级
三、MySQL基于binlog三种模式下的复制
1、基于statement格式的复制
优点：binlog文件小，节省磁盘IO，只记录执行的SQL，方便阅读统计
缺点：对MySQL特殊函数不能复制，可能会导致主从不一致
2、基于row格式的复制
优点：详细记录每一行的修改信息（上下文信息），不会出现某些特定情况下函数、触发器、存储过程不执行的情况，方便flashback
缺点：binlog文件会更大，不能直接看到用户执行的SQL语句
3、基于mixed格式的复制
汇合使用row格式和statement格式，对于DDL记录会statement格式，对于table里的行操作记录为row格式。但是bug较多，不建议使用
四、MySQL的主从配置
1、建立用于同步的账号(权限replication slave)
2、确保server-id全局唯一，并开启log-bin
3、备份主库，恢复到从库，确保主从数据一致
4、查看主库位置信息并配置连接，开启主从
Tips:
1、如果库表较多，可以使用percona-tools工具集进行主从校验
2、也可以看slave status输出，了解同步状况
>show slave status\G;关注下面几个信息
Slave_IO_Running：从库I/O线程是否工作
Slave_SQL_Running：从库SQL线程是否工作
Seconds_Behind_Master：同步延迟时间，单位秒
Master_Log_File:主库上的I/O线程目前读到的binlog
Read_Master_Log_Pos:主库上的I/O线程目前读到的binlog的点
Relay_Master_Log_File:从库SQL线程目前执行到的主库binlog
Exec_Master_Log_Pos:从库SQL线程目前执行到的主库binlog的点
Relay_Log_File:从库SQL线程目前执行的relay log
Relay_Log_Pos:从库SQL线程目前执行的relay log的点
五、MySQL主从复制怎么保证数据一致性
在MySQL中，一次事务提交后，需要写undo、写redo、写binlog，写数据文件等等。在这个过程中，可能在某个步骤发生crash，就有可能导致主从数据的不一致。那么如何保证复制数据一致性呢？
1、在master上修改配置，设置双一，保证每次提交后，日志落盘
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
2、在slave上修改配置，前两个选项是确保slave上和复制相关的元数据表也采用innoDB引擎，受到innoDB事务安全的保护，后一个选项是开启relay log自动修复机制，尽可能避免数据丢失
master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"
relay_log_recovery = 1
通过以上两个方式基本可以保证主从环境里数据一致性，如果还是出现主从数据不一致，可通过pt-table-checksum 和 pt-table-sync 工具来进行数据的校验和修复。
六、MySQL主从复制常见问题及处理
MySQL同步中断常见是由两个方面引起：
I/O中断 ：网络问题、主机宕机、授权改变
SQL中断：执行的SQL出错
1、主从复制过程中，修改用于复制账号的密码，导致主从不同步
原来主从正常运行，后来修改用于复制账号的密码，修改完后重新执行
change master to master_host='xxx.xxx.xxx.xxx', master_user='replication user', master_password='passwd', master_port=xxxx, master_log_file='mysql-binlog.000xxx', master_log_pos=xxx;
悲剧了，如果指定MASTER_HOST与MASTER_PORT参数，slave会认为master与之前的不是同一个（即便MASTER_HOST 与 MASTER_PORT所带的参数与之前相同），之前指定的master的binlog文件名及位置将不再适用。
因此，更新密码后，只需要
change master to master_user='replication user', master_password='new passwd';
2、同步延时
seconds_behind_master>0
可能的原因：
l 主库在大量导入数据
l 从库硬件跟不上，导致性能下降
l 从库读负载较大
l RAID卡充放电
3、常见的error code及意义
1062错误：
主键冲突，一般可以跳过此类错误或者在配置文件中设置slave-skip-errors=1062
1032错误：
找不到这条记录，可以在从库手动插入数据。或者跳过这个错误，要视情况而定。
1050错误：
从库上表存在了，直接删除这个表即可
参考文档：
FAQ系列 | 如何保证主从复制数据一致性
http://imysql.com/2015/11/20/mysql-faq-how-make-sure-replicate-data-consistant.shtml
为了方便大家交流，本人开通了微信公众号，和QQ群291519319。喜欢技术的一起来交流吧
