怪兽和他的朋友们

Monster

0%

初识 MariaDB 的主从复制

发表于 分类于
本文字数： 13k 阅读时长 ≈ 12 分钟

关于 MariaDB 系列其他文章的传送门

  1. 《 浅谈 MariaDB 的存储引擎和日志 》

  2. 《 初识 MariaDB 的主从复制 》　　　　　　您当前所在位置

  3. 《 未雨绸缪，快速学习 MariaDB 的备份与恢复，包含 InnoDB 优化参数 》

logo

一、MariaDB 的可用架构

MariaDB 中可用的 主/从架构 有：

  • 异步复制；
  • 半同步复制；
  • 一主多从；
  • 一从一主；
  • 级联复制；
  • 循环复制；
  • 双主复制；
  • 一从多主：需要每个主服务器提供不同的数据库给从服务器；

本文记录常见的三种：

  1. 一从一主，包括读写分离器；
  2. 双主复制；
  3. 半同步复制。

二、主从复制模型

master_slave

首先确保以下问题：

  • 所有节点已经做好时间同步；
  • 确认关闭了 SELinux 和 Firewalld；
  • 主从的版本号尽量一致，如不一致，从的版本号应高于主的版本号。

注意从节点复制的开始位置是否从 0 字节开始。
如果是通过备份数据创建的从节点，启动复制线程时，注意备份的起始点、所处的日志文件及其事件位置，防止数据重复；

配置主服务器

1、编辑配置文件 /etc/my.cnf。

1
2
3
4
5
[mysqld]
...
server_id=#          # Server ID 不可重复
log_bin=/PATH_FILE   # 开启并定义二进制文件位置，不要加 log 后缀
skip_name_resolve=ON # 跳过主机名解析

2、启动服务

1
systemctl start mariadb
  • 检查二进制文件的配置结果。
1
SHOW BINARY LOGS;

3、授权一个同步账号，给从服务器使用，应注意授权。

1
2
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
mysql> FLUSH PRIVILEGES;

配置从服务器

1、编辑配置文件 /etc/my.cnf。

1
2
3
4
5
6
[mysqld]
...
server_id=#          # Server ID 不可重复
relay_log=relay-log  # 开启并定义中继日志位置，不要加 log 后缀
read_only=ON         # 设置从服务器只能读，不能写
skip_name_resolve=ON # 跳过主机名解析

2、启动服务

1
systemctl start mariadb

3、配置连接至主服务器。

  • CHANGE MASTER TO：此语句用来定义与主节点的连接信息，可使用 help CHANGE MASTER TO 查看帮助信息。
1
2
CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];   # 启动从服务，可以指定只启 IO 或 SQL 线程，不指定默认都启：

4、查看 I/O 和 SQL 线程是否启动。

1
2
3
4
5
6
7
mysql> SHOW SLAVE STATUS\G
...
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
...

5、如果想关闭并删除 SLAVE 信息，则使用：

1
2
3
STOP SLAVE;          # 先停止 SLAVE 线程
reset slave;         # 其实是把 master.info 和 relay-log.info 文件给删除
reset slave all;     # 用这个，让其清除的彻彻底底

主从复制模型中需要注意的一些配置参数

1. 从库要设定为“只读”

在从库要启动 read_only 防止写操作，但仅对非 SUPER 权限的用户有效；
如果要阻止所有用户，需要在从服务器上加读锁：

1
mysql> FLUSH TABLES WITH READ LOCK;

2. 尽量确保复制时的事务安全

在 MASTER 节点启用参数：

  • sync_binlog=N：当每进行 N 次事务提交之后，MariaDB 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。默认的设置是 0，这时候的性能是最好的，但是风险也是最大的。设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。所以很多 MySQL DBA 设置的并不是使用最安全的 1，而是 100 或者是 0，这样牺牲一定的一致性，可以获得更高的并发和性能。

如果用到的是 InnoDB 存储引擎：

  • innodb_flush_log_at_trx_commit=ON：当有事务提交，要立刻把事务日志从内存缓冲区同步到磁盘的日志文件中。
  • innodb_support_xa=ON：启用 InnoDB 的分布式事务。

