mysql可靠性（异步复制）

最新推荐文章于 2023-05-04 15:20:17 发布

原创最新推荐文章于 2023-05-04 15:20:17 发布 · 775 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #docker

数据库同时被 3 个专栏收录

16 篇文章

订阅专栏

mysql

7 篇文章

订阅专栏

docker

4 篇文章

订阅专栏

本文介绍MySQL异步复制机制的基本原理及其配置方法，包括主从服务的设置与启动过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql现在比较主流的关系型数据库，数据库一般是服务数据的来源，如果数据库服务失效，应用服务基本上也是不可用的，所以数据库的可靠性尤为重要。mysql内建了异步复制机制用来复制mysql中的数据以保证mysql服务的可靠性，支持版本为5.0以上。
1、复制解决的问题
在以下情况下可能需要用到mysql的复制：
（1）保证服务中的高可用，即备份。一般认为没有备份的服务是不可靠的，比如服务器硬件坏了，可能需要暂停业务很久。
（2）负载均衡，特别是对读压力比较大的应用中。
（3）控制数据的分布。
2、异步复制的原理
2.1、异步复制大致流程
mysql主服务将更新（增、删、改）写入二进制日志文件，并维护文件的一个索引以跟踪日志。这些日志会将更新记录发送到从服务并执行更新操作。当一个从服务连接主服务时，会通知主服务在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发送的所有更新。
2.2、mysql支持的复制类型
（1）基于语句复制：主服务上执行的更新语句，在从服务器上执行同样的语句。mysql默认使用基于语句复制，效率比较高。
（2）基于行复制：当发现基于语句无法精确复制是，会自动选基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
（3）混合类型的复制：（1）+（2）

