环境准备

system version: CentOS Linux release 7.7.1908
mariadb version: 10.5.2
maxscale version: 2.5.3 GA
client: 10.10.110.84
mariadb master: 10.10.110.80
mariadb slave1: 10.10.110.81
mariadb slave2: 10.10.110.82
maxscale proxy: 10.10.110.83

主从复制原理

当 mariadb master 服务器上的数据发生改变时(增、删、改),则将其改变写入 binlog 二进制日志中。slave 服务器会在一定时间间隔内对 master 二进制日志进行探测其是否发生改变,如果发生改变,则开启一个 I/O 线程请求 master 二进制事件,同时主节点为每个 I/O 线程启动一个 dump 线程,用于向其发送二进制事件,并保存至从库本地的中继日志中,从库将启动 SQL 线程从中继日志中读取二进制日志,在本地回放,使得从库数据和主库的数据保持一致,最后 IO 线程和 SQL 线程将进入睡眠状态,等待下一次被唤醒

主从复制的前提条件

master 一定要开启 binlog 二进制日志,并且授予 slave 远程连接的权限

主从复制至少需要两个 mysql 实例,可以分布在不同服务器,也可以在同一台服务器

master 实例和 slave 实例的 mysql 版本最好相同(如果不同,那么 master 实例版本需要低于 slave 实例)

master 实例和 slave 实例之间时间同步

配置 MariaDB Master 节点

# 安装MariaDB
tee /etc/yum.repos.d/Mariadb.repo << EOF
# mariadb 10.5 CentOS repository list - created 2020-09-14 10:57 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name=mariadb
baseurl=https://mirrors.aliyun.com/mariadb/yum/10.5/centos7-amd64/
gpgkey=https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

yum clean all && yum makecache
yum install -y MariaDB-server MariaDB-client

# 配置文件
cat /etc/my.cnf.d/server.cnf
...
[mysqld]
log-bin=mariadb-bin
server_id=180
port=53306
...

# 启动mariadb
systemctl start mariadb

# 添加用户slave授予远程连接的权限,供从节点复制binlog
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.10.110.%' IDENTIFIED BY '123456';

