MYSQL/MARIADB database for master-slave replication based on SSL

Objective

Backup database is the first task in production environment, sometimes have to replicate the database through the network, because the master-slave copy of MYSQL/MARIADB is transmitted in plaintext, if it is transmitted across the network in production environment, the security of data cannot be guaranteed completely, in order to solve this problem, We need a secure way to transmit data, which is based on SSL encryption.

Deployment Configuration

Experimental topology

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6E/B1/wKiom1WCldGRBkWCAAC-pNiwFH4375.jpg "title=" MySQL SSL-based secure replication architecture. jpg "alt=" wkiom1wcldgrbkwcaac-pniwfh4375.jpg "/>

Experimental environment

System Environment: CentOS6.6

Database version: mariadb-5.5.36

#注意: The master-Slave server database version must be consistent: Master and slave server time must be synchronized # This experiment takes only one group from the server as an example

Configuring Master-slave replication

Installing MARIADB

[[email protected] ~]# mkdir/mydata/data-pv[[email protected] ~]# groupadd-r mysql[[email protected] ~]# useradd-g Mys Ql-r Mysql[[email protected] ~]# chown-r mysql.mysql/mydata/data[[email protected] ~]# tar XF mariadb-5.5.36-linux-x86_ 64.tar.gz-c/usr/local[[email protected] ~]# cd/usr/local[[email protected] local]# LN-SV mariadb-5.5.36-linux-x86_64 m Ysql[[email protected] local]# chown-r root.mysql MySQL

[[EMAIL PROTECTED] LOCAL]# MKDIR /ETC/MYSQL[[EMAIL PROTECTED] LOCAL]# CD  mysql[[email protected] mysql]# cp /support-files/my-large.cnf /etc/mysql/ my.cnf[[email protected] mysql]# cp support-files/mysql.server /etc/rc.d/init.d/ mysqld[[email protected] mysql]# chmod +x /etc/rc.d/init.d/mysqld[[email  protected] mysql]# chkconfig --add mysqld[[email protected] mysql]#  chkconfig mysqld on# the master-slave node performs the above operation # The following work is possible, omit [[email protected] ~]# echo  ' export  path=/usr/local/mysql/bin: $PATH '  > /etc/profile.d/mysql.sh[[email protected] ~]#  source /etc/profile.d/mysql.sh[[email protected] ~]# ln -sv /usr/local/ mysql/include  /usr/include/mysql[[email protected] ~]# echo  '/usr/local/mysql/ Lib '  > /etc/ld.so.conf.d/mysql.Conf 

Primary server Configuration

[[email protected] mysql]# mkdir/mydata/binlogs[[email protected] mysql]# chown-r Mysql.mysql/mydata[[email protected] mysql]# vim/etc/mysql/my.cnf datadir =/mydata/datalog-bin=/mydata/binlogs/master-binbinlog_format=mixed # Binary log format Server-id = 1 #主服务器与从服务器不可一致 [[email protected] mysql]# scripts/mysql_install_db--user=mysql--datadir=/myd Ata/data[[email protected] mysql]# service mysqld start

Authorization to copy accounts from the server

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/AE/wKioL1WCoC2ToyYCAAC4pjiPaWE060.jpg "title=" 1.jpg " alt= "Wkiol1wcoc2toyycaac4pjipawe060.jpg"/>

View Binary Log Locations

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/AE/wKioL1WCoHziwbRiAADEpCgm-8I976.jpg "title=" 2.jpg " alt= "Wkiol1wcohziwbriaadepcgm-8i976.jpg"/>

From server configuration

[[email protected] mysql]# mkdir /mydata/relaylogs[[ Email protected] mysql]# chown -r mysql.mysql /mydata[[email protected]  mysql]# vim /etc/mysql/my.cnf datadir = /mydata/data#log-bin=mysql-bin#binlog_ format=mixed#log-slave-updates = 1# if the above three items must be enabled for the other slave server master server from the server server-id        = 2                     #不可与主服务器一致relay-log=/mydata/relaylogs/relay-bin  #中继日志位置read_only  = 1                            #设置为只读 [[Email protected] mysql]# scripts/mysql_ Install_db --user=mysql --datadir=/mydata/data[[email protected] mysql]# service  mysqld start 

Connecting to the master server from the server

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/B1/wKiom1WCp52R9u3PAADO_tlVTrc206.jpg "title=" 3.jpg " alt= "Wkiom1wcp52r9u3paado_tlvtrc206.jpg"/>

View from server Status

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/AE/wKioL1WCqmOj5uRqAAF1KBXlJuU704.jpg "title=" 4.jpg " alt= "Wkiol1wcqmoj5urqaaf1kbxljuu704.jpg"/>

#通过查看从服务器状态可发现主从已完全同步

Master-slave replication test

master server Add Data

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/AE/wKioL1WCu-DRxM3hAAGoBZzC9eI678.jpg "title=" 5.jpg " alt= "Wkiol1wcu-drxm3haagobzzc9ei678.jpg"/>

View data and status from server

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/AE/wKioL1WCvOLwMEVHAABPo6lcMZA721.jpg "title=" 7.jpg " alt= "Wkiol1wcvolwmevhaabpo6lcmza721.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/AE/wKioL1WCvBaAdrxQAAFE0oDjly8858.jpg "title=" 6.jpg " alt= "Wkiol1wcvbaadrxqaafe0odjly8858.jpg"/>

#与主服务器一致, master-slave replication configuration complete

Enable secure SSL Transport

Check SSL status

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/C4/wKiom1WGTAaxhhWPAADDfVJT-oA030.jpg "title=" 8.jpg " alt= "Wkiom1wgtaaxhhwpaaddfvjt-oa030.jpg"/>

To configure the primary server as a CA

CA configuration do not make detailed comments, detailed CA construction Please see the previous blog

[[email protected] ~]# Cd/etc/pki/ca[[email protected] ca]# (umask 077;openssl genrsa-out PRIVATE/CAKEY.PEM 2048) [[Emai L protected] ca]# OpenSSL req-new-x509-key private/cakey.pem-out cacert.pem-days 3650[[email protected] ca]# Touch {i Ndex.txt,serial}[[email protected] ca]# echo > serial

Master server generate Certificate

[[email protected] ca]# mkdir/etc/mysql/ssl[[email protected] ca]# Cd/etc/mysql/ssl[[email protected] ssl]# (umask 077;o Penssl genrsa-out master.key 2048) [[email protected] ssl]# OpenSSL req-new-key master.key-out master.csr[[email Protec Ted] ssl]# OpenSSL ca-in master.csr-out master.crt-days 3650

Generate a certificate request from the server

[Email protected] ssl]# (umask 077;openssl genrsa-out slave.key 2048) [[email protected] ssl]# OpenSSL Req-new-key Slav E.key-out SLAVE.CSR

[[email protected] ssl]# scp slave.csr node1:/tmpslave.csr                                             100% 1013     1.0kb/s   00:00  [[email  protected] ssl]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt  -days 3650[[email protected] ssl]# scp /tmp/slave.crt node2:/etc/mysql/ sslslave.crt                                            100% 4520     4.4KB/s    00:00

Copy the CA certificate to each server

[Email protected] ssl]# CP/ETC/PKI/CA/CACERT.PEM/[[email protected] ssl]# Scp/etc/pki/ca/cacert.pem node2:/etc/ Mysql/sslcacert.pem 100% 1403 1.4kb/s 00:00

Permissions

[Email protected] ssl]# chown-r mysql.mysql./[[email protected] ssl]# lltotal 20-rw-r--r--1 mysql MySQL 1403 June 19 14 : cacert.pem-rw-r--r--1 mysql mysql 4596 June 14:27 master.crt-rw-r--r--1 MySQL MySQL 1045 June, 14:24 Master.csr-r w-------1 MySQL MySQL 1679 June 14:22 master.key# master and slave servers perform the above actions

