文章 78
评论 0
浏览 8355
8- MySQL 集群-主从复制

8- MySQL 集群-主从复制

6 MySQL 集群 Cluster

服务性能扩展方式

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

6.1 MySQL 主从复制

6.1.1 主从复制架构和原理

6.1.1.1 MySQL的主从复制

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

6.1.1.2 复制的功用

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

6.1.1.3 复制架构

一主一从复制架构

clipboard.png

一主多从复制架构
clipboard.png

6.1.1.4 主从复制原理

clipboard.png

主从复制相关线程

  • 主节点
    • 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日志的对应关系
  • mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

说明:

MySQL8.0 取消master.info和relay-log.info文件

6.1.1.5 主从复制特点

  • 异步复制: 客户端性能良好
  • 主从数据不一致比较常见

6.1.1.6 各种复制架构

clipboard.png

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

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

  • STATEMENT(5.0之前), Mariadb5.5 默认使用此格式
  • ROW(5.1之后,推荐),MySQL 8.0 默认使用此格式
  • MIXED: Mariadb10.3 默认使用此格式

6.1.2 实现主从复制配置

官网参考

https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
https://mariadb.com/kb/en/library/setting-up-replication/

主节点配置:

(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) 查看从二进制日志的文件和位置开始进行复制

SHOW MASTER STATUS;

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

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;

范例:新建主从复制

clipboard.png

