环境：
IP地址 | 备注 |
192.168.1.65 | master(主) |
192.168.1.67 | slave（从） |
MySQL主上操作
1.在主 mysql 创建 SSL/RSA 文件
[root@master data]# cd /usr/local/mysql/bin/
[root@master bin]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data[root@master data]# ls
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql mysql.pid performance_schema public_key.pem server-key.pem
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 ibtmp1 mysqld.err mytest private_key.pem server-cert.pem sys赋予server-key.pem 权限
[root@master data]# chmod +r /usr/local/mysql/data/server-key.pem
[root@master data]# ll server-key.pem
-rw-r--r--. 1 root root 1679 Feb 25 20:08 server-key.pem登入MySQL查看是否支持ssl
mysql> show variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES | 支持
| have_ssl | YES | 支持
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+---------------+-----------------+
9 rows in set (0.00 sec)注：启用 mysql 支持 ssl 安全连接主要用于 mysql 主从复制（局域网可以非 ssh 连接即明文复制，但 internet 复制建议采用 ssl 连接）
2.创建用户用于主从复制
mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123.com' require ssl;3.启用master上的二进制日志
[root@master ~]# cat /etc/my.cnf
[mysqld]
......
server_id = 1
log-bin=mysql-bin
！重启MySQL4.查看MySQL的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)要记住上图所显示的 file 和 position 的值，配置从服务器要用到
5.配置防火墙通信
[root@master ~]# firewall-cmd --add-port=3306/tcp --permanent
success
[root@master ~]# firewall-cmd --reload
success从服务器配置
1.my.cnf文件内容
[root@slave ~]# cat /etc/my.cnf
.....
server_id = 2 #唯一性
relay-log = /usr/local/mysql/data/relay-log-bin #中继日志 ，默认开启
relay-log-index = /usr/local/mysql/data/slave-relay-bin.indexserver_id 要唯一，不能和其他 mysql 主机的重复
2.复制master主机上的证书到从服务器中（master主机操作）
[root@master data]# pwd
/usr/local/mysql/data
[root@master data]# scp ca.pem client-cert.pem client-key.pem root@192.168.1.67:/usr/local/mysql/data3.查看复制来的证书并赋权
[root@slave ~]# ls /usr/local/mysql/data/
auto.cnf client-key.pem ib_logfile1 mysql.pid public_key.pem
ca-key.pem ib_buffer_pool ibtmp1 mytest server-cert.pem
ca.pem ibdata1 mysql performance_schema server-key.pem
client-cert.pem ib_logfile0 mysqld.err private_key.pem sys[root@slave data]# chmod +r client-key.pem
//赋予client-key.pem 的 r 权限4./etc/my.cnf 文件中添加ssl配置
ssl-ca = /usr/local/mysql/data/ca.pem
ssl-cert = /usr/local/mysql/data/client-cert.pem
ssl-key = /usr/local/mysql/data/client-key.pem
!重启MySQL5.查看是否支持ssl
mysql> show variables like '%ssl%';
+---------------+---------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/data/ca.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/data/client-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/data/client-key.pem |
+---------------+---------------------------------------+
9 rows in set (0.00 sec)6.测试sql账号
测试在master上创建的账号，是否可以在slave上登录
[root@slave data]# mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -urep -p123.com -h 192.168.1.657.在从上 change master to
mysql> change master to master_host='192.168.1.65',
-> master_user='rep',
-> master_password='123.com',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=623,
-> master_ssl=1,
-> master_ssl_ca='/usr/local/mysql/data/ca.pem',
-> master_ssl_cert='/usr/local/mysql/data/client-cert.pem',
-> master_ssl_key='/usr/local/mysql/data/client-key.pem';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave
-> ;
Query OK, 0 rows affected (0.00 sec)查看从的状态，以下两个值必须为 yes,代表从服务器能正常连接主服务器
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.65
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 623
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
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: 623
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/data/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /usr/local/mysql/data/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/data/client-key.pem
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: 1
Master_UUID: a5383d98-2b2e-11eb-9c6d-000c29578d1c
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)8.在master创建数据库，并查看slave上是否创建
//master上创建
mysql> create database qin;
Query OK, 1 row affected (0.00 sec)//slave上验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
| qin |
| sys |
+--------------------+
6 rows in set (0.01 sec)
