实验：MySQL复制的SSL加密

三台主机：

主服务器、从服务器、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的实现！