maria db galera 节点上的不同 bin 日志文件索引

2024-6-1 • tag-icon

设置：

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

答案1

相关内容