寫在前面

MySQL的主從復(fù)制是明文傳輸?shù)?，這對一些特殊場景來說是不允許的，因?yàn)閿?shù)據(jù)安全性會受到威脅。有關(guān)MySQL的主從復(fù)制以及SSL的相關(guān)概念網(wǎng)上相關(guān)資料比較多，在這里不予過多涉及。下面將嘗試來構(gòu)建基于SSL的MySQL的主從復(fù)制。

實(shí)驗(yàn)實(shí)現(xiàn)

實(shí)驗(yàn)環(huán)境準(zhǔn)備

實(shí)驗(yàn)環(huán)境在同一個網(wǎng)絡(luò)中
三臺虛擬機(jī) （centos7.3）
node1 ：主mysql服務(wù)器，ip地址是192.168.1.16
node2 ：從mysql 服務(wù)器，ip地址是192.168.1.7
node3 ： CA服務(wù)器，ip地址是192.168.1.15
數(shù)據(jù)庫使用的是 mariadb-server 5.5.52
確保所有虛擬機(jī)上面的防火墻和selinux處于關(guān)閉狀態(tài)
各節(jié)點(diǎn)時間必須同步
各節(jié)點(diǎn)之間通過主機(jī)名互相通信，建議使用/etc/hosts文件實(shí)現(xiàn)
各節(jié)點(diǎn)之間的root用戶可以基于密鑰認(rèn)證的ssh服務(wù)完成互相通信

實(shí)驗(yàn)過程

一、在node1和node2上面
之前在node1和node2上面，已經(jīng)安裝了mariadb數(shù)據(jù)庫以及數(shù)據(jù)庫客戶端工具，并且mariadb數(shù)據(jù)庫處于運(yùn)行狀態(tài)。

