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，如需转载请自行联系原作者