2.3、主从复制过程
大体上，复制主要分为三个工作过程：
（1）master主服务将改变记录到二进制日志（binary log）中；
（2）slave从服务将master主服务中的binary log events拷贝到其中继日志（relay log）
（3）slave备服务从中继日志中获取事件，改变自己的数据。
复制过程如下图：
异步复制
（1）master主服务中的数据和slave备服务中数据一致的时候当master服务执行更新事务时：
首先master服务记录二进制日志，每个事务更新数据返回前，mysql在二进制记录这些改变。将更新修改的事务串行写入二进制日志中，大部分情况下，事务都是交叉执行的。在事务写入二进制日志后，并向slave的I/O线程发送有更新消息后，master通知存储引擎提交事务。
当slave的I/O接收到master节点发送的有数据更新后。向master请求master中binary log中的数据并拷贝到中继日志中。IO线程在master上打开一个普通的连接。然后开始binlog dump process，从master的二进制日志中读取事件。如果再无日志更新，I/O线程会进入睡眠状态。
SQL slave thread(SQL从线程）处理主从同步的最后一步，SQL线程从中继日志中读取事件，并重发其中的事件以更新slave的数据，使其和master中的数据一致。
（2）slave服务中的数据和master中的数据大大落后于。
当master服务运行一段时间后才启动slave服务，就会造成slave服务器中的数据和master中的数据落后与master。这时候slave服务的I/O线程会一直读取master服务中还没有读取过来的二进制日志中的事件到中继日志中，SQL从线程也一直根据中继日志更新数据。知道slave跟上master后，就会和（1）一样，等待master服务中的binary log更新后发消息让其接收更新数据。

另外，在master服务中，和其他mysql的连接一样，slave服务在master服务中打开的连接，master服务也会开始一个线程。这个线程是个长连接，每个slave对应一个线程，在单线程条件下，保证复制的串行化。

3、异步复制配置 —基于docker
前提条件：
（1）主从服务上的表结构必须一致，否则slave服务可能会在更新过程中出现语句执行失败。
（2）主服务上需要开启二进制日志功能。
（3）mysql版本在5.0及以上

配置步骤：
1、获取高于5.0版本的mysql镜像。

$ docker pull mysql:8.0
8.0: Pulling from library/mysql
85b1f47fba49: Pull complete
27dc53f13a11: Pull complete
095c8ae4182d: Pull complete
0972f6b9a7de: Pull complete
1b199048e1da: Pull complete
159de3cf101e: Pull complete
69f475e9e479: Pull complete
441312cc0ec5: Pull complete
66cf4618860d: Pull complete
70412d5bf270: Pull complete
6972ae417158: Pull complete
Digest: sha256:bc8c4b1dc9b7033253b5b5b8f7ac30af46a6ba49a362c84e01f86349ba68a093
Status: Downloaded newer image for mysql:8.0

2、制作mysql异步复制mater、slave服务镜像：
（1）运行下载的干净的mysql容器：

fanastic@DESKTOP-MRSQ5RC MINGW64 /bin
$ docker run -it -p 3306:3306  -e MYSQL_ROOT_PASSWORD=$MYSQL_MASTER_PASSWORD -d  mysql:8.0

(2)修改slave服务中的my.conf,主要是开启中继日志：
my.cnf:



[mysqld]
user=mysql
pid_file               = /var/lib/mysql/mysqld.pid
socket                 = /var/lib/mysql/mysql.sock
port                    = 13306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                 = /tmp
server_id               = 2
relay_log_index         = slave_relay_bin.index
relay_log               = slave_relay_bin
innodb_log_file_size         = 256M
expire-logs-days = 3

(3)制作slave镜像，将（2）步骤配置后的容器制作镜像mysql-slave

$ docker commit 容器Id mysql-slave:1.0.0
sha256:b3c6a9e6bc857c79d632dda5ea2a0adff14c2cc3bd1509a04efb5ba75d355f18

（4）再次修改正在运行的容器中的my.conf文件，开启binary log功能：

my.cnf

[mysqld]
user=mysql
pid_file               = /var/lib/mysql/mysqld.pid
socket                 = /var/lib/mysql/mysql.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                 = /tmp
log-bin                 = master-bin
log-bin-index           = master-bin.index
server_id               = 1
innodb_log_file_size         = 256M
expire-logs-days = 3

(5)创建一个复制用户：

mysql> create user repl_user2;
Query OK, 0 rows affected (0.04sec)
mysql> grant replication slave on *.* to repl_user2 identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(6)制作master镜像，将（5）步骤配置后的容器制作镜像mysql-master

$ docker commit 97f92dbdf97f mysql-master:1.0.0
sha256:b4183f8d28356cdaf44a8ec6f3f2e3c7943190f6e102254d92c67978b43bbed9

3、启动mysql-master和mysql-slave容器：

$ docker run -it -p 3306:3306  -e MYSQL_ROOT_PASSWORD=$MYSQL_MASTER_PASSWORD -d  mysql-master:1.0.0
c6f2d2d2c1c03f9535aedfc593b199bc9c63c7d0aac6d99bc8f3bb3c4e118723
$ docker run -it -p 13306:13306  -e MYSQL_ROOT_PASSWORD=$MYSQL_SLAVE_PASSWORD -d  mysql-slave:1.0.0
24c312f8df15e1223522586ab5badff892663e0eeb2a08b8869a06c1c58d4ab4

4、在slave备服务器上通过命令启动IO线程和SQL线程

mysql> change master to master_host='master主服务器IP',master_port=mysql master主服务器端口,master_user='repl_user2',master_password='配置的repl_user2的密码';
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;

5、查看各个线程是否已经启动：
（1）在slave服务上用“SHOW SLAVE STATUS\G”命令查看：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.99.100
                  Master_User: repl_user2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 887
               Relay_Log_File: slave_relay_bin.000004
                Relay_Log_Pos: 1102
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 887
              Relay_Log_Space: 1524
              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
                  Master_UUID: 66ca73c4-b318-11e7-9327-0242ac110004
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.01 sec)

有看到：

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

即可。

（2）在主从服务器查看3个相关工作线程是否存在：
1>master服务器上的Binlog Dump线程

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 21
   User: repl_user2
   Host: 172.17.0.1:42316
     db: NULL
Command: Binlog Dump
   Time: 9991
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

2>slave服务上的IO线程、SQL线程

mysql> show processlist\G
*************************** 2. row ***************************
     Id: 21
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 10134
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 22
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 10068
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
3 rows in set (0.00 sec)