【MySQL】——主从架构（读写分离）

怼烎@ 2022-02-22 06:16 285阅读 0赞

### 一、前言 ###

目前，大部分的主流关系型数据库都提供了主从热备功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，**实现数据库的读写分离，从而改善数据库的负载压力**。

![master-slave][]

利用数据库的读写分离，Web服务器在写数据的时候，访问主数据库（Master），主数据库通过**主从复制机制**将数据更新同步到从数据库（Slave），这样当Web服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的Web应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是***一举两得***的方案。

### 二、主从复制原理 ###

关系型数据库的读写分离能够实现数据库的主从架构，那么主从架构中最重要的数据复制又是怎么一回事呢？MySQL作为最流行的关系型数据库之一，通过了解MySQL的数据复制流程，会使得我们对主从复制的认知会有一定的帮助。

![mysql][]

从上图来看，整体上有如下三个步凑：

（1）Master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

（2）Slave将Master的二进制日志事件(binary log events)拷贝到它的中继日志(relay log)；

> **PS：**从图中可以看出，Slave服务器中有一个I/O线程(I/O Thread)在不停地监听Master的二进制日志(Binary Log)是否有更新：如果没有它会睡眠等待Master产生新的日志事件；如果有新的日志事件(Log Events)，则会将其拷贝至Slave服务器中的中继日志(Relay Log)。

（3）Slave重做中继日志(Relay Log)中的事件，将Master上的改变反映到它自己的数据库中。

> **PS：**从图中可以看出，Slave服务器中有一个SQL线程(SQL Thread)从中继日志读取事件，并重做其中的事件从而更新Slave的数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

### 三、mysql主从架构实践    ###

**第一步：**在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制。

**第二步：**此时，Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容。

**第三步：**Master服务器接受到来自Slave服务器的I/O线程请求后，其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端I/O线程。返回的信息中除了binlog日志内容外，还有在Master服务器段记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。

**第四步：**当Slave服务器I/O线程获取到Master服务器上I/O线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（MySQL-relay-bin.xxxxxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。

**第五步：**Slave服务器段的SQL线程会实时检测本地的Relay Log中I/O线程新增加的日志内容，然后及时地把Relay Log文件中的内容解析成SQL语句，并在自身的Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句，并在relay-log.info中记录当前应用中继日志的文件名及位置点。

**3.1 开启主库binlog功能**

<code class="language-bash">[root@localhost ~]# grep log-bin /data/3306/my.cnf
 log-bin = /data/3306/mysql-bin</code>

**3.2 确保所有实例server-id不同**

<code class="language-bash">[root@localhost ~]# grep server-id /data/330{6..7}/my.cnf
 /data/3306/my.cnf:server-id = 1
 /data/3307/my.cnf:server-id = 3</code>

**3.3 主库授权复制的用户rep**

<code class="language-bash">mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> flush privileges;
 Query OK, 0 rows affected (0.01 sec)</code>

**3.4 主库导出备**

对主数据库锁表

<code class="language-bash">mysql> flush table with read lock;
 Query OK, 0 rows affected (0.00 sec)</code>

注：锁表之后窗口不能退出！！再开一个窗口登录。

查看binlog文件及位置点（--master-data=2）

新开一个shell窗口导出全备数据

解锁，开放用户写入

<code class="language-bash">mysql> unlock table;
 Query OK, 0 rows affected (0.00 sec)</code>

**3.5 从库确保server-id不同**

<code class="language-bash">[root@localhost ~]# grep server-id /data/3307/my.cnf
 server-id = 3</code>

**3.6 主库全备导入从库**

**3.7 从库找位置点，配置master.info**

在上面的show master info得到的位置信息。

<code class="language-bash">mysql-bin.000010 | 5218</code>

从库连接主库的配置如下：

<code class="language-bash">CHANGE MASTER TO
 MASTER_HOST='10.0.0.16',
 MASTER_PORT=3306,
 MASTER_USER='rep',
 MASTER_PASSWORD='123456',
 MASTER_LOG_FILE='mysql-bin.000010',
 MASTER_LOG_POS=4547;</code>

登录备库，执行如上指令：

