[MySQL] 基于SSL的mysql主从复制

[root@node3 support-files]# crontab -e          ####主节点
*/3 * * * *  /usr/sbin/ntpdate 172.16.0.1 &> /dev/null
[root@node1 CA ]# crontab -e                  ####从节
 */3 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null

[root@node1 CA ]#(umask 077;openssl genrsa -out private/cakey.pem 1024)
Generating RSA private key, 1024 bit long modulus
...................++++++
................++++++
e is 65537 (0x10001)

[root@node1 CA ]#openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365
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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:14qi
Common Name (eg,your name or your server's hostname) []:cacert
Email Address []:admin.stu11.com
[root@node1 CA ]# touch index.txt      
[root@node1 CA ]# echo 01 > serial

[root@node1 CA ]# cd /etc/mysql/ssl/
[root@node1 ssl ]# (umask 077;openssl genrsa -out master.key 1024)
Generating RSA
private key, 1024 bit long modulus
...................................++++++
.............................++++++
e is 65537 (0x10001)

[root@node1ssl ]# openssl req -new -key master.key -out master.csr -days 365 
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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:14qi
Common Name (eg, your name or your server's hostname) []:master.crt
Email Address[]:admin@stu11.com
Please enter thefollowing 'extra' attributes
to be sent with your certificate request
A challenge password[]:
An optional company name []:

[root@node1 ssl ]#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: Jan 25 07:12:12 2015
GMT
      Not After : Jan 25 07:12:12 2016
GMT
    Subject:
      countryName         = CN
      stateOrProvinceName     = HA
      organizationName      = magedu
      organizationalUnitName  = 14qi
      commonName        = master.crt
      emailAddress        = admin@stu11.com
    X509v3 extensions:
      X509v3 Basic Constraints:
        CA:FALSE
      Netscape Comment:
        OpenSSL Generated Certificate
      X509v3 Subject Key Identifier:
         
93:50:74:97:39:91:86:5A:1F:C6:2F:6A:87:FB:77:04:7B:70:33:5C
      X509v3 Authority Key Identifier:      
keyid:C0:69:22:4E:9A:E5:BD:13:2B:BD:93:7B:0F:99:E6:0F:3A:FA:40:7E
Certificate is to be
certified until Jan 25 07:12:12 2016 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@node1 ssl ]#
ls
master.crt  master.csr 
master.key
[root@node1 ssl ]#
chown -R mysql:mysql *
[root@node1 ssl ]#ll
total 16 
-rw-r--r-- 1 mysql mysql 1013 Jan 25 15:12 cacert.pem
-rw-r--r-- 1 mysql mysql 3161 Jan 25 15:12 master.crt
-rw-r--r-- 1 mysql mysql  680 Jan 25 15:11 master.csr
-rw------- 1 mysql mysql  887 Jan 25 15:09 master.key

[root@node3 ssl]# (umask 077;openssl genrsa -out slave.key 1024)
Generating RSA private key, 1024 bit long modulus
..........................++++++
.........................++++++
e is 65537 (0x10001)

[root@node3 ssl]# openssl req -new -key slave.key -out slave.csr -days 365
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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:14qi
Common Name (eg, your name or your server's hostname) []:slave.cert
Email Address []:admin@stu11.com 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@node3 ssl]# scp slave.csr 172.16.249.141:/etc/pki/CA/   
[root@node1 CA ]# openssl ca -in slave.csr -out slave.crt -days 365   
Using configuration from /etc/pki/tls/openssl.cnf Check that the
request matches the signature
Signature ok
Certificate Details:
    Serial Number: 2 (0x2)
    Validity
      Not Before: Jan 25 07:21:11 2015
GMT
      Not After : Jan 25 07:21:11 2016
GMT
    Subject:
      countryName         = CN
      stateOrProvinceName     = HA
      organizationName      = magedu
      organizationalUnitName  = 14qi
      commonName        = slave.cert
      emailAddress        = admin@stu11.com
    X509v3 extensions:
      X509v3 Basic Constraints:
        CA:FALSE
      Netscape Comment:
        OpenSSL Generated Certificate
      X509v3 Subject Key Identifier:
         
F8:06:AD:F0:1D:8A:78:62:ED:A7:FF:BB:7A:F6:79:14:D4:FB:26:39
      X509v3 Authority Key Identifier:       
keyid:C0:69:22:4E:9A:E5:BD:13:2B:BD:93:7B:0F:99:E6:0F:3A:FA:40:7E
Certificate is to be certified until Jan 25 07:21:11 2016 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@node1 CA ]# scp slave.crt 172.16.11.3:/etc/mysql/ssl/
[root@node1 CA ]# scp cacert.pem 172.16.11.3:/etc/mysql/ssl/
[root@node3 ssl]# chown -R mysql:mysql *
[root@node3 ssl]# ll
total 16
-rw-r--r-- 1 mysql mysql 1013 Jan 25 15:22 cacert.pem
-rw-r--r-- 1 mysql mysql 3161 Jan 25 15:21 slave.crt
-rw-r--r-- 1 mysql mysql  680 Jan 25 15:19 slave.csr
-rw------- 1 mysql mysql  887 Jan 25 15:14 slave.key

