mysql复制集群

mysql复制集群实验,包括主从复制、级联主从复制、级联主从半同步复制、gtid复制、复制加密等...

mysql分库分表

分库

业务涉及较大的表时,数据表分库方法:

  • 垂直切分
  • 水平切分

垂直

将存放不同业务数据的表,分到不同数据库中,且不同的数据库可以是在不同的数据库实例中,如下:

image-20200928162959704

水平

按照时间或地区等标准做水平切分,如电商的交易数据,按照不同的省市地区或时间划分,分别存储不同于不同的库,(也可以是在不同的实例)

image-20200928163206599

分表

一张表数据量较大时,数据表切分方法:

  • 垂直切分
  • 水平切分

垂直

对于数据量较大的表,可以将不常用字段切分,单独放在一个表中

水平

对于数据量较大的表,可以按照时间,id取模等方式做水平的切分,分别存储于不同的数据表中;

路由

分库分表之后,不同于单实例单库单表的查询,需要有一个sql路由,在php或java等发起数据读写请求时,根据某些条件,将查询路由到一个个被分开的数据库实例上;

路由本身也要做高可用;

类似dns查询,可以选择迭代或递归查询2种方式;

可以做mysql路由中间件的有,mysql router、mycat、proxysql

mysql常用复制架构

  • 主从复制
  • 级联主从复制
  • 主主复制
  • 半同步复制
  • 基于gitd的复制
  • 复制相关配置
    • 复制过滤器
    • 复制加密
    • 复制集群的监控与维护
  • 读写分离
    • 常用的读写分离路由软件
    • proxy sql

主从复制

https://mariadb.com/kb/en/setting-up-replication/

https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html

主从架构图

  1. 主节点数据更新时,相关操作会写入binlog
  2. 主节点开启slave线程将自己的binlog发送给从节点的io线程
  3. 从节点io线程接收到主节点的binlog之后,写入自己本地的中继日志,本质也算是binlog,
  4. 从节点的sql线程,读取本地relay log中继日志,应用到数据库中,形成和主库一样的数据更新

image-20200928161748067

相关线程

主节点:

dump thread:主节点会为每一个从节点启动一个dump线程,发送自己的binlog

master.info:保存了各个从节点连过来时的信息,如账户,密码,ip等

从节点:

io thread:向主节点请求binlog,并接收到本地,保存为relay log

sql thread:从relay log中读取日志中sql,在本地重放

relay-log.info:保存了当前salve节点已经复制的binlog和本地relay log的对应关系

主从结构特点

  1. 异步复制:主从数据同步有延时,数据不一致
  2. 一主多从
  3. 一从多主:一个实例做多个主节点的从
  4. 级联复制
  5. 主主
  6. 环形复制
  7. 复制时,binlog选择的格式:row,statements,mixed

image-20200928165418205

配置过程

主节点

  1. 启用binlog
  2. 设置唯一的server_id
  3. 创建具有复制数据权限的账户
  4. 在创建了用户后再备份,这样从库恢复后就带有该用户,不必再手动创建
  5. 创建2个库,模拟备份后数据修改;
[mysqld]
...
log-bin=master-bin
server_id=1
[root@host2 ~]# service mysqld restart

MariaDB [(none)]> grant replication slave on *.* to repl@'192.168.80.102' identified by 'wang';
Query OK, 0 rows affected (0.00 sec)


[root@host2 ~]#  mysqldump -uroot -pwangbo  -A -F -E -R --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > /root/`date +%F`.sql
[root@host2 ~]# ll
total 492
-rw-r--r--  1 root root 490508 Sep 28 18:17 2020-09-28.sql

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database test2;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db3                |
| db4                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test1              |
| test2              |
+--------------------+

从节点

  1. 设置全局唯一server_id
  2. 设置数据库只读
  3. 设置relay log路径
  4. 设置relay log 的index文件
  5. 用主节点全备sql文件恢复
  6. 在从节点用change master命令配置主节点的信息,如ip,端口,刚刚创建的账户等;(此时从节点就配置好了主节点信息)
  7. 但从节点相关线程还没开启,所有主从同步没开始;
  8. 从节点启动2个相关线程,
  9. 检查线程状态,查看同步是否正常;
# The MariaDB server
[mysqld]
server_id=2
read_only=on
relay_log=relay-log
relay_log_index=relay-log.index

[root@host3 ~]# vim /etc/my.cnf
[root@host3 ~]# service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]
# 设置后重启服务器

# 应用全备sql,从节点恢复到了主节点备份时刻状态;
[root@host3 ~]# mysql < 2020-09-28.sql 
[root@host3 ~]# mysql -uroot -pwangbo
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db3                |
| db4                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

# 此时还没有slave线程信息;
MariaDB [(none)]> show slave status;
Empty set (0.00 sec)

# 在从节点上配置主节点信息,ip,端口,从哪个位置应用binlog等
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.80.101',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='wang',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000011', 
    ->   MASTER_LOG_POS=385,
    ->   MASTER_CONNECT_RETRY=10;
    
# 此时因为2个线程没启动,尚未开始同步    
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.80.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 385
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: No
            Slave_SQL_Running: No

# 启动2个线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

# 错误:因为主从节点一致,配置文件了设置了没生效;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.80.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 385
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
         Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
               Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it


# 命令行临时修改生效了
MariaDB [(none)]> set global server_id=22;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like '%server%';
+---------------------------------+-----------------+
| Variable_name                   | Value           |
+---------------------------------+-----------------+
| character_set_server            | utf8            |
| collation_server                | utf8_general_ci |
| innodb_ft_server_stopword_table |                 |
| server_id                       | 22              |


# 重启slave的2个线程;
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 342
               Relay_Log_File: relay-log.000005
                Relay_Log_Pos: 641
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

# 此时已经和主节点数据一致了;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db3                |
| db4                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test1              |
| test2              |
+--------------------+

# 主节点数据修改测试
# 主节点删除2个库
MariaDB [(none)]> drop database db4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> drop database db3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test1              |
| test2              |
+--------------------+

# 从节点也随之删除,此时主从节点已经成功同步;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

serverid设置不生效原因

# 打印mysqld的启动参数发现,有2个server-id,
[root@host3 ~]# mysqld --print-defaults
mysqld would have been started with the following arguments:
--server_id=2 --read_only=on --relay_log=relay-log --relay_log_index=relay-log.index --port=3306 --socket=/data/mysql/mysql.sock --skip-external-locking --key_buffer_size=256M --max_allowed_packet=1M --table_open_cache=256 --sort_buffer_size=1M --read_buffer_size=1M --read_rnd_buffer_size=4M --myisam_sort_buffer_size=64M --thread_cache_size=8 --query_cache_size=16M --thread_concurrency=8 --datadir=/data/mysql --skip_name_resolve=on --log-bin=mysql-bin --binlog_format=mixed --server-id=1 

# 检查配置文件发现,原本配置文件默认有一个server-id=1,后来自己添加的server-id就没生效,将其注释,手动添加的server-id就会生效;
[root@host3 ~]# vim /etc/my.cnf
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id      = 1

[root@host3 ~]# service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]
[root@host3 ~]# mysqld --print-defaults
mysqld would have been started with the following arguments:
--server_id=2 --read_only=on --relay_log=relay-log --relay_log_index=relay-log.index --port=3306 --socket=/data/mysql/mysql.sock --skip-external-locking --key_buffer_size=256M --max_allowed_packet=1M --table_open_cache=256 --sort_buffer_size=1M --read_buffer_size=1M --read_rnd_buffer_size=4M --myisam_sort_buffer_size=64M --thread_cache_size=8 --query_cache_size=16M --thread_concurrency=8 --datadir=/data/mysql --skip_name_resolve=on --log-bin=mysql-bin --binlog_format=mixed 

主从复制注意点

  • 从服务器应该只读,避免有写操作,避免主从数据不一致:read-only=on
  • reset slave
  • reset salve all清楚所有从服务器信息
  • sql_slave_skip_counter=N,从服务器忽略N个主服务器的复制事件,避免因几个sql错误阻塞了后续的主从复制;
  • 主节点数据量已经较大时,可以先用mysqldump备份出来,恢复到从节点,再从change master记录的binlog位置开始做主从同步,上步实验即是如此;

