MySQL基于SSL协议的主从复制
? ?數(shù)據(jù)對于大部分公司來說都是最重要的部分,而MySQL的服務(wù)器在同步數(shù)據(jù)時,默認是使用明文進行傳輸,所以接下來就來說說MySQL基于SSL協(xié)議進行密文傳輸數(shù)據(jù)的主從復(fù)制模式。
邏輯拓撲:
? ??
? ? 接下來的實驗中Master節(jié)點服務(wù)器即使Master節(jié)點數(shù)據(jù)庫服務(wù)器,同時也是CA。
環(huán)境準備:
? ? 一、主從服務(wù)器時間需要同步:
? ? ? ? ? ? [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
? ? 二、主節(jié)點node9搭建好CA環(huán)境:
? ? ? ? ? ? [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
? ? 三、主節(jié)點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
????????
? ? 四、從節(jié)點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證書復(fù)制給Master和Slave各一份:
????????[root@node9 ~]# cp /etc/pki/CA/cacert.pem /var/lib/mysql/ssl/??? #復(fù)制CA證書到本地
????????[root@node9 ~]# scp /etc/pki/CA/cacert.pem 192.168.17.100:/var/lib/mysql/ssl/?? #復(fù)制CA到node10
? ? 六、修改/var/lib/mysql/ssl/下文件的屬主屬組以及給予最小權(quán)限:
????????[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節(jié)點CA證書存放位置
????????ssl_cert=/var/lib/mysql/ssl/master.crt?? #Master節(jié)點證書
????????ssl_key=/var/lib/mysql/ssl/master.key???? #Master節(jié)點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節(jié)點CA證書存放位置
????????ssl_cert=/var/lib/mysql/ssl/slave.crt? #Slave節(jié)點證書
????????ssl_key=/var/lib/mysql/ssl/slave.key??? #Slave節(jié)點key
????????
MySQL服務(wù)配置:
? ? 一、啟動MySQL服務(wù),并查看MySQL中SSL信息:
????????[root@node9 ~]# systemctl start mariadb.service?? #啟動node9的MySQL服務(wù)
????????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服務(wù)
????????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節(jié)點授權(quán)一個能用于SSL協(xié)議進行復(fù)制信息的用戶,并測試用戶:
????????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)?? #授權(quán)一個僅能夠通過SSL復(fù)制數(shù)據(jù)的用戶
?????????
????????MariaDB [(none)]> FLUSH PRIVILEGES;???? #刷新權(quán)限
????????Query OK, 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協(xié)議登錄
????????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節(jié)點,指向Master節(jié)點:
????????Master節(jié)點當(dāng)前正在使用的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節(jié)點配置:
????????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狀態(tài)信息
????????*************************** 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)
????????
? ? 四、測試主從同步數(shù)據(jù):
????????Master節(jié)點:???????
????????MariaDB [(none)]> CREATE DATABASE hisendb;? node9主節(jié)點創(chuàng)建hisendb數(shù)據(jù)庫
????????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數(shù)據(jù)庫中創(chuàng)建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表中插入數(shù)據(jù)
????????Records: 4? Duplicates: 0? Warnings: 0
?????????
????????MariaDB [hisendb]> SELECT * FROM friends;?? #查看結(jié)果
????????+----+--------------+------+--------+
????????| 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節(jié)點:
????????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;? #在從節(jié)點可以查看主節(jié)點寫入的數(shù)據(jù)
????????+----+--------------+------+--------+
????????| 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)
?
? ? ? ? 由上可知,主從已經(jīng)完成基于SSL協(xié)議的數(shù)據(jù)復(fù)制。
?
轉(zhuǎn)載于:https://blog.51cto.com/hisen2devops/1909326
總結(jié)
以上是生活随笔為你收集整理的MySQL基于SSL协议的主从复制的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 内存的分配原则
- 下一篇: 关于li标签之间的间隔如何消除!