MySQL主从复制

6.1.1 主从复制架构和原理

6.1.1.1 服务性能扩展方式

• Scale Up，向上扩展，垂直扩展
• Scale Out，向外扩展，横向扩展

6.1.1.2 MySQL的扩展

• 读写分离
• 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制

6.1.1.3 复制的功用

• 数据分布
• 负载均衡读
• 备份
• 高可用和故障切换
• MySQL升级测试

6.1.1.4 复制架构

一主一从复制架构

一主多从复制架构

6.1.1.5 主从复制原理

主从复制相关线程

主节点： dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events 从节点： I/O Thread：向Master请求二进制日志事件，并保存于中继日志中 SQL Thread：从中继日志中读取日志事件，在本地完成重放

跟复制功能相关的文件：

• master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
• relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系

6.1.1.6 主从复制特点

• 异步复制
• 主从数据不一致比较常见

6.1.1.7 各种复制架构

• 一Master/一Slave
• 一主多从
• 从服务器还可以再有从服务器
• Master/Master
• 一从多主:适用于多个不同数据库
• 环状复制

复制需要考虑二进制日志事件记录格式

• STATEMENT（5.0之前）
• ROW（5.1之后，推荐）
• MIXED

6.1.2 实现主从复制配置

参考官网 https://mariadb.com/kb/en/library/setting-up-replication/ https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

主节点配置： (1) 启用二进制日志

[mysqld]
log_bin

(2) 为当前节点设置一个全局惟一的ID号

[mysqld]
server-id=#
log-basename=master  #可选项，设置datadir中日志名称，确保不依赖主机名

说明：

server-id的取值范围

1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1

0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接

(3) 创建有复制权限的用户账号

GRANT REPLICATION SLAVE  ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

从节点配置： (1) 启动中继日志

[mysqld]
server_id=#     #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON        #设置数据库只读，针对supper user无效
relay_log=relay-log     #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index  #默认值hostname-relay-bin.index

(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程

CHANGE MASTER TO MASTER_HOST='masterhost', 
MASTER_USER='repluser', 
MASTER_PASSWORD='replpass',     
MASTER_LOG_FILE='mariadb-bin.xxxxxx', 
MASTER_LOG_POS=#;

START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;

范例：新建主从复制

#主节点
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
[root@master ~]#systemctl restart mariadb

[root@master ~]#mysql
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.39.%' identified by 'magedu';
#查看二进制文件和位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28052 |
| mariadb-bin.000002 |       545 |
+--------------------+-----------+
2 rows in set (0.001 sec)

#从节点
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18  

[root@slave ~]#systemctl restart mariadb
[root@slave1 ~]#mysql 
MariaDB [(none)]> help change master to
MariaDB [(none)]> CHANGE MASTER TO   MASTER_HOST='192.168.39.8',   MASTER_USER='repluser',   MASTER_PASSWORD='magedu',   MASTER_PORT=3306,   MASTER_LOG_FILE='mariadb-bin.000002',   MASTER_LOG_POS=545;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.39.8
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000002
           Read_Master_Log_Pos: 26987890
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 26987902
         Relay_Master_Log_File: mariadb-bin.000002
              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: 26987890
               Relay_Log_Space: 26988213
               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: 8
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     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
              Slave_DDL_Groups: 34
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 100006
1 row in set (0.000 sec)

主服务器非新建时

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点

• 通过备份恢复数据至从服务器
• 复制起始位置为备份时，二进制日志文件及其POS

范例：主服务器运行一段时间后，新增从节点服务器

#在主服务器完全备份
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_date +%F_%T.sql
[root@master ~]#ll /backup/
total 2988
-rw-r--r-- 1 root root 3055918 Nov 27 17:41 fullbackup_2019-11-27_17:41:17.sql
[root@master ~]#scp /backup/fullbackup_2019-11-27_17\:41\:17.sql  192.168.8.11:/data/

#优化主服务器的性能
#global innodb_flush_log_at_trx_commit=2
#sync_binlog=0

MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.001 sec)

