MySQL基于SSL协议的主从复制

   数据对于大部分公司来说都是最重要的部分，而Mysql的服务器在同步数据时，默认是使用明文进行传输，所以接下来就来说说mysql基于SSL协议进行密文传输数据的主从复制模式。

逻辑拓扑：

    接下来的实验中Master节点服务器即使Master节点数据库服务器，同时也是CA。

环境准备：

    一、主从服务器时间需要同步：

            [root@node9 ~]# chronyc sources

            210 Number of sources = 1

            MS Name/IP address         Stratum Poll Reach LastRx Last sample

            ===============================================================================

            ^* server.magelinux.com          3   7   377    82    +71us[ +148us] +/-  100ms

           [root@node10 ~]# chronyc  sources

           210 Number of sources = 1

           MS Name/IP address         Stratum Poll Reach LastRx Last sample

           ===============================================================================

           ^* server.magelinux.com          3   7   377    95   +116us[ +155us] +/-  100ms

    二、主节点node9搭建好CA环境：

            [root@node9 ~]# cd /etc/pki/CA   

            [root@node9 CA]# touch index.txt serial

            [root@node9 CA]# echo 01 > serial                           

            [root@node9 CA]# (umask 077;openssl genrsa -out cakey.pem 2048)          

            [root@node9 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -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) []:BeiJing

        Locality Name (eg, city) [Default City]:BeiJing

        Organization Name (eg, company) [Default Company Ltd]:hisen

        Organizational Unit Name (eg, section) []:Ops

        Common Name (eg, your name or your server's hostname) []:ca.hisen.com

        Email Address []:admin.com

    三、主节点node9生成证书申请，并由CA进行签署：

        [root@node9 ~]# cd /var/lib/mysql/ssl/

        [root@node9 ssl]# (umask 077;openssl genrsa -out master.key 2048)

        [root@node9 ssl]# openssl req -new -key master.key -out master.csr -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) []:BeiJing

        Locality Name (eg, city) [Default City]:BeiJing

        Organization Name (eg, company) [Default Company Ltd]:hisen

        Organizational Unit Name (eg, section) []:Ops

        Common Name (eg, your name or your server's hostname) []:master.hisen.com

        Email Address []:master.com

        Please enter the following 'extra' attributes

        to be sent with your certificate request

        A challenge passWord []:

        An optional company name []:

        [root@node9 ssl]# openssl ca -in master.csr -out master.crt -days 2048

        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: Feb 22 11:21:11 2017 GMT

                    Not After : Oct  2 11:21:11 2022 GMT

                Subject:

                    countryName               = CN

                    stateOrProvinceName       = BeiJing

                    organizationName          = hisen

                    organizationalUnitName    = Ops

                    commonName                = master.hisen.com

                    emailAddress              = master.com

                X509v3 extensions:

                    X509v3 Basic Constraints:

                        CA:FALSE

                    Netscape Comment:

                        OpenSSL Generated Certificate

                    X509v3 Subject Key Identifier:

                        2B:1D:F7:18:00:89:1B:CB:6D:09:59:4B:5E:03:78:BA:60:6A:62:BB

                    X509v3 Authority Key Identifier:

                        keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88

        Certificate is to be certified until Oct  2 11:21:11 2022 GMT (2048 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

        
    四、从节点node10生成证书，并由CA进行签署：

        [root@node10 ~]# cd /var/lib/mysql/ssl

        [root@node10 ssl]# (umask 077;openssl genrsa -out slave.key 2048)

        Generating RSA private key, 2048 bit long modulus

        ..+++

        ...........................................................+++

        e is 65537 (0x10001)

        [root@node10 ssl]# (umask 077;openssl genrsa -out slave.key 2048)

        Generating RSA private key, 2048 bit long modulus

        ..+++

        ...........................................................+++

        e is 65537 (0x10001)

        [root@node10 ssl]# openssl req -new -key slave.key -out slave.csr -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) []:BeiJing 

        Locality Name (eg, city) [Default City]:BeiJing

        Organization Name (eg, company) [Default Company Ltd]:hisen

        Organizational Unit Name (eg, section) []:Ops

        Common Name (eg, your name or your server's hostname) []:slave.hisen.com

        Email Address []:slave.com

        Please enter the following 'extra' attributes

        to be sent with your certificate request

        A challenge password []:

        An optional company name []:

                   [root@node10 ssl]# scp slave.csr 192.168.17.90:/root

        [root@node9 ~]# openssl ca -in slave.csr -out slave.crt -days 3650

        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: Feb 22 11:27:17 2017 GMT

                    Not After : Feb 20 11:27:17 2027 GMT

                Subject:

                    countryName               = CN

                    stateOrProvinceName       = BeiJing

                    organizationName          = hisen

                    organizationalUnitName    = Ops

                    commonName                = slave.hisen.com

                    emailAddress              = slave.com

                X509v3 extensions:

                    X509v3 Basic Constraints:

                        CA:FALSE

                    Netscape Comment:

                        OpenSSL Generated Certificate

                    X509v3 Subject Key Identifier:

                        68:31:D7:B1:03:5A:C0:6E:A3:58:4D:67:53:AC:F7:F5:1E:2A:19:4E

                    X509v3 Authority Key Identifier:

                        keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88

        Certificate is to be certified until Feb 20 11:27:17 2027 GMT (3650 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@node9 ~]# scp slave.crt 192.168.17.100:/var/lib/mysql/ssl/

    五、将node9的CA证书复制给Master和Slave各一份：

        [root@node9 ~]# cp /etc/pki/CA/cacert.pem /var/lib/mysql/ssl/    #复制CA证书到本地

        [root@node9 ~]# scp /etc/pki/CA/cacert.pem 192.168.17.100:/var/lib/mysql/ssl/   #复制CA到node10

    六、修改/var/lib/mysql/ssl/下文件的属主属组以及给予最小权限：

        [root@node9 ~]# chown -R mysql:mysql /var/lib/mysql/ssl/ ; chmod 600 /var/lib/mysql/ssl/*  

        [root@node10 ~]# chown -R mysql:mysql /var/lib/mysql/ssl/ ; chmod 600 /var/lib/mysql/ssl/* 

MySQL文件配置：

Master：        

        [mysqld]

        datadir=/var/lib/mysql

        Socket=/var/lib/mysql/mysql.sock

        symbolic-links=0

        skip_name_resolve=ON

        innodb_file_per_table=1

        server_id=2

        log-bin=master-log

        ssl                                    #开启SSL

        ssl_ca=/var/lib/mysql/ssl/cacert.pem       #Master节点CA证书存放位置

        ssl_cert=/var/lib/mysql/ssl/master.crt   #Master节点证书

        ssl_key=/var/lib/mysql/ssl/master.key     #Master节点key

Slave：

        [mysqld]

        datadir=/var/lib/mysql

        socket=/var/lib/mysql/mysql.sock

        symbolic-links=0

        skip_name_resolve=ON

        innodb_file_per_table=ON

        server_id=3

        relay-log=relay-log

        read-only=1

        ssl                                    #开启SSL

        ssl_ca=/var/lib/mysql/ssl/cacert.pem    #Slave节点CA证书存放位置

        ssl_cert=/var/lib/mysql/ssl/slave.crt  #Slave节点证书

        ssl_key=/var/lib/mysql/ssl/slave.key    #Slave节点key

        
MySQL服务配置：

    一、启动MySQL服务，并查看MySQL中SSL信息：

        [root@node9 ~]# systemctl start mariadb.service   #启动node9的MySQL服务

        MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%';  #查看node9的SSL信息

        +---------------+-------------------------------+

        | Variable_name | Value                         |

        +---------------+-------------------------------+

        | have_openssl  | YES                           |

        | have_ssl      | YES                           |

        | ssl_ca        | /var/lib/mysql/ssl/cacert.pem |

        | ssl_capath    |                               |

        | ssl_cert      | /var/lib/mysql/ssl/master.crt |

        | ssl_cipher    |                               |

        | ssl_key       | /var/lib/mysql/ssl/master.key |

        +---------------+-------------------------------+

        7 rows in set (0.01 sec)

        [root@node10 ssl]# systemctl start mariadb.service  #启动node10的MySQL服务

        MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%';  #查看node10的SSL信息

        +---------------+-------------------------------+

        | Variable_name | Value                         |

        +---------------+-------------------------------+

        | have_openssl  | YES                           |

        | have_ssl      | YES                           |

        | ssl_ca        | /var/lib/mysql/ssl/cacert.pem |

        | ssl_capath    |                               |

        | ssl_cert      | /var/lib/mysql/ssl/slave.crt  |

        | ssl_cipher    |                               |

        | ssl_key       | /var/lib/mysql/ssl/slave.key  |

        +---------------+-------------------------------+

        7 rows in set (0.00 sec)

    二、Master节点授权一个能用于SSL协议进行复制信息的用户，并测试用户：

        MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'userssl'@'192.168.17.%' IDENTIFIED BY 'passwordssl' REQUIRE SSL;

        Query OK, 0 rows affected (0.00 sec)   #授权一个仅能够通过SSL复制数据的用户

        MariaDB [(none)]> FLUSH PRIVILEGES;     #刷新权限

        Query OK, 0 rows affected (0.00 sec)

        [root@node9 ~]# mysql -uuserssl -ppasswordssl -h292.168.17.90 \

        > --ssl_ca=/var/lib/mysql/ssl/cacert.pem \

        > --ssl_cert=/var/lib/mysql/ssl/master.crt \

        > --ssl_key=/var/lib/mysql/ssl/master.key              #测试用户能否使用SSL协议登录

        Welcome to the MariaDB monitor.  Commands end with ; or \g.

        Your MariaDB connection id is 4

        Server version: 5.5.44-MariaDB-log MariaDB Server

        Copyright (c) 2000, 2015, oracle, MariaDB Corporation Ab and others.

        Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

        MariaDB [(none)]> 

    三、配置Slave节点，指向Master节点：

        Master节点当前正在使用的binlog文件：master-log.000004，以及binlog位置：512       

        MariaDB [(none)]> SHOW MASTER STATUS;

        +-------------------+----------+--------------+------------------+

        | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

        +-------------------+----------+--------------+------------------+

        | master-log.000004 |      512 |              |                  |

        +-------------------+----------+--------------+------------------+

        1 row in set (0.00 sec)

        Slave节点配置：

        MariaDB [(none)]> CHANGE MASTER TO

            -> MASTER_HOST='192.168.17.90',

            -> MASTER_USER='userssl',

            -> MASTER_PASSWORD='passwordssl',

            -> MASTER_LOG_FILE='master-log.000004',

            -> MASTER_LOG_POS=512,

            -> MASTER_SSL=1,

            -> MASTER_SSL_CA='/var/lib/mysql/ssl/cacert.pem',

            -> MASTER_SSL_CERT='/var/lib/mysql/ssl/slave.crt',

            -> MASTER_SSL_KEY='/var/lib/mysql/ssl/slave.key';

        Query OK, 0 rows affected (0.01 sec)

        MariaDB [(none)]> START SLAVE;   启动SLAVE功能

        Query OK, 0 rows affected (0.01 sec)

        MariaDB [(none)]> SHOW SLAVE STATUS\G;  查看SLAVE状态信息

        *************************** 1. row ***************************

                       Slave_IO_State: Waiting for master to send event

                          Master_Host: 192.168.17.90

                          Master_User: userssl

                          Master_Port: 3306

                        Connect_Retry: 60

                      Master_Log_File: master-log.000004

                  Read_Master_Log_Pos: 512

                       Relay_Log_File: relay-log.000002

                        Relay_Log_Pos: 530

                Relay_Master_Log_File: master-log.000004

                     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: 512

                      Relay_Log_Space: 818

                      Until_Condition: None

                       Until_Log_File:

                        Until_Log_Pos: 0

                   Master_SSL_Allowed: Yes

                   Master_SSL_CA_File: /var/lib/mysql/ssl/cacert.pem

                   Master_SSL_CA_Path:

                      Master_SSL_Cert: /var/lib/mysql/ssl/slave.crt

                    Master_SSL_Cipher:

                       Master_SSL_Key: /var/lib/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: 2

        1 row in set (0.00 sec)

    四、测试主从同步数据：

        Master节点：       

        MariaDB [(none)]> CREATE DATABASE hisendb;  node9主节点创建hisendb数据库

        Query OK, 1 row affected (0.00 sec)

        MariaDB [(none)]> USE hisendb;

        Database changed

        MariaDB [hisendb]> CREATE TABLE friends(id INT UNSIGNED PRIMARY KEY NOT NULL,Name VARCHAR(20) NOT NULL,Age TINYINT,Gender ENUM('F','M'));

        Query OK, 0 rows affected (0.01 sec)    #在hisendb数据库中创建friends表

        MariaDB [hisendb]> INSERT INTO friends VALUES (1,'Xu He',25,'M'),(2,'Xu Mingying',27,'F'),(3,'Tian Tao',26,'M'),(4,'LiangJuntao',28,'M');

        Query OK, 4 rows affected (0.00 sec)   #在friends表中插入数据

        Records: 4  Duplicates: 0  Warnings: 0

        MariaDB [hisendb]> SELECT * FROM friends;   #查看结果

        +----+--------------+------+--------+

        | id | Name         | Age  | Gender |

        +----+--------------+------+--------+

        |  1 | Xu He        |   25 | M      |

        |  2 | Xu Mingying  |   27 | F      |

        |  3 | Tian Tao     |   26 | M      |

        |  4 | Liang Juntao |   28 | M      |

        +----+--------------+------+--------+

        4 rows in set (0.00 sec)

        Slave节点：

        MariaDB [(none)]> USE hisendb; 

        Reading table information for completion of table and column names

        You can turn off this feature to get a quicker startup with -A

        Database changed

        MariaDB [hisendb]> SELECT * FROM friends;  #在从节点可以查看主节点写入的数据

        +----+--------------+------+--------+

        | id | Name         | Age  | Gender |

        +----+--------------+------+--------+

        |  1 | Xu He        |   25 | M      |

        |  2 | Xu Mingying  |   27 | F      |

        |  3 | Tian Tao     |   26 | M      |

        |  4 | Liang Juntao |   28 | M      |

        +----+--------------+------+--------+

        4 rows in set (0.00 sec)

        由上可知，主从已经完成基于SSL协议的数据复制。