3. 关闭复制线程自启动

在从服务器遭遇意外宕机后，尽量避免复制线程自动启动，因为宕机后可能会出现事务回滚，导致和主节点数据不一致。

4. 从节点设置参数

  • sync_master_info=ON：重放的进度会被记录在 /var/lib/mysql/master.info 中，如果宕机了就从这个文件中读取进度继续重放。但是这个文件也有缓存，宕机时如果缓存中记录的进度没有同步到磁盘的话，有可能会导致语句的重复执行，此参数定义了如果有事务提交，就立刻同步到磁盘上，以避免有的事务重复重放。

  • sync_relay_log_info=ON 每次接收到 MASTER 发送过来的 binlog 日志都要写入系统缓冲区，然后刷入 relay-log.info 里。

5. 当从节点同步错误时，跳过错误的 SQL

1、跳过指定数量的事务:

1
2
3
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1   #跳过一个事务
start slave;

2、修改 mysql 的配置文件，通过 slave_skip_errors 参数来跳所有错误或指定类型的错误

1
2
3
4
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1053,1146     #跳过指定 error no 类型的错误
slave-skip-errors=all                #跳过所有错误

主从复制模型有数据不一致的缺陷

这种主从模型面临以下几个问题：

  • 主节点是单点；
  • 如果客户端在从节点中修改了数据，并不会同步至主节点，导致主从节点的数据可能不一致；
  • 从节点的数据可能因为各种原因落后于主节点，比如主节点二进制日志的记录顺序和实际顺序不一致，或者主节点过于繁忙等等。

而另一个 MySQL 分支的发行版 Percona ，解决了这些问题，称为 Percona Cluster。一般最少使用三个节点，通过第三方协议，把每一个节点数据的变化，在物理层同步给集群内其他的所有节点，使得数据同步不依赖 MariaDB 自身，这样每一个节点都能够读写，后来这种方式被 MariaDB 录用，工作逻辑基本一致，称为 MariaDB Cluster，不过目前还少有人在生产环境中使用，而且并不适用于太大规模的集群。部署时需要使用官网提供的 MariaDB Cluster 的专用版，不能使用 Base 仓库中的版本。

还可以让主节点停下等从节点，但是这样集群会只能读不能写，而且操作比较复杂，容易出错，还有别的解决方案是 MMM/MHA。

三、多主复制模型

多主模型其实就是使用两个主节点，分别都开启二进制日志和中继日志，各自的 ServerID 不同，前端使用 Nginx、HAProxy 等负载均衡器通过 RR 调度。

为了防止事务被分散至不同的节点，应使用基于 IP_hash 的会话绑定，不要求负载平均，只确保两个主节点的数据一致，且能够同时使用，宕机一台集群服务不中断。

多主模型的配置方式很简单，在主从模式的基础上，进行如下操作：

在“从节点”的操作

在已经是 从节点 基础上配置 主节点，其实就是执行一遍“配置主节点”。

1、编辑从节点的配置文件，启用二进制日志。

1
2
3
4
5
6
7
[mysqld]
...
server_id=#
log_bin=/PATH_FILE   # 此行新增，开启并定义二进制文件位置，不要加 log 后缀
relay_log=relay-log
read_only=ON ××××××  # 只读，一定要删除此行，这里为了示例写了出来，实际是没有的
skip_name_resolve=ON

2、从节点也授权一个同步账号，并刷新权限；

注意：如果两节点的同步账号信息一致，那么 START SLAVE 的操作一定要在授权同步账号之后进行，否了授权账号的 SQL 语句也会被同步，和本地已有的账号造成冲突，而如果两节点的同步账号不一致则不用。

1
2
3
 #- 注意变量，要替换为实际的信息
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
mysql> FLUSH PRIVILEGES;

3、重启服务。

1
systemctl restart mariadb
  • 检查二进制文件的配置结果。
1
SHOW BINARY LOGS;
  • 查看 MASTER 的配置信息。