MariaDB [hellodb]> show variables like 'sync_binlog';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| sync_binlog         | 0     |
|---------------------+-------+
5 rows in set (0.001 sec)

#将完全备份还原到新的从节点
[root@slave ~]#dnf -y install mariadb-server

[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=11
read-only

[root@slave ~]#systemctl restart mariadb
#配置从节点，从完全备份的位置之后开始复制
[root@slave ~]#grep '^CHANGE MASTER'  /data/fullbackup_2019-11-27_17\:41\:17.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
[root@slave ~]#vim /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO 
MASTER_HOST='192.168.8.10',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,                                                                          MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;

[root@slave ~]#mysql < /data/fullbackup_2019-11-27_17\:41\:17.sql
[root@slave ~]#mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: 
                   Master_Host: 192.168.8.10
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000003
           Read_Master_Log_Pos: 389
                Relay_Log_File: mariadb-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.000003
              Slave_IO_Running: No
             Slave_SQL_Running: No
               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: 389
               Relay_Log_Space: 256
               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: NULL
 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: 0
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> start slave;

6.1.3 主从复制相关

1. 限制从服务器为只读

   read_only=ON
   #注意：此限制对拥有SUPER权限的用户均无效

注意：以下命令会阻止所有用户, 包括主服务器复制的更新

FLUSH TABLES WITH READ LOCK;

2. 在从节点清除信息

   注意：以下都需要先 STOP SLAVE

RESET SLAVE    #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE  ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和 PASSWORD 等

3. 复制错误解决方法

   可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID
   
   #系统变量，指定跳过复制事件的个数
   SET GLOBAL sql_slave_skip_counter = N
   
   #服务器选项，只读系统变量，指定跳过事件的ID
   [mysqld]
   slave_skip_errors=1007|ALL

4. START SLAVE 语句，指定执到特定的点

   START SLAVE [thread_types]
   START SLAVE [SQL_THREAD] UNTIL
       MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
   START SLAVE [SQL_THREAD] UNTIL
       RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
   
   thread_types:
       [thread_type [, thread_type] ... ]
   
   thread_type: IO_THREAD | SQL_THREAD

范例：复制冲突的解决

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.39.8
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000002
           Read_Master_Log_Pos: 26988271
                Relay_Log_File: mariadb-relay-bin.000003
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 1007
                    Last_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 26988144
               Relay_Log_Space: 26988895
               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: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 1007   #错误编码
                Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 8
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 37
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 100006
1 row in set (0.000 sec)

#方法1
MariaDB [(none)]> stop slave;
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> start slave;

#方法2
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL                                                                                                
[root@slave1 ~]#systemctl restart mariadb

2. 保证主从复制的事务安全 参看https://mariadb.com/kb/en/library/server-system-variables/ 在master节点启用参数：

   sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
   #如果用到的为InnoDB存储引擎：
   innodb_flush_log_at_trx_commit=1    #每次事务提交立即同步日志写磁盘
   innodb_support_xa=ON        #分布式事务MariaDB10.3.0废除
   sync_master_info=#          #次事件后master.info同步到磁盘

   在slave节点启用服务器选项：

   skip-slave-start=ON    #不自动启动slave

   在slave节点启用参数：

   sync_relay_log=#       #次写后同步relay log到磁盘
   sync_relay_log_info=#   #次事务后同步relay-log.info到磁盘

范例：当master服务器宕机，提升一个slave成为新的master

#找到哪个从节点的数据库是最新，让它成为新master
[root@centos8 ~]#cat /var/lib/mysql/relay-log.info 
5
./mariadb-relay-bin.000002
1180
mysql-bin.000002
996
0

#新master修改配置文件，关闭read-only配置
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only=OFF
log-bin=/data/mysql/logbin/mysql-bin  

#清除旧的master复制信息
MariaDB [hellodb]>set global read_only=off;
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;

#在新master上完全备份
[root@slave1 ~]#mysqldump -A --single-transaction --master-data=1 -F > backup.sql
[root@slave1 ~]#scp backup.sql 10.0.0.28:
#分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据

#其它所有 slave 重新还原数据库，指向新的master
[root@slave2 ~]#vim backup.sql 
CHANGE MASTER TO
  MASTER_HOST='10.0.0.18',
    MASTER_USER='repluser',
      MASTER_PASSWORD='centos',
        MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=371;

MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
MariaDB [hellodb]>set sql_log_bin=off;
MariaDB [hellodb]>source backup.sql;
MariaDB [hellodb]>set sql_log_bin=on;
MariaDB [hellodb]>start slave;

6.1.4 实现级联复制

需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

[mysqld]
server-id=18
log_bin
log_slave_updates
read-only

案例：三台主机实现级联复制

#在192.168.100.8充当master
#在192.168.100.18充当级联slave
#在192.168.100.28充当slave

#在master实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin  

[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql 
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.100.%' identified by 'magedu';
[root@centos8 ~]#mysqldump -A -F --single-transaction --master-data=1  > /data/all.sql
[root@centos8 ~]#scp /data/all.sql 192.168.100.18:/data
[root@centos8 ~]#scp /data/all.sql 192.168.100.28:/data

#在中间级联slave实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
read-only
log_slave_updates  #级联复制中间节点的必选项
[root@centos8 ~]#systemctl restart mariadb

#还原数据库
[root@centos8 ~]#vim /data/all.sql
CHANGE MASTER TO
  MASTER_HOST='master节点的iP',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=523;

[root@centos8 ~]#mysql 
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /data/all.sql
MariaDB [(none)]> show master logs;  #记录二进制位置，给第三个节点使用  
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> start slave;

#在第三个节点slave上实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
read-only

[root@centos8 ~]#systemctl restart mariadb

[root@centos8 ~]#vim /data/all.sql 
CHANGE MASTER TO
MASTER_HOST='中间节点的IP',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344; 

[root@centos8 ~]#mysql < /data/all.sql
[root@centos8 ~]#mysql -e 'start slave;'

6.1.5 主主复制

主主复制：两个节点，都可以更新数据，并且互为主从 容易产生的问题：数据不一致；因此慎用 考虑要点：自动增长id

配置一个节点使用奇数id

auto_increment_offset=1        #开始点
auto_increment_increment=2      #增长幅度

另一个节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

主主复制的配置步骤： (1) 各节点使用一个惟一server_id (2) 都启动binary log和relay log (3) 创建拥有复制权限的用户账号 (4) 定义自动增长id字段的数值范围各为奇偶 (5) 均把对方指定为主节点，并启动复制线程

范例：实现两个节点的主主复制模型

#在第一个master节点上实现
[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=8
log-bin
auto_increment_offset=1         #开始点
auto_increment_increment=2      #增长幅度   

[root@master1 ~]#systemctl start mariadb
[root@master1 ~]#mysql 
MariaDB [(none)]>show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28303 |
| mariadb-bin.000002 |       386 |
+--------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.100.%' identified by 'magedu';

#在第二个master节点上实现
[rootmaster2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]                                                                                 server-id=18
log-bin
auto_increment_offset=2         #开始点
auto_increment_increment=2      #增长幅度 

[root@master2 ~]#systemctl start mariadb
[root@master2 ~]#mysql 
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.100.8',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='magedu',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000002',
    ->   MASTER_LOG_POS=386;
Query OK, 0 rows affected (0.019 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> show master logs;  #查看二进制位置
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     28303 |
| mariadb-bin.000002 |       344 |
+--------------------+-----------+
2 rows in set (0.001 sec)

#在第一个master节点上实现
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.100.18',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='magedu',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000002',
    ->   MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10));

#两个节点分别插入数据
#在第一个节点上执行
MariaDB [db1]> create database db1;
MariaDB [db1]> insert t1 (name) values('user1');
#在第二个节点上执行
MariaDB [db1]> insert t1 (name) values('user2');

#两个节点同时插入数据
MariaDB [db1]> insert t1 (name) values('userX');
MariaDB [db1]> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | userX |
|  4 | userX |
+----+-------+
4 rows in set (0.001 sec)

#两个节点同时创建数据库，发生复制冲突
MariaDB [db1]> create database db2;
MariaDB [db1]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.100.18
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000002
           Read_Master_Log_Pos: 1029
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 1110
         Relay_Master_Log_File: mariadb-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 1007
                    Last_Error: Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2'
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 897
               Relay_Log_Space: 1553
               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: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 1007
                Last_SQL_Error: Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2'
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 18
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 2
1 row in set (0.003 sec)

6.1.6 半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

半同步复制实现：

官方文档： https://mariadb.com/kb/en/library/semisynchronous-replication/

范例：CentOS 7 实现半同步复制

#主服务器配置:
INSTALL PLUGIN rpl_semi_sync_master SONAME  'semisync_master.so';
UNINSTALL PLUGIN rpl_semi_sync_master ;
SHOW PLUGINS; #查看插件
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  #超时长1s,默认值为10s
SHOW GLOBAL VARIABLES LIKE '%semi%';
SHOW GLOBAL STATUS LIKE '%semi%';

#从服务器配置:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1;

#mariadb-10.3版以后
#主服务器配置:
[mysqld]
plugin_load_add = semisync_master

#从服务器配置:
[mysqld]
plugin_load_add = semisync_slave

范例：CentOS 8 在Mariadb-10.3.11上实现 实现半同步复制

#在master实现，启用半同步功能
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步，也将返回成功信息给客户端

[root@centos8 ~]#systemctl restart mariadb
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled          | ON           |
| rpl_semi_sync_master_timeout          | 3000         |
| rpl_semi_sync_master_trace_level      | 32           |
| rpl_semi_sync_master_wait_no_slave    | ON           |
| rpl_semi_sync_master_wait_point       | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master      | OFF          |
| rpl_semi_sync_slave_enabled           | OFF          |
| rpl_semi_sync_slave_kill_conn_timeout | 5            |
| rpl_semi_sync_slave_trace_level       | 32           |
+---------------------------------------+--------------+
9 rows in set (0.002 sec)

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_get_ack               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_request_ack           | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_send_ack               | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

#在其它所有slave节点上都实现，启用半同步功能
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=18
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON  

[root@slave ~]#systemctl restart mariadb
[root@slave ~]#mysql 
MariaDB [(none)]>  SHOW GLOBAL VARIABLES  LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled          | OFF          |
| rpl_semi_sync_master_timeout          | 10000        |
| rpl_semi_sync_master_trace_level      | 32           |
| rpl_semi_sync_master_wait_no_slave    | ON           |
| rpl_semi_sync_master_wait_point       | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master      | OFF          |
| rpl_semi_sync_slave_enabled           | ON           |
| rpl_semi_sync_slave_kill_conn_timeout | 5            |
| rpl_semi_sync_slave_trace_level       | 32           |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)

MariaDB [(none)]>  SHOW GLOBAL STATUS  LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_get_ack               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_request_ack           | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_send_ack               | 0     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

MariaDB [(none)]> 

#在master上实现
MariaDB [db1]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |  #两个从节点
| Rpl_semi_sync_master_get_ack               | 4     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 4     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_request_ack           | 3     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1177  |
| Rpl_semi_sync_master_tx_wait_time          | 2355  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 2     |
| Rpl_semi_sync_slave_send_ack               | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

#测试
#在master实现，创建数据库，立即成功
MariaDB [db1]> create database db2;
Query OK, 1 row affected (0.004 sec)

#在所有slave节点实现，停止复制线程
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.011 sec)