# 查看主库的binlog记录日志信息偏移量position
mariadb [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      529 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

配置 MariaDB Slave 节点

mariadb 10.0.2开始,GTID 是默认打开的。因为 maxscale 故障切换功能需要 mariadb 开启基于 GTID 的主从复制,而且是以 master_use_gtid=current_pos 的方式。因为 master_use_gtid=slave_pos 的方式,master 将从最后一个 GTID 开始给 slave 复制 binlog,通过 @@gtid_slave_pos 这个变量来查看目前最后一个 GTID 的位置,如果A节点 (master) 故障了之后由B节点成为了 master,当 A 节点以 slave 的身份加入到集群时,由于 A 节点之前从未成为过 slave,那么 A 节点的 @@gtid_slave_pos 是空的。为了能让故障的 master 节点故障之后能够重新以 slave 的身份加入到集群,我们需要使用 master_use_gtid=current_pos 的 GTID 方式开启主从复制

# 指定主库信息(master信息会存到/var/lib/mysql/master.info文件)
mariadb [(none)]> change master to master_host='10.10.110.80',
    -> master_user='slave',
    -> master_password='123456',
    -> master_port=53306,
    -> master_use_gtid=current_pos,
    -> master_connect_retry=30;
Query OK, 0 rows affected (0.017 sec)

# 启动slave线程,若要更改指定的主库信息,需先执行stop slave,修改完成后执行start slave
mariadb [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)

# 查看slave状态(slave_IO_Running和slave_SQL_Running都为Yes状态)
mariadb [(none)]> show slave status\G
*************************** 1. row ***************************
                slave_IO_State: Waiting for master to send event
                   master_Host: 10.10.110.80
                   master_User: slave
                   master_Port: 53306
                 Connect_Retry: 30
               master_Log_File: mariadb-bin.000001
           Read_master_Log_Pos: 529
                Relay_Log_File: localhost-relay-bin.000002
                 Relay_Log_Pos: 830
         Relay_master_Log_File: mariadb-bin.000001
              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: 529
               Relay_Log_Space: 1143
               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: 80
                master_SSL_Crl: 
            master_SSL_Crlpath: 
                    Using_Gtid: Current_Pos
                   Gtid_IO_Pos: 0-80-1
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       slave_SQL_Running_State: slave has read all relay log; waiting for more updates
              slave_DDL_Groups: 1
slave_Non_Transactional_Groups: 0
    slave_Transactional_Groups: 0
1 row in set (0.001 sec)

验证主从复制

在 mariadb master 上创建测试数据,然后在 mariadb slave 上查看数据是否已经同步过来

create database mariadb;
use mariadb;
create table mariadb(name varchar(25),city varchar(30),age int);
insert into mariadb.mariadb values("mariadb","china",11);

MariaDB 开启并行复制

mariadb的复制通过三步完成:

1.从库的IO线程去主库上读取binlog日志变更,并把读取的事件按顺序存放到relay log

2.从库的SQL线程一次读取relay log中的一个事件

3.SQL线程依次执行relay log中的事件

mariadb 10之前的版本中,第三步是通过SQL线程来执行的,这意味着一次只能执行一个事件,复制本质上是单线程的。mariadb 10之后的版本中,第三步可以由一个单独的复制工作线程池执行,从而通过并行应用多个事件来提高复制性能

cat /etc/my.cnf.d/server.cnf
...
[mysqld]
slave-parallel-threads=8  # 在工作线程池中创建8个线程
...

查看工作线程的数量

MariaDB [(none)]> SHOW PROCESSLIST;
+----+-------------+-----------+------+--------------+------+--------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db   | Command      | Time | State                                                  | Info             | Progress |
+----+-------------+-----------+------+--------------+------+--------------------------------------------------------+------------------+----------+
|  5 | system user |           | NULL | Slave_IO     |   27 | Waiting for master to send event                       | NULL             |    0.000 |
|  7 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
|  8 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
|  9 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
| 10 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
| 11 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
| 12 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
| 13 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
| 14 | system user |           | NULL | Slave_worker |   27 | Waiting for work from SQL thread                       | NULL             |    0.000 |
|  6 | system user |           | NULL | Slave_SQL    |   27 | Slave has read all relay log; waiting for more updates | NULL             |    0.000 |
| 15 | root        | localhost | NULL | Query        |    0 | starting                                               | SHOW PROCESSLIST |    0.000 |
+----+-------------+-----------+------+--------------+------+--------------------------------------------------------+------------------+----------+
11 rows in set (0.000 sec)

判断主从复制延迟

Seconds_Behind_Master 是通过 sql_thread 执行的事件的时间戳和 io_thread 复制好的事件的时间戳进行比较,而得到的一个差值

NULL:表示 io_thread 或是 sql_thread 有任何一个发生故障,也就是该线程的 Running 状态是 No 而非 Yes

0:该值为零,是我们极为渴望看到的情况,表示主从复制良好,可以认为 lag 不存在

正值:表示主从已经出现延时,数字越大表示从库落后主库越多

负值:几乎很少见,其实这是一个 BUG 值,该参数是不应该出现负值的

仅仅依靠 Seconds_Behind_Master 的值来监测主从同步数据是否延迟是绝对不可靠的,如果网络存在延迟,即使我们看到 Seconds_Behind_Master 的值为零,也会存在很大的偏差。更加严谨的判断主从延时的做法是:

对 master 和 slave 同时发起 SHOW BINARY LOGS 请求,判断二者 binlog 的差异

对 slave 发起 SHOW slave STATUS\G 的请求,查看 Read_Master_Log_Pos 和 Exec_Master_Log_Pos 是否一致

读写分离和故障切换的实现

数据写入操作在主库执行,数据读取操作在从库执行,在一定程度上减轻了数据库的压力,主从复制保证了数据的安全

Maxscale 概述

maxscale 是由 mariadb 团队开发的一个数据库代理工具,将数据库语句转发到一个或多个数据库服务器,转发是使用基于对数据库语句的语义理解和后端数据库集群中服务器角色的规则来执行的,设计目的是为应用程序提供透明的负载平衡和高可用性功能。mariadb maxscale 具有可扩展和灵活的架构,通过插件组件支持不同的协议和路由方法

安装 Maxscale

wget https://downloads.mariadb.com/MaxScale/2.5.3/rhel/7/x86_64/maxscale-2.5.3-2.rhel.7.x86_64.rpm

yum -y install maxscale-2.5.3-2.rhel.7.x86_64.rpm

MariaDB Master 主库上创建相关的账户

在开始配置之前,需要在 mariadb master 中为 maxscale 创建两个用户,用于 maxscale 的监控模块和路由模块

monitor_user:该账号监控集群状态,如果发现某个从服务器复制线程停掉了,那么就不向其转发请求了

# 创建监控用户,用于[MariaDB-Monitor]配置
CREATE USER 'monitor_user'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';
# 如果使用 MariaDB Monitor 的自动故障转移,用户将需要额外的授权
GRANT SUPER, RELOAD ON *.* TO 'monitor_user'@'%';

routing_user:该账号将不同的请求分发到不同的节点上,当客户端连接到 maxscale 这个节点上时,maxscale 节点会使用该账号去查后端数据库,检查客户端登陆的用户是否有权限或密码是否正确等等

# 创建routing user,用于[Read-Write-Service]配置
CREATE USER 'routing_user'@'%' IDENTIFIED BY '123456';
GRANT SELECT ON mysql.user TO 'routing_user'@'%';
GRANT SELECT ON mysql.db TO 'routing_user'@'%';
GRANT SELECT ON mysql.tables_priv TO 'routing_user'@'%';
GRANT SELECT ON mysql.columns_priv TO 'routing_user'@'%';
GRANT SELECT ON mysql.proxies_priv TO 'routing_user'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'routing_user'@'%';
GRANT SHOW DATABASES ON *.* TO 'routing_user'@'%';

配置加密密码

我们创建的数据库用户信息需要填写到 maxscale 配置文件中,为了防止配置文件出现明文密码,我们可以使用秘钥为密码加密,然后将加密后的字符串填写在 maxscale 配置文件中

# 生成秘钥,密钥将保存到/var/lib/maxscale/.secrets
maxkeys    
# 基于秘钥生成123456加密后的字符串(记录下来)
maxpasswd /var/lib/maxscale/ 123456

Maxscale 配置文件

[root@localhost ~]# grep -v "^#" /etc/maxscale.cnf
[maxscale]
threads=auto
admin_host=0.0.0.0
admin_port=8080
admin_secure_gui=false    # 关闭GUI安全验证,不然需要配置ssl

[server1]    # 不需要指定哪个是master和slave,maxscale会自动识别
type=server
address=10.10.110.80
port=53306
protocol=MariaDBBackend

[server2]
type=server
address=10.10.110.81
port=53306
protocol=MariaDBBackend

[server3]
type=server
address=10.10.110.82
port=53306
protocol=MariaDBBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=monitor_user
password=EA25B20FBB2B3EF4562F9D585DE8826B64B328C08571D8F656424252F9560A62
monitor_interval=2000

[Read-Write-Service]
type=service
router=readwritesplit    # 配置读写分离的路由
servers=server1,server2,server3
user=routing_user
password=EA25B20FBB2B3EF4562F9D585DE8826B64B328C08571D8F656424252F9560A62

[Read-Write-Listener]
type=listener
service=Read-Write-Service    # 监听读写分离的服务
protocol=MariaDBClient
port=4006    # maxscale代理的端口

启动 Maxscale 服务

systemctl start maxscale.service

Maxctrl 管理工具的使用

maxctrl 如果不指定 COMMAND 将会进入交互式模式,在交互式中可以直接输入 COMMAND,以表格格式显示基础信息

# 显示所有后端服务器
maxctrl -h 10.10.110.83:8080 list servers
┌─────────┬──────────────┬───────┬─────────────┬─────────────────┬─────────┐
│ Server  │ Address      │ Port  │ Connections │ State           │ GTID    │
├─────────┼──────────────┼───────┼─────────────┼─────────────────┼─────────┤
│ server1 │ 10.10.110.80 │ 53306 │ 0           │ Master, Running │ 0-80-15 │
├─────────┼──────────────┼───────┼─────────────┼─────────────────┼─────────┤
│ server2 │ 10.10.110.81 │ 53306 │ 0           │ Slave, Running  │ 0-80-15 │
├─────────┼──────────────┼───────┼─────────────┼─────────────────┼─────────┤
│ server3 │ 10.10.110.82 │ 53306 │ 0           │ Slave, Running  │ 0-80-15 │
└─────────┴──────────────┴───────┴─────────────┴─────────────────┴─────────┘

list services # 显示所有服务,例如读写分离服务等
list monitors # 显示所有监控信息
list listeners # 显示监听信息
# 更多的命令可以去官网自行了解...

登录 Maxscale 仪表板

默认情况下用户名是 admin,密码是 mariadb

仪表板界面可以看到以下资源的概览信息:按 monitor 分组的所有服务器,当前会话和所有服务。这些资源的信息每10秒更新一次。监视器、服务器和服务资源都有自己的详细信息页面。可以通过单击仪表板页面上的资源名称来访问它,在详细信息页中,可以修改部分资源参数的值

Maxscale 测试读写分离

验证读写分离的 “读” 操作是否在从库上

# 分别在两个从库上创建数据
create database slave;
use slave;
create table info(name varchar(25),ip int);
insert into slave.info values("slave",inet_aton('10.10.110.81'));

create database slave;
use slave;
create table info(name varchar(25),ip int);
insert into slave.info values("slave",inet_aton('10.10.110.82'));

# 在主库上创建测试用户
grant all on *.* to 'check'@'%' identified by '123456';

# 连接maxscale查询数据验证读写分离(读操作自动负载均衡)
mysql -ucheck -p123456 -P4006 -h 10.10.110.83

MariaDB [(none)]> select name,inet_ntoa(ip) from slave.info;
+-------+---------------+
| name  | inet_ntoa(ip) |
+-------+---------------+
| slave | 10.10.110.82  |
+-------+---------------+
1 row in set (0.002 sec)

MariaDB [(none)]> select name,inet_ntoa(ip) from slave.info;
+-------+---------------+
| name  | inet_ntoa(ip) |
+-------+---------------+
| slave | 10.10.110.81  |
+-------+---------------+
1 row in set (0.001 sec)

验证读写分离的 “写” 操作是否在主库上

# 连接maxscale往里写数据,看slave上数据有没有同步过来
create database test;
use test;
create table test(name varchar(25),city varchar(30),age int);
insert into test.test values("mariadb","china",11);

Maxscale 配置故障切换

编辑 maxscale 的配置文件,配置故障切换参数

[root@localhost ~]# grep -v "^#" /etc/maxscale.cnf

[maxscale]
threads=auto
admin_host=0.0.0.0
admin_port=8080
admin_secure_gui=false

[server1]
type=server
address=10.10.110.80
port=53306
protocol=MariaDBBackend

[server2]
type=server
address=10.10.110.81
port=53306
protocol=MariaDBBackend

[server3]
type=server
address=10.10.110.82
port=53306
protocol=MariaDBBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=monitor_user
password=EA25B20FBB2B3EF4562F9D585DE8826B64B328C08571D8F656424252F9560A62
monitor_interval=2000

replication_user=slave    # 复制用户
replication_password=EA25B20FBB2B3EF4562F9D585DE8826B64B328C08571D8F656424252F9560A62
auto_failover=true    # 开启自动故障转移
auto_rejoin=true    # 自动重新连接
failcount=3    # 故障次数
failover_timeout=90    # 故障转移超时
switchover_timeout=90    # 故障切换超时
verify_master_failure=true    # 自动故障转移启用额外的主故障验证
master_failure_timeout=10    # 

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=routing_user
password=EA25B20FBB2B3EF4562F9D585DE8826B64B328C08571D8F656424252F9560A62

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

手动关闭 master 节点,使用 maxctrl 查看 master 状态,可以看到 master 节点会自动切换。当 master节点修复之后会自动重新加入集群

[root@localhost ~]# maxctrl -h 10.10.110.83:8080 list servers
┌─────────┬──────────────┬───────┬─────────────┬────────────────┬─────────┐
│ Server  │ Address      │ Port  │ Connections │ State          │ GTID    │
├─────────┼──────────────┼───────┼─────────────┼────────────────┼─────────┤
│ server1 │ 10.10.110.80 │ 53306 │ 0           │ Down           │ 0-80-26 │
├─────────┼──────────────┼───────┼─────────────┼────────────────┼─────────┤
│ server2 │ 10.10.110.81 │ 53306 │ 0           │ Slave, Running │ 0-81-27 │
├─────────┼──────────────┼───────┼─────────────┼────────────────┼─────────┤
│ server3 │ 10.10.110.82 │ 53306 │ 0           │ Slave, Running │ 0-82-27 │
└─────────┴──────────────┴───────┴─────────────┴────────────────┴─────────┘
[root@localhost ~]# maxctrl -h 10.10.110.83:8080 list servers
┌─────────┬──────────────┬───────┬─────────────┬─────────────────┬─────────┐
│ Server  │ Address      │ Port  │ Connections │ State           │ GTID    │
├─────────┼──────────────┼───────┼─────────────┼─────────────────┼─────────┤
│ server1 │ 10.10.110.80 │ 53306 │ 0           │ Down            │ 0-80-26 │
├─────────┼──────────────┼───────┼─────────────┼─────────────────┼─────────┤
│ server2 │ 10.10.110.81 │ 53306 │ 0           │ Master, Running │ 0-81-27 │
├─────────┼──────────────┼───────┼─────────────┼─────────────────┼─────────┤
│ server3 │ 10.10.110.82 │ 53306 │ 0           │ Slave, Running  │ 0-82-27 │
└─────────┴──────────────┴───────┴─────────────┴─────────────────┴─────────┘