一、主从复制原理

MySQL 主从复制延迟 开启多线程复制 mysql主从复制多久一次_SSL


slave节点会从master节点读取binlog来进行数据同步。主要有以下三个步骤：

  • master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件（binary log events）。
  • slave将master的binary log events拷贝到中继日志（relay log）。
  • slave重做中继日志中的事件，将改变应用到自己的数据库中。

MySQL的复制是异步且串行化的。在主从复制过程中，最大的问题就是延时。

在MySQL主从复制中：

  • 每个slave只能有一个master。（一对一）
  • 每个slave只能有一个唯一的服务器ID。
  • 每个master可以有多个slave。（一对多）

二、一主一从

1. 准备工作

在CentOS7中安装MySQL5.7.33，参考

server1（主）

server2（从）

192.168.30.130

192.168.30.131

注：如果2台主机是通过快照复制得到的，需要将mysql的uuid修改为不一致。路径为/var/lib/mysql/auto.cnf

2. 修改主节点配置
（1） 修改/etc/my.cnf,在[mysqld]下面添加如下配置：
server_id = 1
log_bin=mysql-bin
#binlog三种模式：row，statement，mixed
binlog_format = statement
#不同步的数据库,可以配置多个
binlog-ignore-db = mysql
binlog-ignore-db=information_schema
# 自动清理30天之前的log文件
expire_logs_days = 30
（2）重新启动mysql服务
systemctl restart mysqld
（3）创建slave账号并授权（简单密码需要修改密码校验规则）：
mysql> create user slave@'192.168.30.131' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to slave@'192.168.30.131';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
（4）查看master状态：
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000005 |      154 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
3. 修改从节点配置
（1） 修改/etc/my.cnf,在[mysqld]下面增加：
server_id=2
relay_log=mysql-relay-bin
# 只读
read_only=1

其中server_id要和主节点不同。

（2）重启mysql服务
systemctl restart mysqld
(3) 配置从节点

登录mysql，执行：

mysql>  change master to master_host='192.168.30.130',master_user='slave',master_password='slave',master_log_file='mysql-bin.000005' ,master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

host为server1的ip，user和password为主节点授权的slave账号和密码，log_file和log_pos为主节点master状态的参数。

启动从服务器复制功能：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器状态：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             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: 154
              Relay_Log_Space: 527
              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: 3af7a0e7-983f-11eb-a78d-000c29cf1c04
             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.00 sec)

其中Slave_IO_Running和Slave_SQL_Running都是Yes，表面主从复制配置成功。

此时，在主节点创建数据库和表，插入数据，可以在从服务器中查询到相关变化。

4. 关闭主从复制

在master节点：

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

在salve节点：

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

三、双主双从

MySQL 主从复制延迟 开启多线程复制 mysql主从复制多久一次_数据库_02


在双主双从架构中，2台主服务器互为主从。

（一）准备工作

角色

IP

主机

master1

192.168.30.130

server1

slave1

192.168.30.131

server2

master2

192.168.30.132

server3

slave2

192.168.30.133

server4

注：现将一主一从中的测试库test删除

（二）修改主节点配置
配置master1:
  1. 修改/etc/my.cnf，[mysqld]下面的配置：
server_id = 1
log_bin=mysql-bin
#binlog三种模式：row，statement，mixed
binlog_format = statement
#不同步的数据库,可以配置多个
binlog-ignore-db = mysql
binlog-ignore-db=information_schema
# 自动清理30天之前的log文件
expire_logs_days = 30
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates

#表示自增长字段每次递增的量，字段一次递增多少，默认为1
auto-increment-increment = 2
#表示自增长字段从哪个数开始，指自增字段的启始值
auto-increment-offset = 1

重启mysql服务：

systemctl restart mysqld
  1. 对slave1和master2分别创建slave账号并授权（简单密码需要修改密码校验规则）：
mysql> create user slave@'192.168.30.131' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to slave@'192.168.30.131';
Query OK, 0 rows affected (0.01 sec)

mysql> create user slave@'192.168.30.132' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to slave@'192.168.30.132';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  1. 查看master状态：
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 |      452 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
配置master2:
  1. 修改/etc/my.cnf，[mysqld]下面的配置：
server_id = 3
log_bin=mysql-bin
#binlog三种模式：row，statement，mixed
binlog_format = statement
##不同步的数据库,可以配置多个
binlog-ignore-db = mysql
binlog-ignore-db=information_schema
## 自动清理30天之前的log文件
expire_logs_days = 30
##在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
##表示自增长字段每次递增的量，字段一次递增多少，默认为1
auto-increment-increment = 2
##表示自增长字段从哪个数开始，指自增字段的起始值
auto-increment-offset = 2

重启mysql服务：

systemctl restart mysqld
  1. 对slave2和master1分别创建slave账号并授权（简单密码需要修改密码校验规则）：
mysql> create user slave@'192.168.30.130' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to slave@'192.168.30.130';
Query OK, 0 rows affected (0.01 sec)

mysql> create user slave@'192.168.30.133' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to slave@'192.168.30.133';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  1. 查看master状态：
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |     3480 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
（二）修改从节点配置
1. 配置slave1
  1. 修改/etc/my.cnf,在[mysqld]下面增加：
server_id=2
relay_log=mysql-relay-bin
# 只读
read_only=1

重启mysql服务

systemctl restart mysqld

登录mysql，执行：

mysql>  change master to master_host='192.168.30.130',master_user='slave',master_password='slave',master_log_file='mysql-bin.000003' ,master_log_pos=452;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

host为master1的ip，user和password为主节点授权的slave账号和密码，log_file和log_pos为master1主节点的参数。

启动从服务器复制功能：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器状态：

mysql> show slave status \G;
2. 配置slave2
  1. 修改/etc/my.cnf,在[mysqld]下面增加：
server_id=4
relay_log=mysql-relay-bin
# 只读
read_only=1

重启mysql服务

systemctl restart mysqld

登录mysql，执行：

mysql>  change master to master_host='192.168.30.132',master_user='slave',master_password='slave',master_log_file='mysql-bin.000001' ,master_log_pos=3480;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

host为master2的ip，user和password为主节点授权的slave账号和密码，log_file和log_pos为master2主节点的参数。

启动从服务器复制功能：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器状态：

mysql> show slave status \G;
3. 配置master1（作为master2的slave）

登录mysql，执行：

mysql>  change master to master_host='192.168.30.132',master_user='slave',master_password='slave',master_log_file='mysql-bin.000001' ,master_log_pos=3480;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

host为master2的ip，user和password为主节点授权的slave账号和密码，log_file和log_pos为master2主节点的参数。

启动从服务器复制功能：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器状态：

mysql> show slave status \G;
配置master2（作为master1的slave）

登录mysql，执行：

mysql>  change master to master_host='192.168.30.130',master_user='slave',master_password='slave',master_log_file='mysql-bin.000003' ,master_log_pos=452;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

host为master1的ip，user和password为主节点授权的slave账号和密码，log_file和log_pos为master1主节点的参数。

启动从服务器复制功能：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看从服务器状态：

mysql> show slave status \G;
（三）测试

在master1建库建表，并插入数据，可以在4个节点查询到相关数据。