#在master实现，创建数据库，等待3s才能成功
MariaDB [db1]> create database db3;
Query OK, 1 row affected (3.003 sec)

#在任意一个slave节点实现，恢复复制线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.006 sec)

#在master实现，创建数据库，立即成功
MariaDB [db1]> create database db4;
Query OK, 1 row affected (0.002 sec)

6.1.7 复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表 复制过滤器两种实现方式： (1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件

此方法存在的问题：基于二进制还原将无法实现；不建议使用

注意：此项和binlog_format相关 参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db

vim /etc/my.cnf
binlog-do-db    =       #数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-ignore-db    =   #数据库黑名单列表

注意：

This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
This option can not be set dynamically.
When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.

(2) 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地 此方法存在的问题：会造成网络及磁盘IO浪费

从服务器上的复制过滤器相关变量

replicate_do_db=db1,db2,db3        #指定复制库的白名单，变量可以指定逗号分隔的多个值，选项不支持多值
replicate_ignore_db=            #指定复制库黑名单
replicate_do_table=             #指定复制表的白名单
replicate_ignore_table=         #指定复制表的黑名单
replicate_wild_do_table= foo%.bar%    #支持通配符
replicate_wild_ignore_table=

When setting it dynamically with SET GLOBAL, the system variable accepts a comma-separated list of filters.
When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.

注意：跨库的更新将无法同步

MariaDB [db1]> create table db2.t1(id int);
Query OK, 0 rows affected (0.010 sec)

6.1.8 主从复制加密

基于SSL复制：在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性 官网文档：https://mariadb.com/kb/en/library/replication-with-secure-connections/

实现MySQL复制加密

1. 生成 CA 及 master 和 slave 的证书

[root@centos8 ~]#mkdir  /etc/my.cnf.d/ssl/
[root@centos8 ~]#cd  /etc/my.cnf.d/ssl/
[root@centos8 ssl]#openssl genrsa 2048 > cakey.pem
Generating RSA private key, 2048 bit long modulus (2 primes)
...+++++
..........................+++++
e is 65537 (0x010001)
[root@centos8 ssl]#openssl req -new -x509 -key cakey.pem --out cacert.pem -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu 
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:ca.magedu.org
Email Address []:

[root@centos8 ssl]#ls
cacert.pem  cakey.pem

[root@centos8 ssl]#openssl req -newkey rsa:2048 -nodes -keyout master.key > master.csr
Generating a RSA private key
..+++++
.................................................................................................+++++
writing new private key to 'master.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:master.magedu.org
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:  
[root@centos8 data]#ls
cacert.pem  cakey.pem  master.csr  master.key

[root@centos8 ssl]#openssl x509 -req -in master.csr  -CA cacert.pem -CAkey cakey.pem --set_serial 01 > master.crt
Signature ok
subject=C = CN, ST = beijing, L = beijing, O = magedu, OU = opt, CN = master.magedu.org
Getting CA Private Key
[root@centos8 ssl]#ls
cacert.pem  cakey.pem  master.crt  master.csr  master.key

[root@centos8 ssl]#openssl req -newkey rsa:2048 -nodes -keyout slave.key > slave.csr
Generating a RSA private key
.................................+++++
...............................................+++++
writing new private key to 'slave.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:slave.magedu.org
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@centos8 ssl]#
[root@centos8 ssl]#openssl x509 -req -in slave.csr  -CA cacert.pem -CAkey cakey.pem --set_serial 02 > slave.crt
Signature ok
subject=C = CN, ST = beijing, L = beijing, O = magedu, OU = opt, CN = slave.magedu.org
Getting CA Private Key
[root@centos8 ssl]#ls
cacert.pem  cakey.pem  master.crt  master.csr  master.key  slave.crt  slave.csr  slave.key

