mysql可靠性（异步复制）

不念不忘少年蓝@ 2022-06-07 09:45 156阅读 0赞

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备服务从中继日志中获取事件，改变自己的数据。  
复制过程如下图：  
![异步复制][SouthEast]  
（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)

[SouthEast]: /images/20220607/9395dc8d27004661b0c1468fb2ecfbe1.png