MySQL基于SSL协议的主从复制

hc6538

　　数据对于大部分公司来说都是最重要的部分，而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 -outcacert.pem -days 3650
　　You are about to be asked to enter information that will beincorporated
　　intoyour certificate request.
　　Whatyou are about to enter is what is called a Distinguished Name or a DN.
　　Thereare quite a few fields but you can leave some blank
　　Forsome fields there will be a default value,
　　Ifyou enter '.', the field will be left blank.
　　-----
　　CountryName (2 letter code) [XX]:CN
　　Stateor Province Name (full name) []:BeiJing
　　LocalityName (eg, city) [Default City]:BeiJing
　　OrganizationName (eg, company) [Default Company Ltd]:hisen
　　OrganizationalUnit Name (eg, section) []:Ops
　　CommonName (eg, your name or your server's hostname) []:ca.hisen.com
　　EmailAddress []:admin.com
　　三、主节点node9生成证书申请，并由CA进行签署：
　　[root@node9~]# cd /var/lib/mysql/ssl/
　　[root@node9ssl]# (umask 077;openssl genrsa -out master.key 2048)
　　[root@node9ssl]# openssl req -new -key master.key -out master.csr -days 3650
　　Youare about to be asked to enter information that will be incorporated
　　intoyour certificate request.
　　Whatyou are about to enter is what is called a Distinguished Name or a DN.
　　Thereare quite a few fields but you can leave some blank
　　Forsome fields there will be a default value,
　　Ifyou enter '.', the field will be left blank.
　　-----
　　CountryName (2 letter code) [XX]:CN
　　Stateor Province Name (full name) []:BeiJing
　　LocalityName (eg, city) [Default City]:BeiJing
　　OrganizationName (eg, company) [Default Company Ltd]:hisen
　　OrganizationalUnit Name (eg, section) []:Ops
　　CommonName (eg, your name or your server's hostname) []:master.hisen.com
　　EmailAddress []:master.com
　　Pleaseenter the following 'extra' attributes
　　tobe sent with your certificate request
　　Achallenge password []:
　　Anoptional company name []:
　　[root@node9ssl]# openssl ca -in master.csr -out master.crt -days 2048
　　Usingconfiguration from /etc/pki/tls/openssl.cnf
　　Checkthat the request matches the signature
　　Signatureok
　　CertificateDetails:
　　Serial Number: 1 (0x1)
　　Validity
　　Not Before: Feb 22 11:21:11 2017GMT
　　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>　　2B:1D:F7:18:00:89:1B:CB:6D:09:59:4B:5E:03:78:BA:60:6A:62:BB

　　X509v3 Authority Key>　　keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88
　　Certificateis to be certified until Oct  2 11:21:112022 GMT (2048 days)
　　Signthe certificate? [y/n]:y
　　1out of 1 certificate requests certified, commit? [y/n]y
　　Writeout database with 1 new entries
　　DataBase Updated
　　四、从节点node10生成证书，并由CA进行签署：
　　[root@node10 ~]# cd/var/lib/mysql/ssl
　　[root@node10ssl]# (umask 077;openssl genrsa -out slave.key 2048)
　　GeneratingRSA private key, 2048 bit long modulus
　　..+++
　　...........................................................+++
　　eis 65537 (0x10001)
　　[root@node10ssl]# (umask 077;openssl genrsa -out slave.key 2048)
　　GeneratingRSA private key, 2048 bit long modulus
　　..+++
　　...........................................................+++
　　eis 65537 (0x10001)
　　[root@node10ssl]# openssl req -new -key slave.key -out slave.csr -days 3650
　　Youare about to be asked to enter information that will be incorporated
　　intoyour certificate request.
　　Whatyou are about to enter is what is called a Distinguished Name or a DN.
　　Thereare quite a few fields but you can leave some blank
　　Forsome fields there will be a default value,
　　Ifyou enter '.', the field will be left blank.
　　-----
　　CountryName (2 letter code) [XX]:CN
　　Stateor Province Name (full name) []:BeiJing
　　LocalityName (eg, city) [Default City]:BeiJing
　　OrganizationName (eg, company) [Default Company Ltd]:hisen
　　OrganizationalUnit Name (eg, section) []:Ops
　　CommonName (eg, your name or your server's hostname) []:slave.hisen.com
　　EmailAddress []:slave.com
　　Pleaseenter the following 'extra' attributes
　　tobe sent with your certificate request
　　Achallenge password []:
　　Anoptional 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
　　Usingconfiguration from /etc/pki/tls/openssl.cnf
　　Checkthat the request matches the signature
　　Signatureok
　　CertificateDetails:
　　Serial Number: 2 (0x2)
　　Validity
　　Not Before: Feb 22 11:27:17 2017GMT
　　Not After : Feb 20 11:27:17 2027GMT
　　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>　　68:31:D7:B1:03:5A:C0:6E:A3:58:4D:67:53:AC:F7:F5:1E:2A:19:4E

　　X509v3 Authority Key>　　keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88
　　Certificateis to be certified until Feb 20 11:27:17 2027 GMT (3650 days)
　　Signthe certificate? [y/n]:y
　　1out of 1 certificate requests certified, commit? [y/n]y
　　Writeout database with 1 new entries
　　DataBase 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 startmariadb.service   #启动node9的MySQL服务
　　MariaDB [(none)]> SHOW GLOBALVARIABLES 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 |
　　+---------------+-------------------------------+
　　7rows in set (0.01 sec)
　　[root@node10 ssl]# systemctl start mariadb.service #启动node10的MySQL服务
　　MariaDB [(none)]> SHOW GLOBALVARIABLES 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  |
　　+---------------+-------------------------------+
　　7rows in set (0.00 sec)
　　二、Master节点授权一个能用于SSL协议进行复制信息的用户，并测试用户：

　　MariaDB[(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO'userssl'@'192.168.17.%'>　　Query OK, 0 rows affected (0.00 sec)  #授权一个仅能够通过SSL复制数据的用户
　　MariaDB [(none)]> FLUSH PRIVILEGES;    #刷新权限
　　QueryOK, 0 rows affected (0.00 sec)
　　[root@node9~]# mysql -uuserssl -ppasswordssl -h192.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协议登录
　　Welcometo the MariaDB monitor.  Commands endwith ; or \g.

　　YourMariaDB connection>　　Serverversion: 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 |              |                  |
　　+-------------------+----------+--------------+------------------+
　　1row 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';
　　QueryOK, 0 rows affected (0.01 sec)
　　MariaDB [(none)]> START SLAVE;   启动SLAVE功能
　　QueryOK, 0 rows affected (0.01 sec)
　　MariaDB [(none)]> SHOW SLAVESTATUS\G;  查看SLAVE状态信息
　　***************************1. row ***************************
　　Slave_IO_State: Waiting formaster 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

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

　　>　　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
　　1row in set (0.00 sec)
　　四、测试主从同步数据：
　　Master节点：
　　MariaDB [(none)]> CREATE DATABASEhisendb;  node9主节点创建hisendb数据库
　　QueryOK, 1 row affected (0.00 sec)
　　MariaDB[(none)]> USE hisendb;
　　Databasechanged
　　MariaDB [hisendb]> CREATE TABLE friends(id INT UNSIGNED PRIMARYKEY 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,'XuMingying',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 * FROMfriends;   #查看结果
　　+----+--------------+------+--------+
　　|id | Name         | Age  | Gender |
　　+----+--------------+------+--------+
　　|  1 | Xu He        |  25 | M      |
　　|  2 | Xu Mingying  |   27| F      |
　　|  3 | Tian Tao     |  26 | M      |
　　|  4 | Liang Juntao |   28 | M     |
　　+----+--------------+------+--------+
　　4rows in set (0.00 sec)
　　Slave节点：
　　MariaDB [(none)]> USE hisendb;
　　Readingtable information for completion of table and column names
　　Youcan turn off this feature to get a quicker startup with -A
　　Databasechanged
　　MariaDB [hisendb]> SELECT * FROMfriends;  #在从节点可以查看主节点写入的数据
　　+----+--------------+------+--------+
　　|id | Name         | Age  | Gender |
　　+----+--------------+------+--------+
　　|  1 | Xu He        |  25 | M      |
　　|  2 | Xu Mingying  |   27| F      |
　　|  3 | Tian Tao     |  26 | M      |
　　|  4 | Liang Juntao |   28 | M     |
　　+----+--------------+------+--------+
　　4rows in set (0.00 sec)
　　由上可知，主从已经完成基于SSL协议的数据复制。