#主节点
[10:33:48 root@master ~]#dnf install -y mysql-server
[10:34:41 root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=81
log-bin  
[10:35:17 root@master ~]#systemctl enable --now mysqld.service 
#查看二进制文件和位置
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.000001 |       179 | No        |
| master-bin.000002 |       156 | No        |
+-------------------+-----------+-----------+
#创建复制用户
MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.10.%'
identified by '123456';
#mysql8.0需要分为俩步
mysql> create user 'slave'@'192.168.10.%' identified by '123456';
mysql> grant replication slave on *.* to 'slave'@'192.168.10.%';

#从节点
[10:40:46 root@slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=82 
mysql> change master to master_host='192.168.10.81',master_user='slave',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=179;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.81
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 1341
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 1558
        Relay_Master_Log_File: master-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: 1341
              Relay_Log_Space: 1936
              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: 81
                  Master_UUID: a478643e-7e24-11eb-b07c-000c297ba8b9
             Master_Info_File: mysql.slave_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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
#查看线程
mysql> mysql> show processlist;

范例:主服务器非新建时,主服务器运行一段时间后,新增从节点服务器
clipboard.png

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

  • 通过备份恢复数据至从服务器
  • 复制起始位置为备份时,二进制日志文件及其POS
#在服务器完全备份
[11:20:13 root@master ~]#mysqldump -A -F --single-transaction --master-data=1 >all.sql
[11:20:59 root@master ~]#ll all.sql 
-rw-r--r-- 1 root root 3646553 Mar  6 11:20 all.sql
[11:21:20 root@master ~]#scp all.sql 192.168.10.83:/root
#将完全备份还原到新的从节点
[11:20:00 root@centos8 ~]#yum install -y mysql-server
[mysqld]
server-id=83
read-only    #只读数据库,对root没有效果
[11:23:58 root@centos8 ~]#systemctl enable --now mysqld

#配置从节点,从完全备份的位置之后开始复制
[11:25:07 root@centos8 ~]#vim all.sql 
CHANGE MASTER TO 
master_host='192.168.10.81',
master_user='slave',
master_password='123456',
master_port=3306,
MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=156; 
[11:29:03 root@centos8 ~]#mysql < all.sql 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.10.81
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 156
               Relay_Log_File: centos8-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
mysql> start slave;

6.1.3 主从复制相关

6.1.3.1 限制从服务器为只读

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

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

FLUSH TABLES WITH READ LOCK;

6.1.3.2 在从节点清除信息

注意:以下都需要先 STOP SLAVE

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

6.1.3.3 复制错误解决方法

可以在从服务器忽略几个主服务器的复制事件,此为global变量,或指定跳过事件的ID

注意: Centos 8.1以上版本上的MariaDB10.3主从节点同时建同名的库和表不会冲突,建主键记录会产生冲突

#系统变量,指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项,只读系统变量,指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

范例:复制冲突的解决

#CentOS7上Mariadb5.5 在slave创建库和表,再在master上创建同名的库和表,会出现复制冲突,而在
CentOS8上的Mariadb10.3上不会冲突
#如果添加相同的主键记录都会冲突
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

6.1.3.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

6.1.3.5 保证主从复制的事务安全

参看https://mariadb.com/kb/en/library/server-system-variables/

在master节点启用参数:

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

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

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

在slave节点启用参数:

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

6.1.4 实现级联复制

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

[mysqld]
server-id=18
log_bin
log_slave_updates      #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加
read-only

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

clipboard.png

#在192.168.10.81充当master
#在192.168.10.82充当级联slave
#在192.168.10.83充当slave

#在master实现
[mysqld]
server-id=81
log-bin 
[12:10:31 root@master ~]#systemctl enable --now mysqld.service
创建复制用户
mysql> create user slave@'192.168.10.%' identified by '123456';
mysql> grant replication slave on *.* to slave@'192.168.10.%';
[12:12:37 root@master ~]#mysqldump -A -F --single-transaction --master-data=1 >all.sql
[12:13:05 root@master ~]#scp all.sql 192.168.10.82:/root
[12:13:05 root@master ~]#scp all.sql 192.168.10.83:/root

#在中间级联slave实现

[mysqld]
server-id=82
log-bin
read-only
log_slave_updates #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加
[12:16:08 root@slave1 ~]#systemctl start mysqld
[12:16:35 root@slave1 ~]#vim all.sql
CHANGE MASTER TO 
master_host='192.168.10.81',
master_user='slave',
master_password='123456',
master_port=3306,
MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=156;
[12:24:44 root@slave1 ~]#mysql
mysql> set sql_log_bin=0;     #先关闭二进制日志
mysql> source /root/all.sql
mysql> flush PRIVILEGES;      #备份恢复完成后权限得手动生效
mysql> show master logs;      #记录二进制日志,给第三个节点用
mysql> set sql_log_bin=1;     #开启二进制日志
mysql> start slave;

#在第三个节点slave上实现
[12:18:55 root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=83
read-only 
[12:19:37 root@slave2 ~]#systemctl enable --now mysqld
[12:20:04 root@slave2 ~]#vim all.sql
CHANGE MASTER TO 
master_host='192.168.10.82',
master_user='slave',
master_password='123456',
master_port=3306,
MASTER_LOG_FILE='slave1-bin.000002', MASTER_LOG_POS=156;  
[12:26:12 root@slave2 ~]#mysql -e 'start slave'
[12:26:27 root@slave2 ~]#mysql -e 'show slave status\G'

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) 均把对方指定为主节点,并启动复制线程

6.1.6 半同步复制

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

clipboard.png

半同步复制实现:

官方文档:

https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
https://mariadb.com/kb/en/library/semisynchronous-replication/

范例: CentOS8 在MySQL8.0 实现半同步复制

#查看插件文件
[12:31:41 root@master ~]#rpm -ql mysql-server | grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
#master服务器配置
[14:03:27 root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
rpl_semi_sync_master_enabled=ON       #修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动
rpl_semi_sync_master_timeout=3000     #设置3s内无法同步,也将返回成功信息给客户端

#slave1服务器配置
[12:31:51 root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
rpl_semi_sync_slave_enable=ON    #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

#slave2服务器配置
[14:10:57 root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
rpl_semi_sync_slave_enabled=ON

#主服务器配置:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql>UNINSTALL PLUGIN rpl_semi_sync_master ;    #卸载
mysql> show plugins;                             #查看插件
mysql> set global rpl_semi_sync_master_enabled=1;      #临时修改变量
mysql> set global rpl_semi_sync_master_timeout = 3000; #超时长1s,默认值为10s

mysql> 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_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |     #这个客户端个数需要配置从服务器后查看
| 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_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     |
+--------------------------------------------+-------+

#从服务器配置:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

#注意:如果已经实现主从复制,需要stop slave;start slave;
mysql> stop slave;
mysql> start slave;
mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

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

在MariaDB 10.3.3和更高版本中,半同步复制不由插件提供直接集成到mariadb系统中无需安装直接使用。其他版本还需要手动加载。

#先自行实现主从同步

#在master实现,启用半同步功能
[mysqld]
server-id=81
log-bin
plugin-load-add = semisync_master   #加载插件,
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
[14:45:09 root@master ~]#systemctl enable --now mariadb.service
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.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                | 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   |
+--------------------------------------------+-------+

#在其它所有slave节点上都实现,启用半同步功能
[mysqld]
server-id=82
log-bin
read-only
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON
[14:58:06 root@slave2 ~]#systemctl enable --now mariadb.service
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           |
+---------------------------------------+--------------+
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               | 1     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+

6.1.7 复制过滤器

让从节点仅复制指定的数据库,或指定数据库的指定表

复制过滤器两种实现方式:

(1) 服务器选项:主服务器仅向二进制日志中记录与特定数据库相关的事件

缺点:基于二进制还原将无法实现;不建议使用

优点: 只需要在主节点配置一次即可

注意:此项和 binlog_format相关

参看:https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db

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

(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=

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

范例:

[mysqld]
replicate_do_db=db1
replicate_do_db=db2
replicate_do_db=db3

范例: 通过二进制日志服务器选项实现过滤器

[mysqld]
server-id=8
log-bin
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=hellodb

范例: 通过系统变量实现过滤器

MariaDB [(none)]> set global replicate_do_db='db1,hellodb';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> select @@replicate_do_db;
+-------------------+
| @@replicate_do_db |
+-------------------+
| db1,hellodb       |
+-------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> start slave;

6.1.8 主从复制加密

在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的,外网里访问数据或则复制,存在安全隐患。

通过SSL/TLS加密的方式进行复制的方法,来进一步提高数据的安全性

官网文档:https://mariadb.com/kb/en/library/replication-with-secure-connections/

实现MySQL复制加密

  1. 生成 CA 及 master 和 slave 的证书
[15:28:33 root@master ~]#mkdir /etc/my.cnf.d/ssl/
[15:29:16 root@master ~]#cd /etc/my.cnf.d/ssl/
[15:29:26 root@master ssl]#openssl genrsa 2048 > cakey.pem
[15:29:45 root@master ssl]#openssl req -new -x509 -key cakey.pem --out cacert.pem
-days 3650
[15:33:23 root@master ssl]#openssl req -newkey rsa:2048 -nodes -keyout master.key >master.csr
[15:34:25 root@master ssl]#ls
cacert.pem  cakey.pem  master.csr  master.key
[15:35:48 root@master ssl]#openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem --set_serial 01 >master.crt
[15:36:41 root@master ssl]#ls
cacert.pem  cakey.pem  master.crt  master.csr  master.key
[15:36:46 root@master ssl]#openssl req -newkey rsa:2048 -nodes -keyout slave.key >slave.csr
[15:38:16 root@master ssl]#openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem --set_serial 02 >slave.crt
[15:38:46 root@master ssl]#ls
cacert.pem  master.crt  master.key  slave.csr
cakey.pem   master.csr  slave.crt   slave.key
[15:39:06 root@master ssl]#chown -R mysql: /etc/my.cnf.d/ssl
#如果上面没修改权限,会出现ssl无法启动和下面日志错误
MariaDB [(none)]> show variables like '%ssl%';
+---------------------+----------------------------------+
| Variable_name       | Value                           |
+---------------------+----------------------------------+
| have_openssl       | YES                             |
| have_ssl           | DISABLED    #无法启用,正常启用为YES                     |
| ssl_ca             | /etc/my.cnf.d/ssl/cacert.pem     |
| ssl_capath         |                                 |
| ssl_cert           | /etc/my.cnf.d/ssl/master.crt     |
| ssl_cipher         |                                 |
| ssl_crl             |                                 |
| ssl_crlpath         |                                 |
| ssl_key             | /etc/my.cnf.d/ssl/master.key     |
| version_ssl_library | OpenSSL 1.1.1c FIPS  28 May 2019 |
+---------------------+----------------------------------+
10 rows in set (0.001 sec)

[root@centos8 ~]#cat /var/log/mariadb/mariadb.log
2020-10-12 17:55:35 0 [Warning] Failed to setup SSL
2020-10-12 17:55:35 0 [Warning] SSL error: Unable to get private key
2020-10-12 17:55:35 0 [Warning] SSL error: error:0200100D:system
library:fopen:Permission denied
2020-10-12 17:55:35 0 [Warning] SSL error: error:20074002:BIO
routines:file_ctrl:system lib
2020-10-12 17:55:35 0 [Warning] SSL error: error:140B0002:SSL
routines:SSL_CTX_use_PrivateKey_file:system lib
  1. 主服务器开启 SSL,配置证书和私钥路径
[mysqld]
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
[15:41:16 root@master ~]#systemctl restart mariadb.service
MariaDB [(none)]> show variables like '%ssl%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | YES                              |
| have_ssl            | YES                              |
| ssl_ca              | /etc/my.cnf.d/ssl/cacert.pem     |
| ssl_capath          |                                  |
| ssl_cert            | /etc/my.cnf.d/ssl/master.crt     |
| ssl_cipher          |                                  |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             | /etc/my.cnf.d/ssl/master.key     |
| version_ssl_library | OpenSSL 1.1.1g FIPS  21 Apr 2020 |
+---------------------+----------------------------------+
10 rows in set (0.001 sec)
#如需要客户端工具连接此用户也必须指定密钥
[mysql]
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
  1. 创建一个要求必须使用 SSL 连接的复制账号
#ssl账户
MariaDB [(none)]> create user 'slave'@'192.168.10.%' identified by '123456' require ssl;
MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.10.%';
[15:55:28 root@master ~]#scp -r /etc/my.cnf.d/ssl 192.168.10.82:/etc/my.cnf.d/
[15:55:28 root@master ~]#scp -r /etc/my.cnf.d/ssl 192.168.10.83:/etc/my.cnf.d/
  1. 从服务器slave上使用CHANGER MASTER TO 命令时指明ssl相关选项
[15:52:26 root@slave1 ~]#mysql -uslave -p123456 -h192.168.10.81
ERROR 1045 (28000): Access denied for user 'slave'@'192.168.10.82' (using password: YES)
[15:57:15 root@slave1 ~]#chown -R mysql: /etc/my.cnf.d/ssl
#可选方式1
[mysqld]
ssl
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)]>CHANGE MASTER TO
MASTER_HOST='192.168.10.81',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mariadb-bin.000005',
MASTER_LOG_POS=389,
MASTER_SSL=1;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> start slave;

#可选方式2
#需修改配置文件,执行下面命令即可
MariaDB [(none)]>CHANGE MASTER TO
MASTER_HOST='192.168.10.81',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mariadb-bin.000005',
MASTER_LOG_POS=389,
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';

#启动后查看三个参数是否为yes
MariaDB [(none)]> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Allowed: Yes

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 架构
clipboard.png

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'@'10.0.0.%' identified by
'magedu';
  1. 从服务器
vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency
mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.100',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
mysql>start slave;

6.1.10 复制的监控和维护

6.1.10.1 清理日志

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

6.1.10.2 复制监控

SHOW MASTER STATUS     #当前正在使用的二进制日志
SHOW BINARY LOGS       #当前主机中所有的二进制日志
SHOW BINLOG EVENTS     #显示二进制所有日志中每个事务
SHOW SLAVE STATUS      #从节点信息查看
SHOW PROCESSLIST       #查看当前主机中已经连接的线程

6.1.10.3 从服务器是否落后于主服务

MariaDB [(none)]> show slave status\G
Seconds_Behind_Master:0

6.1.10.4 如何确定主从节点数据是否一致

percona-toolkit工具

1.检查数据库是否一致

pt-table-checksum --no-check-binlog-format  h=127.0.0.1,u=pt,P=3306

参数说明:
--nocheck-replication-filters:不检查复制的过滤规则,比如replicate-ignore-db、replicate-wild-do-table。
--no-check-binlog-format:不检查复制的binlog模式,如果binlog模式是row模式,需要启用该参数。
--create-replicate-table:第一次进行checksum需要启用该参数,会进行checksum表的创建,用于存放结果。
--replicate=test.checksums:存放checksum结果的表。
--databases:表示要检查的库。
--tables(-t):表示要检查的表。
--replicate-check-only:表示只显示不同步的表。
--recursion-method:正常情况下工具会自动识别从库,如果识别失败,可以用该参数 指定查找slave的方法,参数有四种,分别是processlist、hosts、dsn=DSN、no四种,用来决定查找slave的方式是通过show processlist、show slave hosts还是通过dsn=DSN的方式。

结果说明:
TS            ERRORS  DIFFS    ROWS  DIFF_ROWS  CHUNKS SKIPPED  TIME TABLE09-04T22:29:52    0      0       1         0       1         0   0.029  test.dsns
TS:完成检查的时间
ERRORS:错误和告警的数量。
DIFFS:是否一致,0代表一致,1代表不一致。
ROWS:表的行数
DIFF_ROWS:CHUNKS:划分的块的数目
SKIPPED:跳过的块的数目
TIME:执行时长
TABLE:表名

2.修复或查看那个语句有问题

#当有多个DSN时,如果指定了--sync-to-master,那么所有的主机均为从库。否则报错:
Can't determine master of D=test,h=…..,p=...,t=t1,u=syncuser at /usr/bin/pt-table-sync line 10020.
#在主节点执行,指定了--sync-to-master本机就是主,所有地址当中都是从写从的信息--print只是打印什么语句可以修复
[17:44:02 root@master ~]# pt-table-sync --sync-to-master --databases=hellodb h=192.168.10.82,u=pt,P=3306 h=192.168.10.83,u=pt,P=3306 --print

参数说明:
--execute: 指定工具执行变更操作,使表数据达成一致。
--print: 打印出工具需要执行哪些语句来变更表。

6.1.10.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 主从数据不一致

造成主从不一致的原因

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

主从不一致修复方法

  • 将从库重新实现

    • 虽然这也是一种解决方法,但是这个方案恢复时间比较慢,而且有时候从库也是承担一部分的查询操作的,不能贸然重建。
  • 使用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=666666;
   #以下指令是为了保障其他表的数据不丢失,一直同步直到那个点结束,A,B,C表的数据在之前的备份已
   经生成了一份快照,只需要导入进入,然后开启同步即可
4. 把A_B_C.sql拷贝到Slave机器上,并做指向新位置
   mysql>start slave until MASTERLOGFILE='mysql-bin.888888',
   MASTERLOGPOS=666666;
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;

如何避免主从不一致

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

标题:8- MySQL 集群-主从复制
作者:Carey
地址:HTTPS://zhangzhuo.ltd/articles/2021/03/07/1615115715082.html

生而为人

取消