Rocky9 Mariadb_10.9.4 使用SSL实现主从同步

一、预备操作
# 确保主从服务器时间同步
# 生成自签发证书 (CA、主库证书、从库证书)

# 主库
IP:192.168.0.10
数据库版本：Mariadb_10.9.4
需要证书：
/data/ca/ca-cert.pem
/data/ca/server-cert.pem
/data/ca/server-key.pem

# 从库
IP:192.168.0.20
数据库版本：Mariadb_10.9.4
需要证书：
/data/ca/ca-cert.pem
/data/ca/client-cert.pem
/data/ca/client-key.pem

二、主库配置

1、修改配置
vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
log-bin-index = mysql-bin.index
sync_binlog = 1
binlog_format=mixed

ssl
ssl-ca=/data/ca/ca-cert.pem
ssl-cert=/data/ca/server-cert.pem
ssl-key=/data/ca/server-key.pem
ssl_cipher= DHE-RSA-AES256-SHA

说明：
server-id 　　 　  # 在复制架构中，需保持全局唯一
log-bin　　　　    # 默认在数据目录下
log-bin-index 　　 #指定二制日志索引文件的路径与名称
sync_binlog = 1    # 每次在提交事务前会将二进制日志同步到磁盘，保证崩溃时不会丢失事件
binlog_format 　  # STATEMENT基于SQL语句的复制(默认)，ROW基于行的复制，MIXED混合模式复制
ca-cert.pem 　　 # 配置CA证书
server-cert.pem　# 配置主库证书
server-key.pem     # 配置主库私钥
DHE-RSA-AES256-SHA 　　 # 指定支持的加密算法

2、重启及连接到主库
systemctl restart mysqld
mysql -u root -p

3、查看是否支持 SSL
MariaDB [(none)]> show variables like '%ssl%'

