MySql之主从复制

痛定思痛。 2023-05-21 13:55 3阅读 0赞

## 什么是MYSQL的主从复制 ##

主从复制是指将一个服务器作为主服务器，所有的数据更新操作都在主服务器进行，并且将主服务器的数据同步到一个或多个从服务器，保证从服务器和主服务器的数据一致。

## 为什么需要主从复制 ##

*  高可用，主从复制实现了数据的热备份，可以有效避免单点故障导致的数据丢失。
 *  高性能，可以用来实现读写分离，提高MYSQL服务的并发性能。

## MYSQL主从复制原理 ##

![原理示意图][cade37fa2431110edb5d04dc0416aae5.png]

*  主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
 *  主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
 *  从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
 *  从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。

## MYSQL主从复制的模式 ##

### 1、异步模式（默认方式） ###

![异步模式][dd89595b38527634dc79d16225c56d9b.png]

异步模式下，主节点执行完客户端提交的事务后立即提交事务并返回给客户端，并不关心 dump 线程是否成功地将将此次事务写进 bing log 并且发送给从库。假如执行事务的主线程提交事务后，log dump 线程还未来得及写入 binlog，此时系统宕机，则会造成 binglog 中没有保存刚才提交的事务，造成主从数据不一致。

优点：异步模式下，主线程不用关关心同步操作，性能最好。

缺点：可能导致主从数据的不一致。

### 2、半同步复制 ###

![半同步复制][49f7c385e0edb7ad0ce8730c5f808e2e.png]

半同步方式，当主库在执行完客户端提交的事务后不是立即提交事务，而是等待 dump 线程将此次事务同步到bin log 发送给从库，并且至少一个从库成功保存到其relay log中，此时主库的才提交事务并返回客户端。

优点：相比于异步模式，半同步方式一定程度上保证了数据同步的可靠性。

缺点：增加了主库响应客户端的延时，延时至少为一个 TCP/IP 的往返时间，即 bin log 发送给从库至收到从库的响应时间。

### 3、全同步复制 ###

全同步方式，当主库在执行完客户端提交的事务后，必须等待此次的bin log发送给从库，并且所有从库成功地执行完该事务后，主库才能返回客户端。其与半同步复制的区别如下：

*  半同步下，主库等待bin log写入到从库的relay log即可返回，全同步方式下，必须等到从库执行事务成功。
 *  半同步下，至少一个从库响应后主库即可返回客户端，全同步下必须等待所有的从库返回。

优点：对比半同步复制方式，全同步复制方式数据一致性的可靠性进一步提高

缺点：执行事务时，主库需要等待所有的从库执行成功后才能返回，所以会大大提高主库的响应时间。

## MYSQL主从复制搭建 ##

### 准备工作 ###

*  主从数据库版本最好一致
 *  主从数据库内数据保持一致
 *  主数据库：192.168.190.131 从数据库：192.168.190.132

### 配置主库Master ###

*  修改MySQL配置

mysql查看默认配置文件：mysql --help|grep 'my.cnf'，找到主数据库的配置文件my.cnf，我的在`/etc/my.cnf`

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
    
    [mysqld]
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    #
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    # Recommended in standard MySQL setup
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
    
    #主服务器唯一ID
    server-id=1
    #设置需要复制的数据库
    binlog-do-db=testdb
    #二进制日志
    #配置开启 binlog日志,日志的文件前缀为mysqlbin生成的文件名如: mysqlbin.000001, mysqlbin.000002
    log_bin=mysqlbin
    #配置二进制日志的格式
    binlog_format=STATEMENT
    #自动删除的过期天数
    expire_logs_days=3
    
    #该选项用来开启查询日志，可选值：0关闭;1开启
    general_log=1
    #设置日志的文件名,如果没有指定,默认的文件名为host_name.log
    general_log_file=mysql_query.log
    
    #该参数用来控制慢查询日志是否开启：1开启,0关闭
    slow_query_log=1
    #该参数用来指定慢查询日志的文件名
    slow_query_log_file=slow_query.log
    #该选项用来配置查询的时间限制,超过这个时间将认为是慢查询,需要进行日志记录,默认是10s
    long_query_time=3
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

*  创建用户

重启服务，创建用于同步的用户，用户：salve密码：123456

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
    GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

*  查看master状态

### 配置从库Slave ###

*  修改MySQL配置

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
    
    [mysqld]
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    #
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    # Recommended in standard MySQL setup
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
    
    #从服务器唯一ID
    server-id=2
    #启用中继日志
    relay-log=mysql-relay
    #二进制日志
    #配置开启 binlog日志,日志的文件前缀为mysqlbin生成的文件名如: mysqlbin.000001, mysqlbin.000002
    log_bin=mysqlbin
    #配置二进制日志的格式
    binlog_format=STATEMENT
    #自动删除的过期天数
    expire_logs_days=3
    
    #该选项用来开启查询日志，可选值：0关闭;1开启
    general_log=1
    #设置日志的文件名,如果没有指定,默认的文件名为host_name.log
    general_log_file=mysql_query.log
    
    #该参数用来控制慢查询日志是否开启：1开启,0关闭
    slow_query_log=1
    #该参数用来指定慢查询日志的文件名
    slow_query_log_file=slow_query.log
    #该选项用来配置查询的时间限制,超过这个时间将认为是慢查询,需要进行日志记录,默认是10s
    long_query_time=3
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

