MySQL主从复制详解

 

一、MySQL 主从复制原理

　　1、简介

　　MySql自带有主从复制功能，该功能是构建大型应用、高性能应用的基础。这种机制是指：将某一台主机（master）上的MySQL数据复制到多台其他主机上（slave），并重新执行一遍来实现。复制过程中一个Mysql实例充当主库（master），其他Mysql实例充当分库（slave）。

　　2、复制架构图

　　　　　　

　主库将更新写入binlog（二进制日志），并维护文件的一个索引以跟踪日志循环。如图：

　　　　

　　从库生成两个线程，一个I/O线程，一个SQL线程。I/O线程去请求主库的binlog，　并将得到的binlog日志写到relay log（中继日志）文件中；

　　主库会生成一个log dump线程，用来给从库I/O线程传binlog；SQL线程会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。

　　3、MySQL支持的复制类型

　　3.1 基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。配置：binlog_format = 'STATEMENT';

　　3.2 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持。配置： binlog_format = 'ROW';

　　3.3 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。配置： binlog_format = 'MIXED';

　　4、解决的问题

• 　　数据分布
• 　　负载平衡
• 　　备份
• 　　高可用性和容错行

　　5、复制的常用拓扑结构

　　复制的拓扑结构有以下一些基本原则：

　　1> 每个Slave只能有一个Master

　　2> 每个Slave只能有一个唯一的服务器ID

　　3> 每个Master可以有很多Slave

　　4> 如果设置了log_slave_updates，Slave可以是其他Slave的Master，从而扩散Master的更新。

　　MySQL不支持多主服务器复制，即一个Slave可以有多个Master。

　　常用拓扑结构：

　　5.1 一主多复制架构

　　由一个Master和多个Slave组成复制系统，Slave之间不通信。

　　在实际场景中，MySQL复制大部分都是一主多复制这种架构。在Master读取请求压力非常大的场景下，把大量对实时性要求不是特别高的读取通过负载均衡到多个从Slave上，降低主库的读取压力。在Master宕机时，可以把一个Slave切换为主库继续提供服务。

　　问题建议：

　　1> 当Slave增加到一定数量时，Slave对Master的负载及网络带宽会成为一个严重的问题。

　　2> 不同的Slave扮演不同的角色（例如使用不同的索引，或者不同的存储引擎）。

　　3> 用一个Slave作为备用Master，只进行复制。

　　4> 用一个远程Slave，用于灾难恢复。

　　5.2 多级复制架构

　　一主多从的架构能够解决大部分请求压力特别大的场景需求，但随着从库的增加，会影响到Master的I/O和网络压力，而使用多级复制架构就可以解决一主多从的这个问题。但同时要注意的是，多级复制场景下主库的数据是经历了多次才到达Salve，期间的延时也会比一主多从的复制要大。

　　问题建议：

　　1> 会根据层级的多少增加复制的延时。

　　2> 这种方案可以与第三方软件结合使用，如：Slave + LVS + Keepalived实现高可用

 

　　5.3 双主复制/Dual Master架构

　　如果写压力比较大，或者DBA做维护需要主从切换，通过双主复制/Dual Master架构可以避免重复搭主从库的麻烦。

　　问题建议：

　　1> 最大的问题就是更新冲突

　　2> 可以采用MySQL Cluster，以及将Cluster和Replication结合起来，可以建立强大的高性能数据平台。

二、MySQL主从配置

　　1、基础环境配置

　　数据库版本：mysql 5.1.73（Slave版本 >= Master版本）

　　IP地址：192.168.1.100 （Master）、192.168.1.101（Slave）

　　2、Master服务器配置

　　2.1（关掉新主库的只读属性）
　　mysql>set global read_only=0;
　　mysql>flush privileges;
　　2.2 开启读写属性
　　mysql>set global read_only=1;
　　mysql>flush privileges;

　　2.3 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持
　　mysql> SET SESSION binlog_format = 'ROW';
　　mysql> SET GLOBAL binlog_format = 'ROW';

 　　2.4 在 Master 的数据库中建立一个复制账户，每个 Slave 使用该账户连接 Master 进行复制，需要 replication slave 和 replication client 权限，Master 的连接信息会存储在文本文件 master.info 文件中。(master.info文件在 Slave 的数据目录中)
