MySQL基于ssl的主从复制

环境：

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
！重启MySQL

4.查看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.index

server_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/data

3.查看复制来的证书并赋权

[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
!重启MySQL

5.查看是否支持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.65

7.在从上 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)