MySQL SSL-based master-slave replication

#注意: Master-Slave Server database version must be consistent: Master and slave server time must be synchronized

Environment:

Centos6.6_x86_64

mysql5.1

master_ip:192.168.122.20

slave_ip:192.168.122.207

Primary server Configuration

[Email protected] mysql]# Mkdir/mydata/binlogs

[Email protected] mysql]# chown-r mysql.mysql/mydata

[Email protected] mysql]# VIM/ETC/MY.CNF

DataDir =/mydata/data

Log-bin=/mydata/binlogs/master-bin

Binlog_format=mixed #二进制日志格式

Server-id = 1 #主服务器与从服务器不可一致

[[email protected] MySQL] #service mysqld start

Authorization to copy an account from the server (done by the Lord)

Mysql>grant replication Slave,replication Client on * * to ' slave ' @ ' 192.168.122.% ' identified by ' 123456 ';

Mysql>flush privileges;

View Binary Log Locations

Mysql> Show Master Status \g

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

#如果从服务器为其他从服务器主服务器须启用以上三项

Server-id = 2 #不可与主服务器一致

Relay-log=/mydata/relaylogs/relay-bin #中继日志位置

Read_Only = 1 #设置为只读

[[email protected] MySQL] #service mysqld start

Connect to the master server from the server (from the top)

Mysql>change Master to master_host= ' master_ip ', master_user= ' previously authorized user in Master ', master_password= ' 123456 ', Master_log _file= ' Master_banary_log ', master_log_pos=336 (this without quotation marks);

View from server Status

Mysql> Show Slave status \g

Master-slave replication test

master server Add Data

mysql>create database Hello;

Mysql>create table Test (id int (2));

Mysql>insert into test values (1);

Mysql>show Master Status \g

View data and status from server

Mysql>select * from Hell.test;

Mysql>show slave Status\g

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

Enable secure SSL Transport

Check SSL status

Mysql> Show variables like '%ssl% ';

To configure the primary server as a CA

[Email protected] ~]# CD/ETC/PKI/CA

[[Email protected] ca]# (umask 077;openssl genrsa-out PRIVATE/CAKEY.PEM 2048)

[email protected] ca]# OpenSSL req-new-x509-key private/cakey.pem-out cacert.pem-days 3650

[[email protected] ca]# Touch {index.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;openssl genrsa-out master.key 2048)

[email protected] ssl]# OpenSSL req-new-key master.key-out MASTER.CSR

[email protected] 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 slave.key-out SLAVE.CSR

To sign a certificate from the server

[Email protected] ssl]# SCP SLAVE.CSR master:/tmp

SLAVE.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 Slave:/etc/mysql/ssl

SLAVE.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 Slave:/etc/mysql/ssl

Cacert.pem

Permissions

[Email protected] ssl]# chown-r mysql.mysql.

[email protected] ssl]# LL

Total 20

-rw-r--r--1 MySQL MySQL 1403 June 14:50 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

-RW-------1 MySQL mysql 1679 June 14:22 Master.key

#主从服务器都执行以上操作

Primary server Configuration SSL

[Email protected] ~]# VIM/ETC/MY.CNF

[Mysqld] #在此段中添加如下配置

SSL #开启SSL功能

Ssl-ca =/var/lib/mysql/ssl/cacert.pem #指定CA文件位置

Ssl-cert =/var/lib/mysql/ssl/master.crt #指定证书文件位置

Ssl-key =/var/lib/mysql/ssl/master.key #指定密钥所在位置

[Email protected] ~]# service mysqld restart

Primary server Configuration

Check SSL status again

Mysql>show variables like '%ssl% ';

Authorized replication users can only replicate over SSL

Mysql>grant replication Slave,replication Client on * * to ' slave ' @ ' 192.168.122.% ' identified by ' 123456 ' require SSL;

From server configuration

Login from server to test

[[email protected] SSL] #mysql-uslave-p123456-h192.168.122.207--SSL-CA=/VAR/LIB/MYSQL/SSL/CACERT.PEM--ssl-cert=/ VAR/LIB/MYSQL/SSL/SLAVE.CRT--ssl-key=/var/lib/mysql/ssl/slave.key

Mysql> \s

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

MySQL Ver 14.14 distrib 5.1.73, for Redhat-linux-gnu (x86_64) using ReadLine 5.1

Connection ID:7

Current database:

Current User:[email protected]

SSL:Cipher in use is Dhe-rsa-aes256-sha

Current Pager:stdout

Using outfile:'

Using delimiter:;

Server version:5.1.73-log Source Distribution

Protocol version:10

This shows that the connection is based on the implementation of SSL encryption,

Start connecting to the primary server and turn on replication (from the top)

mysql> Change Master to master_host= ' 192.168.122.207 ', master_user= ' slave ', master_password= ' 123456 ', Master_log_ File= ' master-bin.000004 ', master_log_pos=367,master_ssl=1,master_ssl_ca= '/var/lib/mysql/ssl/cacert.pem ', master_ Ssl_cert= '/var/lib/mysql/ssl/slave.crt ', master_ssl_key= '/var/lib/mysql/ssl/slave.key ';

View from server Status

Mysql>show slave Status\g

Mysql>show tables in DB;

The test is the same as before

MySQL SSL-based master-slave replication