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

预备操作

# 主库
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

一、确保主从服务器时间同步

chrony来实现时间同步（默认不再支持ntp软件包）

1、设置时区
cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

2、加入开启启动
systemctl enable chronyd && systemctl start chronyd

3、设置NTP同步时间，设置时区
timedatectl set-ntp true
timedatectl set-timezone Asia/Shanghai

4、查看配置文件
cat /etc/chrony.conf

5、查看和更改系统的时间和日期
timedatectl

6、修改时间
date -s 2000-01-01

7、同步时间
systemctl restart chronyd 
chronyc sources -v

8、设置定时任务，自动执行
mkdir -p /data/crond

添加以下内容 （每天 02：00同步一次，并且日志记录到 /data/crond/ntpdate.log）（或者crontab -e 打开后添加）
echo "00 02 * * * systemctl restart chronyd && chronyc sources -v 1>>/data/crond/ntpdate.log 2>&1" >> /var/spool/cron/root

 

二、生成自签发证书 (CA、主库证书、从库证书)

1、创建保存证书目录

mkdir /data/ca -p
cd /data/ca
===================================================================
ca-cert.pem: CA 证书, 用于生成服务器端/客户端的数字证书.
ca-key.pem: CA 私钥, 用于生成服务器端/客户端的数字证书.
server-key.pem: 服务器端的 RSA 私钥
server-req.pem: 服务器端的证书请求文件, 用于生成服务器端的数字证书.
server-cert.pem: 服务器端的数字证书.
client-key.pem: 客户端的 RSA 私钥
client-req.pem: 客户端的证书请求文件, 用于生成客户端的数字证书.
client-cert.pem: 客户端的数字证书.
===================================================================

2、生成CA证书
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem

查看证书 （主要是Issuer和Subject部分的内容）
openssl x509 -in ca-cert.pem -noout -text
Issuer: C = aa, ST = a, L = a, O = a, OU = a, CN = a
Subject: C = aa, ST = a, L = a, O = a, OU = a, CN = a

3、生成服务器端密钥，并用第一步生成的CA签发服务器证书
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

查看证书 （主要是Issuer和Subject部分的内容）
openssl x509 -in server-cert.pem -noout -text
Issuer: C = aa, ST = a, L = a, O = a, OU = a, CN = a
Subject: C = bb, ST = b, L = b, O = b, OU = b, CN = b

验证证书
openssl verify -CAfile ca-cert.pem server-cert.pem

4、让mariadb 支持 SSL
授权证书权限
chown mysql:mysql /data/ca -R

5、在配置文件的[mysqld]之后添加：
vim /etc/my.cnf

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

6、重启服务后连接Mariadb
systemctl restart mysqld

7、登录并查看是否支持 SSL
mysql -u root -p
MariaDB [(none)]> show variables like '%ssl%';
+---------------------+---------------------------+
| Variable_name | Value |
+---------------------+---------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /data/ca/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /data/ca/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /data/ca/server-key.pem |
| version_ssl_library | OpenSSL 3.0.1 14 Dec 2021 |
+---------------------+---------------------------+

8、生成客户端密钥并使用第一步的CA签发客户端证书：
注意:生成证书时，确保服务器证书和客户端证书的Common Name一致

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

查看证书 （主要是Issuer和Subject部分的内容）
openssl x509 -in client-cert.pem -noout -text
Issuer: C = aa, ST = a, L = a, O = a, OU = a, CN = a
Subject: C = bb, ST = b, L = b, O = b, OU = b, CN = c

验证证书
openssl verify -CAfile ca-cert.pem client-cert.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 information_schema performance_schema sys t1 t2 t3> dbbackup.sql
说明：
--single-transaction 这个参数只对innodb适用，实现热备InnoDB表；因此，不需要同时使用--lock-all-tables；
--databases 后面跟除mysql以后的其他所有数据库的库名 (测试库上只有 t1 t2 t3 三个自己创建的库)
--master-data 参数会记录导出快照时候的mysql二进制日志位置，一会用到。
--lock-all-tables 为所有表加读锁

------------------------------------------------------------------------------------------------------------------------

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| database1 |
| database2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| xmaster |
+--------------------+

1.mysqldump备份单库
mysqldump -u root -p'mysql' --databases database1 > backup_01.sql

2.mysqldump备份部分库
mysqldump -u root -p'mysql' --databases database1 database2> backup_02.sql

3.mysqldump备份全库
mysqldump -u root -p'mysql' --all-databases > backup_03.sql

4.mysqldump排除部分库备份 （利用xargs和grep筛选）
排除掉information_schema、performance_schema、sys、mysql、database1几个库，只备份其余的库。

mysql -uroot -p'mysql' -N -e "show databases;"|grep -Ev "information_schema|performance_schema|sys|mysql|database1"|xargs mysqldump -uroot -p'mysql' --databases > backup_04.sql

------------------------------------------------------------------------------------------------------------------------

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 root@192.168.0.20:/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登录名 -p密码 -D数据库名称 -c <sql文件位置
mysql -u登录名 -p密码 -D数据库名称 --comment <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_port=13333,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_port=13333,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_port　　  #指定主服务器端口
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;

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

 

五、同步验证

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

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

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

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

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

6、重启主库后，更新主库，查看是否同步

7、关闭从库后，更新主库，再重库从库是否同步

8、更新主库函数，视图，查看是否同步

9、主库执行大事务，查看是否同步

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

六、同步常错误
1、问题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、问题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 大小根据需要

 

=======================================================================

一、mysql创建用户且只能访问指定数据库表

#创建用户
CREATE user 'discuz'@'%' identified by 'discuz';

#更改用户访问数据库的权限
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `discuz`.* TO 'discuz'@'%';

或者

GRANT all on discuz.* to 'discuz'@'%';

#刷新
FLUSH PRIVILEGES;

查看权限
show grants；

二、创建用户 testUser, 只允许此用户查看 dbtest库的 task表；

#创建用户testUser, 密码12345，%表示所以机器，可以自行指定ip
CREATE USER 'testUser'@'%' IDENTIFIED BY '12345';

#给此用户分配 task表 的select权限
GRANT SELECT ON `dbtest`.task TO 'testUser'@'%';

#给此用户分配 task表 的全部权限
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `dbtest`.task TO 'testUser'@'%';

#刷新
FLUSH PRIVILEGES;

#查看当前用户及权限
SELECT user,host FROM mysql.user;