[root@centos8 ~]#chown -R mysql.mysql  /etc/my.cnf.d/ssl/

2. 主服务器开启 SSL，配置证书和私钥路径

[mysqld]
log-bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key

3. 创建一个要求必须使用 SSL 连接的复制账号

GRANT REPLICATION SLAVE ON *.* TO  'repluser'@'192.168.100.%' IDENTIFIED BY 'magedu' REQUIRE SSL;

4. 从服务器slave上使用CHANGER MASTER TO 命令时指明ssl相关选项

[root@centos8 ~]#mysql -urepluser -pmagedu -h192.168.100.8
ERROR 1045 (28000): Access denied for user 'repluser'@'192.168.100.18' (using password: YES)
[root@centos8 ~]#mysql -urepluser -pmagedu -h192.168.100.8 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key
MariaDB [(none)]> 

[root@centos8 ~]#chown -R mysql.mysql  /etc/my.cnf.d/ssl/
mysql>
CHANGE MASTER TO 
MASTER_HOST='MASTERIP', 
MASTER_USER='repluser', 
MASTER_PASSWORD='magedu',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1, 
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', 
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', 
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';

6.1.9 GTID复制

GTID复制：（Global Transaction ID 全局事务标识符） MySQL 5.6 版本开始支持，GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog文件名和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找到相应的位置开始同步