　　说明：创建了一个用户名为 replication 的用户，密码为 123456 ,只允许在 192.168.1.101 这个 Slave 上登录。
　　mysql>grant replication slave on replication clinet on *.* to 'replication'@'192.168.1.101(slave IP)' identified by '123456';
　　mysql>flush privileges;

　　2.5 修改Master MySQL的数据库配置文件，默认是/etc/my.cnf

　　# 开启二进制日志，并指定文件所在目录，并在服务器上创建目录和授权
　　log-bin=/var/log/mysql/master-bin

　　#增加配置
　　binlog_format=mixed

　　2.6 取消server-id注释，不能和Slave一致

　　server-id = 1

　　2.7 在[mysqld]下面添加

　　binlog-do-db=db_test  #要同步的数据库，如果有多个，则需要多写几行。

　　binlog-ignore-db=db_ignore_test #不需要同步的数据库，如果多个，则需要多写几行。

　　2.8 重启MySQL服务

　　2.9 查看Master状态

　　show master status;

　　File字段和Position字段要注意，在配置Slave时，需要指定相关值。

File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000208	1562	db_test	db_ignore_test

　　

　　3、Slave服务器配置

　　3.1 取消server-id注释，不能和Slave一致　　

　　server-id = 101

　　3.2 添加中继日志，创建日志目录，授权

　　relay-log=/var/log/mysql

　　3.3 重启mysql服务

　　3.4 查看中继日志状态

　　mysql>show global variables like '%relay%';

　　3.5 连接Master服务器

　　mysql>change master to master_host='192.168.1.100',master_user='replication',master_password='123456',master_log_file='master-bin.000208',master_log_pos=1562;

　　选项：

 

• master_host：Master 服务器IP
• master_user：Master 服务器授权用户，也就是 Master 前面创建的那个用户
• master_password：Master 服务器授权用户对应的密码
• master_log_file：Master binlog 文件名，对应查询Master服务器状态时，File字段
• master_log_pos：Master binlog 文件中的 Postion 值，对应查询Master服务器状态时，Position字段
• 更多的选项可以看:http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html　　

 

　　#其中master_log_file、master_log_pos和File、Position不对应会提示1263错误，如：Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Internal MariaDB error code: 1236

　　解决方案是：在Master服务器上，查看Master状态，根据查看结果中的File字段和Position字段。在Slave中执行，

　　stop slave;
　　change master to master_log_file='master-bin.000208',master_log_pos=1562;
　　start slave;

　　3.6 查看主从状态

　　show slave status;

　　如果Last_SQL_Error没有错误提示以及Slave中的Exec_Master_Log_Pos值和Master中的show master status中的Position值一样，这样的话，MySQL主从复制应该是成功的。

　　4、测试

　　在Master数据库中执行sql语句操作，观察Slave是否同步，如果同步则说明配置成功。

　　5、注意

　　1> 主库和从库数据库名称必须相同

　　2> 主库和从库的复制可以精确到表，但是在需要改主库或者从库的数据库结构时需要立刻重启slave

　　3> 不能在MySQL配置文件里直接写入master的配置信息，需要用change master命令来完成。

　　4> 指定replicate_do_db必须在my.cnf里配置，不能用change master命令来完成；

　　5> 如果不及时清理，日积月累二进制日志文件可能会把磁盘空间占满，可以在配置文件里加上expire_logs_days=7，只保留最近7天的日志，建议当slave不再使用时，通过reset slave来取消relaylog；
　　6> 写一个监控脚本，用来监控 Slave 中的两个"yes"，如果只有一个"yes"或者零个，就表明主从有问题

 

　　收集的几个相关知识文章：

　　监控MYSQL主从同步配置中监控从库运行状态的脚本

1. http://hzcsky.blog.51cto.com/1560073/479476/
2. http://storysky.blog.51cto.com/628458/259280
3. http://outofmemory.cn/code-snippet/3177/mysql-zhucong-library-clock-error-%EF%BC%9A-1062-Error-Duplicate-entry-1438019-for-key-PRIMARY-on-quer
4. http://www.lookingss.org/index.php/archives/3.html
5. http://369258.blog.51cto.com/359258/1345239
6. http://www.cnblogs.com/gomysql/p/3662264.html
7. http://tiany.blog.51cto.com/513694/173526
8. http://www.cnblogs.com/chenpingzhao/p/5060874.html