4、创建复制账户：(最小权限，并且明确指定必须使用SSL连接）
MariaDB [mysql]> grant replication slave,replication client on *.* to 'repluser'@'192.168.%.%' identified by 'replpass' require ssl;
MariaDB [mysql]> flush privileges;

5、增加三个测试数据库 t1 t2 t3
MariaDB [(none)]> show databases;
MariaDB [(none)]> create database t1;
MariaDB [(none)]> create database t2;
MariaDB [(none)]> create database t3;
MariaDB [(none)]> show databases;

6、查看主库的状态信息
MariaDB [mysql]> show master status;
+-----------------------+--------------+--------------+------------------+
| File 　　　　       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+--------------+--------------+------------------+
|mysql-bin.000005  | 379 　　| 　　　　　　 | 　　　　　　　　 |
+-----------------------+--------------+--------------+------------------+

7、检查二进制日志功能是否打开
MariaDB [mysql]>show variables like "%log_bin";
+------------------+----------+
| Variable_name | Position |
+------------------+----------+
| lob_bin 　　  | ON　　　 |
| sql_log_bin    | ON　　　 |
+------------------+----------+

8、 将主数据库的现有表，全部复制到从数据库
连接数据库
mysql -u root -p

查看InnoDB所有的数据都同步到磁盘
MariaDB [(none)]> show engine innodb status;

主数据库设置为只读。(从库成功同步后，记得要设置主库可写)
MariaDB [(none)]> set global read_only=1;

9、如果需要可以为mysqldump增加软连接
ll /opt/mysql/bin/mysqldump
ln -s /opt/mysql/bin/mysqldump /bin

10、生成备份文件（生成之后，不要再进行数据库改动，否则同步不上）
cd /data
mysqldump -uroot -p --events --triggers --routines --flush-logs --master-data=2 --lock-all-tables --databases t1 t2 t3> dbbackup.sql
说明：
--single-transaction 这个参数只对innodb适用，实现热备InnoDB表；因此，不需要同时使用--lock-all-tables；
--databases 后面跟除mysql以后的其他所有数据库的库名 (测试库上只有 t1 t2 t3 三个库)
--master-data 参数会记录导出快照时候的mysql二进制日志位置，一会用到。
--lock-all-tables 为所有表加读锁

11、查看备份文件（名称以及位置，应该必须和主服务器当前的 show master status 的一致）
grep -i "change master" dbbackup.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=385;

12、远程复制到从库192.168.0.20的/data目录下
scp /data/dbbackup.sql [email protected]:/data

三、从库配置

1、修改配置
vim /etc/my.cnf
[client]
ssl
ssl_ca = /data/ca/ca-cert.pem
ssl_cert = /data/ca/client-cert.pem
ssl_key =/data/ca/client-key.pem
ssl_cipher = DHE-RSA-AES256-SHA
[mysqld]
server-id = 11
log-bin = none
binlog_format=mixed
relay-log = relay-log
relay-log-index = relay-log.index
replicate-wild-ignore-table=mysql.%
log-slave-updates
read_only = 1

说明：
ca-cert.pem 　　 　　　　　　　　  # 配置CA证书
client-cert.pem 　　 　　　　　　　 # 配置从库证书
client-key.pem 　　 　　　　　　      # 配置众库私钥
DHE-RSA-AES256-SHA 　　 　　　 # 指定支持的加密算法
server-id = 11 　　　　　　　　　　 # 在复制架构中，需保持全局唯一
log-bin = none 　　 　　　　　　　  # 设置为none，即关闭从库的二进制日志
relay-log = relay-log 　　 　　　　    # 设置中继日志文件
relay-log-index = relay-log.index　　# 指定二制日志索引文件的路径与名称
replicate-wild-ignore-table=mysql.%  # 忽略复制 mysql.%的数据库
log-slave-updates 　　　　　　　　 # 允许从库将其重放的事件也记录到自身的二进制日志中
read_only = 1 　　　　　　　　　　# 从库设置为只读

２、重启及连接到主库
systemctl restart mysqld
mysql -u root -p

３、查看数据只否处于只读状态
MariaDB [(none)]> show global variables like 'read%';
+-------------------------+--------------------+
| Variable_name | Value |
+-------------------------+--------------------+
| read_binlog_speed_limit | 0 |
| read_buffer_size | 1048576 |
| read_only | ON |
| read_rnd_buffer_size | 4194304 |
+-------------------------+--------------------+

####################################################
在只读模式下, 只有super权限的用户和slave同步线程才能写入）
####################################################

４、查看备份文件
cd /data
grep -i "change master" dbbackup.sql

５、将主数据库快照备份文件，恢复到从服务器上
mysql -u root -p
MariaDB [(none)]> source /data/dbbackup.sql

６、指定master_ssl=1，以支持SSL连接
MariaDB [mysql]> change master to master_host='192.168.0.10',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000010',master_log_pos=776,master_ssl=1;
=====================================================================
方法2：从库语句配置时，指定证书（不需要在配置文件中指定证书）
MariaDB [mysql]> change master to master_host='192.168.0.10',master_user='repluser',master_password='replpass',master_ssl=1,master_ssl_ca='/data/ca/ca-cert.pem',master_ssl_cert='/data/ca/client-cert.pem',master_ssl_key='/data/ca/client-key.pem',master_log_file='mysql-bin.000010',master_log_pos=776;
=====================================================================
说明：
master_host 　　 #指定主服务器IP地址
master_user 　　 #主服务器专用于同步的用户名称（之前在主服务器上设置的）
master_password #主服务器专用于同步的用户密码
master_log_file　 #主服务器当前日志文件名称（必须对应）
master_log_pos    #主服务器当前日志位置（必须对应）
master_ssl 　　   #指定支持SSL连接
master_ssl_ca     #配置CA证书
master_ssl_cert   #配置主库证书
master_ssl_key   #配置主库私钥

7、启用及查看同步信息
MariaDB [mysql]> start slave;
MariaDB [mysql]> show slave status\G

查看从库状态(主查查看是否与主库保持一致,主从同步是否正常运行）
Master_Log_File:master-bin.000005
Read_Master_Log_Pos:379
slave_IO_Runing:Yes
slave_SQL_Running:Yes
Master_SSL_Allowed:Yes

说明：
Master_Log_File和Read_Master_Log_Pos，是否对应主服务器当前的名称和位置
Slave_IO_Running和Slave_SQL_Running的状态，如果都为Yes，则同步成功
Master_SSL_Allowed 说明是否验证SSL连接

####################################################
如果一切正常，将不会有错误信息
如果额外错误行信息（查明原因后，再重新同步操作）
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table hellodb.teachers; ...

停止同步状态
MariaDB [(none)]>stop slave;

清空同步状态（清空后，同步的日志名称、日志位置都会为空，要注意）
MariaDB [(none)]>reset slave;

重新同步操作
####################################################

8、同步成功后，主库要设置回可写
MariaDB [(none)]> set global read_only=0;

9、验证同步

# 在主库上新建数据库并创建数据
MariaDB [(none)]> show databases;
MariaDB [(none)]> drop database t1;
MariaDB [(none)]> create database t4;
MariaDB [(none)]> show databases;

# 查看主服务器状态（注意当前日志文件名称，日志位置）
MariaDB [(none)]> show master status;

# 查看从库能否正常同步数据
MariaDB [(none)]> show databases;
MariaDB [(none)]> show slave status\G

# 查看从库状态(主查查看是否与主库保持一致，应该会有变化）
Master_Log_File:master-bin.000005
Read_Master_Log_Pos:1010

# 重启主库后，更新主库，查看是否同步
# 关闭从库后，更新主库，再重库从库是否同步

# 更新主库函数，视图，查看是否同步
# 主库执行大事务，查看是否同步

# 主库执行大事务，中途重启从库，查看是否同步

-------------------------------------------------------------------------------------------------------------
10、同步常错误
问题1：sync_binlog=0 的情况，很容易出现。

默认设置是0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。
因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

而当设置为1，是最安全但是性能损耗最大的设置。
因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。
从以往经验和相关测试来看，对于高并发事务的系统来说，设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

sync_binlog=0，当事务提交之后，
MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，
而让系统自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，
MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

问题2：mysql/mariadb 主从同步出错 exceeded max_allowed_packet；
max_allowed_packet 过小
show VARIABLES like '%max_allowed_packet%';
set global max_allowed_packet =1*1024*1024*1024; ##1G 大小根据需要