GTID 架构

GTID = server_uuid:transaction_id，在一组复制中，全局唯一 server_uuid 来源于 /var/lib/mysql/auto.cnf

GTID服务器相关选项

gtid_mode                  #gtid模式
enforce_gtid_consistency    #保证GTID安全的参数

GTID配置范例

1. 主服务器

vim /etc/my.cnf
server-id=1 
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
mysql> grant replication slave on *.* to 'repluser'@'192.168.100.%' identified by 'magedu';

2. 从服务器

vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency

mysql>CHANGE MASTER TO  MASTER_HOST='192.168.100.100',
  MASTER_USER='repluser',
  MASTER_PASSWORD='magedu',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;
mysql>start slave;

6.1.10 复制的监控和维护

(1) 清理日志

PURGE { BINARY | MASTER } LOGS   { TO 'log_name' | BEFORE  datetime_expr }
RESET MASTER TO #  #mysql 不支持
RESET SLAVE [ALL]

(2) 复制监控

SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

(3) 从服务器是否落后于主服务

Seconds_Behind_Master：0

(4) 如何确定主从节点数据是否一致 percona-toolkit (5) 数据不一致如何修复 删除从数据库，重新复制

6.1.11 复制的问题和解决方案

6.1.11.1 数据损坏或丢失

