分享程序员开发的那些事...
更新时间：2022-09-18 11:49:45
一、准备：
mysql replication（ssl加密方式传输）
mysql-5.5.45-linux2.6-i686.tar.gz（通用二进制格式包）
两node，一主一从
master（node1:192.168.41.131，既是CA又是master）
slave（node2:192.168.41.132）
环境：
[root@node1 ~]# uname -a
Linux node1.magedu.com 2.6.18-308.el5 #1SMP Fri Jan 27 17:21:15 EST 2012 i686 i686 i386 GNU/Linux
注意：master和slave的私钥及证书要同名（本例中两端均为mysql.key、mysql.crt，要在两端各自生成），否则无法使用ssl传输
二、操作：
1、在master和slave上安装mysql
node{1,2}-side：
[root@node1 ~]# mkdir /mydata/data -pv（生产环境***将数据目录放在LVM中）
[root@node1 ~]# useradd -r mysql
[root@node1 ~]# chown -R mysql.mysql/mydata/data/
[root@node1 ~]# tar xf mysql-5.5.45-linux2.6-i686.tar.gz -C /usr/local/
[root@node1 ~]# cd /usr/local/
[root@node1 local]# ln -sv mysql-5.5.45-linux2.6-i686/ mysql
[root@node1 local]# ll
……
lrwxrwxrwx 1 root root 27 Dec 18 23:13mysql -> mysql-5.5.45-linux2.6-i686/
……
[root@node1 mysql]# chown -R root.mysql ./
[root@node1 mysql]# ll
……
[root@node1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
[root@node1 mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@node1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node1 mysql]# chkconfig --add mysqld
[root@node1 mysql]# chkconfig mysqld on
[root@node1 mysql]# chkconfig --list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@node1 mysql]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@node1 mysql]# . !$
2、在master上（生成ca私钥及颁发ca自签证书；并签署颁发mysql-master的证书）
node1-side：
[root@node1 mysql]# mkdir ssl/
[root@node1 mysql]# cd ssl
[root@node1 ssl]# vim /etc/pki/tls/openssl.cnf
[ CA_default ]
dir = /etc/pki/CA
[root@node1 ssl]# cd /etc/pki/CA
[root@node1 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)（生成ca私钥）
Generating RSA private key, 2048 bit longmodulus
................+++
...........................................................................................................+++
e is 65537 (0x10001)
[root@node1 CA]# ll private/
total 8
-rw------- 1 root root 1679 Dec 19 06:51cakey.pem
[root@node1 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem（颁发ca自签证书，注意server’shostname）
Country Name (2 letter code) [GB]:CN
State or Province Name (full name)[Berkshire]:SH
Locality Name (eg, city) [Newbury]:SH
Organization Name (eg, company) [My CompanyLtd]:itownet
Organizational Unit Name (eg, section)[]:TECH
Common Name (eg, your name or your server'shostname) []:ca.magedu.com
Email Address []:ca@magedu.com
[root@node1 CA]# touch index.txt
[root@node1 CA]# echo 01 > serial
[root@node1 CA]# ll
total 52
-rw-r--r-- 1 root root 1586 Dec 19 06:54cacert.pem
drwxr-xr-x 2 root root 4096 Dec 19 02:14certs
drwxr-xr-x 2 root root 4096 Dec 19 02:14crl
-rw-r--r-- 1 root root 0 Dec 19 06:55 index.txt
drwxr-xr-x 2 root root 4096 Dec 19 04:29newcerts
drwx------ 2 root root 4096 Dec 19 06:51private
-rw-r--r-- 1 root root 3 Dec 19 06:55 serial
[root@node1 CA]# cd /usr/local/mysql/ssl
[root@node1 ssl]# (umask 077;openssl genrsa -out mysql.key 1024)（生成master端的私钥）
Generating RSA private key, 1024 bit longmodulus
....++++++
...............................................................................++++++
e is 65537 (0x10001)
[root@node1 ssl]# openssl req -new -key mysql.key -out mysql.csr（生成master的证书签署请求certificate signature request，注意server’s hostname一定不能与slave端的重名）
……
Country Name (2 letter code) [GB]:CN
State or Province Name (full name)[Berkshire]:SH
Locality Name (eg, city) [Newbury]:SH
Organization Name (eg, company) [My CompanyLtd]:itownet
Organizational Unit Name (eg, section)[]:TECH
Common Name (eg, your name or your server'shostname) []:master.magedu.com
Email Address []:master@magedu.com
Please enter the following 'extra'attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@node1 ssl]# openssl ca -in mysql.csr -out mysql.crt -days 365（为master颁发证书）
……
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified,commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
[root@node1 ssl]# cp /etc/pki/CA/cacert.pem./
[root@node1 ssl]# chown -R mysql.mysql ./
[root@node1 ssl]# ll
total 32
-rw-r--r-- 1 mysql mysql 1586 Dec 19 07:10 cacert.pem
-rw-r--r-- 1 mysql mysql 3830 Dec 19 07:08mysql.crt
-rw-r--r-- 1 mysql mysql 692 Dec 19 07:03 mysql.csr
-rw------- 1 mysql mysql 887 Dec 19 07:02 mysql.key
3、在slave上（生成私钥及slave的证书签署请求，传至ca端签署）
node2-side：
[root@node2 ~]# mkdir /usr/local/mysql/ssl
[root@node2 ~]# cd !$
cd /usr/local/mysql/ssl
[root@node2 ssl]# (umask 077;openssl genrsa -out mysql.key 1024)（私钥）
Generating RSA private key, 1024 bit longmodulus
................................++++++
..++++++
e is 65537 (0x10001)
[root@node2 ssl]# openssl req -new -key mysql.key -out mysql.csr（证书签署请求，注意server’s hostname）
……
Country Name (2 letter code) [GB]:CN
State or Province Name (full name)[Berkshire]:SH
Locality Name (eg, city) [Newbury]:SH
Organization Name (eg, company) [My CompanyLtd]:itownet
Organizational Unit Name (eg, section)[]:TECH
Common Name (eg, your name or your server'shostname) []:slave.magedu.com
Email Address []:slave@magedu.com
Please enter the following 'extra'attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@node2 ssl]# scp mysql.csrnode1:/root/
mysql.csr 100% 692 0.7KB/s 00:00
node1-side：（在主上签署请求，并将颁发给slave的证书及ca自签证书一同传给slave）
[root@node1 ssl]# cd
[root@node1 ~]# openssl ca -in mysql.csr -out mysql.crt -days 365（在主上给予签署）
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified,commit? [y/n]y
[root@node1 ~]# scp mysql.crt node2:/usr/local/mysql/ssl/
mysql.crt 100% 3824 3.7KB/s 00:00
[root@node1 ~]# scp /etc/pki/CA/cacert.pem node2:/usr/local/mysql/ssl/
cacert.pem 100%1586 1.6KB/s 00:00
node2-side：
[root@node2 ssl]# chown -R mysql.mysql ./
[root@node2 ssl]# ll
total 32
-rw-r--r-- 1 mysql mysql 1586 Dec 19 07:23cacert.pem
-rw-r--r-- 1 mysql mysql 3824 Dec 19 07:23mysql.crt
-rw-r--r-- 1 mysql mysql 692 Dec 19 07:17 mysql.csr
-rw------- 1 mysql mysql 891 Dec 19 07:16 mysql.key
4、编辑master、slave的配置文件，并启动服务：
node1-side：
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
log-bin = mysql-bin
log-bin-index = mysql-bin.index
server-id = 1（注意主从不能一样，MySQL集群内唯一，范围1至2^32-1）
datadir = /mydata/data
ssl（表示开启ssl）
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_cert = /usr/local/mysql/ssl/mysql.crt
ssl_key = /usr/local/mysql/ssl/mysql.key
innodb_file_per_table = 1
[root@node1 ~]# service mysqld start
Starting MySQL.. [ OK ]
node2-side：（中继日志必须开，可以不开启二进制日志）
[mysqld]
relay-log = relay-log
relay-log-index = relay-log.index
server-id = 11
datadir = /mydata/data
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_cert = /usr/local/mysql/ssl/mysql.crt
ssl_key = /usr/local/mysql/ssl/mysql.key
innodb_file_per_table = 1
[root@node2 ~]# service mysqld start
Starting MySQL.. [ OK ]
5、在主端授权（复制仅能通过ssl传输复制），在从端连接到主：
node1-side：
[root@node1 ~]# mysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'jowin'@'192.168.41.%' IDENTIFIED BY 'jowin' REQUIRE SSL;
Query OK, 0 rows affected (0.17 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 | 347 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
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/mysql.crt |
| ssl_cipher | |
| ssl_key | /usr/local/mysql/ssl/mysql.key |
+---------------+---------------------------------+
7 rows in set (0.00 sec)
node2-side：
[root@node2 ~]# mysql
mysql> change master to master_host='192.168.41.131',master_user='jowin',master_password='jowin',master_log_file='mysql-bin.000011',master_log_pos=347,master_ssl=1,master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',master_ssl_cert='/usr/local/mysql/ssl/mysql.crt',master_ssl_key='/usr/local/mysql/ssl/mysql.key';
Query OK, 0 rows affected (0.13 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
6、测试：
node1-side：
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)
node2-side：
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.08 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.41.131
Master_User: jowin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000005
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000012
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: 107
Relay_Log_Space: 549
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/mysql.crt
Master_SSL_Cipher:
Master_SSL_Key:/usr/local/mysql/ssl/mysql.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)
[root@node2 ~]# mysql -u jowin -p -h192.168.41.131（若不用ssl方式连接，则连不上）
Enter password:
ERROR 1045 (28000): Access denied for user'jowin'@'node2.magedu.com' (using password: YES)
[root@node2 ~]# mysql -ujowin -p -h192.168.41.131 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/mysql.crt --ssl-key=/usr/local/mysql/ssl/mysql.key（使用ssl方式则连接正常）
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.45-log MySQL CommunityServer (GPL)
Copyright (c) 2000, 2015, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql>
本文转自 chaijowin 51CTO博客，原文链接：http://blog.51cto.com/jowin/1728229，如需转载请自行联系原作者