1
SHOW MASTER STATUS；

在“主节点”的操作

在已经是 主节点 基础上配置 从节点，其实就是执行一遍“配置从节点”。

1、在主节点的 /etc/my.cnf 中添加一行中继日志的配置，其他不变。

1
2
3
...
relay_log=/PATH   # 启用中继日志
...

2、重启服务。

1
systemctl restart mariadb

3、配置连接至从节点的信息。

  • 需要注意的是：
    • 在 CHANGE MASTER TO 时，使用刚刚在从节点授权的账号；
    • 在 CHANGE MASTER TO 时，应注意从二进制日志的哪个位置开始复制，使用 MASTER_LOG_POS=# 定义起始位置；
    • 关于启动 I/O 线程和 SQL 线程，如果只启动 I/O 线程，那么复制的数据只会保存在中继日志中，而不写入 SQL，导致两台数据不一致。可以在 /var/log/mariadb.log 查看到日志。
1
2
CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];   # 启动从服务，可以指定只启 IO 或 SQL 线程，不指定默认都启：

4、查看 I/O 和 SQL 线程是否启动。

1
mysql> SHOW SLAVE STATUS\G

现在两个都是主节点了。

测试效果

在节点 1 上创建一个库，看节点 2 是否同步过来了。

1
2
3
4
5
CREATE DATABASE mydb1;
use mydb1;
CREATE TABLE tbl1 (id INT UNSIGNED,name CHAR(30));
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G

在节点 2 上创建一个库，看节点 1 是否同步过来了。

1
2
3
4
5
CREATE DATABASE mydb2;
use mydb2;
CREATE TABLE tbl1 (id INT UNSIGNED,name CHAR(30));
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G

关于自动增长的字段

如果有字段是自动增长的，且是主键时，当两节点同时插入数据时，会因为冲突导致无法同步，有两种解决方法：

1. 定义不同的增长跨度

定义两个节点的自动增长跨度不一样，使得一个节点只用奇数，一个节点只用偶数。

  • 定义一个节点只使用奇数 ID。
    • auto_increment_offset=1：定义基数；
    • auto_increment_increment=2：定义一次增长的跨度；
  • 定义另一个节点只使用偶数 ID
    • auto_increment_offset=2
    • auto_increment_increment=2

但是这样有一个小缺点，如果其中一个节点已经使用了 1 和 3，那么另一个节点则会使用当前最大序号的下一个继续使用 4 和 6，而不是 2 和 4，导致序号可能会出现间隙。

2. 使用序号服务中心

另一个方法，不会出现序号间隙，但是需要有一个额外的 ID 生成服务中心来提供序号服务，当插入数据时先向中心服务器请求一个序号来使用，可避免冲突。序号中心服务器需要程序设计时就实现，且也要考虑单点问题。

四、主从复制的读写分离器

在一主一从或一主多从的环境中，读写分离器可以使集群对用户表现为一个整体，而不用管哪个节点可以读，哪个节点可以写。读写分离器其实就是一个 SQL ROUTE，能够识别 SQL 语句的含义，并调度发给后端节点。

但是读写分离器本身是个单点，不过好在读写分离器是无状态的，可以使用 Keepalived 等建立高可用。

而且需要注意的是，MASTER 节点也是个单点，如果宕机了集群也无法使用，有两个解决办法：

  • 如果读写分离器支持的话，可以自动在主节点宕机时提升一个从为主；
  • 使用其他的组件在主节点宕机时提升一个从为主，但是读写分离器无法感知，所以在读写分离器中定义的主节点 IP 也需要更换，不然还是无法工作。不过一般情况下读写分离器都支持使用一个 web 接口在线修改。

双主或多主模型是无须实现读写分离，仅需要负载均衡：haproxy, nginx, lvs, ...

