创新互联主要从事网站制作、成都做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务源城,10多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18980820575
192.168.205.37: as master server
192.168.205.47: as middle server
192.168.205.57: as slave server
OS: centos 7 1810 with mini install
mariadb-5.5.60
有时我们的数据库复制可能要跨网络复制，如果不想在复制过程中让别人嗅探，我们可以使用ssl协议实现复制过程中数据的加密传输，此实验使用三台服务器实现半同步复制，并他复制之间启用加密复制
使用如下脚本安装三台服务器
[root@centos7 data]#cat /data/maridb_yum.sh
#!/bin/bash
# use last digit of IP as server-id
ID=`ip a show dev eth0 | sed -r '3!d;s@(.*inet)(.*)(/.*)@\2@' | cut -d. -f4`
# install mariadb-server and create data and logs directory
rpm -q mariadb-server ||yum install -y mariadb-server
[ -d /data/MySQL ] || mkdir -p /data/mysql
[ -d /data/logs ] || mkdir -p /data/logs
chown mysql:mysql /data/{mysql,logs}
# modify the my.cnf
#设置数据文件位置
sed -i 's@datadir=/var/lib/mysql@datadir=/data/mysql@' /etc/my.cnf
#开启二进制日志并文件的起始名称
sed -i 's@log-bin=mysql-bin@log-bin=/data/logs/bin@' /etc/my.cnf
#设置innodb表分离文件
grep "innodb_file_per_table" /etc/my.cnf || sed -i '/\[mysqld\]/a innodb_file_per_table = on' /etc/my.cnf
#跳过名称解析
grep "skip_name_resolve" /etc/my.cnf || sed -i '/\[mysqld\]/a skip_name_resolve = on' /etc/my.cnf
#将server-id设为eth0的IP的最后一位数，可跟据自己的需求更改
grep "server-id" /etc/my.cnf || sed -i "/\[mysqld\]/a server-id=$ID" /etc/my.cnf
#启动服务
service mariadb restart[root@slave1 ~]#rpm -ql mariadb-server
…
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
…MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';MariaDB [(none)]> show master logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| bin.000001 | 30373 |
| bin.000002 | 1038814 |
| bin.000003 | 401 |
+------------+-----------+
3 rows in set (0.00 sec)MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; MariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)MariaDB [(none)]> set global rpl_semi_sync_master_enabled=onMariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)MariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 363 |
| Rpl_semi_sync_master_net_wait_time | 25473 |
| Rpl_semi_sync_master_net_waits | 70 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 380 |
| Rpl_semi_sync_master_tx_wait_time | 13305 |
| Rpl_semi_sync_master_tx_waits | 35 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 35 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.205.37',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='bin.000003',
-> MASTER_LOG_POS=401,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)此进查看状态为OFF,我们需要开启slave线程
MariaDB [(none)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> start salve;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'salve' at line 1
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.37
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: bin.000003
Read_Master_Log_Pos: 401
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 523
Relay_Master_Log_File: bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: YesMariaDB [(none)]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 | #已经有两个客户端说明正常
| Rpl_semi_sync_master_net_avg_wait_time | 363 |
| Rpl_semi_sync_master_net_wait_time | 25473 |
| Rpl_semi_sync_master_net_waits | 70 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 380 |
| Rpl_semi_sync_master_tx_wait_time | 13305 |
| Rpl_semi_sync_master_tx_waits | 35 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 35 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)[root@master ~]#mysql < hellodb_innodb.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
两台从服务器上查看库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)[root@master ~]#mkdir /etc/my.cnf.d/ssl
[root@master ~]#cd /etc/my.cnf.d/ssl
[root@master ssl]#openssl genrsa 2048 > cakey.pem[root@master ssl]#openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650为简化我们先产生一个私钥，并使用这个私钥为master生成证书请求文件，注意这时不是证书，是证书请求文件
[root@master ssl]#openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr
Generating a 1024 bit RSA private key
.............++++++
...++++++
writing new private key to 'master.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:contoso
Organizational Unit Name (eg, section) []:devops
Common Name (eg, your name or your server's hostname) []:master.contoso.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@centos7 ssl]#ls
cacert.pem cakey.pem master.csr master.key[root@master ssl]#openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
Signature ok
subject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=master.contoso.com
Getting CA Private Key
[root@master ssl]#ll
total 20
-rw-r--r-- 1 root root 1334 Aug 11 21:55 cacert.pem
-rw-r--r-- 1 root root 1675 Aug 11 21:52 cakey.pem
-rw-r--r-- 1 root root 1034 Aug 11 23:01 master.crt
-rw-r--r-- 1 root root 664 Aug 11 21:59 master.csr
-rw-r--r-- 1 root root 916 Aug 11 21:59 master.key重复18和19再生成两个从节点证书文件
[root@master ssl]#openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave1.key > slave1.csr
Generating a 1024 bit RSA private key
.....++++++
........++++++
writing new private key to 'slave1.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:contoso
Organizational Unit Name (eg, section) []:devops
Common Name (eg, your name or your server's hostname) []:slave1.contoso.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@master ssl]#openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave2.key > slave2.csr
Generating a 1024 bit RSA private key
.++++++
........++++++
writing new private key to 'slave2.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:contoso
Organizational Unit Name (eg, section) []:devops
Common Name (eg, your name or your server's hostname) []:slave2.contoso.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@master ssl]#openssl x509 -req -in slave1.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave1.crt
Signature ok
subject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=slave1.contoso.com
Getting CA Private Key
[root@master ssl]#openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 > slave2.crt
Signature ok
subject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=slave2.contoso.com
Getting CA Private Key[root@master ssl]#ll
total 44
-rw-r--r-- 1 root root 1334 Aug 11 21:55 cacert.pem
-rw-r--r-- 1 root root 1675 Aug 11 21:52 cakey.pem
-rw-r--r-- 1 root root 1034 Aug 11 23:01 master.crt
-rw-r--r-- 1 root root 664 Aug 11 21:59 master.csr
-rw-r--r-- 1 root root 916 Aug 11 21:59 master.key
-rw-r--r-- 1 root root 1034 Aug 11 23:05 slave1.crt
-rw-r--r-- 1 root root 664 Aug 11 23:04 slave1.csr
-rw-r--r-- 1 root root 916 Aug 11 23:04 slave1.key
-rw-r--r-- 1 root root 1034 Aug 11 23:06 slave2.crt
-rw-r--r-- 1 root root 664 Aug 11 23:05 slave2.csr
-rw-r--r-- 1 root root 916 Aug 11 23:05 slave2.key[root@master ssl]#scp -r /etc/my.cnf.d/ssl/ 192.168.205.47:/etc/my.cnf.d/
[root@master ssl]#scp -r /etc/my.cnf.d/ssl/ 192.168.205.57:/etc/my.cnf.d/MariaDB [(none)]> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+----------+
7 rows in set (0.00 sec)[root@master ssl]#vi /etc/my.cnf
[mysqld]
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
[root@master ssl]#systemctl restart mariadb此时查看变量值，但因为你连接时没有起用加密，所以状态的ssl为not in use
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 |
+---------------+------------------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
…使用客户端加密的方式连接，可以看到状态为加密的
[root@master ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 5
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
…我们再从节点上测试用ssl连接主节点
[root@slave1 ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=slave1.crt --ssl-key=slave1.key -h292.168.205.37 -urepluser -pcentos
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: repluser@192.168.205.47
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384MariaDB [(none)]> grant replication slave on *.* to repluser2@'192.168.205.%' identified by 'centos' require ssl;
Query OK, 0 rows affected (0.00 sec)用建立的帐号从另外一台从服务器尝试去登录
[root@slave1 ssl]#mysql -h292.168.205.37 -urepluser2 -pcentos
ERROR 1045 (28000): Access denied for user 'repluser2'@'192.168.205.47' (using password: YES)
[root@slave1 ssl]#mysql --ssl-ca=cacert.pem --ssl-cert=slave1.crt --ssl-key=slave1.key -h292.168.205.37 -urepluser2 -pcentos
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 14
Current database:
Current user: repluser2@192.168.205.47
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384所以如果使用repluser2去和主服务器建立复制，我们需要修改配置文件
[root@slave1 ssl]#vi /etc/my.cnf
[mysqld]
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave1.crt
ssl-key=/etc/my.cnf.d/ssl/slave1.key
[root@slave1 ssl]#systemctl restart mariadb
[root@slave2 ssl]#vi /etc/my.cnf
[mysqld]
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave2.crt
ssl-key=/etc/my.cnf.d/ssl/salve2.key
[root@slave1 ssl]#systemctl restart mariadbMariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.205.37',
-> MASTER_USER='repluser2',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='bin.000004',
-> MASTER_LOG_POS=496,
-> MASTER_SSL=1;
Query OK, 0 rows affected (0.01 sec)启动slave查看状态，一连接和复制正常
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.37
Master_User: repluser2
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: bin.000004
Read_Master_Log_Pos: 415
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 693
Relay_Master_Log_File: bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Master_SSL_Allowed: Yes
…删除以前不用的复制帐号，建表或删库测试，
MariaDB [(none)]> drop user repluser@'192.168.205.%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,password from mysql.user;
+-----------+---------------------+-------------------------------------------+
| user | host | password |
+-----------+---------------------+-------------------------------------------+
| root | localhost | |
| root | centos7.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | centos7.localdomain | |
| repluser2 | 192.168.205.% | *128977E278358FF80A246B5046F51043A2B1FCED |
+-----------+---------------------+-------------------------------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> create database db1
-> ;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)在从节点上测试库是否建立, 发现出错，原因是从服务器在帐号repluser建立后复制的，所以当我们删除时因为从服务器上没有，所以出错误了，解决办法是跳过这次错误， 再次测试，发现db1复制成功，在slave2做同样的测试。
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.37
Master_User: repluser2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000004
Read_Master_Log_Pos: 749
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 602
Relay_Master_Log_File: bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Error 'Operation DROP USER failed for 'repluser'@'192.168.205.%'' on query. Default database: ''. Query: 'drop user repluser@'192.168.205.%''
….
#注意此跳包括正确和错误的计数，如果正确的被跳过可能出现错误复制。
MariaDB [(none)]> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.37
Master_User: repluser2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000004
Read_Master_Log_Pos: 749
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 523
Relay_Master_Log_File: bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
….
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)