概述:
本章将主要介绍MySQL复制中如何过滤,监控维护,以及基于SSL的主从复制,具体内容如下:
- MySQL 复制过滤器
·从服务器库级别过滤
- MySQL 清理日志:PURGE
- 复制监控
·Master
·Slave
- 如何确定主从节点的数据是否一致
- MySQL基于SSL的主从复制(重要)
===============================================================================
MySQL 复制过滤器: ---仅复制有限一个或几个数据库相关的数据,而非所有;由复制过滤器进行; ★有两种实现思路: ☉主服务器 ⒈主服务器仅向二进制日志中记录有关特定数据库相关的写操作; ⒉问题:其它库的point-recovery将无从实现; ⒊配置参数: ⒋注意: ☉从服务器 ⒈从服务器的SQL THREAD仅重放关注的数据库或表相关的事件,并将其应用于本地; ⒉问题:网络IO和磁盘IO; ⒊配置参数:
①库级别控制
②表级别控制 Replicate_Do_Table= Replicate_Ignore_Table=
③基于通配符做表名匹配 演示:
基于主从复制实现从服务器仅对把主节点服务器的mydb数据库从中继日志中同步到磁盘中,实验过程如下:
1.停止从服务器的mysql服务,编辑其配置文件,添加需要过滤的白名单,如下:
1
2
3
4
5
6
| [iyunv@slave ~]# vim /etc/my.cnf
skip-name-resolve = ON
innodb-file-per-table = ON
relay-log=relay-log
server-id=2
replicate-do-db = mydb # 添加白名单仅复制数据库mydb
|
2.启动mysql服务,查看Slave的状态,可以看到复制的数据库Replicate——Do_DB为mydb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
| MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.252.153
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000004
Read_Master_Log_Pos: 245
Relay_Log_File: relay-log.000009
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb # 仅复制mydb的数据库
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 818
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
|
测试:
1)首先在主服务器上的hellodb数据库中创建表,在从服务器上检查发现,并没有hellodb数据库中创建的表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| # 在主服务器的hellodb数据库中创建表tbl
MariaDB [hellodb]> CREATE TABLE tbl1 (id int);
Query OK, 0 rows affected (10.09 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| tbl1 |
| teachers |
| toc |
+-------------------+
8 rows in set (0.01 sec)
#========================================================================================
# 在从服务器中查看,并未在hellodb数据库中发现表tbl,说明定义的白名单生效了,过滤了其他库
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
|
2)然后,再在主服务器上的mydb数据库中创建表,在从服务器上检查发现,这次mydb数据库中已然存在创建的表tbl2,说明仅复制了mydb数据库中的表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| # 在主服务器中,复制helldb数据库中的students表到mydb数据库中的表tbl2中
MariaDB [mydb]> CREATE TABLE tbl2 AS SELECT * FROM hellodb.students;
Query OK, 25 rows affected (0.03 sec)
Records: 25 Duplicates: 0 Warnings: 0
#========================================================================================
# 在从服务器mydb数据库中查看,发现表tbl2
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| tbl2 |
+----------------+
1 row in set (0.00 sec)
MariaDB [mydb]> select * from tbl2;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
|
如上,就是主从复制的过滤的整个过程...
MySQL 复制的监控和维护:★清理日志:PURGE ★复制监控 ☉MASTER: SHOW MASTER STATUS; SHOW BINLOG EVENTS; SHOW BINARY LOGS;
☉SLAVE: ★如何确定主从节点数据是否一致? ★主从数据不一致时的修复方法?
演示:
1.清理二进制日志(在删除之前最好先备份一下)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| [iyunv@master ~]# cd /var/lib/mysql/
[iyunv@master mysql]# ls
aria_log.00000001 hellodb ib_logfile0 master-log.000001 master-log.000003 master-log.index
aria_log_control ibdata1 ib_logfile1 master-log.000002 master-log.000004 mydb
[iyunv@master mysql]# cat master-log.index //索引的二进制日志文件
./master-log.000001
./master-log.000002
./master-log.000003
./master-log.000004
MariaDB [(none)]> SHOW MASTER LOGS; # 查看现有的二进制日志文件
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-log.000001 | 30337 |
| master-log.000002 | 1038814 |
| master-log.000003 | 7931 |
| master-log.000004 | 543 |
+-------------------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> PURGE BINARY LOGS TO 'master-log.000004'; # 清空之前的文件
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-log.000004 | 543 |
+-------------------+-----------+
1 row in set (0.00 sec)
|
MySQL 主从复制的读写分离:★主从复制的读写分离: mysql-proxy --> atlas
amoeba AliSQL:
★mysqlrouter:
MySQL 基于SSL的主从复制★步骤概览 1.配置Master为CA证书服务器 ☉生成私钥文件/etc/pki/CA/private/cakey.pem; ☉在/etc/pki/CA目录中,使用私钥文件生成自签证书; ☉为CA提供所需的目录和文件; 2.为Master创建证书申请,并由CA服务器签发证书: ☉在master数据库目录中创建一个ssl目录 ☉在ssl目录中创建用于master主服务器通信时的私钥; ☉在ssl目录中利用生成的私钥进行证书签署请求; ☉在ssl目录中由CA服务器(master所在本主机)为master服务器签发证书: 3.为slave从服务器创建证书申请,并由CA服务器签发证书 ☉在slave数据库目录中创建一个ssl目录 ☉在ssl目录中创建用于slave主服务器通信时的私钥; ☉在ssl目录中利用生成的私钥进行证书签署请求; ☉将证书签署请求远程复制到CA服务器 ☉由CA服务器(master所在本主机)为slave服务器签发证书,并回传到slave服务器 4.将CA证书分别拷贝到主master和从slave服务器的/var/lib/mysql/ssl中 5.修改master主服务器和slave从服务器的证书属主、属组为mysql用户 6.分别编辑Master和Slave的配置文件/etc/my.cnf开启ssl加密功能; ☉Master: skip_name_resolve = ON innodb_file_per_table = ON log_bin=master-log server-id=1 //此id必须全局唯一 sync_binlog = 1 //每次提交立即将二进制事件从内存同步到二进制日志中 innodb_flush_logs_at_trx_commit=1 //事务日志提交时立刻刷写到磁盘 innodb_support_xa=1 //支持分布式事务 ssl ssl_ca = /var/lib/mysql/ssl/cacert.pem ssl_cert = /var/lib/mysql/ssl/master.crt ssl_key = /var/lib/mysql/ssl/master.key
☉Slave: skip_name_resolve = ON innodb_file_per_table = ON relay-log=relay-log server-id=2 read-only=1 sync_master_info=ON sync_relay_log_info=ON ssl ssl_ca = /var/lib/mysql/ssl/cacert.pem ssl_cert = /var/lib/mysql/ssl/master.crt ssl_key = /var/lib/mysql/ssl/master.key
7.在主节点启动mysql服务,查看ssl加密服务是否开启,并授权一个可以基于SSL连接复制的用户账号: systemctl start mariadb.service mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.1.%.%' IDENTIFIED BY 'replpass' REQUIRE SSL; mysql> FLUSH PRIVILEGES;
8.在从节点启动mysql服务,并查看ssl加密服务是否开启,然后,连接mysql,设置连接主服务器的相关信息,并启动复制 -> MASTER_HOST='10.1.252.153', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_LOG_FILE='master-log.000003', -> MASTER_LOG_POS=506, -> 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'; 实验环境:
具体实验步骤如下:
1.配置Master为CA证书服务器
1)生成私钥文件/etc/pki/CA/private/cakey.pem 1
2
3
4
5
6
7
8
9
10
11
| [iyunv@master ~]# ls /etc/pki/CA/private/ # 查看私钥文件为空
[iyunv@master ~]# (umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 4096) # 生成私钥文件
Generating RSA private key, 4096 bit long modulus
..............++
...........................................................................................................................................................................................................................................................................................................................++
e is 65537 (0x10001)
[iyunv@centos7 ~]# ls /etc/pki/CA/private/
cakey.pem
[iyunv@centos7 ~]# ll /etc/pki/CA/private/ # 查看文件,并确定其权限仅为属主自己
total 4
-rw------- 1 root root 3247 Sep 28 19:03 cakey.pem
|
2)生成自签证书
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| [iyunv@master ~]# cd /etc/pki/CA/
# 生成自签证书指明私钥文件,证书保存路径,有效期限等
[iyunv@master CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem
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]:MageEdu
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:ca.taotao.com
Email Address []:xiuxiu@taotao.com
[iyunv@centos7 ~]# ls /etc/pki/CA
cacert.pem # 生成的自签证书 certs crl newcerts private
|
3)为CA提供所需的目录和文件 1
2
3
4
| [iyunv@master ~]# touch /etc/pki/CA/index.txt # 创建数据库文件
[iyunv@master ~]# echo 01 > /etc/pki/CA/serial # 创建序列号文件并给明第一个证书的序列号码
[iyunv@master ~]# ls /etc/pki/CA/
cacert.pem certs crl index.txt newcerts private serial
|
2.为Master创建证书申请,并由CA服务器签发证书:
1)创建用于master主服务器通信时的私钥
1
2
3
4
5
6
7
8
9
10
11
12
13
| [iyunv@master ~]# cd /var/lib/mysql/
[iyunv@master mysql]# mkdir ssl # 创建一个ssl目录
[iyunv@master mysql]# ls
ssl
[iyunv@master mysql]# cd ssl # 在目录中创建私钥
[iyunv@master ssl]# (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
.................................+++
........................................................................................................+++
e is 65537 (0x10001)
[iyunv@master ssl]# ll
total 4
-rw------- 1 root root 1679 Dec 5 16:18 master.key # 创建完成
|
2)利用生成的私钥进行证书签署请求
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| [iyunv@master ssl]# openssl req -new -key master.key -out master.csr -days 3650
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]:MageEdu
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:ca.taotao.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[iyunv@master ssl]# ls
master.csr master.key
|
3)由CA服务器(master所在本主机)为master服务器签发证书:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| [iyunv@master ssl]# openssl ca -in master.csr -out master.crt -days 3650 # 签发证书
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Dec 5 08:30:27 2016 GMT
Not After : Dec 3 08:30:27 2026 GMT
Subject:
countryName = CN
stateOrProvinceName = Beijing
organizationName = MageEdu
organizationalUnitName = ops
commonName = ca.taotao.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
66:32:B3:BE:D0:16:45:E9:5B:CE:19:E3:51:4E:2F:27:BC:CB:37:7A
X509v3 Authority Key Identifier:
keyid:A5:72:A5:C4:94:F4:E8:23:BC:E6:A8:BF:9A:F8:B6:A1:CF:74:E9:32
Certificate is to be certified until Dec 3 08:30:27 2026 GMT (3650 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
[iyunv@master ssl]# ls
master.crt master.csr master.key
|
3.为slave从服务器创建证书申请,并由CA服务器签发证书
1)创建用于slave从服务器通信时的私钥
1
2
3
4
5
6
7
8
9
| [iyunv@slave ~]# mkdir /var/lib/mysql/ssl
[iyunv@slave ~]# cd /var/lib/mysql/ssl
[iyunv@slave ssl]# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.............+++
............................+++
e is 65537 (0x10001)
[iyunv@slave ssl]# ls
slave.key
|
2)利用生成的私钥进行证书签署请求
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| [iyunv@slave ssl]# openssl req -new -key slave.key -out slave.csr -days 3650
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]:MageEdu
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:slave.taotao.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[iyunv@slave ssl]# ls
slave.csr # 生成的证书申请 slave.key
|
3)将slave服务器的证书申请远程复制到CA所在服务器的/tmp目录下:
1
2
3
4
5
6
7
| [iyunv@slave ssl]# scp slave.csr root@10.1.252.153:/tmp/
The authenticity of host '10.1.252.153 (10.1.252.153)' can't be established.
ECDSA key fingerprint is b5:e2:ea:f0:a9:be:12:70:23:5a:6b:fb:e7:0a:17:1a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.1.252.153' (ECDSA) to the list of known hosts.
root@10.1.252.153's password:
slave.csr
|
4)在CA服务器(master所在本主机)查看slave服务器传过来的证书,为slave服务器签发证书:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
| [iyunv@master ~]# ls /tmp
slave.csr # 传过来的slave从服务器证书申请
# 为从服务器slave签发证书
[iyunv@master ~]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 3650
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Dec 5 08:46:48 2016 GMT
Not After : Dec 3 08:46:48 2026 GMT
Subject:
countryName = CN
stateOrProvinceName = Beijing
organizationName = MageEdu
organizationalUnitName = ops
commonName = slave.taotao.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
0B:6D:20:00:23:50:7B:2C:8D:83:6A:C3:42:B7:39:11:95:70:74:E7
X509v3 Authority Key Identifier:
keyid:A5:72:A5:C4:94:F4:E8:23:BC:E6:A8:BF:9A:F8:B6:A1:CF:74:E9:32
Certificate is to be certified until Dec 3 08:46:48 2026 GMT (3650 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
[iyunv@master ~]# ls /tmp
slave.crt # 签发的证书 slave.csr
|
5)在CA服务器,将签发好的slave证书传给slave服务器的/var/lib/mysql/ssl中
1
2
3
4
5
| [iyunv@master ~]# scp /tmp/slave.crt 10.1.249.103:/var/lib/mysql/ssl/
# 在slave从服务器中查看
[iyunv@slave ssl]# ls
slave.crt # 签署的证书 slave.csr slave.key
|
4.将CA证书拷贝到slave从服务器,并为master也拷贝一份
1
2
3
| [iyunv@master ~]# scp /etc/pki/CA/cacert.pem 10.1.249.103:/var/lib/mysql/ssl/
100% 2114 2.1KB/s 00:00
[iyunv@master ~]# cp /etc/pki/CA/cacert.pem /var/lib/mysql/ssl
|
5.修改master主服务器和slave从服务器的证书属主、属组为mysql用户
1)Master主节点
1
2
3
4
5
6
7
8
9
10
| [iyunv@master ~]# cd /var/lib/mysql/ssl
[iyunv@master ssl]# ls
cacert.pem master.crt master.csr master.key
[iyunv@master ssl]# chown -R mysql.mysql ./*
[iyunv@master ssl]# ll
total 20
-rw-r--r-- 1 mysql mysql 2114 Dec 5 16:58 cacert.pem
-rw-r--r-- 1 mysql mysql 5792 Dec 5 16:30 master.crt
-rw-r--r-- 1 mysql mysql 1005 Dec 5 16:25 master.csr
-rw------- 1 mysql mysql 1679 Dec 5 16:18 master.key
|
2)Slave从节点
1
2
3
4
5
6
7
8
9
| [iyunv@slave ssl]# ls
cacert.pem slave.crt slave.csr slave.key
[iyunv@slave ssl]# chown -R mysql.mysql ./*
[iyunv@slave ssl]# ll
total 20
-rw-r--r-- 1 mysql mysql 2114 Dec 5 17:00 cacert.pem
-rw-r--r-- 1 mysql mysql 5799 Dec 5 16:53 slave.crt
-rw-r--r-- 1 mysql mysql 1009 Dec 5 16:42 slave.csr
-rw------- 1 mysql mysql 1679 Dec 5 16:36 slave.key
|
6.分别在编辑Master和Slave的配置文件/etc/my.cnf开启ssl加密功能;
1)Master
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| [iyunv@master ssl]# vim /etc/my.cnf
[mysqld]
skip_name_resolve = ON
innodb_file_per_table = ON
log_bin=master-log # 因为为主节点,所以开启二进制日志
server-id=1 # server-id=1
sync_binlog = 1
innodb_flush_logs_at_trx_commit=ON
innodb_support_xa=ON
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 # 指定密钥文件所在位置
|
2)Slave
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| [iyunv@master ssl]# vim /etc/my.cnf
[mysqld]
skip_name_resolve = ON
innodb_file_per_table = ON
relay-log=relay-log # 作为从节点要开启中继日志
server-id=2 # server-id=1
read_only=1
sync_master_info=ON
sync_relay_log_info=ON
ssl # 开启ssl功能
ssl_ca = /var/lib/mysql/ssl/cacert.pem # 指定CA文件位置
ssl_cert = /var/lib/mysql/ssl/slave.crt # 指定证书文件位置
ssl_key = /var/lib/mysql/ssl/slave.key # 指定密钥文件所在位置
|
7.在主节点启动mysql服务,查看ssl加密服务是否开启;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| [iyunv@master ~]# systemctl start mariadb.service # 启动服务
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%SSL%'; # 查看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/master.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/master.key |
+---------------+-------------------------------+
7 rows in set (0.00 sec)
|
1)并授权一个基于SSL加密可以连接复制的用户,并查看二进制日志的位置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.1.%.%' IDENTIFIED BY 'replpass' REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
# 查看二进制日志的位置
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 506 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
|
2)测试使用加密用户指定密钥连接Master服务器
1
2
3
4
5
6
7
8
9
10
| [iyunv@master ~]# mysql -urepluser -h10.1.252.153 -preplpass --ssl-ca=/var/lib/mysql/ssl/cacert.pem --ssl-cert=/var/lib/mysql/ssl/master.crt --ssl-key=/var/lib/mysql/ssl/master.key
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)] # 正常连接
|
8.在从节点启动mysql服务,并查看ssl加密服务是否开启
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| [iyunv@slave ~]# systemctl start mariadb.service
MariaDB [(none)]> SHOW GLOBAL 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 |
+---------------+-------------------------------+
7 rows in set (0.01 sec)
|
1)在从服务器上设置连接主服务器的相关信息,并启动复制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.1.252.153',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-log.000003',
-> MASTER_LOG_POS=506,
-> 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';
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> START SLAVE; # 启动复制功能
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G # 查看状态如下:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.252.153
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 506
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 506
Relay_Log_Space: 818
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
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
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
|
测试,
在主节点上创建一个数据库,在从节点查看已经否复制过来了,说明配置的没有问题,如下:
1)主节点创建mydb数据库,查看二进制日志位置为589
1
2
3
4
5
6
7
8
9
10
| MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 589 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
|
2)在从节点查看其数据库,以及是否有延迟
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
| MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb | # 创建的数据库
| mysql |
| performance_schema |
| ssl |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.252.153
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 589
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 613
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 589 # 位置589
Relay_Log_Space: 901
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
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
Seconds_Behind_Master: 0 # 无延迟
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
|
如上,就是基于SSL加密主从复制的整个过程...
|