基于SSL的mysql(MariaDB)主从复制

[root@master ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 172.16.0.1 &>/dev/null

thread_concurrency = 4
datadir = /mydata/data    #数据目录
log-bin=/mydata/binlogs/master-bin
relay-log=/mydata/relaylogs/relay
sync_binlog = 1    #设定每1秒钟同步一次缓冲中的数据到日志文件中
binlog_format=mixed    #二进制日志格式为混合模式
server-id       = 1       #主服务器的server-id=1,从的等于2
#
#
#slave（172.16.7.250）同master

[root@master ~]# cd /etc/pki/CA
[root@master CA]#
[root@master CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
..........................................+++
..................+++
e is 65537 (0x10001)
[root@master CA]#
[root@master CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
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) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:sina
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server‘s hostname) []:master.sina.com
Email Address []:
[root@master CA]# touch index.txt serial crlnumber
[root@master CA]# echo 01 > serial

[root@slave ~]# mkdir /usr/local/mysql/ssl
[root@slave ~]# cd /usr/local/mysql/ssl
[root@slave ssl]# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.....................................................+++
........................................+++
e is 65537 (0x10001)
[root@slave ssl]#
[root@slave ssl]# openssl req -new -key slave.key -out slave.csr -days 36500
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) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:sina
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server‘s hostname) []:slave.sina.com
Email Address []:
Please enter the following ‘extra‘ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@slave ssl]#
[root@slave ssl]#
[root@slave ssl]# scp slave.csr root@172.16.7.202:/root

[root@master ~]# openssl ca -in slave.csr -out slave.crt -days 36500
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: May  3 13:43:28 2014 GMT
            Not After : Apr  9 13:43:28 2114 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Beijing
            organizationName          = sina
            organizationalUnitName    = mysql
            commonName                = slave.sina.com
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
            Netscape Comment:
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier:
                20:CB:55:9C:D0:7A:F0:25:70:AC:84:2B:8E:F4:24:FB:1F:51:48:9D
            X509v3 Authority Key Identifier:
                keyid:6B:73:D6:FE:81:13:2C:0E:EC:61:EE:F7:6F:92:91:6D:82:37:A0:11
Certificate is to be certified until Apr  9 13:43:28 2114 GMT (36500 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@master ~]#
[root@master ~]#
[root@master ~]# scp slave.crt root@172.16.7.250:/usr/local/mysql/ssl/

[root@master ~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
[root@master ~]# scp /etc/pki/CA/cacert.pem root@172.16.7.250:/usr/local/mysql/ssl/

[root@master ~]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@master ~]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 May  3 21:48 cacert.pem
-rw-r--r-- 1 mysql mysql 4465 May  3 21:35 master.crt
-rw-r--r-- 1 mysql mysql 1009 May  3 21:33 master.csr
-rw------- 1 mysql mysql 1675 May  3 21:32 master.key
###
###
[root@slave ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@slave ssl]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 May  3 21:49 cacert.pem
-rw-r--r-- 1 mysql mysql 4460 May  3 21:44 slave.crt
-rw-r--r-- 1 mysql mysql 1005 May  3 21:40 slave.csr
-rw------- 1 mysql mysql 1679 May  3 21:38 slave.key

[root@master ~]# vim /etc/my.cnf
[mysqld]
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_key = /usr/local/mysql/ssl/master.key
ssl_cert = /usr/local/mysql/ssl/master.crt
[root@master ~]# service mysqld restart
#
#
[root@slave ~]# vim /etc/my.cnf
[mysqld]
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_key = /usr/local/mysql/ssl/slave.key
ssl_cert = /usr/local/mysql/ssl/slave.crt
[root@slave ~]# service mysqld restart

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      652 |              |                  |
+-------------------+----------+--------------+------------------+

[root@slave ~]# mysql
MariaDB [(none)]>
MariaDB [(none)]> show variables like ‘%ssl%‘;
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | NO                              |
| have_ssl      | YES                             |
| ssl_ca        | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath    |                                 |
| ssl_cert      | /usr/local/mysql/ssl/slave.crt  |
| ssl_cipher    |                                 |
| ssl_crl       |                                 |
| ssl_crlpath   |                                 |
| ssl_key       | /usr/local/mysql/ssl/slave.key  |
+---------------+---------------------------------+

MariaDB [(none)]> create database hlbrc;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hlbrc              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hlbrc              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+