• Master：MHA + semisync replication
• Slave： 重新复制

6.1.11.2 不惟一的server id

重新复制

6.1.11.3 复制延迟

• 需要额外的监控工具的辅助
• 一从多主：mariadb10 版后支持
• 多线程复制：对多个数据库复制

6.1.11.4 MySQL主从数据不一致

1. 造成主从不一致的原因

• 主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
• 主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
• 从节点未设置只读，误操作写入数据
• 主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
• 主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
• MySQL自身bug导致

1. 主从不一致修复方法

• 将从库重新实现

虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。

• 使用percona-toolkit工具辅助

PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用

关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html

• 手动重建不一致的表

在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致

这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的

范例：A,B,C这三张表主从数据不一致

1、从库停止Slave复制
mysql>stop slave;

2、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -pmagedu -q --single-transaction --master-data=2 testdb A B C >/backup/A_B_C.sql

3、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=66666666;

4、把A_B_C.sql拷贝到Slave机器上，并做Change master to指向新位置
mysql>start slave until MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=66666666;

#以上指令是为了保障其他表的数据不丢失，一直同步，直到同步完那个点结束，A,B,C表的数据在之前的备份已经生成了一份快照，只需要导入进入，然后开启同步即可

5、在Slave机器上导入A_B_C.sql 
mysql -uroot -pmagedu  testdb 
mysql>set sql_log_bin=0;
mysql>source  /backup/A_B_C.sql
mysql>set sql_log_bin=1;

6、导入完毕后，从库开启同步即可。
mysql>start slave;

1. 如何避免主从不一致

• 主库binlog采用ROW格式
• 主从实例数据库版本保持一致
• 主库做好账号权限把控，不可以执行set sql_log_bin=0
• 从库开启只读，不允许人为写入
• 定期进行主从一致性检验

本文链接：https://www.yunweipai.com/34252.html