mysql主从备份 ssl_mysql -- mysql基于ssl的主从复制

mysql基于ssl的主从复制

由于mysql在复制过程中是明文的，所以就大大降低了安全性，因此需要借助于ssl加密来增加其复制的安全性。

主服务器

node1:172.16.200.1

从服务器

node2:172.16.200.2

在配置ssl前我们先看下一下ssl的状态信息如下图所示

输出DISABLE表示ssl还未开启，只需在/etc/my.cnf文件中加入ssl即可

#vim /etc/my.cnf

[mysqld]

ssl

#service mysqld restart //重启使配置文件生效

配置主从服务的ssl功能

node1(master)

1、配置CA服务器(这里在node1上配置)

# vim /etc/pki/tls/openssl.cnf

dir = /etc/pki/CA

# cd /etc/pki/CA/

# mkdir certs newcerts crl

# touch index.txt

# echo 01 > serial

# (umask 077;openssl genrsa -out private/cakey.pem 1024)

# openssl req -x509 -new -key private/cakey.pem -out cacert.pem -days 365

2、为主服务器node1准备私钥并颁发证书

创建存放证书的目录

# mkdir /usr/local/mysql/ssl

# cd /usr/local/mysql/ssl

创建所需要的证书

# (umask 077;openssl genrsa 1024 > node1.key)

# openssl req -new -key node1.key -out node1.csr

# openssl ca -in node1.csr -out node1.crt -days 365

#cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl

#chown -R mysql:mysql /usr/local/mysql/ssl

3、为slave上的mysql准备私钥及申请证书

创建存放证书的目录

# mkdir /usr/local/mysql/ssl

# cd /usr/local/mysql/ssl

创建所需要的证书

# (umask 077;openssl genrsa 1024 > node2.key)

# openssl req -new -key node2.key -out node2.csr

#scp ./node2.csr node1：/root

在node1(主服务器)上为 node2(从服务器)签发证书

#openssl ca -in node2.csr -out node2.crt

#cd /usr/local/mysql/ssl

#scp node2.crt /etc/pki/CA/cacert.pem nodes2:/usr/local/mysql/ssl

到此为止证书已经准备完成，请确保node1和node2上有如下文件，并且属主和属组为mysql

在Master即node1上

[root@mynode1 ~]# ll /usr/local/mysql/ssl

total 104

-rw-r--r-- 1 mysql mysql 1103 Aug 9 20:51 cacert.pem

-rw-r--r-- 1 mysql mysql 3047 Aug 9 20:40 node1.crt

-rw-r--r-- 1 mysql mysql 639 Aug 9 20:35 node1.csr

-rw------- 1 mysql mysql 887 Aug 9 20:34 node1.key

在Slave即node2上

[root@mynode1 ~]# ll /usr/local/mysql/ssl

total 104

-rw-r--r-- 1 mysql mysql 1103 Aug 9 20:51 cacert.pem

-rw-r--r-- 1 mysql mysql 3047 Aug 9 20:40 node2.crt

-rw-r--r-- 1 mysql mysql 639 Aug 9 20:35 node2.csr

-rw------- 1 mysql mysql 887 Aug 9 20:34 node2.key

4、修改配置文件

# vim /etc/my.cnf

服务器node1:

[mysqld]

skip-slave-start=1 //设置重启服务不自动开启线程，需要手动开启

ssl //指定ssl,CA信息

ssl-ca=/usr/local/mysql/ssl/cacert.pem

ssl-cert=/usr/local/mysql/ssl/node1.crt

ssl-key=/usr/local/mysql/ssl/node1.key

log-bin=mysql-bin

relay-log=mysql-relay //开启中继日志

auto-increment-increment = 2 //每次ID加2

auto-increment-offset = 1 //设置起始自动增长

server-id = 10

服务器node2:

skip-slave-start=1

ssl

log-bin=mysql-bin

relay-log=mysql-relay

server-id = 20

5、重启后生效并查看有关ssl的信信息，在node1上进行操作

# service mysqld restart

mysql> show variables like '%ssl%';

6、在node1上创建具有复制权限的用户并授权给从服务器

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repluser@'172.16.200.%' IDENTIFIED BY '1234' REQUIRE SSL;

mysql> flush privileges;

7、查看自己的master状态信息并记录

服务器node1

mysql>show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.00005 | 107 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec

8、配置slave,在配置之前需要说明一下，如果已经启动slave,并配置过slave信息的，需要先停止slave并删除配置的slave。

在node2上配置slave：

mysql> CHANGE MASTER TO MASTER_HOST = '172.16.200.1' ,

-> MASTER_USER = 'repluser' ,

-> MASTER_PASSWORD = '1234' ,

-> MASTER_LOG_FILE = 'mysql-bin.00005' ,

-> MASTER_LOG_POS = 107 ,

-> MASTER_SSL = 1 ,

-> MASTER_SSL_CA = '/usr/local/mysql/ssl/cacert.pem' ,

-> MASTER_SSL_CERT = '/usr/local/mysql/ssl/node2.crt' ,

-> MASTER_SSL_KEY = '/usr/local/mysql/ssl/node2.key';

9、配置完成后，启动slave并查看信息如下

mysql>start slave;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.200.1

Master_User: repluser

Master_Port: 3306

connect_Retry: 60

Master_Log_file: mysql-bin.000005

Read_Master_Log_Pos: 107

Relay_Log_file: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_file: mysql-bin.000005

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: 608

Relay_Log_Space: 403

Until_condition: None

Until_Log_file:

Until_Log_Pos: 0

Master_SSL_Allowed: Yes

Master_SSL_cA_file: /usr/local/mysql/ssl/cacert.pem

Master_SSL_cA_Path:

Master_SSL_cert: /usr/local/mysql/ssl/node2.crt

Master_SSL_cipher:

Master_SSL_Key: /usr/local/mysql/ssl/node2.key

Seconds_Behind_Master: 2132

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

到此为止基于ssl的mysql主从复制已经配置成功。

10、测试ssl是否生效

在从服务器Slave即node2上：

使用repluser用户登录mysql

#mysql --ssl-ca=/usr/local/mysql/ssl/cacert.pem \

--ssl-cert=/usr/local/mysql/ssl/node2.crt \

--ssl-key=/usr/local/mysql/ssl/node2.key -urepluser -h172.16.200.1 -p1234

mysql> \s

--------------

mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i386) using readline 5.1

connection id: 3

current database:

current user: repluser@172.16.200.1

SSL: cipher in use is DHE-RSA-AES256-SHA

current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.5.19-log MySQL community Server (GPL)

Protocol version: 10

connection: 172.16.200.1 via TcP/IP

Server characterset: latin1

Db characterset: latin1

client characterset: latin1

conn. characterset: latin1

TcP port: 3306

Uptime: 8 min 18 sec

Threads: 3 Questions: 20 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg

输出信息：

SSL: cipher in use is DHE-RSA-AES256-SHA

参考文章： http://blog.51cto.com/sxhxt/960352