【mysql进阶】——主从架构（读写分离） - Go语言中文社区

【mysql进阶】——主从架构（读写分离）

一、前言

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

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功能

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@localhost ~<span style="color:#999999">]</span><span style="color:slategray"># grep log-bin /data/3306/my.cnf</span>
log-bin <span style="color:#9a6e3a">=</span> /data/3306/mysql-bin</code></span></span>

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

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@localhost ~<span style="color:#999999">]</span><span style="color:slategray"># grep server-id /data/330{6..7}/my.cnf</span>
/data/3306/my.cnf:server-id <span style="color:#9a6e3a">=</span> 1
/data/3307/my.cnf:server-id <span style="color:#9a6e3a">=</span> 3</code></span></span>

3.3 主库授权复制的用户rep

<span style="color:#333333"><span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> grant replication slave on *.* to <span style="color:#669900">'rep'</span>@<span style="color:#669900">'10.0.0.%'</span> identified by <span style="color:#669900">'123456'</span><span style="color:#999999">;</span>
Query OK, 0 rows affected <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span>
 
mysql<span style="color:#9a6e3a">></span> flush privileges<span style="color:#999999">;</span>
Query OK, 0 rows affected <span style="color:#999999">(</span>0.01 sec<span style="color:#999999">)</span></code></span></span>

3.4 主库导出备

对主数据库锁表

<span style="color:#333333"><span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> flush table with <span style="color:#dd4a68">read</span> lock<span style="color:#999999">;</span>
Query OK, 0 rows affected <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span></span>

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

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

<span style="color:#333333"><span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> show master status<span style="color:#999999">;</span>
+------------------+----------+--------------+------------------+
<span style="color:#9a6e3a">|</span> File             <span style="color:#9a6e3a">|</span> Position <span style="color:#9a6e3a">|</span> Binlog_Do_DB <span style="color:#9a6e3a">|</span> Binlog_Ignore_DB <span style="color:#9a6e3a">|</span>
+------------------+----------+--------------+------------------+
<span style="color:#9a6e3a">|</span> mysql-bin.000010 <span style="color:#9a6e3a">|</span>     5218 <span style="color:#9a6e3a">|</span>              <span style="color:#9a6e3a">|</span>                  <span style="color:#9a6e3a">|</span>
+------------------+----------+--------------+------------------+
1 row <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span></span>

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

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

 解锁，开放用户写入

<span style="color:#333333"><span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> unlock table<span style="color:#999999">;</span>
Query OK, 0 rows affected <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span></span>

3.5 从库确保server-id不同

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@localhost ~<span style="color:#999999">]</span><span style="color:slategray"># grep server-id /data/3307/my.cnf</span>
server-id <span style="color:#9a6e3a">=</span> 3</code></span></span>

3.6 主库全备导入从库

<span style="color:#333333"><span style="color:black"><code class="language-bash"><span style="color:#999999">[</span>root@localhost backup<span style="color:#999999">]</span><span style="color:slategray"># mysql -uroot -p123456 -S /data/3307/mysql.sock < rep_bak_2018-02-06.sql</span>
<span style="color:#999999">[</span>root@localhost backup<span style="color:#999999">]</span><span style="color:slategray"># mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show databases;"</span>
+--------------------+
<span style="color:#9a6e3a">|</span> Database           <span style="color:#9a6e3a">|</span>
+--------------------+
<span style="color:#9a6e3a">|</span> information_schema <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> mysql              <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> performance_schema <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> <span style="color:#dd4a68">test</span>               <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> test_dbk           <span style="color:#9a6e3a">|</span>
+--------------------+</code></span></span>

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>

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

<span style="color:#333333"><span style="color:black"><code class="language-bash">CHANGE MASTER TO
MASTER_HOST<span style="color:#9a6e3a">=</span><span style="color:#669900">'10.0.0.16'</span>,
MASTER_PORT<span style="color:#9a6e3a">=</span>3306,
MASTER_USER<span style="color:#9a6e3a">=</span><span style="color:#669900">'rep'</span>,
MASTER_PASSWORD<span style="color:#9a6e3a">=</span><span style="color:#669900">'123456'</span>,
MASTER_LOG_FILE<span style="color:#9a6e3a">=</span><span style="color:#669900">'mysql-bin.000010'</span>,
MASTER_LOG_POS<span style="color:#9a6e3a">=</span>4547<span style="color:#999999">;</span></code></span></span>

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

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

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

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

3.8 开启从库备份开关

<span style="color:#333333"><span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> start slave<span style="color:#999999">;</span>
Query OK, 0 rows affected <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span></span>

3.9 查看从库同步状态

<span style="color:#333333"><span style="color:black"><code class="language-bash">mysql<span style="color:#9a6e3a">></span> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting <span style="color:#0077aa">for</span> 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              <span style="color:slategray">#<==看到有这两个线程在的时候基本就成功了。</span>
              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        <span style="color:slategray">#<==查看是否有延迟</span>
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 <span style="color:#0077aa">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span>0.00 sec<span style="color:#999999">)</span></code></span></span>

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/

版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/weixin_40449300/article/details/89117936
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。
  • 发表于 2020-06-27 23:42:36
  • 阅读 ( 1193 )
  • 分类：架构

你可能感兴趣的文章

精选的优质文章

0 条评论

请先 登录 后评论

官方社群

GO教程

推荐文章

猜你喜欢

随便看看

来源51CTO

如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！