mysql基于ssl加密认证的主从复制:
Master:
IP地址:172.16.77.77
MySQL版本:5.5.28
操作系统:RHEL5.8
Slave:
IP地址:172.16.12.7
MySQL版本:5.5.28
操作系统:RHEL5.8
基于ssl加密的mysql主从复制概述:
若要基于ssl加密进行复制传输,则就需要进行证书的签署和认证,这里在主服务器上进行自签署证书,分别为主服务器的mysql和从服务器的mysql颁发证书。
一、准备证书,私钥
1、配置Master为CA服务器
#vim /etc/pki/tls/openssl.cnf
将dir= ../../CA修改为
dir= /etc/pki/CA
#(umask 077;openssl genrsa 2048>private/cakey.pem)
#openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
#mkdir certs crl newcerts
#touch index.txt
#echo 01>serial
2、为Master上的Mysql准备私钥以及颁发证书
#mkdir /usr/local/mysql/ssl
#cd ssl/
#(umask 077;openssl genrsa 1024>sql.key)
#openssl req -new -key mysql.key -out sql.csr
#openssl ca -in mysql.csr -out sql.crt
#cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
#chown -R mysql.mysql ssl/
3、为Slave上的Mysql准备私钥以及申请证书
#mkdir /usr/local/mysql/ssl
#cd ssl/
#(umask 077;openssl genrsa 1024>sql.key)
#openssl req -new -key mysql.key -out sql.csr
#scp ./mysql.csr 172.16.77.77:/tmp
4、在Master上为Slave签发证书
#cd /tmp
#openssl ca -in sql.csr -out sql.crt
#scp sql.crt 172.16.12.7:/usr/local/mysql/ssl
#cd /etc/pki/CA
#scp cacert.pem 172.16.12.7:/usr/local/mysql/ssl
二、设置主从服务
在172.16.77.77服务器上
编辑/etc/my.cnf
#vim /etc/my.cnf
将serier_id修改为1
server_id=1#修改server_id=1
log_bin=mysql-bin #开启二进制日志
sync_binlog=1#任何一个事务提交之后就立即写入到磁盘中的二进制文件
innodb_flush_logs_at_trx_commit=1#任何一个事物提交之后就立即写入到磁盘中的日志文件
保存退出,启动mysql
#service mysqld start
在172.16.12.7服务器上
编辑/etc/my.cnf
#vim /etc/my.cnf
server_id=7#修改server_id=7
#log-bin #注释掉log-bin,从服务器不需要二进制日志,因此将其关闭
relay-log=mysql-relay #定义中继日志名,开启从服务器中继日志
relay-log-index=mysql-relay.index #定义中继日志索引名,开启从服务器中继索引
read_only=1#设定从服务器只能进行读操作,不能进行写操作
保存退出,启动mysql
#service mysqld start
三、开启Mysql的ssl功能
在Master上:
登录Mysql查看
mysql>show global variables like '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+----------+
输出为DISABLED表示ssl还未开启,
编辑/etc/my.cnf
在[mysqld]和[mysqldump]之间,加入以下内容:
ssl #表示开启mysql的ssl功能
保存后重新启动mysql,再次登录mysql
mysql>show global variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+-------+
输出为YES表示ssl已经开启。
在Slave上:执行同样的操作
mysql>show global variables like '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+----------+
输出为DISABLED表示ssl还未开启,
编辑/etc/my.cnf
在[mysqld]和[mysqldump]之间,加入一行ssl:
ssl
保存后重新启动mysql,再次登录mysql
mysql>show global variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+-------+
##输出为YES表示ssl已经开启
四、配置主从服务的ssl功能
在Master上:
编辑配置文件:
vim /etc/my.cnf
在之前添加的ssl下面添加以下内容:
ssl-ca=/usr/local/mysql/ssl/cacert.pem
ssl-cert=/usr/local/mysql/ssl/sql.crt
ssl-key=/usr/local/mysql/ssl/sql.key
这里一定要对应到所存放证书和私钥的绝对路径
保存退出,重新启动Mysql
mysql>show global variables like '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/sql.crt |
| ssl_cipher | |
| ssl_key | /usr/local/mysql/ssl/sql.key |
+---------------+---------------------------------+
在Slave上:
编辑配置文件:
#vim /etc/my.cnf
在之前添加的ssl下面添加以下内容:
ssl-ca=/usr/local/mysql/ssl/cacert.pem
ssl-cert=/usr/local/mysql/ssl/sql.crt
ssl-key=/usr/local/mysql/ssl/sql.key
这里一定要对应到您所存放证书和私钥的绝对路径
保存退出,重新启动Mysql
mysql>show global variables like '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/sql.crt |
| ssl_cipher | |
| ssl_key | /usr/local/mysql/ssl/sql.key |
+---------------+---------------------------------+
在Slave上:
mysql>change master to
->master_host='172.16.77.77',
->master_user='weiyang',
->master_password='weiyang',
->master_log_file='mysql-bin.000020',
->master_log_pos=107,
->master_ssl=1,
->master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',
->master_ssl_cert='/usr/local/mysql/ssl/sql.crt',
->master_ssl_key='/usr/local/mysql/ssl/sql.key';
Query OK, 0 rows affected (0.17 sec)
mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.77.77
Master_User: weiyang
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000020
Read_Master_Log_Pos: 608
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000020
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: 107
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: /usr/local/mysql/ssl
Master_SSL_Cert: /usr/local/mysql/ssl/sql.crt
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/ssl/sql.key
Seconds_Behind_Master: NULL
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
mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
输出信息为 Slave_IO_Running: Yes Slave_SQL_Running: Yes Master_SSL_Allowed: Yes 说明,基于ssl的配置已经成功