MySQL数据库——集群篇——3
1.MySQL 集群 Cluster
半同步复制
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。
官方文档： https://mariadb.com/kb/en/library/semisynchronous-replication/
范例：CentOS 7 在Mariadb-10.6 上实现 实现半同步复制
#在master实现，启用半同步功能
#配置mariadb10.6网络源,需要阿里与源和本地仓库源
[root@master /etc/yum.repos.d]#pwd
/etc/yum.repos.d
[root@master /etc/yum.repos.d]#cat mariadb106.repo
[mariadb]
name=mariadb
baseurl=https://mirrors.aliyun.com/mariadb/mariadb-10.6.21/yum/centos7-amd64/
enabled=1
gpgcheck=0
[root@master ~]#yum install -y MariaDB-server
[root@master ~]#mysql -V
mysql Ver 15.1 Distrib 10.3.36-MariaDB, for Linux (x86_64) using readline 5.1
[root@master ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=71
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端
[root@master ~]#systemctl enable --now mariadb
[root@master ~]#mysql
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 |
+--------------------------------------------+-------+
18 rows in set (0.000 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY '1';
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 516 |
+-------------------+-----------+
1 row in set (0.000 sec)
#在其它所有slave节点上都实现，启用半同步功能
#slave1上操作
[root@slave1 ~]#vim /etc/yum.repos.d/mariadb103.repo
[mariadb]
name=mariadb
baseurl=https://mirrors.aliyun.com/mariadb/yum/10.3.36/centos7-amd64
enabled=1
gpgcheck=0
[root@slave1 ~]#yum install -y MariaDB-server
[root@slave1 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=72
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON
[root@slave1 ~]#systemctl enable --now mariadb
[root@slave1 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.2.71',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='1',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000001',
-> MASTER_LOG_POS=516;
Query OK, 0 rows affected (0.078 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.71
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 516
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000001
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: 516
Relay_Log_Space: 866
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: 71
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: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 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)
#slave2上操作
[root@slave2 ~]#vim /etc/yum.repos.d/mariadb103.repo
[mariadb]
name=mariadb
baseurl=https://mirrors.aliyun.com/mariadb/yum/10.3.36/centos7-amd64
enabled=1
gpgcheck=0
[root@slave2 ~]#yum install -y MariaDB-server
[root@slave2 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=73
log-bin
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON
[root@slave2 ~]#systemctl enable --now mariadb
[root@slave2 /var/lib/mysql]#mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.2.71',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='1',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000001',
-> MASTER_LOG_POS=516;
Query OK, 0 rows affected (0.042 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.71
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 516
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000001
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: 516
Relay_Log_Space: 866
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: 71
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: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 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)
#在master上实现
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 | #两个从节点
| 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.000 sec)
#测试
#在master实现，创建数据库，立即成功
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.002 sec)
#在所有slave节点实现，停止复制线程
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.007 sec)
#在master实现，创建数据库，等待3s才能成功
MariaDB [(none)]> create database db3;
Query OK, 1 row affected (3.001 sec)
#在任意一个slave节点实现，恢复复制线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
#在master实现，创建数据库，立即成功
MariaDB [(none)]> create database db4;
Query OK, 1 row affected (0.001 sec)范例：CentOS 7 实现Mariadb 5.5.65 的半同步复制
#主服务器配置:
MariaDB [(none)]>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MariaDB [(none)]>UNINSTALL PLUGIN rpl_semi_sync_master ;
MariaDB [(none)]>SHOW PLUGINS; #查看插件
MariaDB [(none)]>SET GLOBAL rpl_semi_sync_master_enabled=1;
MariaDB [(none)]>SET GLOBAL rpl_semi_sync_master_timeout = 1000; #超时长1s,默认值为10s
MariaDB [(none)]>SHOW GLOBAL VARIABLES LIKE '%semi%';
MariaDB [(none)]>SHOW GLOBAL STATUS LIKE '%semi%';
#从服务器配置:
MariaDB [(none)]>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]>SET GLOBAL rpl_semi_sync_slave_enabled=1;
#mariadb-10.3版以后
#主服务器配置:
[mysqld]
plugin_load_add = semisync_master
#从服务器配置:
[mysqld]
plugin_load_add = semisync_slave复制过滤器
让从节点仅复制指定的数据库，或指定数据库的指定表
复制过滤器两种实现方式：
(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= #数据库黑名单列表注意：
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.
此选项在使用基于语句的日志记录进行跨数据库更新时不起作用。有关更多信息，请参见 “基于语句的日志记录” 部分。此选项不能动态设置。在命令行上或在选项文件的服务器选项组中设置此选项时，该选项不接受逗号分隔的列表。如果要指定多个筛选器，则需要多次指定该选项。范例: 通过二进制日志服务器选项实现过滤器**（主库操作）
[root@master ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
binlog-do-db=db2
server-id=71
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
[root@master ~]#systemctl restart mariadb
[root@master ~]#mysql
#可以看到db2是在白名单中
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000007 | 343 | db2 | |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> create database db88;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use db3;
MariaDB [db3]> create table t1(id int);
MariaDB [db3]> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| student |
| t1 |
+---------------+
2 rows in set (0.000 sec)
#从库查看,则没有db88这个库和t1这个表
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| ccc |
| db2 |
| db3 |
| db4 |
| db77 |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
9 rows in set (0.000 sec)
MariaDB [db3]> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)(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
#主库操作
MariaDB [(none)]> use db77;
Database changed
MariaDB [db77]> create table t7(id int);
Query OK, 0 rows affected (0.053 sec)
#从库查看
MariaDB [(none)]> use db77;
Database changed
MariaDB [db77]> show tables;
Empty set (0.000 sec)
#主库操作
MariaDB [db77]> use db2;
MariaDB [db2]> create table t22(id int);
Query OK, 0 rows affected (0.007 sec)
#从库查看
MariaDB [db77]> use db2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| student |
| t22 |
+---------------+
2 rows in set (0.000 sec)范例: 通过系统变量实现过滤器
MariaDB [(none)]> set replicate_do_db='db1,hellodb';
ERROR 1229 (HY000): Variable 'replicate_do_db' is a GLOBAL variable and should
be set with SET GLOBAL
MariaDB [(none)]> set global replicate_do_db='db1,hellodb';
ERROR 1198 (HY000): This operation cannot be performed as you have a running
slave ''; run STOP SLAVE '' first
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.009 sec)
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;
Query OK, 0 rows affected (0.001 sec)注意：跨库的更新将无法同步
#主库操作
MariaDB [db2]> use mysql;
MariaDB [mysql]> create table db2.t33(id int);
Query OK, 0 rows affected (0.007 sec)
#从库查看
MariaDB [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| student |
| t22 |
+---------------+
2 rows in set (0.000 sec)主从复制加密
在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网进行访问数据或则复制，存在安全隐患。
通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性
官网文档：https://mariadb.com/kb/en/library/replication-with-secure-connections/
实现MySQL复制加密
1.生成 CA 及 master 和 slave 的证书
[root@centos7 ~]# mkdir /etc/my.cnf.d/ssl
[root@centos7 ~]# cd /etc/my.cnf.d/ssl
# 生成CA私钥（权限600）
[root@centos7 /etc/my.cnf.d/ssl]# openssl genrsa -out cakey.pem 4096
[root@centos7 /etc/my.cnf.d/ssl]# chmod 600 cakey.pem
# 生成CA证书（带-subj避免交互式输入）
[root@centos7 /etc/my.cnf.d/ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 \
-subj "/C=CN/ST=hubei/L=wuhan/O=ops/OU=ops.org/CN=ca.ops.org"
# 生成服务端私钥（权限600）
[root@centos7 /etc/my.cnf.d/ssl]# openssl genrsa -out master.key 2048
[root@centos7 /etc/my.cnf.d/ssl]# chmod 600 master.key
# 生成CSR（证书签名请求）
[root@centos7 /etc/my.cnf.d/ssl]# openssl req -new -key master.key -out master.csr \
-subj "/C=CN/ST=hubei/L=wuhan/O=ops/OU=ops/CN=master.ops.org"
# 使用CA签发证书，并添加服务器认证扩展
[root@centos7 /etc/my.cnf.d/ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem \
-CAcreateserial -out master.crt -days 365 \
-extfile <(echo "extendedKeyUsage=serverAuth")
# 生成客户端私钥
[root@centos7 /etc/my.cnf.d/ssl]# openssl genrsa -out slave.key 2048
[root@centos7 /etc/my.cnf.d/ssl]# chmod 600 slave.key
# 生成CSR
[root@centos7 /etc/my.cnf.d/ssl]# openssl req -new -key slave.key -out slave.csr \
-subj "/C=CN/ST=hubei/L=wuhan/O=ops/OU=ops/CN=slave.ops.org"
# 签发客户端证书（添加客户端认证扩展）
[root@centos7 /etc/my.cnf.d/ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem \
-CAcreateserial -out slave.crt -days 365 \
-extfile <(echo "extendedKeyUsage=clientAuth")
[root@centos7 /etc/my.cnf.d/ssl]# chown -R mysql:mysql /etc/my.cnf.d/ssl
[root@centos7 /etc/my.cnf.d/ssl]# chmod 600 /etc/my.cnf.d/ssl/*.key # 私钥必须为600
[root@centos7 /etc/my.cnf.d/ssl]# chmod 644 /etc/my.cnf.d/ssl/*.{crt,pem} # 证书可读
[root@centos7 /etc/my.cnf.d/ssl]# ll
总用量 36
-rw-r--r-- 1 mysql mysql 2000 3月 5 18:36 cacert.pem
-rw-r--r-- 1 mysql mysql 17 3月 5 18:37 cacert.srl
-rw-r--r-- 1 mysql mysql 3247 3月 5 18:36 cakey.pem
-rw-r--r-- 1 mysql mysql 1574 3月 5 18:36 master.crt
-rw-r--r-- 1 mysql mysql 997 3月 5 18:36 master.csr
-rw------- 1 mysql mysql 1679 3月 5 18:36 master.key
-rw-r--r-- 1 mysql mysql 1574 3月 5 18:37 slave.crt
-rw-r--r-- 1 mysql mysql 997 3月 5 18:37 slave.csr
-rw------- 1 mysql mysql 1679 3月 5 18:37 slave.key2.主服务器开启 SSL，配置证书和私钥路径
[root@master ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=71
log-bin
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
# 强制所有用户使用SSL（可选）
# require_secure_transport = ON
[root@master ~]#systemctl restart mariadb
[root@master ~]#mysql -uroot -p1
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.0.2k-fips 26 Jan 2017 |
+---------------------+----------------------------------+
10 rows in set (0.001 sec)3.创建一个要求必须使用 SSL 连接的复制账号
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY '1' REQUIRE SSL;
Query OK, 0 rows affected (0.000 sec)
[root@master ~]#rsync -av /etc/my.cnf.d/ssl 192.168.2.72:/etc/my.cnf.d/
[root@slave1 ~]#ls /etc/my.cnf.d/ssl/
cacert.pem cakey.pem master.crt master.csr master.key slave.crt slave.csr slave.key
[root@slave1 ~]#chown -R mysql.mysql /etc/my.cnf.d/ssl/4.从服务器slave上使用CHANGER MASTER TO 命令时指明ssl相关选项
[root@slave1 ~]#mysql -urepluser -p1 -h192.168.2.71
ERROR 1045 (28000): Access denied for user 'repluser'@'192.168.2.72' (using password: YES)
--ssl-ca：CA根证书，用于验证服务器（主库）的证书。
--ssl-cert：客户端（从库）的证书，供服务器验证客户端身份。
--ssl-key：客户端证书对应的私钥，必须与证书匹配。
[root@slave1 ~]#mysql -urepluser -p1 -h192.168.2.71 --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
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.6.19-MariaDB-log 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)]>
#可选方式1
#主库查看
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 329 |
+-------------------+-----------+
1 row in set (0.000 sec)
#从库操作
[root@slave1 ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=72
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@slave1 ~]# systemctl restart mariadb
[root@slave1 ~]# mysql -uroot -p1
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_USER='repluser',
MASTER_PASSWORD='1',
MASTER_PORT=3306,
MASTER_LOG_FILE='centos7-bin.000001',
MASTER_LOG_POS=529,
MASTER_SSL=1;
Query OK, 0 rows affected (0.028 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.71
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 329
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000001
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: 329
Relay_Log_Space: 866
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
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: 71
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: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
#可选方式2
#需修改配置文件,执行下面命令即可
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.2.71',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='1',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000001',
-> MASTER_LOG_POS=329,
-> 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';
Query OK, 0 rows affected (0.040 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.71
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 329
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 556
Relay_Master_Log_File: master-bin.000001
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: 329
Relay_Log_Space: 866
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
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: 71
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: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
更新主从加密
[root@master ~]# chmod +x mysql_ssl_rsa_setup
[root@master ~]# mkdir /etc/my.cnf.d/ssl/
[root@master ~]# ./mysql_ssl_rsa_setup --datadir=/opt
[root@master ~]# mv /opt/* /etc/my.cnf.d/ssl/
[root@master ~]# cd /etc/my.cnf.d/ssl/
[root@master /etc/my.cnf.d/ssl]#ls
ca-key.pem client-cert.pem private_key.pem server-cert.pem
ca.pem client-key.pem public_key.pem server-key.pem
[root@master /etc/my.cnf.d/ssl]#chown -R mysql.mysql /etc/my.cnf.d/ssl
[root@master ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
ssl
ssl_ca=/etc/my.cnf.d/ssl/ca.pem
ssl_cert=/etc/my.cnf.d/ssl/server-cert.pem
ssl_key=/etc/my.cnf.d/ssl/server-key.pem
server-id=10
[root@master ~]#systemctl restart mariadb
[root@master ~]#rsync -av /etc/my.cnf.d/ssl 10.0.0.20:/etc/my.cnf.d/
[root@slave ~]#cd /etc/my.cnf.d/ssl/
[root@slave /etc/my.cnf.d/ssl]#ls
ca-key.pem client-cert.pem private_key.pem server-cert.pem
ca.pem client-key.pem public_key.pem server-key.pem
[root@slave /etc/my.cnf.d/ssl]#mysql -urepluser -p1 -h10.0.0.10 --ssl-ca=/etc/my.cnf.d/ssl/ca.pem --ssl-cert=/etc/my.cnf.d/ssl/client-cert.pem --ssl-key=/etc/my.cnf.d/ssl/client-key.pem
CHANGE MASTER TO
MASTER_HOST='10.0.0.10',
MASTER_USER='repluser',
MASTER_PASSWORD='1',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=329,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/ca.pem',
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/client-cert.pem',
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/client-key.pem';2.复制的监控和维护
清理日志
RESET SLAVE [ALL]复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST从服务器是否落后于主服务
Seconds_Behind_Master：0如何确定主从节点数据是否一致
percona-toolkit数据不一致如何修复
删除从数据库，重新复制3.复制的问题和解决方案
数据损坏或丢失
Master：MHA + semisync replication
Slave： 重新复制
不惟一的 server id
重新复制
复制延迟
需要额外的监控工具的辅助
一从多主：mariadb10 版后支持
多线程复制：对多个数据库复制
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
从库开启只读，不允许人为写入
定期进行主从一致性检验
阅读建议
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果