目前实现了读写分离的工具有很多：

  • amoeba for MySQL：阿里的读写分离、分片；
  • mysql-proxy：一直是 Bate 版，很多坑，后来 360 公司进行了二次研发并填了很多坑，而且开源了并一直在维护，叫 atlas，既然 360 都可以用，那么你也可以用于国内的中小型公司。
  • Mycat：国内的著名的创业公司，但是官方没有说对 MairaDB 的支持情况。
  • OneProxy：被 MairaDB 收购，配置比较繁琐，使用者不多。
  • ProxySQL：是 DBA 运维人员开发的，易用，易配置，号称最懂 DBA 的需求。

配置 ProxySQL

既然 ProxySQL 号称是由 DBA 运维人员开发的，而且最懂 DBA 的痛点，那么主要介绍 ProxySQL 的配置方法，非常非常简单。

从 GitHub 项目地址 下载并安装，我这里目前 verson 是 2.0.6。

1
yum install ./proxysql-2.0.6-1-centos7.x86_64.rpm

配置示例：

vim /etc/proxysql.cnf

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
datadir="/var/lib/proxysql"     # proxysql 的数据目录
admin_variables=   ######## 关于管理信息的变量
{
    admin_credentials="admin:admin"                         # 登录管理接口的账号和密码
    mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"  # 定义管理接口监听的地址和端口
}
mysql_variables=   ######## 配置作为 MariaDB 代理的一些特性，可以理解为前端
{
    threads=4                   # 并发线程数
    max_connections=2048        # 最大并发连接
    default_query_delay=0       # 默认的查询延迟时间
    default_query_timeout=36000000   # 默认的查询超时时长
    have_compress=true          # 数据传输时要不要压缩
    poll_timeout=2000           # 轮询时后端的超出时长，如超时则换另一节点
    interfaces="0.0.0.0:3306;/tmp/mysql.sock"   # 作为 MariaDB 代理时的业务监听端口
    default_schema="information_schema" # 用户连接时默认 use 哪个数据库
    stacksize=1048576           # 定义栈大小
    server_version="5.5.30"
    monitor_username="monitor"  # 向后端 MariaDB 发起监控请求时的账号和密码，创建一个权限小的账号
    monitor_password="monitor"
    connect_timeout_server=3000 # 连接后端服务器的超时时长
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server=120000  # ping 后端的间隔时长
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}
mysql_servers =    ######## 定义后端 Server 组
(
    {
        address = "172.18.0.67"
        port = 3306
        hostgroup = 0             # 主库所属的组编号
        status = "ONLINE"
        weight = 1
        compression = 0           # 是否允许压缩
        max_replication_lag = 10  # 从节点的复制延迟超过此阈值，则不使用该从服务器，默认 0
    },
    {
        address = "172.18.0.68"
        port = 3306
        hostgroup = 1             # 从库所属的组编号
        status = "ONLINE"
        weight = 1
        compression = 0
    },
    {
        address = "172.18.0.69"
        port = 3306
        hostgroup = 1             # 从库所属的组编号
        status = "ONLINE"
        weight = 1
        compression = 0
    }
)
mysql_users:       ######## 配置 MariaDB 的认证信息
(
    {
        username = "root"
        password = "monster"
        default_hostgroup = 0   # 默认的组
        max_connections=1000    # 最大连接数
        default_schema="mydb"   # 默认 use 的数据库
        active = 1
    }
)
mysql_query_rules: ########定义查询规则
(
    {
            rule_id=1
            active=1
            match_pattern="^SELECT .* FOR UPDATE$"   # 虽然是 SELECT 语句，但是是请求施加写锁的，这种独特的语句
            destination_hostgroup=0                  # 发给 0 组而不是 1 组
            apply=1
    },
    {
            rule_id=2
            active=1
            match_pattern="^SELECT"                  # SELECT 查询的语句发给 1 组
            destination_hostgroup=1
            apply=1
    }
)
    scheduler=
(
)
mysql_replication_hostgroups=   ######## 定义读写分离的配置
(
    {
        writer_hostgroup=0　　　＃ 定义负责写的组 ID
        reader_hostgroup=1　　　＃ 定义负责读的组 ID
        comment="test repl 1"   # 定义一个 tag

    }
)

