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.99node3 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服务器
# touch /etc/pki/CA/index.txt # echo 01 >/etc/pki/CA/serial# cd /etc/pki/CA/# (umask 066; openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048) #创建CA所需秘钥# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500#生成自签名证书（…………此处略去N行）-----Country Name (2 letter code) :CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) :BeiJing Organization Name (eg, company) :ITOrganizational Unit Name (eg, section) []:OPTCommon Name (eg, your name or your server's hostname) []:node1.magedu.comEmail Address []:
2、为node1（172.16.69.66）节点签发证书
# mkdir /etc/my.cnf.d/ssl# cd /etc/my.cnf.d/ssl/# mkdir /etc/my.cnf.d/ssl# (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node1.key 2048) #生成申请证书所需秘钥# 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) :CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) :BeiJingOrganization Name (eg, company) :ITOrganizational Unit Name (eg, section) []:OPTCommon Name (eg, your name or your server's hostname) []:node1.magedu.comEmail Address []:Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []: # cd /etc/my.cnf.d/ssl/# openssl ca -in node1.csr -out master.crt -days 36500#CA为申请主机签署证书Using configuration from /etc/pki/tls/openssl.cnfCheck that the request matches the signatureSignature okCertificate 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:C6Certificate is to be certified until Jan 30 12:48:02 2117 GMT (36500 days)Sign the certificate? :y1 out of 1 certificate requests certified, commit? yWrite out database with 1 new entriesData Base Updated
3、分别为Slave服务器生成证书请求
（1）为node2（172.16.69.99）生成证书请求
# mkdir /etc/my.cnf.d/ssl# cd /etc/my.cnf.d/ssl# ls#(umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node2.key 2048)#生成申请证书所需秘钥# 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) :CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) :BeiJingOrganization Name (eg, company) :ITOrganizational Unit Name (eg, section) []:OPTCommon Name (eg, your name or your server's hostname) []:node2.magedu.comEmail Address []:Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []:
将证书拷贝到CA服务器node1（172.16.69.66）：
# scp node2.csr root@172.16.69.66:/root
（2）为node3（172.16.69.111）生成证书请求
# (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node3.key 2048) # (umask 077;openssl^Cenrsa -out /etc/my.cnf.d/ssl/node3.key 2048) #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) :CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) :BeiJing Organization Name (eg, company) :ITOrganizational Unit Name (eg, section) []:OPTCommon Name (eg, your name or your server's hostname) []:node3.magedu.comEmail Address []:Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []:An optional company name []:
将证书拷贝到CA服务器node1（172.16.69.66）：
# scp node3.csr root@172.16.69.66:/root
4、为slaev服务器签发证书
（1）为各节点证书申请文件签名：
# lsanaconda-ks.cfgnode2.csrnode3.csr# openssl ca -in node2.csr -out node2.crt -days 36500# openssl ca -in node3.csr -out node3.crt -days 36500# lsanaconda-ks.cfgnode2.crtnode2.csrnode3.crtnode3.csr
（2）将签署过的证书发回至各节点：
# scp node2.crt root@172.16.69.99:/etc/my.cnf.d/ssl# scp node3.crt root@172.16.69.111:/etc/my.cnf.d/ssl
（3）为各节点提供CA证书：
# cp /etc/pki/CA/cacert.pem /etc/my.cnf.d/ssl/cacert.pem# scp /etc/pki/CA/cacert.pem root@172.16.69.99:/etc/my.cnf.d/ssl# scp /etc/pki/CA/cacert.pem root@172.16.69.111:/etc/my.cnf.d/ssl
5、修改master和slvae的属主属组为”mysql”
# chown -R mysql.mysql /etc/my.cnf.d/ssl/# 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# chown -R mysql.mysql /etc/my.cnf.d/ssl/# 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#chown -R mysql.mysql /etc/my.cnf.d/ssl/# 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 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 = ON #设定每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加密功能开启并创建基于密钥认证用户
# mysqlMariaDB [(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）
# mysqlMariaDB [(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）
# mysqlMariaDB [(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 TOName: '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: 0Master_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主从服务配置过程的细节。希望本文能为其他初学者提供帮助。
页:
[1]