首页 > 代码库 > MySQL 复制过滤器、监控维护及主从复制的读写分离
MySQL 复制过滤器、监控维护及主从复制的读写分离
MySQL 复制过滤器、监控维护及基于SSL的主从复制
===============================================================================
概述：
本章将主要介绍MySQL复制中如何过滤，监控维护，以及基于SSL的主从复制，具体内容如下：
MySQL 复制过滤器
·从服务器库级别过滤
MySQL 清理日志：PURGE
复制监控
·Master
·Slave
如何确定主从节点的数据是否一致
MySQL基于SSL的主从复制（重要）
===============================================================================
MySQL 复制过滤器：
---仅复制有限一个或几个数据库相关的数据，而非所有；由复制过滤器进行；
★有两种实现思路：
☉主服务器
⒈主服务器仅向二进制日志中记录有关特定数据库相关的写操作；
⒉问题：其它库的point-recovery将无从实现；
⒊配置参数：
binlog_do_db= //白名单，仅把指定库记录于二进制日志中
binlog_ignore_db= //黑名单，忽略指定库，其余的的都记录于二进制日志文件中
⒋注意：
白黑名单二者选其一；
因为在主服务器上进行过滤其他库的事件将无法记录二进制日志文件中，如果出现故障因为没有二进制日志所以就不能做时间地点还原，所以，一般不使用。
☉从服务器
⒈从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；
⒉问题：网络IO和磁盘IO；
⒊配置参数：
①库级别控制
Replicate_Do_DB= //白名单
Replicate_Ignore_DB= //黑名单
②表级别控制
Replicate_Do_Table=
Replicate_Ignore_Table=
③基于通配符做表名匹配
Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=
演示：
基于主从复制实现从服务器仅对把主节点服务器的mydb数据库从中继日志中同步到磁盘中，实验过程如下：
1.停止从服务器的mysql服务，编辑其配置文件，添加需要过滤的白名单，如下：
[root@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
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数据库中创建的表
# 在主服务器的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数据库中的表
# 在主服务器中，复制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
PURGE { BINARY | MASTER } LOGS { TO ‘log_name‘ | BEFORE datetime_expr };
★复制监控
☉MASTER:
SHOW MASTER STATUS;
SHOW BINLOG EVENTS;
SHOW BINARY LOGS;
☉SLAVE:
SHOW SLAVE STATUS;
判断从服务器是否落后于主服务器：
·Seconds_Behind_Master: 0 //如果落后的话需要重启一下从服务器的IO线程
★如何确定主从节点数据是否一致？
通过表的CHECKSUM检查；
使用percona-tools中pt-table-checksum；
★主从数据不一致时的修复方法？
重新复制；
单表导入导出
演示：
1.清理二进制日志（在删除之前最好先备份一下）
[root@master ~]# cd /var/lib/mysql/ [root@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 [root@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 Router 是轻量级 MySQL 中间件，提供应用与任意 MySQL 服务器后端的透明路由。MySQL Router 可以广泛应用在各种用案例中，比如通过高效路由数据库流量提供高可用性和可伸缩的 MySQL 服务器后端。Oracle 官方出品。
MySQL 基于SSL的主从复制
★步骤概览
1.配置Master为CA证书服务器
☉生成私钥文件/etc/pki/CA/private/cakey.pem；
(umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 4096)
☉在/etc/pki/CA目录中，使用私钥文件生成自签证书；
openssl req -new -x509 -key private/cakey.pem -out cacert.pem
☉为CA提供所需的目录和文件；
touch /etc/pki/CA/index.txt
echo 01 > /etc/pki/CA/serial
2.为Master创建证书申请，并由CA服务器签发证书：
☉在master数据库目录中创建一个ssl目录
mkdir /var/lib/mysql/ssl
☉在ssl目录中创建用于master主服务器通信时的私钥；
(umask 077;openssl genrsa -out master.key 2048)
☉在ssl目录中利用生成的私钥进行证书签署请求；
openssl req -new -key master.key -out master.csr -days 3650
☉在ssl目录中由CA服务器（master所在本主机）为master服务器签发证书：
openssl ca -in master.csr -out master.crt -days 3650
3.为slave从服务器创建证书申请，并由CA服务器签发证书
☉在slave数据库目录中创建一个ssl目录
mkdir /var/lib/mysql/ssl
☉在ssl目录中创建用于slave主服务器通信时的私钥；
(umask 077;openssl genrsa -out master.key 2048)
☉在ssl目录中利用生成的私钥进行证书签署请求；
openssl req -new -key master.key -out master.csr -days 3650
☉将证书签署请求远程复制到CA服务器
scp slave.csr root@10.1.252.153:/tmp/
☉由CA服务器（master所在本主机）为slave服务器签发证书，并回传到slave服务器
openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 3650
scp /tmp/slave.crt 10.1.249.103:/var/lib/mysql/ssl/
4.将CA证书分别拷贝到主master和从slave服务器的/var/lib/mysql/ssl中
scp /etc/pki/CA/cacert.pem 10.1.249.103:/var/lib/mysql/ssl/
cp /etc/pki/CA/cacert.pem /var/lib/mysql/ssl
5.修改master主服务器和slave从服务器的证书属主、属组为mysql用户
chown -R /var/lib/mysql/ssl/
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，设置连接主服务器的相关信息，并启动复制
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‘;
MariaDB [(none)]> START SLAVE
MariaDB [(none)]> SHOW SLAVE STATUS\G
实验环境：
两台CentOS 7的虚拟主机，一台做为mysql的主服务器，一台作为从服务器；
两台服务器的MySQL的版本均为：mariadb-server-5.5.44-2.el7.centos.x86_64
具体实验步骤如下：
1.配置Master为CA证书服务器
1）生成私钥文件/etc/pki/CA/private/cakey.pem
[root@master ~]# ls /etc/pki/CA/private/ # 查看私钥文件为空 [root@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) [root@centos7 ~]# ls /etc/pki/CA/private/ cakey.pem [root@centos7 ~]# ll /etc/pki/CA/private/ # 查看文件，并确定其权限仅为属主自己 total 4 -rw------- 1 root root 3247 Sep 28 19:03 cakey.pem
2)生成自签证书
[root@master ~]# cd /etc/pki/CA/ # 生成自签证书指明私钥文件，证书保存路径，有效期限等 [root@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 [root@centos7 ~]# ls /etc/pki/CA cacert.pem # 生成的自签证书 certs crl newcerts private
3）为CA提供所需的目录和文件
[root@master ~]# touch /etc/pki/CA/index.txt # 创建数据库文件 [root@master ~]# echo 01 > /etc/pki/CA/serial # 创建序列号文件并给明第一个证书的序列号码 [root@master ~]# ls /etc/pki/CA/ cacert.pem certs crl index.txt newcerts private serial
2.为Master创建证书申请，并由CA服务器签发证书：
1）创建用于master主服务器通信时的私钥
[root@master ~]# cd /var/lib/mysql/ [root@master mysql]# mkdir ssl # 创建一个ssl目录 [root@master mysql]# ls ssl [root@master mysql]# cd ssl # 在目录中创建私钥 [root@master ssl]# (umask 077;openssl genrsa -out master.key 2048) Generating RSA private key, 2048 bit long modulus .................................+++ ........................................................................................................+++ e is 65537 (0x10001) [root@master ssl]# ll total 4 -rw------- 1 root root 1679 Dec 5 16:18 master.key # 创建完成
2）利用生成的私钥进行证书签署请求
[root@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 []: [root@master ssl]# ls master.csr master.key
3）由CA服务器（master所在本主机）为master服务器签发证书：
[root@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 [root@master ssl]# ls master.crt master.csr master.key
3.为slave从服务器创建证书申请，并由CA服务器签发证书
1）创建用于slave从服务器通信时的私钥
[root@slave ~]# mkdir /var/lib/mysql/ssl [root@slave ~]# cd /var/lib/mysql/ssl [root@slave ssl]# (umask 077;openssl genrsa -out slave.key 2048) Generating RSA private key, 2048 bit long modulus .............+++ ............................+++ e is 65537 (0x10001) [root@slave ssl]# ls slave.key
2）利用生成的私钥进行证书签署请求
[root@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 []: [root@slave ssl]# ls slave.csr # 生成的证书申请 slave.key
3）将slave服务器的证书申请远程复制到CA所在服务器的/tmp目录下：
[root@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服务器签发证书：
[root@master ~]# ls /tmp slave.csr # 传过来的slave从服务器证书申请 # 为从服务器slave签发证书 [root@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 [root@master ~]# ls /tmp slave.crt # 签发的证书 slave.csr
5）在CA服务器，将签发好的slave证书传给slave服务器的/var/lib/mysql/ssl中
[root@master ~]# scp /tmp/slave.crt 10.1.249.103:/var/lib/mysql/ssl/ # 在slave从服务器中查看 [root@slave ssl]# ls slave.crt # 签署的证书 slave.csr slave.key
4.将CA证书拷贝到slave从服务器，并为master也拷贝一份
[root@master ~]# scp /etc/pki/CA/cacert.pem 10.1.249.103:/var/lib/mysql/ssl/ 100% 2114 2.1KB/s 00:00 [root@master ~]# cp /etc/pki/CA/cacert.pem /var/lib/mysql/ssl
5.修改master主服务器和slave从服务器的证书属主、属组为mysql用户
1）Master主节点
[root@master ~]# cd /var/lib/mysql/ssl [root@master ssl]# ls cacert.pem master.crt master.csr master.key [root@master ssl]# chown -R mysql.mysql ./* [root@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从节点
[root@slave ssl]# ls cacert.pem slave.crt slave.csr slave.key [root@slave ssl]# chown -R mysql.mysql ./* [root@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
[root@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
[root@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加密服务是否开启；
[root@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加密可以连接复制的用户，并查看二进制日志的位置
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服务器
[root@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加密服务是否开启
[root@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)在从服务器上设置连接主服务器的相关信息，并启动复制
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
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）在从节点查看其数据库，以及是否有延迟
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加密主从复制的整个过程...
本文出自 “逐梦小涛” 博客，请务必保留此出处http://1992tao.blog.51cto.com/11606804/1879669
MySQL 复制过滤器、监控维护及主从复制的读写分离