在主库中添加 ProxySQL 的监控账户，如果从库会自动同步过去，就不用单独在从库创建了；注意生产环境中不应给予全部权限；

1
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' IDENTIFIED BY 'monitor' WITH GRANT OPTION;

然后启动服务即可

1
systemctl start proxysql

五、半同步复制模型

1. 关于异步复制、全同步复制与半同步复制

异步复制（Asynchronous replication）

  • 逻辑上：MySQL 默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果 crash 掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

  • 技术上：主库将事务 Binlog 事件写入到 Binlog 文件中，此时主库只会通知一下 Dump 线程发送这些新的 Binlog，然后主库就会继续处理提交操作，而此时不会保证这些 Binlog 传到任何一个从库节点上。

全同步复制（Fully synchronous replication）

  • 逻辑上：指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
  • 技术上：当主库提交事务之后，所有的从库节点必须收到。APPLY 并且提交这些事务，然后主库线程才能继续做后续操作。但缺点是，主库完成一个事务的时间会被拉长，性能降低。

半同步复制（Semisynchronous replication）

  • 逻辑上：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间。
  • 技术上：介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个 TCP/IP 往返的时间。所以，半同步复制最好在低延时的网络中使用。

总结

MariaDB 的主从模式默认是 异步复制 的，而 MariaDB Cluster 是 同步复制 的，只要设置为相应的模式即是在使用相应的同步策略。

从 MariaDB 5.5 开始，就支持以插件的形式支持 半同步复制。其实说明半同步复制是更好的方式，兼顾了同步和性能的问题。

2. 配置半同步模型

而这里所谓的半同步复制模型指的是：主库和从库 A 之间是同步复制的，而剩余的从库 B、C、D 等与从库 A 之间为半同步复制的。

  • 依然还是在主从节点的基础上继续操作；
  • 半同步复制需要启用插件来实现。

加载半同步复制插件

SHOW PLUGINS：可以查看已启用插件。

半同步插件本身自带，但是默认没有启用。可以在 /usr/lib64/mysql/plugins/ 下各种插件文件。

启用插件，注意主和从的插件名不一样：

  • 主节点插件的文件名为 semisync_master.so，插件名为 rpl_semi_sync_master。
  • 从节点插件的文件名为 semisync_slave.so，插件名为 rpl_semi_sync_slave。

主库启用插件：

1
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从库启用插件：

1
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

使用 SHOW PLUGINS 查看插件是否加载。

启用半同步复制插件

  • 虽然加载了，但是可能还没启用，使用 SHOW GLOBAL VARIABLES LIKE 'rpl%'; 查看是否启用，OFF 表示还未启用。
  • rpl_semi_sync_master_timeout 10000 表示等待从节点同步数据的超时时长，默认 10 秒，超出后降级为异步。
1
2
3
4
5
6
7
8
9
10
11
 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| 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    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

在主节点启用半同步。

1
SET GLOBAL rpl_semi_sync_master_enabled=ON;

查看计数器：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 1           | # 表示有几个节点是作为半同步复制的节点
| Rpl_semi_sync_master_net_avg_wait_time     | 0           | # 每次网络的平均等待时长
| Rpl_semi_sync_master_net_wait_time         | 0           | # 总的等待时长
| Rpl_semi_sync_master_net_waits             | 0           | # 等了多少次
| Rpl_semi_sync_master_no_times              | 0           |
| Rpl_semi_sync_master_no_tx                 | 0           |
| Rpl_semi_sync_master_status                | OFF         |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0           | # tx 开头是关于事务的计数器
| Rpl_semi_sync_master_tx_wait_time          | 0           |
| Rpl_semi_sync_master_tx_waits              | 0           |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 0           |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.01 sec)

六、复制过滤器

复制过滤器的作用是实现仅复制有限一个或几个数据库相关的数据，而非所有，有两种实现思路：

1. 在“主库”配置

实现方法：主库仅向二进制日志中记录有关特定数据库相关的写操作；

问题：其它库的 time-point recovery 将无从实现；

配置方法：以下方式只用一种，且只能过滤至库级别。

  • binlog_do_db=DB_NAME：白名单；
  • binlog_ignore_db=DB_NAME：黑名单；

