5.主从复制

青旅半醒 2022-05-15 10:15 192阅读 0赞

**知识点：**

1.  概念
2.  作用
3.  原理和流程
4.  实现方式
5.  实现复制
6.  复制的日志格式（3种）
7.  主从复制延迟
8.  注意

### 一.概念 ###

指将主数据库的DDL和DML操作通过二进制日志传到复制服务器上，然后在复制服务器上将这些日志文件重新执行，从而使复制服务器和主服务器的数据保持同步。复制过程中一个服务器充当主服务器（master），而一个或多个其它服务器充当从服务器（slaves）。主服务器将更新重新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器、从服务器在日志中读取的最后一次成功更新的位置。从服务器接受从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

### 二.作用 ###

*  服务切换：主库故障，可以快速切换至从库提供服务；
 *  读写分离：在从库执行查询操作，降低主库的访问压力；
 *  数据备份：在从库执行备份，避免备份期间对主库影响；

### 三.原理和流程 ###

![827392-20170621105418273-1637623522.jpg][]

1.  在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。
2.  此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容
3.  Master服务器接收到来自Slave服务器的IO线程的请求后，二进制转储IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
4.  当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（[MySQL][]\-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
5.  Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

### 四.实现方式 ###

1.  DRBD是一种用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。
2.  Mysql cluster（又称mysql簇）。Mysql replicaion（复制）本身是一个比较简单的结构，即一台从服务器（slave）从一台主服务器（master）读取二进制日志然后再解析并应用到自身。
3.  一个简单复制环境只需要两台运行mysql的主机即可，甚至可以在一台物理服务器主机上启动两个mysqld实例。一个作为master而另一个作为slave来完成复制环境的搭配。但是在实际应用环境中，可以根据实际的业务需求利用mysql复制的功能自己搭建出其他多种更利于扩展的复制架构，如最常用的主从架构。

主从架构指的是使用一台mysql服务器作为master，一台或多台mysql服务器作为slave，将master的数据复制到slave上。在实际应用场合，主从架构模式是mysql复制最常用的。一般在这种架构下，系统的写操作都是在master中进行，而读操作则分散到各个slave中进行，因此这种架构特别适合现在互联网高读写的问题。

### 五.实现复制 ###

实现复制有以下步骤：

Master：192.168.11.139  
Slave：192.168.11.130

**（1）控制主服务器操作：**

**第一步：在主服务器上开启二进制日志**

OFF表示二进制日志是关闭的

**开启日志3步骤：**

1.  打开mysql安装目录/my.cnf
2.  找到\[mysqld\]这个标签，在此标签下面一行，添加语句如下：
    
        log_bin[filename]
    
    在该语句中，log-bin说明要开启二进制文件；filename是二进制日志的名字。如果没有指定，默认为主机名后面跟-bin作为文件名，默认存放在datadir目录中。在这里指定binary\_log如果只对指定数据库生成二进制文件，则需要添加如下语句
    
        Binlog-do-db=db_name（数据库名称）
    
    如果不对指定数据库生成二进制文件日志，则需要添加如下语句
    
        Binlog-ignore-db-db_name（数据库名称）
3.  重启mysql服务。可以在mysql安装目录/data文件夹下看到“binary\_log.数字编号”文件，如binary\_log.00001.以后每重启一次mysql服务，都会重新生成二进制文件，文件名中的数字编号一次增加。

**第二步：修改mysql的配置文件my.cnf，设置server-id（MySQL配置文件一般存放在/etc/my.cnf）**

Server-id=1
    Binlog-do-db=xscj
    Binlog-ignore-db=mysql
    Server-id=1：每一个数据库服务器都要指定一个唯一的server-id，通常主服务器为1，master和slave的server-id不能相同。
    Binlog-do-db：表示需要复制的数据库，这里以xscj为例
    Binlog-ignore-db：表示不需要复制的数据库

**第三步：在master上创建复制所需要的用户**

