mysql分库分表
分库
业务涉及较大的表时，数据表分库方法：
- 垂直切分
- 水平切分
垂直
将存放不同业务数据的表，分到不同数据库中，且不同的数据库可以是在不同的数据库实例中，如下：
水平
按照时间或地区等标准做水平切分，如电商的交易数据，按照不同的省市地区或时间划分，分别存储不同于不同的库，（也可以是在不同的实例）
分表
一张表数据量较大时，数据表切分方法：
- 垂直切分
- 水平切分
垂直
对于数据量较大的表，可以将不常用字段切分，单独放在一个表中
水平
对于数据量较大的表，可以按照时间，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
主从架构图
- 主节点数据更新时，相关操作会写入binlog
- 主节点开启slave线程将自己的binlog发送给从节点的io线程
- 从节点io线程接收到主节点的binlog之后，写入自己本地的中继日志，本质也算是binlog，
- 从节点的sql线程，读取本地relay log中继日志，应用到数据库中，形成和主库一样的数据更新
相关线程
主节点：
dump thread：主节点会为每一个从节点启动一个dump线程，发送自己的binlog
master.info：保存了各个从节点连过来时的信息，如账户，密码，ip等
从节点：
io thread：向主节点请求binlog，并接收到本地，保存为relay log
sql thread：从relay log中读取日志中sql，在本地重放
relay-log.info：保存了当前salve节点已经复制的binlog和本地relay log的对应关系
主从结构特点
- 异步复制：主从数据同步有延时，数据不一致
- 一主多从
- 一从多主：一个实例做多个主节点的从
- 级联复制
- 主主
- 环形复制
- 复制时，binlog选择的格式：row，statements，mixed
配置过程
主节点
- 启用binlog
- 设置唯一的server_id
- 创建具有复制数据权限的账户
- 在创建了用户后再备份，这样从库恢复后就带有该用户，不必再手动创建
- 创建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 |
+--------------------+
从节点
- 设置全局唯一server_id
- 设置数据库只读
- 设置relay log路径
- 设置relay log 的index文件
- 用主节点全备sql文件恢复
- 在从节点用change master命令配置主节点的信息，如ip，端口，刚刚创建的账户等；（此时从节点就配置好了主节点信息）
- 但从节点相关线程还没开启，所有主从同步没开始；
- 从节点启动2个相关线程，
- 检查线程状态，查看同步是否正常；
# 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，从节点较多的情况下，会加大主节点的压力，因此更合理的做法为，一主挂一从，再由从节点向其他从节点进行复制，以此减轻主节点的压力；
实验步骤：
- 前提：
- 在配置好一主一从的情况下；
- 从节点：
- 从节点修改服务器参数；开启binlog，log_slave_updates，去掉read-only
- 从节点做全备，并记录binlog位置；（可利用mysqldump）
- 若用mysqldump做全备，将从节点的全备sql文件，复制到新加入的末端从节点，可以有多个
- 末端从节点：
- 利用中间从节点的全备做恢复；
- 修改my.cnf，加入从节点配置参数；serverid、read-only，relay-log
- 利用change master命令，连接到从节点，从备份时记录的日志位置开始做同步；
- 启动末端从节点的slave进程；start slave
- 检查slave线程和日志同步状态；
- 在主节点做数据修改，检查主节点-》中间从节点-》末端从节点的数据是否能正常同步；
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...
-
配置步骤
- 各节点使用唯一的server id；
- 都启动bin log和relay log
- 创建用于复制权限的账户
- 启动自动增长的id各为奇偶，避免冲突；
- 都把对方指定为主节点，都启动复制线程；
半同步复制
半同步工作模型
默认情况下，mysql复制是异步的，即主库发送日志后即返回，可以提交事务做数据持久化变更，不关心从库是否正确接收并应用到本地，性能好，但是主库挂掉，从库又没来得及接受对应binlog的情况，就会丢失一部分数据，
半同步复制，主库的数据变更至少发送到一个从库上，并确认从库成功接收并应用后再返回，才能进行事务的提交，即数据变更至少保存2份，在2个节点上，从而提高了数据的安全性，常结合mha做高可用集群
半同步配置
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/
配置实现：
- 主服务器开启ssl认证，配置ca和自己的证书和自己的私钥路径；
- 创建的replication salve复制账户要求使用ssl连接
- 从服务器使用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
#数据不一致修复方法
删除从库，从头重新复制