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.178linux.com/69945

评论列表（1条）

Forever 2017-04-24 03:45

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