MySQL基于SSL协议的主从复制
数据对于大部分公司来说都是最重要的部分，而MySQL的服务器在同步数据时，默认是使用明文进行传输，所以接下来就来说说MySQL基于SSL协议进行密文传输数据的主从复制模式。逻辑拓扑：
接下来的实验中Master节点服务器即使Master节点数据库服务器，同时也是CA。
环境准备：
一、主从服务器时间需要同步：
# 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
# 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环境：
# cd /etc/pki/CA
# touch index.txt serial
# echo 01 > serial
# (umask 077;openssl genrsa -out cakey.pem 2048)
# 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) :CN
Stateor Province Name (full name) []:BeiJing
LocalityName (eg, city) :BeiJing
OrganizationName (eg, company) :hisen
OrganizationalUnit Name (eg, section) []:Ops
CommonName (eg, your name or your server's hostname) []:ca.hisen.com
EmailAddress []:admin.com
三、主节点node9生成证书申请，并由CA进行签署：
# cd /var/lib/mysql/ssl/
# (umask 077;openssl genrsa -out master.key 2048)
# 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) :CN
Stateor Province Name (full name) []:BeiJing
LocalityName (eg, city) :BeiJing
OrganizationName (eg, company) :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 []:
# 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 : Oct2 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
Certificateis to be certified until Oct2 11:21:112022 GMT (2048 days)
Signthe certificate? :y
1out of 1 certificate requests certified, commit? y
Writeout database with 1 new entries
DataBase Updated
四、从节点node10生成证书，并由CA进行签署：
# cd/var/lib/mysql/ssl
# (umask 077;openssl genrsa -out slave.key 2048)
GeneratingRSA private key, 2048 bit long modulus
..+++
...........................................................+++
eis 65537 (0x10001)
# (umask 077;openssl genrsa -out slave.key 2048)
GeneratingRSA private key, 2048 bit long modulus
..+++
...........................................................+++
eis 65537 (0x10001)
# 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) :CN
Stateor Province Name (full name) []:BeiJing
LocalityName (eg, city) :BeiJing
OrganizationName (eg, company) :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 []:
# scp slave.csr 192.168.17.90:/root
# 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 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
Certificateis to be certified until Feb 20 11:27:17 2027 GMT (3650 days)
Signthe certificate? :y
1out of 1 certificate requests certified, commit? y
Writeout database with 1 new entries
DataBase Updated
# scp slave.crt 192.168.17.100:/var/lib/mysql/ssl/
五、将node9的CA证书复制给Master和Slave各一份：
# cp/etc/pki/CA/cacert.pem /var/lib/mysql/ssl/ #复制CA证书到本地
# scp/etc/pki/CA/cacert.pem 192.168.17.100:/var/lib/mysql/ssl/#复制CA到node10
六、修改/var/lib/mysql/ssl/下文件的属主属组以及给予最小权限：
# chown -R mysql:mysql /var/lib/mysql/ssl/ ; chmod 600/var/lib/mysql/ssl/*
# chown-R mysql:mysql /var/lib/mysql/ssl/ ; chmod 600 /var/lib/mysql/ssl/*
MySQL文件配置：
Master：
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：
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信息：
# 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)
# 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.%' IDENTIFIED BY 'passwordssl' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)#授权一个仅能够通过SSL复制数据的用户
MariaDB [(none)]> FLUSH PRIVILEGES; #刷新权限
QueryOK, 0 rows affected (0.00 sec)
# 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 id is 4
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
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
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 > 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> 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:4Duplicates: 0Warnings: 0
MariaDB > 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 > 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协议的数据复制。
页:
[1]