Mysql主从原理

基本介绍

MySQL 内建的复制功能是构建大型，高性能应用程序的基础。将 MySQL 的数亿数据分布到到多个系统上去，这种分步机制，是通过将 MySQL 的某一台主机的数据复制到其它主机( Slave )上，并重新执行一遍来实现的。
复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置，从服务器接收从那时起发生的任何更新，然后封锁等等主服务器通知新的更新。

MySQL支持的复制类型

• 基于语句的复制。 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。配置：
  注释内容
  binlog_format = STATEMENT
• 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持，配置：
  binlog_format = ROW
• 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置：
  binlog_format = MIXED

Mysql同步解决的问题

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

同步是如何工作的

MySQL之间数据同步的基础是二进制日志文件（binary log file）。
一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以 事件 的方式记录在二进制日志中，
其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化同步到自己的中继日志中，
然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从同步。

可以简化为三个步骤：

1. Master 将改变记录到二进制日志中。
2. Slave 将 Master 的二进制日志拷贝到它的中继日志( Relay_log )
3. Slave 重做中继日志中的事件，将改变反映它自己的数据
   

说明:

同步实现细节分析

MySQL主从同步功能使用三个线程实现，一个在主服务器上，两个在从服务器上

(1)Binlog转储线程。

当从服务器与主服务器连接时，主服务器会创建一个线程将二进制日志内容发送到从服务器。
该线程可以使用 语句 SHOW PROCESSLIST(下面有示例介绍) 在服务器 sql 控制台输出中标识为Binlog Dump线程。

二进制日志转储线程获取服务器上二进制日志上的锁，用于读取要发送到从服务器的每个事件。一旦事件被读取，即使在将事件发送到从服务器之前，锁会被释放。

(2)从服务器I/O线程。

当在从服务器sql 控制台发出 START SLAVE语句时，从服务器将创建一个I/O线程，该线程连接到主服务器，并要求它发送记录在主服务器上的二进制更新日志。

从机I/O线程读取主服务器Binlog Dump线程发送的更新 （参考上面 Binlog转储线程 介绍），并将它们复制到自己的本地文件二进制日志中。

该线程的状态显示详情 Slave_IO_running 在输出端 使用 命令SHOW SLAVE STATUS

使用\G语句终结符,而不是分号,是为了，易读的垂直布局

这个命令在上面 查看从服务器状态 用到过

1

mysql> SHOW SLAVE STATUS\G

(3)从服务器SQL线程。

从服务器创建一条SQL线程来读取由主服务器I/O线程写入的二级制日志，并执行其中包含的事件。

在前面的描述中，每个主/从连接有三个线程。主服务器为每个当前连接的从服务器创建一个二进制日志转储线程，每个从服务器都有自己的I/O和SQL线程。
从服务器使用两个线程将读取更新与主服务器更新事件，并将其执行为独立任务。因此，如果语句执行缓慢，则读取语句的任务不会减慢。

例如，如果从服务器开始几分钟没有运行，或者即使SQL线程远远落后，它的I/O线程也可以从主服务器建立连接时，快速获取所有二进制日志内容。

如果从服务器在SQL线程执行所有获取的语句之前停止，则I/O线程至少获取已经读取到的内容，以便将语句的安全副本存储在自己的二级制日志文件中，准备下次执行主从服务器建立连接，继续同步。

使用命令 SHOW FULL PROCESSLIST\G 可以查看有关复制的信息

1
2
3
4
5
6
7
8
9
10

mysql>  SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 22
   User: repl
   Host: node:39114
     db: NULL
Command: Binlog Dump
   Time: 4435
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

Id: 22是Binlog Dump服务连接的从站的复制线程
Host: node:39114 是从服务，主机名 及端口
State: 信息表示所有更新都已同步发送到从服务器，并且主服务器正在等待更多更新发生。
如果Binlog Dump在主服务器上看不到 线程，意味着主从复制没有配置成功; 也就是说，没有从服务器连接主服务器。

命令 SHOW PROCESSLIST\G

在 Slave 从服务器 ，查看两个线程的更新状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 6
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 6810
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 7
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3069
  State: Slave has read all relay log; waiting for more updates
   Info: NULL

Id: 6是与主服务器通信的I/O线程
Id: 7是正在处理存储在中继日志中的更新的SQL线程

在 运行 SHOW PROCESSLIST 命令时，两个线程都空闲，等待进一步更新

如果在主服务器上在设置的超时，时间内 Binlog Dump线程没有活动，则主服务器会和从服务器断开连接。超时取决于的 服务器系统变量 值 net_write_timeout(在中止写入之前等待块写入连接的秒数，默认10秒)和 net_retry_count;(如果通信端口上的读取或写入中断，请在重试次数，默认10次) 设置 服务器系统变量

该SHOW SLAVE STATUS语句提供了有关从服务器上复制处理的附加信息。

