mariadb/mysql基于SSL主從複制

#修改Master與Slave伺服器證書屬主、屬組為mysql使用者

主節點的權限授予：

[root@node7 ssl]# chown -R mysql.mysql /etc/my.cnf.d/ssl/

[root@node7 ssl]# ls /etc/my.cnf.d/ssl/ -l

#從節點的權限授予：

[root@node8 ssl]# chown -R mysql.mysql /etc/my.cnf.d/ssl/

[root@node8 ssl]# ls -l /etc/my.cnf.d/ssl/

4.在Master與Slave伺服器修改主配置檔案開啟SSL加密功能

#node7: mariadb主伺服器配置

[root@node7 ~]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

#######以下的内容為添加########

ssl #開啟SSL功能

ssl_ca = /etc/my.cnf.d/ssl/cacert.pem #指定CA檔案位置

ssl_cert = /etc/my.cnf.d/ssl/master.crt #指定證書檔案位置

ssl_key = /etc/my.cnf.d/ssl/master.key #指定密鑰所在位置

#二進制變更日志

log-bin=mysql-bin

#二進制日志格式為混合模式

binlog_format=mixed

#為主伺服器node7的ID值

server-id = 7

innodb_file_per_table = on

skip_name_resolve = on

####### 以下内容非必要 #########

port = 3306

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 4

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

#############################

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

#

# include all files from the config directory

!includedir /etc/my.cnf.d

############### End for my.cnf #################

node8: mariadb從伺服器配置

[root@node8 ~]# vim /etc/my.cnf

########## 添加以下内容 ##########

ssl

ssl_ca = /etc/my.cnf.d/ssl/cacert.pem

ssl_cert = /etc/my.cnf.d/ssl/slave.crt

ssl_key = /etc/my.cnf.d/ssl/slave.key

server-id = 8

relay-log = relay-bin

log_slave_updates = 1

read_only = on

######### 以下内容非必要 ############

####################################

############# End of my.cnf ###############

5.在Master伺服器檢視SSL加密是否開啟；然後建立授權一個基于密鑰認證的使用者

[root@node7 ~]# systemctl start mariadb

[root@node7 ~]# 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/master.crt |

| ssl_cipher    |                              |

| ssl_key       | /etc/my.cnf.d/ssl/master.key |

MariaDB [(none)]> grant replication client,replication slave on *.* to 'slaveuser'@'172.16.%.%' identified by 'oracle' require ssl;

MariaDB [(none)]> flush privileges;

MariaDB [(none)]> show master status\G

*************************** 1. row ***************************

            File: mysql-bin.000003

        Position: 507

6. 在Slave伺服器 (node8) 測試使用加密使用者指定密鑰連接配接Master伺服器

[root@node8 ~]# mysql \

-uslaveuser -poracle -h172.16.92.7 \

--ssl-ca=/etc/my.cnf.d/ssl/cacert.pem \

--ssl-cert=/etc/my.cnf.d/ssl/slave.crt \

--ssl-key=/etc/my.cnf.d/ssl/slave.key

        Position: 507        #可以看出時間節點和上面顯示的一緻；

######### 記下這兩個選項, 設定中繼日志時有用 #########

MariaDB [(none)]> quit

7. slave伺服器 (node8) 

[root@node8 ~]# systemctl start mariadb

[root@node8 ~]# mysql

MariaDB [(none)]> show global variables like '%read_only%'\G

Variable_name: read_only

        Value: ON

------------------------------

| ssl_cert      | /etc/my.cnf.d/ssl/slave.crt  |

| ssl_key       | /etc/my.cnf.d/ssl/slave.key  |

#設定連接配接master節點；

MariaDB [(none)]> change master to 

master_host='172.16.92.7',

master_user='slaveuser',

master_password='oracle',

master_log_file='mysql-bin.000003',

master_log_pos=507,

master_ssl=1,

master_ssl_ca='/etc/my.cnf.d/ssl/cacert.pem',

master_ssl_cert='/etc/my.cnf.d/ssl/slave.crt',

master_ssl_key='/etc/my.cnf.d/ssl/slave.key';

MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status\G

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.92.1

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 5

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 497

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 529

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

    ........ 其餘資訊略 ........

           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/slave.crt

            Master_SSL_Cipher: 

               Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key

MariaDB [(none)]> show processlist\G

*************************** 3. row ***************************

   State: Slave has read all relay log; waiting for the slave I/O thread to update it

#說明: 從節點已經接收到所有的中繼日志, 并且以啟動I/O線程等待更新

node7 主節點上可檢視到此程序

*************************** 2. row ***************************

   State: Master has sent all binlog to slave; waiting for binlog to be updated

#說明: 主節點已經發送所有的二進制日志到從伺服器

在主節點上建立資料庫測試是否能主從同步

MariaDB [(none)]> create database zzz;

在從節點上可看到hellodb資料庫, 說明主從同步成功!

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

| information_schema |

| mysql              |

| performance_schema |

| test               |

| zzz                |

############# mysql/mariadb基于SSL加密主從複制已全部完成 ##############

七.複制相關的檔案介紹

我們到slave節點檢視資料檔案：

[root@node8 ~]# ls /mydata/data/

aria_log.00000001 master.info mysql-bin.000005 performance_schema

aria_log_control multi-master.info mysql-bin.000006 relay-bin.000001

binlog mysql mysql-bin.000007 relay-bin.000002

hellodb mysql-bin.000001 mysql-bin.000008 relay-bin.index

ibdata1 mysql-bin.000002 mysql-bin.index relaylog

ib_logfile0 mysql-bin.000003 node8.centos7.com.err relay-log.info

ib_logfile1 mysql-bin.000004 node8.centos7.com.pid test

這裡除了基本的資料庫檔案和二進制日志，還有一些與複制相關的檔案。如下：

(1)mysql-bin.index

伺服器一旦開啟二進制日志，會産生一個與二日志檔案同名，但是以.index結尾的檔案。它用于跟蹤磁盤上存在哪些二進制日志檔案。MySQL用它來定位二進制日志檔案。它的内容如下：

[root@node8 ~]# cat /mydata/data/mysql-bin.index

./mysql-bin.000001

./mysql-bin.000002

./mysql-bin.000003

./mysql-bin.000004

./mysql-bin.000005

./mysql-bin.000006

./mysql-bin.000007

./mysql-bin.000008

(2)mysql-relay-bin.index

該檔案的功能與mysql-bin.index類似，但是它是針對中繼日志，而不是二進制日志。内容如下：

[root@node8 ~]# cat /mydata/data/relay-bin.index

./relay-bin.000001

./relay-bin.000002

(3)master.info

儲存master的相關資訊。不要删除它，否則，slave重新開機後不能連接配接master。内容如圖：

MariaDB資料庫主從複制、雙主複制、半同步複制、基于SSL的安全複制實作及其功能特性介紹

(4)relay-log.info

包含slave中目前二進制日志和中繼日志的資訊。

[root@node8 ~]# cat /mydata/data/relay-log.info

8849

mysql-bin.000008

8970

本文轉自 zhuhc1988 51CTO部落格，原文連結：http://blog.51cto.com/changeflyhigh/1711201，如需轉載請自行聯系原作者