Mariadb基于ssl的主從復制
一、前言
備份數據庫是生產環境中的首要任務，重中之重。一般配置中mariadb的主從傳輸是明文傳輸，但是有時候對一些特殊業務來說是不允許的，為了保證數據在傳輸過程中的安全性，因此使用基于SSL的復制會大大加強數據的安全性。
二、準備工作
1、實驗系統環境：
CentOS7.2，SELinux關閉，iptables關閉，安裝openssl組件，mariadb為基于yum安裝的5.5.52版本。注意：主從服務器mariadb程序版本要一致，如果不一致，從服務器版本要高于主服務器。
2、mariadb說明
（1）主服務器節點：
node1 IP：172.16.69.66
（2）從服務器節點：
node2 IP：172.16.69.99 node3 IP：172.16.69.111
（3）數據目錄:
/mydata/data
（4）二進制日志目錄：
/mydata/binlogs
（5）中繼日志目錄:
/mydata/relaylogs
3、主從服務器時間同步：
在三個節點上分別運行一下命令:
# ntpdate 172.16.0.1 ##172.16.0.1為時間服務器IP
三、SSL主從復制的實現
1、將node1（172.16.69.66）節點作為CA服務器
[root@node1 ~]# touch /etc/pki/CA/index.txt [root@node1 ~]# echo 01 >/etc/pki/CA/serial [root@node1 ~]# cd /etc/pki/CA/ [root@node1 CA]# (umask 066; openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048) #創建CA所需秘鑰 [root@node1 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500 #生成自簽名證書 （…………此處略去N行） ----- 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]:IT Organizational Unit Name (eg, section) []:OPT Common Name (eg, your name or your server's hostname) []:node1.magedu.com Email Address []:
2、為node1（172.16.69.66）節點簽發證書
[root@node1 ~]# mkdir /etc/my.cnf.d/ssl
[root@node1 ~]# cd /etc/my.cnf.d/ssl/
[root@node1 ssl]# mkdir /etc/my.cnf.d/ssl
[root@node1 ssl]# (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node1.key 2048) #生成申請證書所需秘鑰
[root@node1 CA]# openssl req -new -key /etc/my.cnf.d/ssl/node1.key -days 36500 -out /etc/my.cnf.d/ssl/node1.csr #生成證書申請文件
（…………此處略去N行）
-----
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]:IT
Organizational Unit Name (eg, section) []:OPT
Common Name (eg, your name or your server's hostname) []:node1.magedu.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@node1 ssl]# cd /etc/my.cnf.d/ssl/
[root@node1 ssl]# openssl ca -in node1.csr -out master.crt -days 36500 #CA為申請主機簽署證書
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 23 12:48:02 2017 GMT
Not After : Jan 30 12:48:02 2117 GMT
Subject:
countryName = CN
stateOrProvinceName = BeiJing
organizationName = IT
organizationalUnitName = OPT
commonName = node1.magedu.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
86:11:7C:13:93:64:6E:9A:9B:A6:E4:ED:34:1E:55:04:D1:D2:D1:AE
X509v3 Authority Key Identifier:
keyid:57:A8:09:4A:FB:C4:39:30:F8:01:19:08:7F:EC:46:FD:81:38:DF:C6
Certificate is to be certified until Jan 30 12:48:02 2117 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
3、分別為Slave服務器生成證書請求
（1）為node2（172.16.69.99）生成證書請求
[root@node2 ~]# mkdir /etc/my.cnf.d/ssl [root@node2 ~]# cd /etc/my.cnf.d/ssl [root@node2 ssl]# ls [root@node2 ssl]# (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node2.key 2048) #生成申請證書所需秘鑰 [root@node2 ssl]# openssl req -new -key /etc/my.cnf.d/ssl/node2.key -days 36500 -out /etc/my.cnf.d/ssl/node2.csr #生成證書申請文件 （…………此處略去N行） ----- 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]:IT Organizational Unit Name (eg, section) []:OPT Common Name (eg, your name or your server's hostname) []:node2.magedu.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
將證書拷貝到CA服務器node1（172.16.69.66）：
[root@node2 ssl]# scp node2.csr root@172.16.69.66:/root
（2）為node3（172.16.69.111）生成證書請求
[root@node3 ssl]# (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node3.key 2048) [root@node3 ssl]# (umask 077;openssl^Cenrsa -out /etc/my.cnf.d/ssl/node3.key 2048) [root@node3 ssl]# openssl req -new -key /etc/my.cnf.d/ssl/node3.key -days 36500 -out /etc/my.cnf.d/ssl/node3.csr （…………此處略去N行） ----- 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]:IT Organizational Unit Name (eg, section) []:OPT Common Name (eg, your name or your server's hostname) []:node3.magedu.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
將證書拷貝到CA服務器node1（172.16.69.66）：
[root@node3 ssl]# scp node3.csr root@172.16.69.66:/root
4、為slaev服務器簽發證書
（1）為各節點證書申請文件簽名：
[root@node1 ~]# ls anaconda-ks.cfg node2.csr node3.csr [root@node1 ~]# openssl ca -in node2.csr -out node2.crt -days 36500 [root@node1 ~]# openssl ca -in node3.csr -out node3.crt -days 36500 [root@node1 ~]# ls anaconda-ks.cfg node2.crt node2.csr node3.crt node3.csr
（2）將簽署過的證書發回至各節點：
[root@node1 ~]# scp node2.crt root@172.16.69.99:/etc/my.cnf.d/ssl [root@node1 ~]# scp node3.crt root@172.16.69.111:/etc/my.cnf.d/ssl
（3）為各節點提供CA證書：
[root@node1 ~]# cp /etc/pki/CA/cacert.pem /etc/my.cnf.d/ssl/cacert.pem [root@node1 ~]# scp /etc/pki/CA/cacert.pem root@172.16.69.99:/etc/my.cnf.d/ssl [root@node1 ~]# scp /etc/pki/CA/cacert.pem root@172.16.69.111:/etc/my.cnf.d/ssl
5、修改master和slvae的屬主屬組為”mysql”
[root@node1 ~]# chown -R mysql.mysql /etc/my.cnf.d/ssl/ [root@node1 ~]# ll /etc/my.cnf.d/ssl/ total 20 -rw-r--r-- 1 mysql mysql 1326 Feb 23 23:17 cacert.pem -rw-r--r--. 1 mysql mysql 4457 Feb 23 20:49 node1.crt -rw-r--r--. 1 mysql mysql 1005 Feb 23 20:40 node1.csr -rw-------. 1 mysql mysql 1675 Feb 23 20:25 node1.key [root@node2 ~]# chown -R mysql.mysql /etc/my.cnf.d/ssl/ [root@node2 ~]# ll /etc/my.cnf.d/ssl/ total 20 -rw-r--r-- 1 mysql mysql 1326 Feb 23 23:19 cacert.pem -rw-r--r--. 1 mysql mysql 4457 Feb 23 21:25 node2.crt -rw-r--r--. 1 mysql mysql 1005 Feb 23 21:03 node2.csr -rw-------. 1 mysql mysql 1679 Feb 23 20:59 node2.key [root@node3 ssl]# chown -R mysql.mysql /etc/my.cnf.d/ssl/ [root@node3 ~]# ll /etc/my.cnf.d/ssl/ total 20 -rw-r--r-- 1 mysql mysql 1326 Feb 23 23:19 cacert.pem -rw-r--r--. 1 mysql mysql 4457 Feb 23 21:25 node3.crt -rw-r--r--. 1 mysql mysql 1005 Feb 23 21:05 node3.csr -rw-------. 1 mysql mysql 1679 Feb 23 21:00 node3.key
6、修改各節點mariadb配置文件
在三個節點上運行以下命令：
# mkdir -pv /mydata/{data,binlogs,relaylogs}
# chown -R mysql:mysql /mydata
# vim /etc/my.cnf
[mysqld]
datadir=/mydata/data #數據存放目錄
socket=/var/lib/mysql/mysql.sock #本地通訊使用的套接字
log_bin=/mydata/binlogs/log-bin #二進制日志目錄，主節點所需配置項
relay_log=/mydata/relaylogs/relay-log #中繼日志目錄，從節點所需配置項
server-id=1 #服務器ID，各服務器要不同，node2節點為2，node3節點為3
innodb_file_per_table=ON #開啟獨立表空間模式
skip_name_resolve=ON #禁止域名方向解析，此項可按需選擇
sync_binlog = 1 #設定每1秒鐘同步一次緩沖中的數據到日志文件中
binlog_format=mixed #二進制日志格式為混合模式
ssl #開啟ssl加密功能
ssl_ca=/etc/my.cnf.d/ssl/cacert.pem #指明CA證書文件
ssl_key=/etc/my.cnf.d/ssl/node1.key #指明私鑰文件，各節點指明本機私鑰文件
ssl_cert=/etc/my.cnf.d/ssl/node1.crt #指明已簽名證書文件，各節點指明本機證書文件
7、在主服務器node1（172.16.69.66）節點上驗證ssl加密功能開啟并創建基于密鑰認證用戶
[root@node1 ~]# mysql MariaDB [(none)]> SHOW VARIABLES LIKE "%ssl%"; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/my.cnf.d/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /etc/my.cnf.d/ssl/node1.crt | | ssl_cipher | | | ssl_key | /etc/my.cnf.d/ssl/node1.key | +---------------+------------------------------+ MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass' REQUIRE SSL; MariaDB [(none)]> FLUSH PRIVILEGES;
8、查看master服務器node1（172.16.69.66）二進制日志文件和事件位置用于slave服務器鏈接從這個位置開始復制
MariaDB [(none)]> SHOW MASTER STATUS; +----------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------+----------+--------------+------------------+ | log-bin.000006 | 245 | | | +----------------+----------+--------------+------------------+
9、驗證從服務器node2（172.16.69.99）、node3（172.16.69.111）開啟SSL加密功能
（1）node2（172.16.69.99）
[root@node2 ~]# mysql MariaDB [(none)]> SHOW VARIABLES LIKE "%ssl%"; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/my.cnf.d/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /etc/my.cnf.d/ssl/node2.crt | | ssl_cipher | | | ssl_key | /etc/my.cnf.d/ssl/node2.key | +---------------+------------------------------+
（2）node3（172.16.69.111）
[root@node3 ~]# mysql MariaDB [(none)]> SHOW VARIABLES LIKE "%ssl%"; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/my.cnf.d/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /etc/my.cnf.d/ssl/node3.crt | | ssl_cipher | | | ssl_key | /etc/my.cnf.d/ssl/node3.key | +---------------+------------------------------+
10、slave服務器鏈接master服務器
（1）獲取鏈接master選項幫助
MariaDB [(none)]> HELP CHANGE MASTER TO
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num #指明端口號
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name' #指明從主服務器哪個二進制文件開始復制
| MASTER_LOG_POS = master_log_pos #指明從二進制文件的哪個位置開始復制
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1} #開啟SSL加密功能
| MASTER_SSL_CA = 'ca_file_name' #CA證書位置
| MASTER_SSL_CAPATH = 'ca_directory_name' #當多個CA證書時，可指定所在目錄
| MASTER_SSL_CERT = 'cert_file_name' #指明自己的證書
| MASTER_SSL_KEY = 'key_file_name' #指明自己的密鑰文件
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
（2）slave服務器node2（172.16.69.99）鏈接master服務器node1（172.16.69.66）
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.69.66',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='log-bin.000006',MASTER_LOG_POS=245,MASTER_SSL=1,MASTER_SSL_CA='/etc/my.cnf.d/ssl/cacert.pem',MASTER_SSL_CERT='/etc/my.cnf.d/ssl/node2.crt',MASTER_SSL_KEY='/etc/my.cnf.d/ssl/node2.key'; MariaDB [(none)]> START SLAVE;
（3）slave服務器node3（172.16.69.111）鏈接master服務器node1（172.16.69.66）
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.69.66',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='log-bin.000006',MASTER_LOG_POS=245,MASTER_SSL=1,MASTER_SSL_CA='/etc/my.cnf.d/ssl/cacert.pem',MASTER_SSL_CERT='/etc/my.cnf.d/ssl/node3.crt',MASTER_SSL_KEY='/etc/my.cnf.d/ssl/node3.key'; MariaDB [(none)]> START SLAVE;
（4）查看slave服務器node2、node3的狀態
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.69.66
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000006
Read_Master_Log_Pos: 320
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 602
Relay_Master_Log_File: log-bin.000006
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: 320
Relay_Log_Space: 890
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/my.cnf.d/ssl/node2.crt
Master_SSL_Cipher:
Master_SSL_Key: /etc/my.cnf.d/ssl/node2.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: 1
四、同步驗證
1、在master服務器node1上新建數據庫hellodb
MariaDB [(none)]> CREATE DATABASE hellodb; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
2、在slave服務器node2、node3上驗證
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
總結：
通過此次配置，更加熟悉私人CA的創建與證書的申請，深入了解mariadb主從服務配置過程的細節。希望本文能為其他初學者提供幫助。
原創文章，作者：征（_少，如若轉載，請注明出處：http://www.www58058.com/69945
Eres una artista, te sale idncit??os. Y yo me los com?-a todos los d?-as, era un intercambio de estudiante y era lo que nos pon?-an para el almuerzo. El pan tiene una textura especial, es diferente al de aqui. Me encantan, y con verduritas uhmmm que rico, y yo sin poder comer hidratos. Me la apunto y la hago seguro.Bss