同步常用类型

同步的常用体系结构基本原则

1. 每个 Slave 只能有一个 Master；
2. 每个 Slave 只能有一个唯一的服务器ID；
3. 每个 Master 可以有很多 Slave;

如果你设置了 log_slave_updates，Slave 可以是其他 Slave 的 Master，从而扩散 Master 的更新
MySQL 不支持多主服务器复制—即一个 Slave 可以有多个 Master，但是，通过一些简单的组合，我们却可以建立灵活而强大的复制体系结构。

mysql主从配置实践

实现MySQL主从复制需要进行的配置：

主服务器：

• 开启二进制日志
• 配置唯一的server-id
• 获得master二进制日志文件名及位置
• 创建一个用于slave和master通信的用户账号

从服务器：

• 配置唯一的server-id
• 使用master分配的用户账号读取master二进制日志
• 启用slave服务

具体实现过程如下：

基础环境配置

数据库版本： mysql 5.5.71 ( Slave 版本可以大于或者等于 Master版本)
操作系统： CentOS 6.7 x86_64
IP地址：192.168.18.10 ( Master ) 192.168.18.20 ( Slave )

Master-Server配置

(1)修改Master mysql配置

找到主数据库的配置文件my.cnf(或者my.ini)，如在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：

1
2
3

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

重启MySQL服务
$ service mysqld restart

(2)创建用于同步的用户账号(复制帐号)

在主服务器上为从服务器分配一个账号，就像一把钥匙，从服务器拿着这个钥匙，才能到主服务器上来共享主服务器的日志文件。

在 Master 的数据库中建立一个复制账户，每个 Slave 使用该账户连接 Master 进行复制，需要 replication slave 和 replication client 权限，Master 的连接信息会存储在文本文件 master.info 文件中。(master.info文件在 Slave 的数据目录中)

1
2

mysql>grant replication slave on *.* to 'replication'@'192.168.18.20' identified by '123456';#创建用户和分配权限
mysql>flush privileges;#刷新权限

(3)查询master的状态

查看 File(日志文件名) 和 Postition(日志地址)，下面配置 Slave 的时候需要用。执行完之后记录下这两值，然后在配置完从服务器之前不要对主服务器进行任何操作，因为每次操作数据库时这两值会发生改变

mysql>show master status;

注：执行完这个步骤后不要再操作主数据库了，防止主数据库状态值变化

Slave-Server 配置

(1)修改mysql配置

关闭slave（如果你以前配置过主从的话，一定要先关闭）
命令：stop slave;

找到从数据库的配置文件my.cnf(或者my.ini)，如/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：
（Master-Server和Slave-Server 的server-id必须不一样）

1
2
3

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=2 #设置server-id

重启MySQL服务
$ service mysqld restart

(2)执行同步命令

执行同步命令，设置主数据库ip，同步帐号密码，同步位置

1
2
3
4
5
6

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.18.10',
    ->     MASTER_USER='replication',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_LOG_FILE='mysql-bin.000002',
    ->     MASTER_LOG_POS=1169678;

选项：

• master_host：Master 服务器IP
• master_user：Master 服务器授权用户，也就是 Master 前面创建的那个用户
• master_password：Master 服务器授权用户对应的密码
• master_log_file：Master binlog 文件名
• master_log_pos：Master binlog 文件中的 Postion 值

更多的选项可以看:http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

(3)启动slave同步进程：

mysql>start slave;

(4)查看slave状态：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.10
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 11662
                Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 11765
        Relay_Master_Log_File: mysql-bin.000013
              Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
        ...

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体请看下节

其他可能用到的相关参数

master端：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  

# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game  

# 日志保留时间  
expire_logs_days = 10  

# 控制binlog的写入频率。每执行多少次事务写入一次  
# 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失  
sync_binlog = 5  

# 日志格式，建议mixed  
# statement 保存SQL语句  
# row 保存影响记录数据  
# mixed 前面两种的结合  
binlog_format = mixed

slave端：

1
2
3
4
5
6
7
8

# 停止主从同步  
mysql> stop slave;  

# 连接断开时，重新连接超时时间  
mysql> change master to master_connect_retry=50;  

# 开启主从同步  
mysql> start slave;

测试

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

注意事项

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 主从错误处理

解决和处理主从错误这个是最重要的，比配置更更要。提高处理问题的能力，要熟悉原理，多处理积累，多学习其他网友的处理方式。出现错误都会在 Last_SQL_Error 中显示错误，一般根据错误提示进行处理，如果不太清楚，可以谷歌查询一下，不过操作完之后，同步正常后，一定要核对一下数据是否一致。

以下是收集的几个处理主从问题的链接：

• http://hzcsky.blog.51cto.com/1560073/479476/
• http://storysky.blog.51cto.com/628458/259280
• https://dev.mysql.com/doc/refman/5.7/en/faqs-replication.html