看了好几个博客,也参考了别人的文档 ,排了好多坑。总算是做出来了
总结一下步骤:
1,下载openssl
2,生成CA机构,生成证书。
3,对mysql 的master,slave生成对应的证书,密钥
4,修改mysql的配置文件
5,对服务器进行ssl登陆的检测
6,进行主从复制
一,生成CA机构和自签证书
#yum -y install openssl //安装OpenSSL
#vim /etc/pki/tls/openssl.cnf
certificate = $dir/ca.crt //生成的自签证书文件名
private_key = $dir/private/ca.key //生成的ca的私钥文件名
#cd /etc/pki/CA
#(umask 077; openssl genrsa -out private/ca.key 2048) //生成私钥
//genrsa 加密方式 2048 加密字符串长度 private/ca.key 生成的文件名
#openssl req -new -x509 -key private/ca.key -out ca.crt -days 3650 【具体步骤见尾部】
//生成自签证书 x509 是证书模板格式 ca.crt 是CA生成的自签证书 3650 是有效期(10年)
#touch index.txt //ca必需的文件
#echo 01 > serial //ca必需的文件
二,生成mysql服务器的证书
master
#cd /etc/my.cnf.d/ //进入目录,位置随便,后面指定就好了
#(umask 077; openssl genrsa -out master.key 2048) //生成主服务器的密钥
#openssl req -new -key master.key -out master.csr //生成证书的申请
【在ca服务器执行】 将master.csr传到ca服务器
#openssl ca -in master.csr -out master.crt -days 365 //将证书申请发送到CA服务器,生成证书
【将maste.crt传到主服务器】
【将ca.crt传到主服务器】
ls /etc/my.cnf.d/
ca.crt master.crt master.key 最后目录里存在master的证书,密钥,还有CA服务器自签证书
chown -R mysql.mysql /etc/my.cnf.d/ 赋权
chmod 600 /etc/my.cnf.d/ //固定的权限,不要进行其他的修改,否则会出错
slave 服务器
#cd /etc/my.cnf.d/ //进入目录,位置随便,后面指定就好了
#(umask 077; openssl genrsa -out slave.key 2048) //生成主服务器的密钥
#openssl req -new -key slave.key -out slave.csr //生成证书的申请
【在ca服务器执行】 将slave.csr传到ca服务器
#openssl ca -in slave.csr -out slave.crt -days 365 //将证书申请发送到CA服务器,生成证书
【将maste.crt传到从服务器】
【将ca.crt传到从服务器】
ls /etc/my.cnf.d/
ca.crt slave.crt slave.key 最后目录里存在master的证书,密钥,还有CA服务器自签证书
chown -R mysql.mysql /etc/my.cnf.d/ 赋权
chmod 600 /etc/my.cnf.d/ //固定的权限,不要进行其他的修改,否则会出错
三,修改mysql的配置文件
master
#vim /etc/my.cnf
添加
ssl_ca = /etc/my.cnf.d/ca.crt #指定CA证书的位置
ssl_cert = /etc/my.cnf.d/master.crt #指定master证书的位置
ssl_key = /etc/my.cnf.d/master.key #指定master密钥的位置
重启服务
#systemctl restart mysqld
slave
#vim /etc/my.cnf
添加
ssl_ca = /etc/my.cnf.d/ca.crt #指定CA证书的位置
ssl_cert = /etc/my.cnf.d/slave.crt #指定master证书的位置
ssl_key = /etc/my.cnf.d/slave.key #指定master密钥的位置
重启服务
#systemctl restart mysqld
四,检查配置是否成功
mysql> show variables like '%ssl%';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| have_openssl | YES | 为yes是为可用
| have_ssl | YES | 为yes是为可用
| ssl_ca | /etc/my.cnf.d/ca.crt |
| ssl_capath | |
| ssl_cert | /etc/my.cnf.d/master.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/my.cnf.d/master.key |
+---------------+--------------------------+
9 rows in set (0.00 sec)
登陆验证【master/slave】
mysql --ssl-ca=/etc/my.cnf.d/ca.crt --ssl-cert=/etc/my.cnf.d/master.crt --ssl-key=/etc/my.cnf.d/master.key -uroot -p'Wenliang@123'
如果进入msyql视为成功
五,开始进行主从复制的配置
master
mysql >grant replication slave on *.* to 'repl'@'10.18.44.%' identified by 'Wenliang@123' require ssl;
mysql> flush privileges;
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 2247
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
slave
mysql> stop slave;
mysql> change master to
master_host = '10.18.44.189',
master_user = 'repl',
master_password = 'Wenliang@123',
master_log_file = 'mysql-bin.000003',
master_log_pos = 2247,
master_ssl=1,
master_ssl_ca='/etc/my.cnf.d/ca.crt',
master_ssl_cert='/etc/my.cnf.d/slave.crt',
master_ssl_key='/etc/my.cnf.d/slave.key';
mysql > start slave;
查看是否成功
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.18.44.189
Master_User: wenliang
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2247
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 2133
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes //此处为yes视为成功
Slave_SQL_Running: Yes //此处为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: 2247
Relay_Log_Space: 2334
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/my.cnf.d/ca.crt
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/my.cnf.d/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /etc/my.cnf.d/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
Master_UUID: 4383e2bb-8f53-11e8-8eb0-000c29affc23
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
附:MySQL主从不同步
主要有三个原因:
1,网络不通
2,指定master时密码不对
3,指定master的pos节点位置不正确
尽量避免的问题
当准备MySQL主动复制时,尽量不要重启master服务器,容易发生节点改变,排错不易。
附:CA认证
[root@mysql3 CA]# openssl req -new -x509 -key private/ca.key -out ca.crt -days 3650
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) []:BJ
Locality Name (eg, city) [Default City]:B
Organization Name (eg, company) [Default Company Ltd]:^C
[root@mysql3 CA]# openssl req -new -x509 -key private/ca.key -out ca.crt -days 3650
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) []:BJ
Locality Name (eg, city) [Default City]:BJ
Organization Name (eg, company) [Default Company Ltd]:WL
Organizational Unit Name (eg, section) []:wenliang
Common Name (eg, your name or your server's hostname) []:www.ca.com
Email Address []:lwl@163.com
mysql的证书申请
master.key 由master服务器传输到CA服务器
[root@mysql3 mysql]# openssl req -new -key master.key -out master.csr
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 //必须与CA一致
State or Province Name (full name) []:BJ //必须与CA一致
Locality Name (eg, city) [Default City]:BJ //必须与CA一致
Organization Name (eg, company) [Default Company Ltd]:WL //必须与CA一致
Organizational Unit Name (eg, section) []:wenliang // //必须与CA一致
Common Name (eg, your name or your server's hostname) []:www.mysql.com
Email Address []:lllzzz@163.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
mysql的证书
[root@mysql3 mysql]# openssl ca -in master.csr -out master.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Jul 24 21:11:12 2018 GMT
Not After : Jul 24 21:11:12 2019 GMT
Subject:
countryName = CN
stateOrProvinceName = BJ
organizationName = WL
organizationalUnitName = wenliang
commonName = www.mysql.com
emailAddress = lllzzz@163.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
AC:E2:CE:E1:DD:C2:EF:F1:3A:F0:29:9C:70:F6:0F:1D:ED:D9:82:C9
X509v3 Authority Key Identifier:
keyid:1B:5D:34:54:18:67:5F:1D:40:88:12:D3:4E:55:B3:2A:DD:F3:7A:1A
Certificate is to be certified until Jul 24 21:11:12 2019 GMT (365 days)
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@mysql3 mysql]# ls
master.crt master.csr master.key