Mariadb基于ssl的主从复制

432edw

一、前言

备份数据库是生产环境中的首要任务，重中之重。一般配置中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服务器

[iyunv@node1 ~]# touch /etc/pki/CA/index.txt [iyunv@node1 ~]# echo 01 >/etc/pki/CA/serial[iyunv@node1 ~]# cd /etc/pki/CA/[iyunv@node1 CA]# (umask 066; openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)    #创建CA所需秘钥[iyunv@node1 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500  #生成自签名证书（…………此处略去N行）-----Country Name (2 letter code) [XX]:CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) [Default City]:BeiJing      Organization Name (eg, company) [Default Company Ltd]: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）节点签发证书

[iyunv@node1 ~]# mkdir /etc/my.cnf.d/ssl[iyunv@node1 ~]# cd /etc/my.cnf.d/ssl/[iyunv@node1 ssl]# mkdir /etc/my.cnf.d/ssl[iyunv@node1 ssl]# (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node1.key 2048) #生成申请证书所需秘钥[iyunv@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]:CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) [Default City]:BeiJingOrganization Name (eg, company) [Default Company Ltd]: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 []: [iyunv@node1 ssl]# cd /etc/my.cnf.d/ssl/[iyunv@node1 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? [y/n]:y1 out of 1 certificate requests certified, commit? [y/n]yWrite out database with 1 new entriesData Base Updated

3、分别为Slave服务器生成证书请求
（1）为node2（172.16.69.99）生成证书请求

[iyunv@node2 ~]# mkdir /etc/my.cnf.d/ssl[iyunv@node2 ~]# cd /etc/my.cnf.d/ssl[iyunv@node2 ssl]# ls[iyunv@node2 ssl]#  (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node2.key 2048)  #生成申请证书所需秘钥[iyunv@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]:CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) [Default City]:BeiJingOrganization Name (eg, company) [Default Company Ltd]: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）：

[iyunv@node2 ssl]# scp node2.csr root@172.16.69.66:/root

（2）为node3（172.16.69.111）生成证书请求

[iyunv@node3 ssl]# (umask 077;openssl genrsa -out /etc/my.cnf.d/ssl/node3.key 2048)   [iyunv@node3 ssl]# (umask 077;openssl^Cenrsa -out /etc/my.cnf.d/ssl/node3.key 2048)   [iyunv@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]:CNState or Province Name (full name) []:BeiJingLocality Name (eg, city) [Default City]:BeiJing   Organization Name (eg, company) [Default Company Ltd]:IT  Organizational 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）：

[iyunv@node3 ssl]# scp node3.csr root@172.16.69.66:/root

4、为slaev服务器签发证书
（1）为各节点证书申请文件签名：

[iyunv@node1 ~]# lsanaconda-ks.cfg  node2.csr  node3.csr[iyunv@node1 ~]# openssl ca -in node2.csr -out node2.crt -days 36500[iyunv@node1 ~]# openssl ca -in node3.csr -out node3.crt -days 36500[iyunv@node1 ~]# lsanaconda-ks.cfg  node2.crt  node2.csr  node3.crt  node3.csr

（2）将签署过的证书发回至各节点：

[iyunv@node1 ~]# scp node2.crt root@172.16.69.99:/etc/my.cnf.d/ssl[iyunv@node1 ~]# scp node3.crt root@172.16.69.111:/etc/my.cnf.d/ssl

（3）为各节点提供CA证书：

[iyunv@node1 ~]# cp /etc/pki/CA/cacert.pem /etc/my.cnf.d/ssl/cacert.pem[iyunv@node1 ~]# scp /etc/pki/CA/cacert.pem root@172.16.69.99:/etc/my.cnf.d/ssl[iyunv@node1 ~]# scp /etc/pki/CA/cacert.pem root@172.16.69.111:/etc/my.cnf.d/ssl

5、修改master和slvae的属主属组为”mysql”

[iyunv@node1 ~]# chown -R mysql.mysql /etc/my.cnf.d/ssl/[iyunv@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[iyunv@node2 ~]# chown -R mysql.mysql /etc/my.cnf.d/ssl/[iyunv@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[iyunv@node3 ssl]#  chown -R mysql.mysql /etc/my.cnf.d/ssl/[iyunv@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 = 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加密功能开启并创建基于密钥认证用户

[iyunv@node1 ~]# 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）

[iyunv@node2 ~]# 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）

[iyunv@node3 ~]# 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主从服务配置过程的细节。希望本文能为其他初学者提供帮助。