主从复制中事务安全参数设置(一般是刷盘频率相关参数设置,)

主节点:

sync_binlog=1 每次写后同步二进制日志到磁盘,性能差

结合innodb引擎时:
innodb_flush_log_at_trx_commit=1 每次事务提交后同步日志到磁盘
sync_master_info=N N次事务后master.info同步到磁盘

从节点:

skip_slave_start=on 
sync_relay_log=N
sync_relay_log_info=N 

级联主从复制

一主多从架构时,主节点会为每个从节点都开启一个dump线程,来发送自己的binlog,从节点较多的情况下,会加大主节点的压力,因此更合理的做法为,一主挂一从,再由从节点向其他从节点进行复制,以此减轻主节点的压力;

实验步骤:

  1. 前提:
    1. 在配置好一主一从的情况下;
  2. 从节点:
    1. 从节点修改服务器参数;开启binlog,log_slave_updates,去掉read-only
    2. 从节点做全备,并记录binlog位置;(可利用mysqldump)
    3. 若用mysqldump做全备,将从节点的全备sql文件,复制到新加入的末端从节点,可以有多个
  3. 末端从节点:
    1. 利用中间从节点的全备做恢复;
    2. 修改my.cnf,加入从节点配置参数;serverid、read-only,relay-log
    3. 利用change master命令,连接到从节点,从备份时记录的日志位置开始做同步;
    4. 启动末端从节点的slave进程;start slave
    5. 检查slave线程和日志同步状态;
    6. 在主节点做数据修改,检查主节点-》中间从节点-》末端从节点的数据是否能正常同步;

1、配置主节点:

