在生產環境中一台mysql主機存在單點故障，所以我們要確保mysql的高可用性，也就是兩台MySQL伺服器如果其中有一台MySQL伺服器掛掉後，另外一台能立刻接替其進行工作。
master記錄二進位日誌，在每個交易更新資料完成之前，master在二日誌記錄這些變更。儲存引擎在二進位日誌完成後收到主伺服器的通知，提交MySQL交易。接下來，slave需要將master的二進位日誌複製到自己的中繼日誌。首先，slave開始一個工作線程——I/O線程，I/O線程在master上打開一個普通的連接，然後開啟binlog dump process。 Binlog dump process從master的二進位日誌中讀取事件，如果已經同步了master，它會睡眠並等待master產生新的事件，I/O線程將這些事件寫入中繼日誌。 The final step of this process is handled by the SQL slave thread.。 SQL執行緒會讀取中繼日誌中的事件，並重現這些事件以更新從庫的數據，以保持與主庫中的數據一致。由於中繼日誌通常儲存在作業系統的快取中，因此只要該執行緒與 I/O 執行緒保持一致，中繼日誌的開銷很少。
環境準備：開啟兩台MySQL伺服器，部署網路環境。
[root@master ~]# cd /usr/local/mysql/bin/ [root@master bin]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/llocal/mysql --datadir=/usr/local/mysql/data
[root@master bin]# chmod +r /usr/local/mysql/data/server-key.pem [root@master bin]# service mysqld restart Shutting down MySQL.. [ 确定 ] Starting MySQL. [ 确定 ]
註：啟用mysql 支援ssl 安全連線主要用於mysql 主從複製（區域網路可以非ssh 連線即明文複製，但internet 複製建議採用ssl 連線）
mysql> grant replication slave on *.* to rep@'192.168.8.3' identified by '123'; Query OK, 0 rows affected, 1 warning (0.07 sec)
要注意的是server_id必須唯一。
[root@master ~]# vim /etc/my.cnf #添加下面内容 log-bin=mysql-bin service_id=1 [root@master ~]# service mysqld restart Shutting down MySQL.. [ 确定 ] Starting MySQL. [ 确定 ] [root@master ~]# mysql -uroot -p123 -e "show master status" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+
[root@master ~]# firewall-cmd --permanent --add-port=3306/tcp success [root@master ~]# firewall-cmd --reload success
[root@master data]# scp ca.pem client-cert.pem client-key.pem root@192.168.8.3:/usr/local/mysql/data The authenticity of host '192.168.8.3 (192.168.8.3)' can't be established. ECDSA key fingerprint is SHA256:LFby9KMDz/kkPfOESbeJ7Qh+3hmQaX2W5gkDDMwSGHA. ECDSA key fingerprint is MD5:03:32:64:b4:c2:5b:6c:a4:e2:f0:7f:df:7a:35:19:80. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.8.3' (ECDSA) to the list of known hosts. root@192.168.8.3's password: ca.pem 100% 1112 232.5KB/s 00:00 client-cert.pem 100% 1112 240.4KB/s 00:00 client-key.pem 100% 1676 205.0KB/s 00:00
1. 開啟ssl、中繼日誌，賦予ssl檔案讀取的權限並重啟mysql。
[root@slave ~]# vim /etc/my.cnf #添加下面内容 server_id=2 relay-log=relay-log ssl_ca=ca.pem ssl_cert=client-cert.pem ssl_key=client-key.pem [root@slave ~]# cd /usr/local/mysql/data [root@slave data]# ll ca.pem client-cert.pem client-key.pem -rw-r--r--. 1 mysql mysql 1112 3月 31 14:31 ca.pem -rw-r--r--. 1 mysql mysql 1112 3月 31 14:31 client-cert.pem -rw-------. 1 mysql mysql 1676 3月 31 14:31 client-key.pem [root@slave data]# chmod +r client-key.pem [root@slave ~]# service mysqld restart Shutting down MySQL.. [ 确定 ] Starting MySQL. [ 确定 ]
2. 確認ssl開啟成功
[root@slave ~]# mysql -uroot -p123 -e "show variables like '%ssl%'" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------------------------+-----------------+ | Variable_name | Value | +-------------------------------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | performance_schema_show_processlist | OFF | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | client-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | client-key.pem | +-------------------------------------+-----------------+
3. 在設定主從複製之前可以在從 mysql 上用 SSL 連線主伺服器試試。
注意已分割IP，8.2是master的IP，可以看到ssl協定Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
[root@slave ~]# cd /usr/local/mysql/data [root@slave data]# mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u rep -p123 -h 192.168.8.2 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 3 Current database: Current user: rep@192.168.8.3 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.40-log MySQL Community Server (GPL) Protocol version: 10 Connection: 192.168.8.2 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 22 min 19 sec Threads: 1 Questions: 8 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.005 --------------
1.登入slave，設定主從伺服器到伺服器replicate
記得先退出連接，登入slave的mysql服務。
mysql> exit
Bye
[root@slave data]# mysql -uroot -p123
#省略部分登录信息
mysql> change master to
-> master_host='192.168.8.2', #masterIP
-> master_user='rep', #master用户
-> master_password='123', #master密码
-> master_log_file='mysql-bin.000001', #master二进制日志文件
-> master_log_pos=154, #master位置
-> master_ssl=1, #masterssl
-> master_ssl_cert='client-cert.pem',
-> master_ssl_key='client-key.pem',
-> master_ssl_ca='ca.pem';
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave; #启用从
Query OK, 0 rows affected (0.02 sec)確認啟用成功。
1. 登入master，寫入一些資料
[root@master ~]# mysql -uroot -p123
#省略部分内容
mysql> create database bbs;
Query OK, 1 row affected (0.01 sec)
mysql> use bbs;
Database changed
mysql> create table tb1(id int,
-> name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tb1 values(1,'z3');
Query OK, 1 row affected (0.02 sec)2. 登入slave，查看資料
[root@slave ~]# mysql -uroot -p123 #省略部分内容 mysql> select * from bbs.tb1; +------+------+ | id | name | +------+------+ | 1 | z3 | +------+------+ 1 row in set (0.01 sec)
最後可以查看到z3，主從成功。
以上是MySQL基於SSL安全連線的主從複製怎麼實現的詳細內容。更多資訊請關注PHP中文網其他相關文章！