systemctl stop mariadb
rm -rf /var/lib/mysql/*

二、在node3上搭建ca服務(wù)器

cd /etc/pki/CA
# 生成證書索引數(shù)據(jù)庫文件
touch /etc/pki/CA/index.txt 
# 指定第一個頒發(fā)證書的序列號
echo 99 > /etc/pki/CA/serial 
# 創(chuàng)建私鑰文件cakey.pem
(umask 066; openssl genrsa -out private/cakey.pem 4096)
# 查看一下生成的私鑰文件
cat private/cakey.pem
# 利用私鑰文件生成自簽名證書
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
# 填寫的信息如下
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:henan
Locality Name (eg, city) [Default City]:zhengzhou
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:Opt
Common Name (eg, your name or your server's hostname) []:ca.magedu.com
Email Address []:
# 查看簽名證書的內(nèi)容
openssl x509 -in  cacert.pem -noout -text

三、在node1上面準(zhǔn)備私鑰及證書申請文件

mkdir /var/lib/mysql/ssl
cd  /var/lib/mysql/ssl
# 創(chuàng)建私鑰文件
(umask 066; openssl genrsa -out mysql.key 1024)
# 利用私鑰生成證書申請文件
openssl req -new -key mysql.key -out mysql.csr
# 填寫信息如下
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:henan
Locality Name (eg, city) [Default City]:zhengzhou
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:Opt
Common Name (eg, your name or your server's hostname) []:mysql.magedu.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# 提交證書申請文件
scp mysql.csr 192.168.1.15:/etc/pki/CA/

四、在node2上面準(zhǔn)備私鑰及證書申請文件

mkdir /var/lib/mysql/ssl
cd  /var/lib/mysql/ssl
# 創(chuàng)建私鑰文件
(umask 066; openssl genrsa -out slave.key 1024)
# 利用私鑰生成證書申請文件
openssl req -new -key slave.key -out slave.csr
# 填寫信息如下
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:henan
Locality Name (eg, city) [Default City]:zhengzhou
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:Opt
Common Name (eg, your name or your server's hostname) []:slavedb.magedu.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# 提交證書申請文件
scp slave.csr 192.168.1.15:/etc/pki/CA/

五、在node3上面頒發(fā)證書

cd /etc/pki/CA
# 生成node1主mysql的證書
openssl ca -in mysql.csr -out certs/mysql.crt -days 300
# 生成node2從mysql的證書
openssl ca -in slave.csr -out certs/slave.crt -days 300
# 將CA自簽名證書 cacert.pem  以及 mysql.crt 拷貝給node1
scp cacert.pem 192.168.1.16:/var/lib/mysql/ssl
scp certs/mysql.crt 192.168.1.16:/var/lib/mysql/ssl
# 將CA自簽名證書 cacert.pem  以及 slave.crt 拷貝給node2
scp cacert.pem 192.168.1.7:/var/lib/mysql/ssl
scp slave.crt 192.168.1.7:/var/lib/mysql/ssl

六、在node1上面配置主mysql

# 確認(rèn)所有證書相關(guān)文件
cd /var/lib/mysql/ssl
ls
cacert.pem  mysql.crt  mysql.csr  mysql.key
# 改變文件的用戶和組，以及修改權(quán)限
chown mysql.mysql * 
chmod 600 * 
# 修改數(shù)據(jù)庫的配置
vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connections = 2000

log_bin = master-log
server_id = 1
        
ssl_ca = /var/lib/mysql/ssl/cacert.pem
ssl_cert = /var/lib/mysql/ssl/mysql.crt
ssl_key = /var/lib/mysql/ssl/mysql.key
# 啟動數(shù)據(jù)庫
systemctl  start mariadb
# 查看數(shù)據(jù)庫ssl相關(guān)變量
mysql
                MariaDB [(none)]> show variables like '%ssl%';
                +---------------+-------------------------------+
                | Variable_name | Value                         |
                +---------------+-------------------------------+
                | have_openssl  | YES                           |
                | have_ssl      | YES                           |
                | ssl_ca        | /var/lib/mysql/ssl/cacert.pem |
                | ssl_capath    |                               |
                | ssl_cert      | /var/lib/mysql/ssl/mysql.crt  |
                | ssl_cipher    |                               |
                | ssl_key       | /var/lib/mysql/ssl/mysql.key  |
                +---------------+-------------------------------+

七、在node2上面配置從mysql

# 確認(rèn)所有證書相關(guān)文件
cd /var/lib/mysql/ssl
ls
cacert.pem  slave.crt  slave.csr  slave.key
# 改變文件的用戶和組，以及修改權(quán)限
chown mysql.mysql * 
chmod 600 * 
# 修改數(shù)據(jù)庫的配置
vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connections = 2000

relay_log = relay-log
server_id = 2
read_only = ON

ssl_ca = /var/lib/mysql/ssl/cacert.pem
ssl_cert = /var/lib/mysql/ssl/slave.crt
ssl_key = /var/lib/mysql/ssl/slave.key
# 啟動數(shù)據(jù)庫
systemctl  start mariadb
# 查看數(shù)據(jù)庫ssl相關(guān)變量
mysql
                MariaDB [(none)]> show variables like '%ssl%';
                +---------------+-------------------------------+
                | Variable_name | Value                         |
                +---------------+-------------------------------+
                | have_openssl  | YES                           |
                | have_ssl      | YES                           |
                | ssl_ca        | /var/lib/mysql/ssl/cacert.pem |
                | ssl_capath    |                               |
                | ssl_cert      | /var/lib/mysql/ssl/slave.crt  |
                | ssl_cipher    |                               |
                | ssl_key       | /var/lib/mysql/ssl/slave.key  |
                +---------------+-------------------------------+

八、在node1上面配置主mysql

mysql
MariaDB [(none)]> show master status;
        +-------------------+----------+--------------+------------------+
        | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +-------------------+----------+--------------+------------------+
        | master-log.000003 |      245 |              |                  |
        +-------------------+----------+--------------+------------------+
#  確認(rèn)沒有 repluser 用戶 
MariaDB [(none)]> select host,user,password from mysql.user;  
MariaDB [(none)]> grant replication client,replication slave on *.* to
               -> 'repluser'@'192.168.1.%' identified by 'centos' require ssl; 
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;
        +-------------------+----------+--------------+------------------+
        | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +-------------------+----------+--------------+------------------+
        | master-log.000003 |      507 |              |                  |
        +-------------------+----------+--------------+------------------+

九、在node2上面配置從mysql

mysql
MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to 
               -> MASTER_HOST='192.168.1.16',
               -> MASTER_USER='repluser',
               -> MASTER_PASSWORD='centos',
               -> MASTER_LOG_FILE='master-log.000003',
               -> MASTER_LOG_POS=507,
               -> 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';
MariaDB [(none)]> start slave IO_THREAD,SQL_THREAD;
MariaDB [(none)]> show slave status\G;
# 主要信息如下
Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.16
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000003
          Read_Master_Log_Pos: 507
        Relay_Master_Log_File: master-log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes    
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /var/lib/mysql/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /var/lib/mysql/ssl/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /var/lib/mysql/ssl/slave.key  
Master_SSL_Verify_Server_Cert: No      
             Master_Server_Id: 1

在node2上面用ssl加密連接node1的mysql

mysql -urepluser -pcentos -h192.168.1.16 --ssl-cert=/var/lib/mysql/ssl/slave.crt --ssl-key=/var/lib/mysql/ssl/slave.key

用status命令查看，下面是出現(xiàn)的信息

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          1004
Current database:
Current user:           repluser@192.168.1.7
SSL:                    Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         5.5.52-MariaDB MariaDB Server
Protocol version:       10
Connection:             192.168.1.16 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 35 min 58 sec

主要是：
Current user: repluser@192.168.1.7
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
也可以用tcpdump等抓包工具查看是否加密。

十、在node1創(chuàng)建數(shù)據(jù)庫和表

mysql
MariaDB [(none)]> create database hidb;
MariaDB [(none)]> use hidb;
MariaDB [(none)]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('F','M'),major VARCHAR(200));

在bash shell 里面執(zhí)行

GENDER=('F' 'M')
for i in {1..1000};do mysql -e "INSERT INTO hidb.students(name,age,gender) VALUES ('stu$i',$[RANDOM%80+18],'${GENDER[RANDOM%2]}')";done
insert into students(name,age,gender,major) values('liuqingyun',52,'M','performer'),('wangfei',48,'F','singer');
insert into students(name,age,gender,major) values('zhouxingchi',52,'M','director'),('Aaron Sorkin',48,'M','Screenwriter');

十一、在node2上面查看數(shù)據(jù)庫和表是否生成

mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hidb               |
| mysql              |
| performance_schema |
| ssl                |
| test               |
+--------------------+
MariaDB [hidb]> use hidb;
# 發(fā)現(xiàn)表文件以及其中的記錄已經(jīng)同步過來
MariaDB [hidb]> select * from students;

十二、出現(xiàn)的問題
在從數(shù)據(jù)庫服務(wù)器中插入數(shù)據(jù)，出現(xiàn)問題，此時在從數(shù)據(jù)庫服務(wù)器一端插入的數(shù)據(jù)并不會同步到主數(shù)據(jù)庫服務(wù)器一端。而且，很有可能會導(dǎo)致，主數(shù)據(jù)庫服務(wù)器一端，對數(shù)據(jù)的修改也無法同步到從數(shù)據(jù)庫一端，會導(dǎo)致主從數(shù)據(jù)不一致?？偨Y(jié)來說就是，從數(shù)據(jù)庫服務(wù)器的數(shù)據(jù)是絕不允許多過主數(shù)據(jù)庫服務(wù)器一端，不然的話，從節(jié)點(diǎn)會無所適從。
如果在從數(shù)據(jù)庫服務(wù)器一端向hidb數(shù)據(jù)庫插入數(shù)據(jù)，導(dǎo)致同步出錯，可以如下進(jìn)行操作。

mysql
MariaDB [hidb]> stop slave;
MariaDB [hidb]> show databases;
MariaDB [hidb]> drop database hidb; 
MariaDB [(none)]> change master to 
               -> MASTER_HOST='192.168.1.16',
               -> MASTER_USER='repluser',
               -> MASTER_PASSWORD='centos',
               -> MASTER_LOG_FILE='master-log.000003',
               -> MASTER_LOG_POS=507,
               -> 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';
MariaDB [(none)]> start slave IO_THREAD,SQL_THREAD;

至此，基于SSL實(shí)現(xiàn)MySQL的加密主從復(fù)制以及可能會出現(xiàn)的問題就已經(jīng)基本說明清楚了，博客參考了
http://blog.csdn.net/u012974916/article/details/53316758
http://www.zuimoban.com/jiaocheng/mysql/11089.html
不足之處，請多指正。

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

基于SSL實(shí)現(xiàn)MySQL的加密主從復(fù)制

基于SSL實(shí)現(xiàn)MySQL的加密主從復(fù)制

寫在前面

實(shí)驗(yàn)實(shí)現(xiàn)

實(shí)驗(yàn)環(huán)境準(zhǔn)備

實(shí)驗(yàn)過程

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

基于SSL實(shí)現(xiàn)MySQL的加密主從復(fù)制

寫在前面

實(shí)驗(yàn)實(shí)現(xiàn)

實(shí)驗(yàn)環(huán)境準(zhǔn)備

實(shí)驗(yàn)過程

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av