三臺主機：
主服務器、從服務器、CA服務器
跨公網的主從復制建議加密
例如北京的和上海的主機實現數據庫的主從復制
主服務器和從服務器，都要向CA申請證書
yum -y install mariadb-server
主機1
hostname ca
exec bash
主機2
hostname slave
exec bash
CA服務器
搭建CA
[root@ca ~]# ll /etc/pki/CA/
total 0
drwxr-xr-x. 2 root root 6 Aug ?4 ?2017 certs
drwxr-xr-x. 2 root root 6 Aug ?4 ?2017 crl
drwxr-xr-x. 2 root root 6 Aug ?4 ?2017 newcerts
drwx——. 2 root root 6 Aug ?4 ?2017 private
創建一個文件夾，放mysql的證書相關的信息
mkdir /etc/my.cnf.d/ssl
cd /etc/my.cnf.d/ssl
生成私鑰文件
[root@ca /etc/my.cnf.d/ssl]# openssl genrsa 2048 > cakey.pem
Generating RSA private key, 2048 bit long modulus
……………+++
…………………………………………………………………………………….+++
e is 65537 (0x10001)
[root@ca /etc/my.cnf.d/ssl]#
為了安全需要改權限，以前是使用umask的方式修改的權限
chmod 600 cakey.pem
給自己頒發證書
[root@ca /etc/my.cnf.d/ssl]# openssl req -new -x509 -key cakey.pem -days 3650 -out cacert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server’s hostname) []:ca.magedu.com
Email Address []:
[root@ca /etc/my.cnf.d/ssl]# ll
total 8
-rw-r–r–. 1 root root 1334 Feb 28 08:31 cacert.pem
-rw——-. 1 root root 1679 Feb 28 08:28 cakey.pem
申請證書，正常流程是在自己的機器上發申請，企業內部，自己可以自己給自己頒發證書
解決master的證書
創建私鑰和申請一塊做，命令如下
-inodes 指定為不加密
國家、省、公司要求要一樣
[root@ca /etc/my.cnf.d/ssl]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr
Generating a 1024 bit RSA private key
…++++++
……………………..++++++
writing new private key to ‘master.key’
—–
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:IT_OPT
Common Name (eg, your name or your server’s hostname) []:master.magedu.com
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@ca /etc/my.cnf.d/ssl]# ll
total 16
-rw-r–r–. 1 root root 1334 Feb 28 08:31 cacert.pem
-rw——-. 1 root root 1679 Feb 28 08:28 cakey.pem
-rw-r–r–. 1 root root ?668 Feb 28 08:38 master.csr 申請
-rw-r–r–. 1 root root ?916 Feb 28 08:38 master.key 私鑰
頒發證書
證書文件：cacert.pem
證書序列號：-set_serial 01
[root@ca /etc/my.cnf.d/ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
Signature ok
subject=/C=CN/ST=beijing/L=beijing/O=magedu.com/OU=IT_OPT/CN=master.magedu.com
Getting CA Private Key
[root@ca /etc/my.cnf.d/ssl]#
slave證書申請
[root@ca /etc/my.cnf.d/ssl]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave.key > slave.csr
Generating a 1024 bit RSA private key
……………..++++++
……………………..++++++
writing new private key to ‘slave.key’
—–
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:it_yunwei
Common Name (eg, your name or your server’s hostname) []:slave.magedu.com
Email Address []:
Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@ca /etc/my.cnf.d/ssl]#
頒發slave證書
[root@ca /etc/my.cnf.d/ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
Signature ok
subject=/C=CN/ST=beijing/L=beijing/O=magedu.com/OU=it-yunwei/CN=slave.magedu.com
Getting CA Private Key
[root@ca /etc/my.cnf.d/ssl]# ll
total 32
-rw-r–r–. 1 root root 1334 Feb 28 08:31 cacert.pem
-rw——-. 1 root root 1679 Feb 28 08:28 cakey.pem
-rw-r–r–. 1 root root 1034 Feb 28 08:40 master.crt
-rw-r–r–. 1 root root ?668 Feb 28 08:38 master.csr
-rw-r–r–. 1 root root ?916 Feb 28 08:38 master.key
-rw-r–r–. 1 root root 1038 Feb 28 08:52 slave.crt
-rw-r–r–. 1 root root ?668 Feb 28 08:50 slave.csr
-rw-r–r–. 1 root root ?916 Feb 28 08:50 slave.key
[root@ca /etc/my.cnf.d/ssl]#
?
?
需要復制到master的證書文件
cacert.pem
master.crt
master.key
?
需要復制到salve的證書文件
cacert.pem
slave.crt
slave.key
?
把證書文件復制到master和salve
該文件夾，最小化安裝的系統可能會沒有
[root@master ~]# ls /etc/my.cnf.d/
mysql-clients.cnf
/etc/my.cnf.d/目錄，即使沒有裝maraidb，只要裝mariadb-libs包就會有
master機器上
hostname master
exec bash
mkdir /etc/my.cnf.d/ssl
cd /etc/my.cnf.d/ssl/
從ca機器復制到master
scp -r cacert.pem 192.168.159.102:/etc/my.cnf.d/ssl
scp -r master.crt 192.168.159.102:/etc/my.cnf.d/ssl
scp -r master.key 192.168.159.102:/etc/my.cnf.d/ssl
slave機器上
hostname slave
exec bash
mkdir /etc/my.cnf.d/ssl
cd /etc/my.cnf.d/ssl/
從ca機器復制到slave
scp -r cacert.pem 192.168.159.102:/etc/my.cnf.d/ssl
scp -r slave.crt 192.168.159.103:/etc/my.cnf.d/ssl
scp -r slave.key 192.168.159.103:/etc/my.cnf.d/ssl
查看復制過去的證書文件
[root@master ~]# cd /etc/my.cnf.d/ssl/
[root@master /etc/my.cnf.d/ssl]# ll
total 12
-rw-r–r–. 1 root root 1334 Feb 28 13:32 cacert.pem
-rw-r–r–. 1 root root 1034 Feb 28 13:34 master.crt
-rw-r–r–. 1 root root ?916 Feb 28 13:34 master.key
[root@slave /etc/my.cnf.d/ssl]# ll
total 12
-rw-r–r–. 1 root root 1334 Feb 28 13:35 cacert.pem
-rw-r–r–. 1 root root 1038 Feb 28 13:37 slave.crt
-rw-r–r–. 1 root root ?916 Feb 28 13:38 slave.key
CA機器已經用不到了，開始配置主從復制
測試證書有效性，用CA的證書驗證頒發的證書是否合法
[root@ca /etc/my.cnf.d/ssl]# openssl verify -CAfile cacert.pem master.crt slave.crt
master.crt: OK
slave.crt: OK
master機器
yum -y install mariadb-server
修改配置文件、啟動服務
[root@master ~]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table
log-bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
systemctl start mariadb
修改配置文件并重啟服務后，連接查看ssl的狀態
[root@master ~]# mysql
Welcome to the MariaDB monitor. ?Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server
……
MariaDB [(none)]> show variables like ‘%ssl%’;
+—————+——————————+
| Variable_name | Value ???????????????????????|
+—————+——————————+
| have_openssl ?| YES ?????????????????????????|
| have_ssl ?????| YES ?????????????????????????|
| ssl_ca ???????| /etc/my.cnf.d/ssl/cacert.pem |
| ssl_capath ???| ?????????????????????????????|
| ssl_cert ?????| /etc/my.cnf.d/ssl/master.crt |
| ssl_cipher ???| ?????????????????????????????|
| ssl_key ??????| /etc/my.cnf.d/ssl/master.key |
+—————+——————————+
7 rows in set (0.00 sec)
have_openssl 和 have_ssl 為 yes 是因為在配置文件中添加的ssl的功能
slave機器
yum -y install mariadb-server
systemctl start mariadb
從服務器上還沒有修改配置文件，查看ssl狀態
MariaDB [(none)]> show variables like ‘%ssl%’;
+—————+———-+
| Variable_name | Value ???|
+—————+———-+
| have_openssl ?| DISABLED |
| have_ssl ?????| DISABLED |
| ssl_ca ???????| ?????????|
| ssl_capath ???| ?????????|
| ssl_cert ?????| ?????????|
| ssl_cipher ???| ?????????|
| ssl_key ??????| ?????????|
+—————+———-+
7 rows in set (0.00 sec)
默認ssl的狀態是DISABLED，如果不是DISABLED而顯示的是NO，就表明數據庫在在編譯的時候就沒有支持ssl的功能，就需要源碼編譯了
master機器
創建主從同步使用的賬戶（不強制要求使用加密進行數據同步）
MariaDB [(none)]> grant replication slave on *.* to repluser@’192.168.159.%’ identified by ‘1234’;
grant replication slave on *.* to repluser@’192.168.159.%’ identified by ‘1234’ require ssl;
require ssl; 要求使用加密進行數據的同步
到此master服務器配置完成，查看master服務器的狀態
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: mariadb-bin.000001
Position: 399
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
slave機器
修改配置文件
[mysqld]
innodb_file_per_table
server_id=2
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key
注意：ssl的證書選項也可以在CHANGE MASTER TO命令中添加
重新啟動服務
[root@slave ~]# systemctl restart mariadb
執行命令開始復制
[root@slave ~]# mysql
Welcome to the MariaDB monitor. ?Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server
……
MariaDB [(none)]> help CHANGE MASTER TO
Name: ‘CHANGE MASTER TO’
Description:
Syntax:
CHANGE MASTER TO option [, option] …
……
CHANGE MASTER TO
MASTER_HOST=’master2.mycompany.com’,
MASTER_USER=’replication’,
MASTER_PASSWORD=’bigs3cret’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’master2-bin.001′,
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
查看復制命令的用法后，修改復制命令，復制到mysql的命令行執行
CHANGE MASTER TO
MASTER_HOST=’192.168.159.102′,
MASTER_USER=’repluser’,
MASTER_PASSWORD=’1234′,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mariadb-bin.000001‘,
MASTER_LOG_POS=399,
MASTER_CONNECT_RETRY=10,
MASTER_SSL=1;
復制到mysql命令行執行
MariaDB [(none)]> CHANGE MASTER TO
-> ??MASTER_HOST=’192.168.159.102′,
-> ??MASTER_USER=’repluser’,
-> ??MASTER_PASSWORD=’1234′,
-> ??MASTER_PORT=3306,
-> ??MASTER_LOG_FILE=‘mariadb-bin.000001′,
-> ??MASTER_LOG_POS=399,
-> ??MASTER_CONNECT_RETRY=10,
-> ??MASTER_SSL=1;
Query OK, 0 rows affected (0.03 sec)
查看從服務器的狀態
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.159.102
Master_User: repluser
Master_Port: 3306
…………
Master_SSL_Allowed: Yes
……
Master_Server_Id: 0
1 row in set (0.00 sec)
開始同步
MariaDB [(none)]> start slave;
查看從服務器的狀態
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.159.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 399
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
Exec_Master_Log_Pos: 399
Relay_Log_Space: 827
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
……
Master_Server_Id: 1
1 row in set (0.00 sec)
測試
在主服務器上創建數據庫
create database db1;
在從服務器上查看是否同步
MariaDB [(none)]> show databases;
+——————–+
| Database ??????????|
+——————–+
| information_schema |
| db1 ???????????????|
也可以用對應的slave的命令去連接的時候，可以測試能否用ssl進行加密
在進行加密的時候，授權用戶，沒有強迫授權用戶必須加密
在master服務器上刪除創建的授權用戶，重新創建，指定為強制使用加密
salve服務器先停止同步
stop slave;
master刪除創建的同步用戶
drop user repluser@’192.168.159.%’;
重新創建同步授權用戶，要求強制使用加密，必須以加密的方式進行主從復制，而不是可選的
grant replication slave on *.* to repluser@’192.168.159.%’ identified by ‘1234’ require ssl;
因為剛才創建的同步賬戶被刪除，刪除并重新創建同步賬戶賬戶的日志會記錄，并同步到從服務器上。從服務器上沒有之前創建的同步賬戶，會報錯，但是不影響正常同步
從服務器啟動同步（報錯不影響，可以修改同步的位置避免）
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
……
Last_SQL_Error: Error ‘Operation DROP USER failed for ‘repluser’@’192.168.159.%” on query. Default database: ”. Query: ‘drop user repluser@’192.168.159.%’‘
修改為同步二進制文件的位置
查看master的狀態
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: mariadb-bin.000001
Position: 739
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
從服務器停止同步、修改同步的位置、開啟同步
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO ??MASTER_HOST=’192.168.159.102′, ??MASTER_USER=’repluser’, ??MASTER_PASSWORD=’1234′, ??MASTER_PORT=3306, ??MASTER_LOG_FILE=’mariadb-bin.000001′, ??MASTER_LOG_POS=739, ??MASTER_CONNECT_RETRY=10, ??MASTER_SSL=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
master機器創建一個數據庫
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)
slave機器查看同步的情況
MariaDB [(none)]> show databases;
+——————–+
| Database ??????????|
+——————–+
| information_schema |
| db1 ???????????????|
| db2 ???????????????|
可以復制
在命令行上測試加密
[root@slave ~]# mysql –ssl-ca=/etc/my.cnf.d/ssl/cacert.pem –ssl-cert=/etc/my.cnf.d/ssl/slave.crt –ssl-key=/etc/my.cnf.d/ssl/slave.key -h192.168.159.102 -urepluser -p1234
Welcome to the MariaDB monitor. ?Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.56-MariaDB MariaDB Server
如果key錯誤，就無法連接了
[root@slave ~]# mysql –ssl-ca=/etc/my.cnf.d/ssl/cacert.pem –ssl-cert=/etc/my.cnf.d/ssl/slave.crt –ssl-key=/etc/my.cnf.d/ssl/slave.key_test?-h192.168.159.102 -urepluser -p1234
SSL error: Unable to get private key from ‘/etc/my.cnf.d/ssl/slave.key_test’
ERROR 2026 (HY000): SSL connection error: Unable to get private key
[root@slave ~]#
修改從服務器的配置文件，在同步選項中加入ssl文件的相關路徑，不在配置文件中添加了
[root@slave ~]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table
server_id=2
ssl
#ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
#ssl-cert=/etc/my.cnf.d/ssl/slave.crt
#ssl-key=/etc/my.cnf.d/ssl/slave.key
重新啟動服務
systemctl restart mariadb
停止同步
stop slave;
在master服務器查看master的狀態
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: mariadb-bin.000001
Position: 820
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
修改同步選項
CHANGE MASTER TO
MASTER_HOST=’192.168.159.102′,
MASTER_USER=’repluser’,
MASTER_PASSWORD=’1234′,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mariadb-bin.000001′,
MASTER_LOG_POS=820,
MASTER_CONNECT_RETRY=10,
MASTER_SSL_CA=’/etc/my.cnf.d/ssl/cacert.pem’,
MASTER_SSL_CERT=’/etc/my.cnf.d/ssl/slave.crt’,
MASTER_SSL_KEY=’/etc/my.cnf.d/ssl/slave.key’,
MASTER_SSL=1;
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=’192.168.159.102′,
-> MASTER_USER=’repluser’,
-> MASTER_PASSWORD=’1234′,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=’mariadb-bin.000001′,
-> MASTER_LOG_POS=820,
-> MASTER_CONNECT_RETRY=10,
-> MASTER_SSL_CA=’/etc/my.cnf.d/ssl/cacert.pem’,
-> MASTER_SSL_CERT=’/etc/my.cnf.d/ssl/slave.crt’,
-> MASTER_SSL_KEY=’/etc/my.cnf.d/ssl/slave.key’,
-> MASTER_SSL=1;
Query OK, 0 rows affected (0.00 sec)
啟動同步
start slave;
查看從服務器的狀態
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.159.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 820
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
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: 820
Relay_Log_Space: 827
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key
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
1 row in set (0.00 sec)
因為是CHANGE MASTER TO命令添加的，所以可以看到添加的ssl選項的詳細信息！
測試同步
master
create database db3;
slave
MariaDB [(none)]> show databases;
+——————–+
| Database ??????????|
+——————–+
| information_schema |
| db1 ???????????????|
| db2 ???????????????|
| db3 ???????????????|
這就是基于ssl的實現！
本文來自投稿，不代表Linux運維部落立場，如若轉載，請注明出處：http://www.www58058.com/91781
