三台主机：
主服务器、从服务器、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.178linux.com/91781
