mariadb/mysql基于SSL主从复制
node7: 172.16.92.7/16 mariadb主服务器
node8: 172.16.92.8/16 mariadb从服务器
以上节点均为CentOS 7.1
配置环境
1. 配置好光盘yum源
2. 关闭selinux和iptables
node7, node8 都安装好mariadb-server
[root@node* ~]# yum -y install mariadb-server
1. node7主节点自签证书作为CA服务器
[root@node7 ~]# cd /etc/pki/CA
#创建私钥;
[root@node7 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
#生成自签署证书;
[root@node7 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
CN
GD
GZ
mariadb
ca
ca.mariadb.com
ca@mariadb.com
---------------------
创建索引库文件和序列号文件：
[root@node7 CA]# touch index.txt; echo 01 > serial;
[root@node7 CA]# ls
cacert.pem certs crl index.txt newcerts private serial
2.为Master节点node7创建证书申请并由CA服务器签发证书
#为数据库服务器创建ssl证书存放路径
[root@node7 CA]# mkdir -pv /etc/my.cnf.d/ssl
[root@node7 CA]# cd /etc/my.cnf.d/ssl
#创建私钥文件；
[root@node7 ssl]# (umask 077;openssl genrsa -out master.key 2048)
#生成证书申请；
[root@node7 ssl]# openssl req -new -key master.key -out master.csr -days 3650
CN
GD
GZ
mariadb
master
master.mariadb.com
master@mariadb.com
--------------------------
#CA服务器签署证书请求；
[root@node7 ssl]# openssl ca -in master.csr -out master.crt -days 3650
Subject:
countryName = CN
stateOrProvinceName = GD
organizationName = mariadb
organizationalUnitName = master
commonName = master.mariadb.com
emailAddress = master@mariadb.com
------------------------------------------------------------
3.Slave节点node8创建证书申请并由CA服务器签署证书
#创建证书存放路径；
[root@node8 ~]# mkdir /etc/my.cnf.d/ssl
[root@node8 ~]# cd /etc/my.cnf.d/ssl
#生成私钥；
[root@node8 ssl]# (umask 077;openssl genrsa -out slave.key 2048)
#生成证书申请文件；
[root@node8 ssl]# openssl req -new -key slave.key -out slave.csr -days 3650
CN
GD
GZ
mariadb
slave
slave.mariadb.com
slave@mariadb.com
--------------------------
#将slave节点的证书申请发送到CA服务器，
[root@node8 ssl]# scp slave.csr node7:/root
#让CA签署Slave服务器证书；
[root@node7 ssl]# cd /etc/pki/CA
[root@node7 CA]# openssl ca -in /root/slave.csr -out certs/slave.crt -days 1000
Subject:
countryName = CN
stateOrProvinceName = GD
organizationName = mariadb
organizationalUnitName = slave
commonName = slave.mariadb.com
emailAddress = slave@mariadb.com
---------------------------------------------------------------
#证书在CA服务器节点签署好后，发回slave服务器：
[root@node7 CA]# scp certs/slave.crt node8:/etc/my.cnf.d/ssl/
#从服务器查看一下证书
[root@node8 ssl]# ls
slave.crt slave.csr slave.key
#将CA证书拷贝到Slave服务器并为Master拷贝一份
[root@node7 ssl]# scp /etc/pki/CA/cacert.pem node8:/etc/my.cnf.d/ssl/
[root@node7 ssl]# cp /etc/pki/CA/cacert.pem /etc/my.cnf.d/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
[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_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
log-bin=mysql-bin
binlog_format=mixed
server-id = 8
relay-log = relay-bin
log_slave_updates = 1
read_only = on
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 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
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
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
*************************** 1. row ***************************
Variable_name: read_only
Value: ON
------------------------------
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/slave.crt |
| ssl_cipher | |
| 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
*************************** 1. row ***************************
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 主节点上可查看到此进程
MariaDB [(none)]> show processlist\G
*************************** 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
./relay-bin.000002
8849
mysql-bin.000008
8970
转载于:https://blog.51cto.com/changeflyhigh/1711201
2969
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