mysql> grant replication slave on *.* to rep_user@'%';
    Query OK, 0 rows affected (0.00 sec)
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec
     
    mysql> show master status\G
    *************************** 1. row ***************************
          File: binary_log.000001
        Position: 303
      Binlog_Do_DB:
    Binlog_Ignore_DB:
    row in set (0.00 sec

**第四步：将master主机的数据备份出来，保存在/data/binary\_dump.txt文件中，然后导入到slave从机中去，具体执行语句如下**

[root@localhost bin]# mysqldump -h localhost>/data/binary_dump.txt

**（2）控制从服务器操作**

**第五步：修改从服务器的数据库配置文件，配置如下：**

Server-id=2 ##设置从服务器id
    Master-host=192.168.11.129
    Master-user=rep_user
    Master-password=  ##设置连接主服务器的密码
    Replicate-do-db ##设置你要同步的数据库，可以设置多个
    Master-port=<port> ##配置端口号

**第六步：开启复制**

重启slave，在slave主机的mysql重新执行如下命令

设置slave实现复制相关的信息，执行如下命令
    mysql> CHANGE MASTER TO 
    -> MASTER_HOST='host',
    -> MASTER_USER='user',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;
    
    输入:show slave status\G用于提供有关从服务器线程的关键参数信息。
    注意最后的两个命令：MASTER_LOG_FILE和MASTER_LOG_POS，表示从库的从哪个二进制文件开始读取，偏移量从那里开始，这两个参数可以从我们导入的SQL里面找到。

开启复制

start slave;

--------------------

这时候就完成了复制，在主库更新一个数据或者新增数据在从库都可以查询到结果。

在主库上也可以查询的到复制线程的状态。

**常用命令如下**

<table> 
 <tbody> 
  <tr> 
   <td> <p>选项</p> </td> 
   <td> <p>功能</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Slave start</p> </td> 
   <td> <p>启动复制线程</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Slave stop</p> </td> 
   <td> <p>停止复制线程</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Reset slave</p> </td> 
   <td> <p>重置复制线程</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Show slave status</p> </td> 
   <td> <p>显示复制线程状态</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Show slave status\g</p> </td> 
   <td> <p>显示复制线程状态（分行显示）</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Show master status\G</p> </td> 
   <td> <p>显示主数据库的状态（分行显示）</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Show master logs</p> </td> 
   <td> <p>显示主数据库日志</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Change master to</p> </td> 
   <td> <p>动态改变到主数据库的配置</p> </td> 
  </tr> 
  <tr> 
   <td> <p>Show processlistv</p> </td> 
   <td> <p>显示有哪些线程正在运行</p> </td> 
  </tr> 
 </tbody> 
</table>

### 六.复制的日志格式 ###

MySQL复制的日志格式有三种，根据主库存放数据的方式不同有以下三种：

<table> 
 <tbody> 
  <tr> 
   <td>复制方式</td> 
   <td>特点</td> 
   <td>优点</td> 
   <td>缺点</td> 
  </tr> 
  <tr> 
   <td>row</td> 
   <td><span style="color:#f33b45;">基于行的格式复制</span>，记录需要修改的每行的数据信息。 如果一个SQL修改了2w行的数据，那么就会记录2w行的日志格式</td> 
   <td>保证了数据的强一致性，且由于记录的是执行后的结果，在从库上执行还原也会比较快</td> 
   <td>日志记录数量很多，主从之间的传输需要更多的时间。</td> 
  </tr> 
  <tr> 
   <td>statement</td> 
   <td><span style="color:#f33b45;">基于段的日志格式复制</span>，也就是记录下更改的SQL记录，而不是更改的行的记录。</td> 
   <td>日志记录量最小。</td> 
   <td>对于一些输出结果不确定的函数，在从库上执行一遍很可能会出现问题，如uuid，从库根据日志还原主库数据的时候需要执行一遍SQL，时间相对较慢。</td> 
  </tr> 
  <tr> 
   <td>mixed</td> 
   <td><span style="color:#f33b45;">混合</span>上面两种日志格式记录记录日志，至于什么时候使用哪种日志方式由MySQL本身决定。混合上面两种日志格式记录记录日志，至于什么时候使用哪种日志方式由MySQL本身决定。</td> 
   <td>可以平衡上面两种日志格式的优缺点。</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

mysql5.7以前默认使用statement格式。

设置方式，可以在配置文件设置（首选）：

binlog_format=ROW

或临时设置全局变量（当前mysql连接有效）：

查看日志格式
    mysql > show variables like 'binlog_format';
    设置日志格式
    mysql > set binlog_format='row';

由于两个主从服务器一般都会放在同一个机房里面，两者之间同步的速度会会比较快，为保证强一致性，应该首选行的日志格式记录（row），保证传输速度可以选择混合方式（mixed）。  
而行的日志格式有下面三种记录方式：

<table> 
 <thead> 
  <tr> 
   <th>记录方式</th> 
   <th>特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>minimal</td> 
   <td>只记录被修改列的数据</td> 
  </tr> 
  <tr> 
   <td>full</td> 
   <td>记录被修改的行的全部列的数据</td> 
  </tr> 
  <tr> 
   <td>noblob</td> 
   <td>特点同上，只是如果没有修改blob和text类型的列的情况下，不会记录这些列的数据（也就是大数据列）</td> 
  </tr> 
 </tbody> 
</table>

mysql默认是full，最好修改成minimal。

binlog_row_image=minimal

### 七.主从复制延迟 ###

由于主库和从库之间不在同一个主机上，数据同步之间不可以避免地具有延迟，解决的方法有添加缓存，业务层的跳转等待，如果非得从数据库层面去减缓延迟问题，可以从复制时候的三大步骤（主库产生日志，主从传输日志，从库还原日志内容）入手：

1.  主库写入到日志的速度  
    控制主库的事务大小，分割大事务为多个小事务。  
    如插入20w的数据，改成插入多次5000行（可以利用分页的思路）
2.  二进制日志在主从之间传输时间  
    主从之间尽量在同一个机房或地域。  
    日志格式改用MIXED，且设置行的日志格式未minimal，原理详见上面的日志格式介绍。
3.  减少从库还原日志的时间  
    在MySQL5.7版本后可以利用逻辑时钟方式分配SQL多线程。  
    设置逻辑时钟：slave\_parallel\_type=‘logical\_clock';  
    设置复制线程个数：slave\_parallel\_workers=4;

### 八.注意  ###

重启MySQL最好切换未MySQL用户再进行操作，不然文件启动后会有权限问题。  
搭建好MySQL的环境后就设置好配置里的log-bin选项，这样以后如果数据库需要从库的复制，就不需要重启数据库，打断业务的进行。  
需要打开主库的防火墙的对应的mysql端口。  
由于从库同步主库的方式，监听主库发送的信息，而不是轮询，因此如果出现通信出现了故障，重新连接后如果主库没有进行数据更改的操作，从库不会同步数据，因此可以通过插入空事务的方式同步数据。

[827392-20170621105418273-1637623522.jpg]: /images/20220515/3f6347b4bd134d419b7b87371dd4b4cf.png
[MySQL]: http://lib.csdn.net/base/mysql