![maria db galera 节点上的不同 bin 日志文件索引](https://linux22.com/image/777960/maria%20db%20galera%20%E8%8A%82%E7%82%B9%E4%B8%8A%E7%9A%84%E4%B8%8D%E5%90%8C%20bin%20%E6%97%A5%E5%BF%97%E6%96%87%E4%BB%B6%E7%B4%A2%E5%BC%95.png)
设置:
Galera 集群有 3 个节点,在 Docker 容器中运行。每个节点前面都有一个 maxscale 代理。远程是一个使用 bin-log 复制运行的复制从属服务器,其中集群充当主服务器(在 maxscale 代理后面)。集群中的每个节点都启用了 bin-log 并且log_slave_updates=ON
。从属服务器上未配置 GTID。
问题
不知何故,DB2(见下文)有另一个 bin-log 文件编号,.000016
而不是.000015
其他两个服务器的编号。有趣的是,它们具有相同的位置,因此我假设它们具有相同的内容,因为它们具有相同的创建时间和完全相同的文件大小(尽管 md5sum 显示不同的校验和)。
在某些时候,从属服务器与以下日志不同步:
2022-06-08 0:27:20 9 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'data-master-bin.000015' at position 3374796
2022-06-08 0:28:20 9 [ERROR] Slave I/O: error reconnecting to master '[email protected]:some_port' - retry-time: 60 maximum-retries: 86400 message: Can't connect to MySQL server on 'url.to.server' (110 "Connection timed out"), Internal MariaDB error code: 2003
2022-06-08 0:29:20 9 [Note] Slave: connected to master '[email protected]:some_port',replication resumed in log 'data-master-bin.000015' at position 3374796
2022-06-08 0:29:20 9 [ERROR] Error reading packet from server: binlog truncated in the middle of event; consider out of disk space on master; the first event 'data-master-bin.000015' at 3374796, the last event read from 'data-master-bin.000015' at 3374796, the last byte read from 'data-master-bin.000015' at 3374815. (server_errno=1236)
2022-06-08 0:29:20 9 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'data-master-bin.000015' at 3374796, the last event read from 'data-master-bin.000015' at 3374796, the last byte read from 'data-master-bin.000015' at 3374815.', Internal MariaDB error code: 1236
2022-06-08 0:29:20 9 [Note] Slave I/O thread exiting, read up to log 'data-master-bin.000015', position 3374796
我无法确切地说出在某个时刻是否没有连接。我的假设是,maxscale 代理通常会将所有传入请求重定向到第一个服务器,因为数据库上的负载并不大。然而在这个特定的时间,请求被重定向到 db-02,这导致了错误,因为那里的 data-master-bin.000015 与其他两台服务器不同。我能够START SLAVE
在从属服务器上运行,复制现在已启动并再次运行,但我担心一旦 maxscale 再次重定向到 db-02,它就会中断。
我的问题是:
- 什么原因导致 bin-log 文件索引增加?
- 我怎样才能使 3 个集群节点上的 bin-log 文件重新同步?
配置:
每个节点上的 db-server.cnf:
[mysqld]
log_bin
server_id=1
log_slave_updates=ON
log-basename=data-master
#Configure GTID
wsrep_gtid_mode=ON
wsrep_gtid_domain_id=1
每台服务器上的代理 .cnf
[Read-Only-Service]
type=service
router=readconnroute
servers=db-01, db-02, db-03
user=maxscale
password=******
router_options=synced
[Replication-Listener]
type=listener
service=Read-Only-Service
protocol=MariaDBClient
port=/*some-port*/
ssl=required
ssl_cert=/path/to/server-cert.pem
ssl_key=/path/to/server-key.pem
ssl_ca_cert=/path/to/ca-cert.pem
DB1
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| data-master-bin.000015 | 3534812 | | |
+------------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
ls -alc --fu /var/lib/mysql
...
413 2021-12-02 10:06:08.187023308 +0000 data-master-bin.000009
387 2021-12-02 10:09:23.020291420 +0000 data-master-bin.000010
8883991 2022-03-02 16:53:11.689571461 +0000 data-master-bin.000011
1260112 2022-03-10 13:10:08.730056029 +0000 data-master-bin.000012
466 2022-03-10 13:10:29.106313267 +0000 data-master-bin.000013
5967037 2022-04-27 16:41:46.021414396 +0000 data-master-bin.000014
3672324 2022-06-13 22:03:54.919102767 +0000 data-master-bin.000015
175 2022-04-27 16:42:46.150317967 +0000 data-master-bin.index
...
DB2
MariaDB [(none)]> show master status;
+------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| data-master-bin.000016 | 3534812 | | |
+------------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
ls -alc --fu /var/lib/mysql
...
413 2022-03-10 13:10:34.036661850 +0000 data-master-bin.000014
5966988 2022-04-27 16:44:37.871084681 +0000 data-master-bin.000015
3672324 2022-06-13 22:03:54.935182267 +0000 data-master-bin.000016
75 2022-04-27 16:45:21.228903025 +0000 data-master-bin.index
...
DB3
MariaDB [(none)]> SHOW Master status;
+------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| data-master-bin.000015 | 3534812 | | |
+------------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
ls -alc --fu /var/lib/mysql
...
413 2022-03-10 13:10:12.637403075 +0000 data-master-bin.000013
5966988 2022-04-27 16:46:33.269169792 +0000 data-master-bin.000014
3672324 2022-06-13 22:03:54.929455002 +0000 data-master-bin.000015
75 2022-04-28 00:37:55.597345308 +0000 data-master-bin.index
...
我正在使用 Maria DB 10.4
答案1
我通过将服务器设置为仅 来缓解此问题db-01
。这样,bin 日志就完全相同了。我相信应该有更好的方法来处理此问题。
[Read-Only-Service]
type=service
router=readconnroute
servers=db-01
user=maxscale
password=******
router_options=synced
[Replication-Listener]
type=listener
service=Read-Only-Service
protocol=MariaDBClient
port=/*some-port*/
ssl=required
ssl_cert=/path/to/server-cert.pem
ssl_key=/path/to/server-key.pem
ssl_ca_cert=/path/to/ca-cert.pem