*   重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)

mysql> CHANGE MASTER TO
        -> MASTER_HOST='192.168.190.131',
        -> MASTER_USER='salve',
        -> MASTER_PASSWORD='123456',
        -> MASTER_LOG_FILE='mysqlbin.000007',
        -> MASTER_LOG_POS=2400;
    Query OK, 0 rows affected, 2 warnings (0.02 sec)

*   启动slave同步进程

mysql>start slave;

停止stop slave;重新配置主从reset master；

*  查看slave状态

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.190.131
                      Master_User: salve
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysqlbin.000007
              Read_Master_Log_Pos: 2400
                   Relay_Log_File: mysql-relay.000003
                    Relay_Log_Pos: 2277
            Relay_Master_Log_File: mysqlbin.000007
                 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: 2400
                  Relay_Log_Space: 2446
                  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: 9510d25d-7d7b-11ea-9a71-000c29980d35
                 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 the slave I/O thread to update it
               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
    1 row in set (0.00 sec)

当Slave\_IO\_Running和Slave\_SQL\_Running都为YES的时候就表示主从同步设置成功了。如果是虚拟机克隆的情况下，注意修改/var/lib/mysql/auto.cnf中的uuid

### 注意事项 ###

**master主节点**  
在主节点的二进制事件日志中仅记录与指定数据库（数据表）相关的事件日志，但是主节点的二进制日志不完整，没有记录所有对主节点的修改操作。如果要使用该方式，则在主节点的配置文件中添加如下参数:

binlog_do_db="***,***,***";      #数据库白名单列表,二进制日志记录的数据库（多数据库用逗号隔开或重复设置多行）,即需要同步的库.不在内的不同步。（不添加这行表示同步所有）
    binlog_ingore_db="***,***,***";  #数据库黑名单列表, 二进制日志中忽略的数据库 （多数据库用逗号隔开或重复设置多行）,即不需要同步,要过滤掉的库.

**slave从节点**  
从服务器SQL Thread在Replay中继日志中的事件时仅读取于特定数据库相关的事件，并应用于本地. (但是浪费I/O ,浪费带宽) 推荐使用从节点复制过滤相关设置项:

replicate_do_db ="webdb";         #复制库的白名单. 设定需要复制的数据库（多数据库使用逗号隔开或重复设置多行）
    replicate_ingore_db ="mysql";     #复制库的黑名单. 设定需要忽略的复制数据库 （多数据库使用逗号隔开或重复设置多行）
    replicate_do_table="webdb.user";  #复制表的白名单. 设定需要复制的表（多数据库使用逗号隔开或重复设置多行）
    relicate_ingore_table="webdb.uw"; #复制表的黑名单. 设定需要忽略的复制的表（多数据库使用逗号隔开或重复设置多行）
       
    replicate-wild-do-table           #同replication-do-table功能一样，但是可以通配符. 更高级别的应用，通配符,应用到哪一类表的。
    replicate-wild-ignore-table       #同replication-ignore-table功能一样，但是可以加通配符.

当在主库存在的库而从库不存在的库同步时,会出现sql错误,这时候可以排除或者从库手动导入主库数据库。`从库可以使用通配符``"库名.%"``方式过滤主从同步时某个库的设置`

replicate-wild-do-table=webdb.%      #只复制webdb库下的所有表
    replicate-wild-ignore-table=mysql.%  #忽略mysql库下的所有表

特别注意: 生产库上一般不建议设置过滤规则, 如果非要设置, 强烈建议从库使用通配符方式过滤某个库

replicate-wild-do-table= "库名.%"
    replicate-wild-ignore-table= "库名.%"

而不建议从库使用DB方式过滤某个库

replicate_do_db ="库名"
    replicate_ingore_db ="库名"

### 验证 ###

在主服务器创建testdb数据库，新建mytbl表，插入几条数据，发现从服务器也能查询到testdb数据库，mytbl表，以及表中的数据

[cade37fa2431110edb5d04dc0416aae5.png]: /images/20230521/844f6371a9a24c44974ff2dc3aabf340.png
[dd89595b38527634dc79d16225c56d9b.png]: /images/20230521/8c8220a82cda4daf8bebc0f8aa185d59.png
[49f7c385e0edb7ad0ce8730c5f808e2e.png]: /images/20230521/4c6a90024c864b30b36dcddd0cd0893b.png