Master-Slave Server configuration SSL

[[email protected] ~]# vim /etc/mysql/my.cnf [ mysqld]               #在此段中添加如下配置ssl                                             #开启SSL功能ssl-ca = /etc/mysql/ssl/cacert.pem            #指定CA文件位置ssl-cert = /etc/mysql/ssl/master.crt          #指定证书文件位置ssl-key = /etc/mysql/ssl/master.key           #指定密钥所在位置 [[Email protected] ~]# service mysqld restart 

Primary server Configuration

Check SSL status again

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/B5/wKioL1WDwt-DtKB-AAEkFGpgUhs991.jpg "title=" 9.jpg " alt= "Wkiol1wdwt-dtkb-aaekfgpguhs991.jpg"/>

Authorized replication users can only replicate over SSL

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/C5/wKiom1WGaGuBjBUuAAFvjRanox4478.jpg "title=" 10.jpg "alt=" Wkiom1wgagubjbuuaafvjranox4478.jpg "/>

From server configuration

Login from server to test

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/C1/wKioL1WGajrAZyDMAAI4B_T_9lg596.jpg "title=" 11.jpg "alt=" Wkiol1wgajrazydmaai4b_t_9lg596.jpg "/>

This shows that the connection is based on SSL encryption, and below we start to connect to the master server and turn on replication

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/C5/wKiom1WGaJ6zTnIqAAJqYCwESp8032.jpg "title=" 12.jpg "alt=" Wkiom1wgaj6ztniqaajqycwesp8032.jpg "/>

View from server Status

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/C1/wKioL1WGaubASbhqAAFASa_MFsU947.jpg "title=" 13.jpg "alt=" Wkiol1wgaubasbhqaafasa_mfsu947.jpg "/>

Error, slave_io_running status No description start failed, find error as follows

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/C1/wKioL1WGavSwPFXGAAFUgIowDF4214.jpg "title=" 14.jpg "alt=" Wkiol1wgavswpfxgaafugiowdf4214.jpg "/>

Workaround:

#从服务器停止复制MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.01 sec)

#主服务器刷新日志, record a new location

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/C5/wKiom1WGamfwsJ4oAAD6Jw2mvfs453.jpg "title=" 15.jpg "alt=" Wkiom1wgamfwsj4oaad6jw2mvfs453.jpg "/>

#从服务器更新日志文件及位置, restart replication

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/C1/wKioL1WGbL_iSOEfAAHxAlphNbo750.jpg "title=" 16.jpg "alt=" Wkiol1wgbl_isoefaahxalphnbo750.jpg "/>

#状态都为Yes, Problem solving

Master-slave replication test

master server Add Data

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6E/C5/wKiom1WGbYvBi6zXAACHm0HGV8g455.jpg "title=" 17.jpg "alt=" Wkiom1wgbyvbi6zxaachm0hgv8g455.jpg "/>

Viewing data from the server

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/C1/wKioL1WGcBXQPHTAAAD7mXsG90w591.jpg "title=" 18.jpg "alt=" Wkiol1wgcbxqphtaaad7mxsg90w591.jpg "/>

Consistent master-Slave server data, SSL-based replication complete

The end

MYSQL/MARIADB database based on SSL implementation of master-slave replication Experiment on it, the experimental process will encounter some problems, as long as the patient treatment, everything will be solved, friends of the experimental process if you encounter problems remember message exchange OH. The above is only for individual learning to organize, if there are mistakes, big God do not spray ~ ~ ~

This article is from the "North Scholar" blog, please make sure to keep this source http://scholar.blog.51cto.com/9985645/1664009

MYSQL/MARIADB database for master-slave replication based on SSL