<code class="language-bash">[root@localhost 3307]# mysql -uroot -p123456 -S /data/3307/mysql.sock
 mysql> CHANGE MASTER TO
 -> MASTER_HOST='10.0.0.15',
 -> MASTER_PORT=3306,
 -> MASTER_USER='rep',
 -> MASTER_PASSWORD='123456',
 -> MASTER_LOG_FILE='mysql-bin.000010',
 -> MASTER_LOG_POS=5218;
 Query OK, 0 rows affected (0.03 sec)
 
 mysql></code>

注：1、默认情况下是没有master.info文件的。在运行了如上命令之后，会在备库的数据目录/data/3307/data/下面生成一个master.info文件！

2、如果change错误，可以使用reset sleav all，再次重新设置！！

**3.8 开启从库备份开关**

<code class="language-bash">mysql> start slave;
 Query OK, 0 rows affected (0.00 sec)</code>

**3.9 查看从库同步状态**

<code class="language-bash">mysql> show slave status\G
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.0.0.15
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000010
 Read_Master_Log_Pos: 536031
 Relay_Log_File: relay-log.000002
 Relay_Log_Pos: 531066
 Relay_Master_Log_File: mysql-bin.000010
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes #<==看到有这两个线程在的时候基本就成功了。
 Replicate_Do_DB:
 Replicate_Ignore_DB: mysql
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 536031
 Relay_Log_Space: 531216
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: 0 #<==查看是否有延迟
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
 1 row in set (0.00 sec)</code>

Slave\_IO\_Running: Yes，这个是I/O线程状态，I/O线程负责从从库去主库读取binlog日志，并写入到从库的中继日志中，状态为Yes表示I/O线程正常工作。

Slave\_SQL\_Running: Yes，这个是SQL线程状态，SQL线程负责读取中继日志(relay-log)中的数据并转换为SQL语句应用到从数据库中，状态为Yes表示I/O线程正常工作。

Seconds\_Behind\_Master: 0，这个是在复制过程中，从库比主库延迟的秒数，这个参数很重要，但还有更准确地判断主从延迟的方法：在主库写时间戳，然后从库读取时间戳和当前数据库的时间进行比较，从而认定是否延迟。

**3.10 查看主库线程状态**

使用show processlist;命令可以查看mysql的线程状态：

线程状态说明：

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> 主库I/O线程工作状态 </td> 
 <td style="vertical-align:top;"> 解释说明 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> Sending binlog event to slave </td> 
 <td style="vertical-align:top;"> 线程已经从二进制binlog日志读取了一个事件并且正将它发送到从服务器。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> Finished reading one binlog; switching to &nbsp; next binlog </td> 
 <td style="vertical-align:top;"> 线程已经读完二进制binlog日志文件，并且正打开下一个要发送到从服务器的binlog日志文件。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> Has sent all binlog to slave; waiting for &nbsp; binlog to be updated </td> 
 <td style="vertical-align:top;"> 线程已经从binlog日志读取所有更新并已经发送到了从数据库服务器。线程现在为空闲状态，等待由主服务器上二进制binlog日志中的新事件更新。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> Waiting to finalize termination </td> 
 <td style="vertical-align:top;"> 线程停止时发生的一个很简单的状态。 </td> 
 </tr> 
 </tbody> 
</table>

### 【参考资料】 ###

1、参考资料：mysql主从架构的实现  [https://www.cnblogs.com/keerya/p/7873502.html][https_www.cnblogs.com_keerya_p_7873502.html]

2、参考资料：[https://blog.51cto.com/13178102/2082767][https_blog.51cto.com_13178102_2082767]

2、视频资料：mysql主从架构[https://www.bilibili.com/video/av23402013/][https_www.bilibili.com_video_av23402013]

[master-slave]: /images/20220222/fd5ee96938bb4c25bf6cc034848c7c06.png
[mysql]: /images/20220222/79fd8664f7a44b719424a4778e7ff37f.png
[https_www.cnblogs.com_keerya_p_7873502.html]: https://www.cnblogs.com/keerya/p/7873502.html
[https_blog.51cto.com_13178102_2082767]: https://blog.51cto.com/13178102/2082767
[https_www.bilibili.com_video_av23402013]: https://www.bilibili.com/video/av23402013/