41 thread_concurrency = 8
 42 datadir = /mydata         ####数据目录
 43 innodb_file_per_table =on     ####每表一个innodb
 44 skip_name_resolve =on       ####跳过名称解析
 45 ssl                 ####开启ssl功能
 46 ssl_ca =/etc/mysql/ssl/cacert.pem     ####指定ca位置
 47 ssl_key =  /etc/mysql/ssl/master.key    ####主节点密钥
 48 ssl_cert =  /etc/mysql/ssl/master.crt   ####主节点证书
 63log-bin=/bin/log/master-bin        ####二进制文件开启
 66 binlog_format=mixed             ####二进制文件格式
 71 server-id   = 10              ####唯一的server-id

[root@node1 CA ]# service mysqld start        ####启动主节点
Starting MySQL                      [  OK  ]
##############授权一个可以让从节点复制的用户，并请强制要求使用##############
mysql> grant replication slave,replication client on *.* to 'cpuser'@'%' identified by
'magedu' require ssl;
Query OK, 0 rows affected (0.00 sec) 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

41 thread_concurrency= 8
42 datadir = /mydata      ####数据目录
43 innodb_file_per_table =on  ####每表一个innodb
44 skip_name_resolve =on    ####跳过名称解析
45 ssl                     ###开启ssl功能
46 ssl_ca =/etc/mysql/ssl/cacert.pem     ####指定ca位置
47 ssl_key =  /etc/mysql/ssl/slave.key     ####主节点密钥
48 ssl_cert =  /etc/mysql/ssl/slave.crt    ####主节点证书
66 binlog_format=mixed             ####二进制文件格式
71 server-id   = 10              ####唯一的server-id
72relay-log=relay-bin            ####开启中继日志
73read-only = on               ####从节点只读
[root@node3 CA ]# service mysqld start     ####启动主节点
Starting MySQL                      [  OK  ]

mysql> show
master status;
+-------------------+----------+--------------+------------------------------------+
|File         | Position |Binlog_Do_DB  | Binlog_Ignore_DB           |
+-------------------+----------+--------------+------------------------------------+
|master-bin.000006  |   669  |        |                  |
+-------------------+----------+--------------+------------------------------------+

mysql> change master to
master_host='172.16.249.141',master_user='cpuser',master_password='magedu',master_log_file='master-bin.000006',master_log_pos=669,master_ssl=1,master_ssl_ca='/etc/mysql/ssl/cacert.pem',master_ssl_cert='/etc/mysql/ssl/slave.crt',master_ssl_key='/etc/mysql/ssl/slave.key';
Query OK, 0 rows affected (0.14 sec)
mysql> start slave;                           
#####启动从节点线程
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;                   
#####查看状态
*******************************************1. row *************************************
  Slave_IO_State: Waiting for master to send event
  Master_Host: 172.16.249.141
  Master_User: cpuser
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: master-bin.000006
  Read_Master_Log_Pos: 669
  Relay_Log_File: relay-bin.000002
  Relay_Log_Pos: 536
  Relay_Master_Log_File: master-bin.000006
  Slave_IO_Running: Yes               ####IO线程准备就绪
  Slave_SQL_Running: Yes              ####SQL线程准备就绪
  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: 669
  Relay_Log_Space: 827
  Until_Condition: None
  Until_Log_File: 
  Until_Log_Pos: 0
  Master_SSL_Allowed: Yes
  Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem
  Master_SSL_CA_Path:
  Master_SSL_Cert: /etc/mysql/ssl/slave.crt
  Master_SSL_Cipher:
  Master_SSL_Key: /etc/mysql/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: 10
  Master_SSL_Crl: /etc/mysql/ssl/cacert.pem
  Master_SSL_Crlpath:
  Using_Gtid: No
  Gtid_IO_Pos: