Mariadb galera 无法添加新节点故障处理
Mariadb数据库已用了一段时间，最近为了HA，需要配置 Mariadb galera 集群
配置情况，节点数3，原数据库节点node1，新增node2，node3
系统：CentOS Stream release 8
mariadb版本：mariadb-server-10.3.28
一、数据库安装
新节点安装数据库相关包
yum install mariadb mariadb-server python3-PyMySQL -y
所有节点安装galara相关包
yum install galera mariadb-server-galera -y
node2，node3初始化数据库
systemctl enable mariadb.service systemctl start mariadb.service mysql_secure_installation 配置密码 Remove anonymous users? [Y/n] y Disallow root login remotely? [Y/n] n Remove test database and access to it? [Y/n] y Reload privilege tables now? [Y/n] y
二、galera配置
node1配置如下，其他节点修改wsrep_node_address、wsrep_node_address参数
# cat /etc/my.cnf.d/galera.cnf [mysqld] bind-address=0.0.0.0 binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 innodb_buffer_pool_size=122M wsrep_auto_increment_control=1 wsrep_causal_reads=0 wsrep_certify_nonPK=1 wsrep_cluster_name="my_wsrep_cluster" wsrep_node_address=node1 wsrep_node_address=192.168.1.1 wsrep_cluster_address="gcomm://192.168.1.1,192.168.2.1,192.168.1.3" wsrep_convert_LOCK_to_trx=0 wsrep_debug=0 wsrep_drupal_282555_workaround=0 wsrep_max_ws_rows=0 wsrep_max_ws_size=2147483647 wsrep_notify_cmd= wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_provider_options="gcache.size=300M; gcache.page_size=300M" wsrep_retry_autocommit=1 wsrep_slave_threads=1 wsrep_sst_method=rsync
三、启动服务
node1节点执行
galera_new_cluster
如果mariadb服务已开启，需要先关闭
node2，node3执行
systemctl restart mariadb.service
正常情况下，应该就已经可以了
四、问题
我按以上步骤完成后，发现node2和node3，无法启动
# systemctl restart mariadb Job for mariadb.service failed because a fatal signal was delivered to the control process. See "systemctl status mariadb.service" and "journalctl -xe" for details.
无论重装，调整配置，怎么弄都不行
日志报错
# grep -Ei "err|war" /var/log/mariadb/mariadb.log WSREP_SST: [ERROR] Parent mysqld process (PID:379774) terminated unexpectedly. (20221009 11:01:54.254) 2022-10-09 11:01:59 0 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory) 2022-10-09 11:02:00 1 [Warning] WSREP: Gap in state sequence. Need state transfer. 2022-10-09 11:02:00 1 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (ab886bd7-46d6-11ed-8a83-fe4004c311ab): 1 (Operation not permitted) 2022-10-09 11:02:01 0 [Warning] WSREP: 0.0 (node-1): State transfer to 1.0 (node-2) failed: -255 (Unknown error 255) 2022-10-09 11:02:01 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():780: Will never receive state. Need to abort.
谷歌百度搜遍了也无法解决
a，删除galera.cache、grastate.dat、gvwstate.dat文件 （无效）我甚至将galera相关配置及文件全部删除，重新创建或安装，都不行
b，修改mariadb.service的TimeoutSec，（无效）
c，wsrep_cluster_address配置的地址顺序等（无效），这方案看着就不太靠谱，死马当活马医了
d，防火墙，selinux等等，（无效）
还有一些奇葩方法，一点用都没
直到后来无意中在/var/log/message中看到一条关于rsync的报错
rsyncd[380389]: rsyncd version 3.1.3 starting, listening on port 4444 rsyncd[380409]: connect from node1 (192.168.0.1) rsyncd[380409]: rsync to rsync_sst/ from node1 (192.168.0.1) rsyncd[380409]: rsync: on remote machine: --sparse-block=1024: unknown option rsyncd[380409]: rsync error: requested action not supported (code 4) at clientserver.c(971) [Receiver=3.1.3] rsyncd[380389]: sent 0 bytes received 0 bytes total size 0 rsyncd[380605]: rsyncd version 3.1.3 starting, listening on port 4444
结合mariadb.log中rsync的日志
2022-10-09 3:27:39 2 [Warning] WSREP: Gap in state sequence. Need state transfer. 2022-10-09 3:27:39 0 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '192.168.0.2' --datadir '/var/lib/mysql/' --parent '3693645' --mysqld-args --basedir=/usr' 2022-10-09 3:27:40 2 [Note] WSREP: Prepared SST request: rsync|192.168.0.2:4444/rsync_sst
2022-10-09 3:27:40 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 2022-10-09 3:27:40 2 [Note] WSREP: Assign initial position for certification: 237433, protocol version: 4 2022-10-09 3:27:40 0 [Note] WSREP: Service thread queue flushed. 2022-10-09 3:27:40 2 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (ab886bd7-46d6-11ed-8a83-fe4004c311ab): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():467. IST will be unavailable.
怀疑是rsync有问题，可能版本太低，导致无法识别--sparse-block=1024这个选项，从而导致同步失败无法启动mariadb
于是顺手升级下rsync
# yum update rsync
再次启动mariadb
# systemctl restart mariadb
居然启动成功了，热泪盈眶啊
原版本：rsync-3.1.3-14.el8.2.x86_64
新版本：rsync-3.1.3-19.el8.x86_64
# rpm -qa |grep rsync
rsync-3.1.3-14.el8.2.x86_64
# rsync --help |grep sparse
-S, --sparse turn sequences of nulls into sparse blocks
# rpm -qa |grep rsync
rsync-3.1.3-19.el8.x86_64
# rsync --help |grep sparse
-S, --sparse turn sequences of nulls into sparse blocks
--sparse-block=SIZE set block size used to handle sparse files