#修改主节点配置文件,并重启生效
[root@host2 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log-bin=on

[root@host2 ~]# systemctl restart mariadb
[root@host2 ~]# ll /var/lib/mysql/
-rw-rw---- 1 mysql mysql      245 Oct  5 14:17 on.000001
-rw-rw---- 1 mysql mysql       12 Oct  5 14:17 on.index

#备份现有主节点数据
[root@host2 ~]# mysqldump -uroot -A -F -E -R --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > /root/`date +%F`.sql

#创建复制用的用户账户
MariaDB [(none)]> grant replication slave on *.* to repl@'192.168.80.%' identified by 'wang';

2、配置中间从节点

#修改配置文件并重启
[root@host3 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
log-bin
read-only=on
relay-log=on

[root@host3 ~]# systemctl restart mariadb

#从主节点复制过来备份的sql文件,并恢复,使其达到主节点备份时刻的数据状态
[root@host2 ~]# scp 2020-10-05.sql 192.168.80.102:/root/

[root@host3 ~]# mysql < 2020-10-05.sql 
[root@host3 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

#配置主节点信息,使其从备份时刻的binlog位置开始读取binlog并应用到本地
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.80.101',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='wang',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='on.000002', 
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.01 sec)

#配置好主节点信息,此时尚未开始复制,需要手动启动复制线程,启动后,io和sql线程状态变为yes即正常
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.80.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: on.000002
          Read_Master_Log_Pos: 245
               Relay_Log_File: on.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: on.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No

MariaDB [(none)]> start slave;            
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: on.000002
          Read_Master_Log_Pos: 394
               Relay_Log_File: on.000002
                Relay_Log_Pos: 671
        Relay_Master_Log_File: on.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

# 主节点做数据修改,验证数据同步,主节点创建db1库,从节点可以看到db1随之创建,【主从配置成功】
MariaDB [(none)]> create database db1;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

3、末端从节点

#修改中间从节点配置文件,关闭read-only选项,加入log_slave_updates选项,重启
[root@host3 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
log-bin
# read-only=on
relay-log=on
log_slave_updates
[root@host3 ~]# systemctl restart mariadb

#修改末端从节点参数文件
[root@host4 ~]# vim /etc/my.cnf
[mysqld]
server_id=4
log-bin
read-only=on
relay-log=on
[root@host4 ~]# systemctl start mariadb

#中间从节点,备份,并拷贝到末端节点上,然后恢复到末端节点
[root@host3 ~]# mysqldump -uroot -A -F -E -R --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > /root/`date +%F`.sql
[root@host3 ~]# scp 2020-10-05.sql 192.168.80.103:/root/
[root@host4 ~]# mysql < 2020-10-05.sql 


#末端节点将中间节点指为主,并开启复制线程
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.80.102',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='wang',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000003', 
    ->   MASTER_LOG_POS=245,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.80.102
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: on.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mariadb-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.102
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mariadb-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: on.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


4、测试

#建库后,主节点、中间节点,末端节点都出现了testdb,【级联配置成功】
MariaDB [(none)]> create database testdb;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+

主主复制

要注意的问题

​ 主主复制:2个节点互为主从,2个节点都可以写,每个节点的写操作都会同步到对方节点,容易造成数据不一致问题,因此较少使用;

  • 自动增长id设置,从而避免2个节点写入相同一行的数据,导致数据冲突不一致

  • 写入数据时,一个节点使用奇数 id

    • auto_increment_offset=1
      auto_increment_increment=2
      # 从1开始增长,步长是2,1 3 5 7
      
  • 另一个节点使用偶数id

    • auto_increment_offset=2
      auto_increment_increment=2
      # 从2开始增长,步长是2 ,2 4 6 8...
      

配置步骤

  1. 各节点使用唯一的server id;
  2. 都启动bin log和relay log
  3. 创建用于复制权限的账户
  4. 启动自动增长的id各为奇偶,避免冲突;
  5. 都把对方指定为主节点,都启动复制线程;

半同步复制

半同步工作模型

​ 默认情况下,mysql复制是异步的,即主库发送日志后即返回,可以提交事务做数据持久化变更,不关心从库是否正确接收并应用到本地,性能好,但是主库挂掉,从库又没来得及接受对应binlog的情况,就会丢失一部分数据,

半同步复制,主库的数据变更至少发送到一个从库上,并确认从库成功接收并应用后再返回,才能进行事务的提交,即数据变更至少保存2份,在2个节点上,从而提高了数据的安全性,常结合mha做高可用集群

image-20201005152444759

半同步配置

​ 1、配置主节点

#默认没有开启半同步相关参数
MariaDB [(none)]> show global variables like '%semi%';
Empty set (0.00 sec)

#半同步相关的2个so模块
[root@host4 ~]# rpm -ql mariadb-server |grep so |grep semi
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

#加载安装半同步复制的主节点模块,设置为启用,设置超时时间为1000毫秒
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)

#此时查看相关变量,显示为on
MariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

#此时从节点个数为0,需要配置从节点
MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 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_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     |
+--------------------------------------------+-------+

​ 2、配置从节点

#加载安装半同步复制模块的从节点模块,启用,重启复制线程使得配置生效
MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

#此时从节点半同步模块开启,主节点显示有1个从节点连接
MariaDB [(none)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| 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     |
+--------------------------------------------+-------+

半同步验证

#半同步正常时,创建数据库立即完成
MariaDB [(none)]> create database semi1;
Query OK, 1 row affected (0.00 sec)

#此时显示半同步的从连接有1个
MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 671   |
| Rpl_semi_sync_master_net_wait_time         | 1342  |
| Rpl_semi_sync_master_net_waits             | 2     |
| 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      | 734   |
| Rpl_semi_sync_master_tx_wait_time          | 1469  |
| 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     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)


#关闭从节点的半同步参数,重启复制线程后,才能生效
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+

#此时创建库,会经历1s的超时时长,超过设置的1s超时时间后,会自动降级为异步复制,【避免了从库挂掉,卡住,影响主库的正常写入】
MariaDB [(none)]> create database semi3;
Query OK, 1 row affected (1.01 sec)
#将从库重新开启半同步复制,创建库又立刻完成,【自动恢复为半同步复制】

基于gitd的复制

gtid简介

​ global transaction id全局事务标识符;mysql5.6开始支持,不同于传统的复制方式,需要找到binlog的position,只需要配置主节点的ip,端口,账户,密码即可,会自动寻找同步点

gitd组成:

​ server_uuid:transaction_id,一组复制集群中,全局位置,server_uuid来源于auto.cnf

gtid服务器相关选项

gtid_mode 开启gtid模式
enfore_gtid_consistency 保证gtid安全的参数

gtid架构配置

MariaDB has supported global transaction IDs (GTIDs) for replication since version 10.0.2.

https://mariadb.com/kb/en/gtid/#setting-up-a-new-slave-with-an-empty-server

https://dev.mysql.com/doc/refman/5.6/en//replication-gtids-howto.html

参考文档,mariadb和mysql的gitd实现机制和配置方式均不同

注意配置10.0.2以后的yum源,centos7默认的mariadb5.5不支持

cat > /etc/yum.repos.d/mariadb.repo <<'EOF'
[mariadb]  
name = MariaDB  
baseurl =https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.3.23/yum/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB  
gpgcheck=1  
EOF

主节点:

[root@host2 ~]# vim /etc/my.cnf.d/server.cnf 
# this is only for the mysqld standalone daemon
[mysqld]
server_id=2
log-bin
[root@host2 ~]# systemctl restart mariadb

MariaDB [(none)]> grant replication slave on *.* to repl@'192.168.80.%' identified by 'wang';
#修改配置文件,创建复制用户

从节点:

[root@host2 ~]# vim /etc/my.cnf.d/server.cnf 
# this is only for the mysqld standalone daemon
[mysqld]
server_id=3
[root@host2 ~]# systemctl restart mariadb
#修改配置文件,指明主节点信息

MariaDB [(none)]>   CHANGE MASTER TO master_host="192.168.80.101", master_port=3306, master_user="repl", master_password = 'wang',master_use_gtid=current_pos;
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> show slave status\G;

#之后,主库创建数据库,测试主从库的数据同步,测试正常,截图略

复制相关增强配置

复制过滤器

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

实现方式:

  • 主节点端:主节点上,设置只对特定的库做binlog的记录,没有binlog记录的库,崩溃时无法恢复

    • --binlog-ignore-db=db_name
      --binlog-do-db=db_name
      #相关配置参数
      
  • 从节点端:主节点仍对所有的库的数据更改做binlog记录,从库也全都收,但sql thread读取relay log时,只读取特定的库或表的事件进行重放,缺点是:会造成网络io和磁盘存储的浪费

    • --replicate-do-db=db_name
      --replicate-ignore-db=db_name
      --replicate-do-table=db_name.tbl_name
      --replicate-ignore-table=db_name.tbl_name
      --replicate-wild-do-table=db_name.tbl_name
      --replicate-wild-ignore-table=db_name.tbl_name
      #从节点配置时,相关配置参数
      

配置方式

1、配置主从

​ 略

2、采用主库忽略特定库的binlog方式

#主节点创建3个库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| mysql              |
| performance_schema |
| test1              |
| test2              |
| test23             |
+--------------------+

#修改主节点配置文件,主库不记录test1库的binlog,并重启
[mysqld]
...
binlog-ignore-db=test1


#主节点删除库
MariaDB [(none)]> drop database test23;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> drop database test2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> drop database test1;
Query OK, 0 rows affected (0.00 sec)

#从节点对应的test1库没有删除,因为主节点设置了忽略
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test1              |
+--------------------+

3、采用从库忽略的方式

#主库选择db1库,并创建2个表
MariaDB [db1]> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> create table t2(id int);
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
+---------------+

# 修改从节点的配置文件,配置忽略db1.t1的数据变化
[root@host3 ~]# cat /etc/my.cnf
[mysqld]
server_id=3
log-bin
read-only=on
relay-log=on
replicate-ignore-table=db1.t1
[root@host3 ~]# systemctl restart mariadb

# 主库做数据修改,检查从库的数据变化
#创建了2个表,向表中插入数据后,
MariaDB [db1]> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> create table t2(id int);
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> insert into t2 values(2);
Query OK, 1 row affected (0.00 sec)

#从库上,t1没有随之变化,因为设置了忽略
MariaDB [db1]> select * from t1;
Empty set (0.00 sec)

MariaDB [db1]> select * from t2;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set 

复制加密

​ 默认情况下,复制的通信连接是明文的,具有安全隐患,因为可以采用ssl证书加密通信的方式来加强安全性;

https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-encrypted-connections.html

https://mariadb.com/kb/en/replication-with-secure-connections/

配置实现:

  1. 主服务器开启ssl认证,配置ca和自己的证书和自己的私钥路径;
  2. 创建的replication salve复制账户要求使用ssl连接
  3. 从服务器使用change master to时指明ssl选项,包括自己的从服务器的一套证书,私钥

生成证书和私钥:

#主节点上,创建存储证书,私钥的目录
[root@host2 ~]# mkdir /etc/my.cnf.d/ssl
[root@host2 ~]# cd /etc/my.cnf.d/ssl/
[root@host2 ssl]# ll
total 0

#生成ca的私钥
[root@host2 ssl]# (umask 066;openssl genrsa 2048 > cakey.pem)
Generating RSA private key, 2048 bit long modulus
......+++
........................................................+++
e is 65537 (0x10001)
[root@host2 ssl]# ll
total 4
-rw------- 1 root root 1679 Oct  6 13:44 cakey.pem

#生成ca的自签名证书
[root@host2 ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650

#分别对主和从节点生成私钥和证书请求文件
[root@host2 ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr
[root@host2 ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr

#对主和从节点的2个证书请求文件进行签发
[root@host2 ssl]#  openssl x509 -req -in master.csr  -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
Signature ok

Getting CA Private Key
[root@host2 ssl]# openssl x509 -req -in slave.csr  -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
Signature ok

#将从节点的私钥,证书,ca证书发到从节点上
[root@host2 ssl]# scp slave.key slave.crt cacert.pem 192.168.80.102:/etc/my.cnf.d/ssl/

[root@host3 ~]# mkdir /etc/my.cnf.d/ssl
[root@host3 ~]# cd !$
cd /etc/my.cnf.d/ssl
[root@host3 ssl]# ll
total 0
[root@host3 ssl]# ll
total 12
-rw-r--r-- 1 root root 1334 Oct  6 13:55 cacert.pem
-rw-r--r-- 1 root root 1200 Oct  6 13:55 slave.crt
-rw-r--r-- 1 root root 1704 Oct  6 13:55 slave.key
[root@host3 ssl]# 

主节点:

#配置证书路径,并创建带有ssl选项的复制账户,require ssl
[root@host2 ssl]# vim /etc/my.cnf
[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
[root@host2 ssl]# systemctl restart mariadb

MariaDB [(none)]> grant replication slave on *.* to repl@'192.168.80.%' identified by 'wang' require ssl;
Query OK, 0 rows affected (0.00 sec)

从节点:

#从库修改配置文件,添加ssl选项,和从库相关的证书文件路径
[root@host3 ssl]# vim /etc/my.cnf
[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
[root@host3 ssl]# systemctl restart mariadb

#连入mysql,清空原有的主从配置
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G;
Empty set (0.00 sec)

#change master to添加ssl选项,并指明证书的路径
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.80.101',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='wang',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='on.000006', 
    ->   MASTER_LOG_POS=406,
    ->   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',
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.00 sec)

#启动线程,查看状态,
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.80.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: on.000006
          Read_Master_Log_Pos: 406
               Relay_Log_File: on.000002
                Relay_Log_Pos: 522
        Relay_Master_Log_File: on.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: db1.t1
                Until_Log_Pos: 0
#指明了ssl选项在用,以及证书路径                
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/ssl/slave.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: 2

复制的监控与维护

清理日志:

purge {binary|master} logs {to 'log_name' | before datetime_expr}
reset master
reset slave all

复制监控:

show master status
show binary logs
show binlog events
show slave status
show processlist

#检查从库是否落后于主库,在show slave stauts中
Seconds_Behind_Master: NULL

#主从数据一致性检查工具
percona-tools
https://www.percona.com/software/database-tools/percona-toolkit

#数据不一致修复方法
删除从库,从头重新复制
updatedupdated2021-03-092021-03-09
加载评论