2. 在“从库”配置

实现方法：从服务器的 SQL THREAD 仅重放关注的数据库或表相关的事件，并将其应用于本地；

问题：不需要的数据也会同步至从库的 relay 日志，浪费网络 IO 和磁盘 IO；

配置方法：支持能过滤至表级别。

  • 指定库级别的过滤：
    • replicate_do_db=DB_NAME：白名单；
    • replicate_ignore_db=DB_NAME：黑名单；
  • 指定表级别的过滤：
    • replicate_do_table=TABLE_NAME：表白名单；
    • replicate_ignore_table=TABLE_NAME：表黑名单；
  • 支持通配符的表级过滤：
    • replicate_wild_do_table=REG_TABLE_NAME：表通配符；
    • replicate_wild_ignore_table=REG_TABLE_NAME：表通配符。

示例：

1
SET GLOBAL replicate_do_db=mydb;

七、复制的监控和维护

(1) 如果服务器的二进制日志爆满，使用 PURGE 清理二进制日志。

注意：千万不要直接在硬盘级使用 rm 删除，应使用 PURGE，而且如果不是买不起硬盘，建议保留二进制日志。

语法：PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };

示例：删除 000003 之前的日志，不包括 000003。

1
PURGE BINARY LOGS TO 'bin_log.000003';`

(2) 查看复制监控。

  • MASTER:
    • SHOW MASTER STATUS;
    • SHOW BINLOG EVENTS;
    • SHOW BINARY LOGS;
  • SLAVE:
    • SHOW SLAVE STATUS;：其中 Seconds_Behind_Master: 0 状态可判断从服务器落后于主服务器多久，有时莫名其妙的落后，可以通过重启 I/O 线程来追上。

(3) 如何确定主从节点数据是否一致。

通过检查表 CHECKSUM 是否一致；使用 percona-tools 中的 pt-table-checksum 工具可以计算表的校验和。

(4) 主从数据不一致时的修复方法。

使用 percona-tools 中的 pt-table-sync 工具重新复制；能够自动发现两个实例间不一致的数据，然后进行 sync 操作，但是 pt-table-sync 无法同步表结构，和索引等对象，只能同步数据。

percona-tools 下载地址

使用 yum install ./NAME.rpm 安装。

八、innoDB 存储引擎的相关优化参数

建议使用的一些优化参数：

  • innodb_buffer_pool_size：定义整个数据库的索引、数据、插入数据时的缓冲区，默认为 128MB；当服务器是 Mysql 专用时时，应该尽量调大，可以使用 70-80%。
  • innodb_buffer_pool_instances：定义缓冲区的分片数量；当缓存区比较大时，进行分片管理，可以减少内存碎片。
  • innodb_flush_log_at_trx_commit：每当有事务提交时，定义如何将缓冲区的事务日志同步至磁盘中，以保证事务的安全性。建议同时关闭单语句事务 autocommit，而后将此值设置为 1 或 2。
    • 0：每一秒，将 log buffer（内存）同步一次至 log file（事务日志）；且同时将 log file 同步到 data file（数据文件）；
    • 1：每次提交时，将 log buffer 同步至 log file，且同时将 log file 同步到 data file；偏安全性高；
    • 2：每次提交时，将 log buffer 同步至 log file，但不会将 log file 同步到 data file；偏性能好；
  • innodb_file_per_table=ON：每表使用单独的表空间；
  • innodb_read_io_threads, innodb_write_io_threads：文件读写的 I/O 线程数；可根据并发量和 CPU 核心数适当调整；
  • innodb_open_files：InnoDB 可打开的文件数量上限；
  • innodb_flush_method：定义用户提交时数据的刷写方法；
  • innodb_thread_concurrency：InnoDB 自己的工作线程，建议等于自己的 CPU 核心数；
  • skip_name_resolve：跳过主机名解析；
  • max_connections：MariaDB 最大连接数，可适量调大；

参考 https://www.cnblogs.com/xiaocen/