mysql 主从复制原理与实现

• 创建备份账户并授权（在Master上）
grant replication slave on *.* to /* 用户名： copyer; 密码：12345 */ ‘copyer'@'%' identified by '12345';
• 修改主从的配置
ห้องสมุดไป่ตู้
MySQL主从复制的步骤
• 修改Master的配置信息
MySQL复制的常用拓扑结构 • 主从类型（Master-Slave) • 主主类型（Master-Master) • 级联类型（Master-Slave-Slave)
MySQL主从复制的步骤 • 安装MySQL服务器
分别安装两台MySQL 服务器：(MySQL 版本最好一致） 192.168.0.1 (Master) 192.168.0.2 (Slave)
》检测数据的散列值是否一致 》对表数据进行分块检测（以避免导致从库的延时） 》必须是居于语句的日志模式才能检测（计算散列的函数需要在从库上重做） 》检测要求表、表结构相同（否则检测行为将终止） 安全选项： –check-replication-filters 是否检查复制过滤规则 –check-slave-tables 检查是否所有从库都有被检查的表和列 –chunk-size-limit 每个chunk最大不能超过这个大小，超过就忽略它 限速选项： –check-interval 多久检查一次主从延迟、主库负载是否达到上限 –check-slave-lag 是否只检查这个从库的延迟 –max-lag 最大延迟，超过这个就等待 –max-load 最大负载，超过这个就等待 过滤选项： –databases 只检查某些库 –tables 只检查某些表
2016-04-14
weixin: dragonflyyi
主要议题 • • • • • • • • 为何要做MySQL 主从复制？ MySQL主从复制的原理是什么？ MySQL二进制日志的形式有哪些？ MySQL主从复制有哪些形态？ MySQL主从复制如何来实现？ 如何验证主从数据的质量? 遇到的相关问题与解决方案？ 相关参考资料
• 数据分发 (Data Distribution)
主要用于多数据中心的，数据分发与同步
• 高可用和数据容错(High Availability and Failover)
MySQL 自带的健康监控和检测，(根据配置的时间间隔)可以检测主库是否正常 工作，一旦发现主库宕机或者无法工作，就会选择到最好的一个备库上.
MySQL主从复制参考文献
• • • • • http://dev.mysql.com/doc/refman/5.7/en/replication.html http://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html http://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html https://www.percona.com/blog/2011/07/29/reasons-for-mysql-replication-lag/ https://www.percona.com/blog/2007/10/12/managing-slave-lag-with-mysqlreplication/ • https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html • 推荐书籍： • 《高可用MySQL（第2版）》
》Seconds_Behind_Master 落后主库的时间（秒）
• 可能导致主从延时的因素
》主从时钟是否一致 》网络通信是否存在延时 》是否和日志类型，数据过大有关 》从库性能，有没有开启binlog 》从库查询是否优化（解决慢查询）
MySQL主从数据一致性检测
• pt-table-checksum工具检测
MySQL主从复制的步骤
• 启动Master并查看状况
• 关联Slave(在Slave操作)
• 启动主从复制（在Slave操作）
在启动复制前后，可以执行show slave 命令查询相关参数 Slave_IO_Running=Yes Slave_SQL_Running=Yes 参数非常多，具体的可以参考MySQL官方文档
[mysqld] #设置服务器ID server-id=1 #设置需要写日志的数据库 binlog-do-db=test #设置不需要写日志的数据库 binlog-ignore-db=mysql #日志基于行模式 binlog_format=row #二进制日志文件存放位置 log-bin=d:/log/mysql/mysql_log_bin d:/log/mysql/mysql_log_bin.000001 d:/log/mysql/mysql_log_bin.000002 d:/log/mysql/mysql_log_bin.000003 d:/log/mysql/mysql_log_bin.000004 d:/log/mysql/mysql_log_bin.000005 d:/log/mysql/mysql_log_bin.000006 d:/log/mysql/mysql_log_bin.000007 d:/log/mysql/mysql_log_bin.index
• 基于行的复制(Row-Based Replication)
把改变的内容（数据本身）复制过去，而不是把命令在从服务器上执行一 遍. 从mysql5.0开始支持
• 混合型的复制(Mix-Based Replication)
默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采 用基于行的复制。
MySQL主从复制的原理 • 主要基于MySQL 二进制 日志
MySQL 的日志分类为: 二进制日志、中继日志、错误日志、查询日志、慢查询日志、事务日志
• 主要包括三个进程（2个I/O进程，1个SQL进 程）
1. Master 将数据变化记录到二进制日志中 2. Slave将Master的二进制日志，拷贝到 Slave 的中继日志中
为什么要做MySQL主从复制 • 备份数据(Data Backup)
只是简单的对数据进行备份，降低数据丢失的风险，有 事也用于报表等对数据时效性要求相对较低的场合。
• 负载均衡(Load Balance)
主要用于MySQL 集群，解决单点故障或者故障切换; 降低单台服务的负载, 使得访问均衡或者读写分离.
3. Slave将中继日志中的事件再做一次， 将数据变化，反应到自身（Slave)数据库
MySQL 二进制日志的复制类型
• 基于语句的复制(Statement-Based Replication)
在主服务器上执行的SQL语句，（将语句写入二进制日志），在从服务器上 执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
• 修改Slave的配置信息
[mysqld] #设置服务器ID server-id=2 #设置需要写日志的数据库 replicate-do-db=test #设置不需要写日志的数据库 replicate-ignore-db=mysql #开启slave 的二进制日志 log_slave_updates = 1 #日志基于行模式 binlog_format=row #二进制日志文件存放位置 log-bin=d:/log/mysql/mysql_log_bin
MySQL事务与binlog日志的关系
A. 事务的语句先缓存到事务缓存，直到提交指令，才写入binlog B. 事务是串行写入binlog，依赖于事务发生的次序
MySQL主从复制的状况监测
• 主从状况监测主要参数
》Slave_IO_Running 》Slave_SQL_Running IO线程是否打开：YES/NO/NULL SQL线程是否运行：YES/NO/NULL