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

最新推荐文章于 2024-03-03 16:11:38 发布

ColorPaper

最新推荐文章于 2024-03-03 16:11:38 发布

阅读量347

点赞数

分类专栏：微服務 MySQL

原文链接：https://blog.csdn.net/weixin_40449300/article/details/89117936

版权

MySQL 同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

微服務

16 篇文章 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）

<code class="language-bash">mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 5218 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)</code>

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

<code class="language-bash">[root@localhost backup]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events |gzip> /service/backup/rep_bak_$(date +%F).sql.gz
[root@localhost backup]# ll /service/backup/
total 144
-rw-r--r-- 1 root root 145138 Feb 6 01:04 rep_bak_2018-02-06.sql.gz</code>

解锁，开放用户写入

<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 主库全备导入从库

<code class="language-bash">[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock < rep_bak_2018-02-06.sql
[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test_dbk |
+--------------------+</code>

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

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

<span style="color:#333333"><span style="color:black"><code class="language-bash">mysql-bin.000010 <span style="color:#9a6e3a">|</span>     5218</code></span></span>

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

<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的线程状态：

mysql> show processlist;
+----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 57 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 61 | rep | 10.0.0.16:34487 | NULL | Binlog Dump | 347 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

线程状态说明：

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

【参考资料】

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

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

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

ColorPaper

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MySQL】——主从架构（读写分离）

一、前言目前，大部分的主流关系型数据库都提供了主从热备功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。　　利用数据库的读写分离，Web服务器在写数据的时候，访问主数据库（Master），主数据库通过主从复制机制将数据更新同步到从数据库（Slave），这样当Web服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的Web应用可以轻松地...
复制链接

扫一扫