PXC 5.7 WSREP_SST: [ERROR] xtrabackup_checkpoints missing

最新推荐文章于 2023-03-14 10:22:34 发布
最新推荐文章于 2023-03-14 10:22:34 发布
阅读量248 收藏
点赞数
文章标签： 数据库 操作系统 网络
原文链接：https://yq.aliyun.com/articles/378335
版权

前阵子在配置好了PXC5.7之后，在启动其中的一个节点，碰到了 [ERROR] xtrabackup_checkpoints missing. xtrabackup/SST failed on DONOR。关于这个错误，需要从其它节点来获取更详细的日志描述。下文是对这个问题的描述及解决，供大家参考。

一、故障现象

PXC mysql 5.7某节点重启失败，下面是重启失败后的状态
当前环境
[root@node146 ~]# more /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
[root@node146 ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.19-17, for Linux (x86_64) using 6.2

[root@node146 ~]# systemctl status mysql.service
mysql.service - Percona XtraDB Cluster
Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2017-12-29 09:12:13 CST; 44s ago
Process: 19192 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
Process: 19160 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=2)
Process: 18153 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=1/FAILURE)
Process: 18151 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=1/FAILURE)
Process: 18032 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
Main PID: 18151 (code=exited, status=1/FAILURE)
--下面开始输出错误信息
Dec 29 09:12:13 node146.example.com mysql-systemd[18153]: ERROR! mysqld_safe with PID 18151 has already exited: FAILURE
Dec 29 09:12:13 node146.example.com systemd[1]: mysql.service: control process exited, code=exited status=1
Dec 29 09:12:13 node146.example.com mysql-systemd[19160]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 29 09:12:13 node146.example.com mysql-systemd[19160]: ERROR! mysql already dead
Dec 29 09:12:13 node146.example.com systemd[1]: mysql.service: control process exited, code=exited status=2
Dec 29 09:12:13 node146.example.com mysql-systemd[19192]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 29 09:12:13 node146.example.com mysql-systemd[19192]: WARNING: mysql may be already dead
Dec 29 09:12:13 node146.example.com systemd[1]: Failed to start Percona XtraDB Cluster.
Dec 29 09:12:13 node146.example.com systemd[1]: Unit mysql.service entered failed state.
Dec 29 09:12:13 node146.example.com systemd[1]: mysql.service failed.

Dec 29 09:11:29 node146.example.com polkitd[18024]: Started polkitd version 0.112
Dec 29 09:11:29 node146.example.com polkitd[18024]: Loading rules from directory /etc/polkit-1/rules.d
Dec 29 09:11:29 node146.example.com polkitd[18024]: Loading rules from directory /usr/share/polkit-1/rules.d
Dec 29 09:11:29 node146.example.com polkitd[18024]: Finished loading, compiling and executing 6 rules
Dec 29 09:11:29 node146.example.com dbus[889]: [system] Successfully activated service 'org.freedesktop.PolicyKit1'
Dec 29 09:11:29 node146.example.com dbus-daemon[889]: dbus[889]: [system] Successfully activated service 'org.freedesktop.PolicyKit1'
Dec 29 09:11:29 node146.example.com systemd[1]: Started Authorization Manager.
-- Subject: Unit polkit.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit polkit.service has finished starting up.
--
-- The start-up result is done.
Dec 29 09:11:29 node146.example.com polkitd[18024]: Acquired the name org.freedesktop.PolicyKit1 on the system bus
Dec 29 09:11:29 node146.example.com gnome-session[2869]: PolicyKit daemon reconnected to bus.
Dec 29 09:11:29 node146.example.com gnome-session[2869]: Attempting to re-register as an authentication agent.
Dec 29 09:11:29 node146.example.com polkitd[18024]: Registered Authentication Agent for unix-session:c1 (system bus name :1.30 [gnome-shell --mode=gdm], object path /org/freedesktop/PolicyKit1/Authentication
Dec 29 09:11:29 node146.example.com gnome-session[2869]: We are now a registered authentication agent.
Dec 29 09:11:29 node146.example.com polkitd[18024]: Registered Authentication Agent for unix-process:18020:4562323 (system bus name :1.288 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object path /org/fre
Dec 29 09:11:29 node146.example.com systemd[1]: Starting Percona XtraDB Cluster...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has begun starting up.
Dec 29 09:11:40 node146.example.com mysqld_safe[18151]: 2017-12-29T01:11:40.538125Z mysqld_safe Logging to '/var/log/mysqld.log'.
Dec 29 09:11:40 node146.example.com mysqld_safe[18151]: 2017-12-29T01:11:40.540867Z mysqld_safe Logging to '/var/log/mysqld.log'.
Dec 29 09:11:40 node146.example.com mysqld_safe[18151]: 2017-12-29T01:11:40.565416Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Dec 29 09:11:40 node146.example.com mysqld_safe[18151]: 2017-12-29T01:11:40.569662Z mysqld_safe Skipping wsrep-recover for empty datadir: /var/lib/mysql
Dec 29 09:11:40 node146.example.com mysqld_safe[18151]: 2017-12-29T01:11:40.570611Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position
Dec 29 09:11:43 node146.example.com mysql-systemd[18153]: State transfer in progress, setting sleep higher
Dec 29 09:12:04 node146.example.com systemd[1]: mysql.service: main process exited, code=exited, status=1/FAILURE
Dec 29 09:12:13 node146.example.com mysql-systemd[18153]: /usr/bin/mysql-systemd: line 140: kill: (18151) - No such process
Dec 29 09:12:13 node146.example.com mysql-systemd[18153]: ERROR! mysqld_safe with PID 18151 has already exited: FAILURE
Dec 29 09:12:13 node146.example.com systemd[1]: mysql.service: control process exited, code=exited status=1
Dec 29 09:12:13 node146.example.com mysql-systemd[19160]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 29 09:12:13 node146.example.com mysql-systemd[19160]: ERROR! mysql already dead
Dec 29 09:12:13 node146.example.com systemd[1]: mysql.service: control process exited, code=exited status=2
Dec 29 09:12:13 node146.example.com mysql-systemd[19192]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 29 09:12:13 node146.example.com mysql-systemd[19192]: WARNING: mysql may be already dead
Dec 29 09:12:13 node146.example.com systemd[1]: Failed to start Percona XtraDB Cluster.
-- Subject: Unit mysql.service has failed ## Author : Leshami
-- Defined-By: systemd                           ## Blog : http://blog.csdn.net/leshami
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has failed.
--
-- The result is failed.
Dec 29 09:12:13 node146.example.com systemd[1]: Unit mysql.service entered failed state.
Dec 29 09:12:13 node146.example.com systemd[1]: mysql.service failed.
Dec 29 09:12:13 node146.example.com polkitd[18024]: Unregistered Authentication Agent for unix-process:18020:4562323 (system bus name :1.288, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, loca
lines 2162-2213/2213 (END)

下面查看mysqld的错误日志
[root@node146 ~]# tail -fn 100 /var/log/mysqld.log
2017-12-29T01:11:43.416689Z 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> aeb87793-ebb2-11e7-b33e-eeaf4988bbe4:0
2017-12-29T01:11:43.812807Z WSREP_SST: [INFO] WARNING: Stale temporary SST directory: /var/lib/mysql//.sst from previous state transfer. Removing
2017-12-29T01:11:43.841926Z WSREP_SST: [INFO] Proceeding with SST.........
2017-12-29T01:11:43.867695Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!
2017-12-29T01:11:44.359210Z 0 [Note] WSREP: (3a4aef7c, 'tcp://0.0.0.0:4567') connection to peer 3a4aef7c with addr tcp://192.168.81.146:4567 timed out, no messages seen in PT3S
2017-12-29T01:11:44.359666Z 0 [Note] WSREP: (3a4aef7c, 'tcp://0.0.0.0:4567') turning message relay requesting off
2017-12-29T01:11:53.926533Z WSREP_SST: [ERROR] ******************* FATAL ERROR **********************  这里提示致命的错误，跟xtrabackup有关，提示检查 DONOR log
2017-12-29T01:11:53.927524Z WSREP_SST: [ERROR] xtrabackup_checkpoints missing. xtrabackup/SST failed on DONOR. Check DONOR log
2017-12-29T01:11:53.928649Z WSREP_SST: [ERROR] ******************************************************
2017-12-29T01:11:53.932079Z WSREP_SST: [ERROR] Cleanup after exit with status:2
2017-12-29T01:11:53.942644Z 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.81.146' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '18614' '' : 2 (No such file or directory)
2017-12-29T01:11:53.942695Z 0 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2017-12-29T01:11:53.942703Z 0 [ERROR] WSREP: SST script aborted with error 2 (No such file or directory)
2017-12-29T01:11:53.942729Z 0 [ERROR] WSREP: SST failed: 2 (No such file or directory)
2017-12-29T01:11:53.942735Z 0 [ERROR] Aborting

ssh到另外一个节点1.142上，在mysql数据目录下，查看innobackup日志
[root@node142 ~]# more /var/lib/mysql/innobackup.backup.log
171229 09:11:47 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'sstuser' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock','sstuser',...) failed: Access denied for user 'sstuser'@'localhost' (using password: YES) at - line 1314. --提示访问被拒绝，晕，账户明明已经被创建过了，不知道是不是误删了或者账户被修改。
171229 09:11:47 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: /var/lib/mysql/mysql.sock
Failed to connect to MySQL server: Access denied for user 'sstuser'@'localhost' (using password: YES).

二、故障解决

从上面的错误可知，用于SST同步的用户访问被拒绝，测试SST用户能否登陆，如下，确实无法登陆
[root@node142 mysql]# mysql -usstuser -ps3cretPass
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'sstuser'@'localhost' (using password: YES)

--下面创建这个
[root@node142 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.19-17-57 Percona XtraDB Cluster (GPL), Release rel17, Revision 35cdc81, WSREP version 29.22, wsrep_29.22

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'sstuser'@'localhost' identified by 's3cretPass';  --为账户修改密码
Query OK, 0 rows affected (0.05 sec)

mysql> exit
Bye
[root@node142 mysql]# mysql -usstuser -ps3cretPass --再次登陆成功
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.19-17-57 Percona XtraDB Cluster (GPL), Release rel17, Revision 35cdc81, WSREP version 29.22, wsrep_29.22

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
接下来重启1.146 IP节点成功。

三、更多参考

基于CentOS 7 安装Percona XtraDB Cluster(PXC) 5.7
MySQL 5.7 时间显示修改(log_timestamps UTC)
MySQL PXC 5.7 invalid user‘@MYSQLD_USER@’

DBA牛鹏社(SQL/NOSQL/LINUX)

weixin_34360651
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql启动集群报连接本地失败_启动本地安装的pxc集群失败，前面的步骤都检查没错了...
weixin_29491655的博客
02-08 1314
在真是的华为云linux centos7.4 上执行 systemctl start [email protected] 启动失败了● [email protected] - Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrapLoaded: loaded (/usr/lib/systemd/sy...
pxc_swarm:泊坞窗工人群Percona PXC设置
05-03
PXC Docker Swarm设置 在Docker Swarm之上部署快速简便方法。 详细信息：请参阅
mysql galera status_MySQL galera 安装过程遇到的问题及解决方案
weixin_36462703的博客
01-19 270
1: 缺少 libaio 包， libaio是linux下的一个异步非阻塞方式读写文件的接口。[root@12 mysql]# ./scripts/mysql_install_db --user=mysqlInstalling MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1:...
mysql percona xtradb cluster,Percona XtraDB Cluster 启动失败
weixin_39967670的博客
03-17 431
[root@localhost ~]# systemctl status mysql.service● mysql.service - Percona XtraDB ClusterLoaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)Active: activating (st...
【运维心得】centos7 mysql8.0无法启动常见问题与解决方案
清风唱诗人的博客
03-14 3485
表象: Process: 1018 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)根据配置文件路径，grep log-error /etc/my.cnf 查询error日志路径，默认位置: /var/log/mysqld.log。2. 如1无有效信息，使用 mysql --help | grep my.cn 确定配置文件路径，默认位置: /etc/my.cnf。通常这个问题到这就解决了。
CentOS7 安装Mysql8.0
y_h_t的专栏
06-14 248
1：下载rpm https://dev.mysql.com/downloads/repo/yum/
mysql异常退出定位及解决记录
guotianqing的博客
07-23 5615
现象 运行正常的程序，早上出现了异常。 查看日志报错，是读写数据时失败。这个时候需要检查一下数据库是否正常。 先在终端里用mysql客户端连接一下吧。 mysql -uuser -p 输入密码，果然连接失败。 再查看mysql服务的状态，发现已经挂了： # systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; ena
MySQL PXC_百度大神cnf配置
08-30
MySQL PXC_百度大神cnf配置
deploy_mysql_cluster：一键部署mysql主从积累
02-13
一键部署mysql PXC进行 脚本内容： 自动部署 自动部署mysqlPXC部署 自动部署keepalived异步间隔冗余策略，并侦听用户指定的mysql端口是否开发，如果端口不可用，那么将在两秒内漂移VIP 脚本使用方式： cd deploy_...
PXC550_IM_ZH_A02.pdf
06-18
森海塞尔（Sennheiser）PXC550 中文说明，需要的可以下载。
pxc8_0_21.tar
02-01
docker打包好的镜像percona/percona-xtradb-cluster:8.0.21
如何搭建Percona XtraDB Cluster（PXC）集群
003号猫
08-04 804
my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY 1、关闭防火墙或者允许3306, 4444, 4567和4568四个端口的连接 3306 数据库对外提供服务的端口 4444 镜像数据传输SST，集群数据同步端口，全量同步，新节点加入时起作用 4567 集群节点间相互通信的端口 4568 增量数据同步IST，节点下线、重启后使用该端口，增量同步数据。 #开放3306, 4444, 4567和4568四个端口 firewall-cmd --zone=publi
mysql报错码code=exited,status=2的解决方案
weixin_46020146的博客
08-01 2497
mysql报错码code=exited,status=2的解决方案
MariaDB Galera Cluster 10.3.8 集群搭建报错
vkingnew 的技术博客
07-17 2296
运行环境：Centos7.4+MariaDB 10.3.8 # cat innobackup.backup.log xtrabackup: recognized server arguments: --datadir=/data/mysql/node1/data --server-id=20 --log_bin=/data/mysql/node1/log/node1_bin.log --inn...
mysql xtrabackup-v2_pxc wsrep_sst_method均配置为xtrabackup-v2报错
weixin_42367233的博客
01-27 906
启动第二节点报错：WSREP_SST: [ERROR] Error while getting data from donor node: exit codes: 137 0本来所有节点的wsrep_sst_method均配置为xtrabackup-v2，但是添加第二个节点时报错：WSREP_SST: [ERROR] Error while getting data from donor node...
MySQL galera 安装过程遇到的问题及解决方案
yaoqinglin的博客
04-25 6599
1: 缺少 libaio 包， libaio是linux下的一个异步非阻塞方式读写文件的接口。 [root@12 mysql]# ./scripts/mysql_install_db --user=mysql Installing MySQL system tables... ./bin/mysqld: error while loading shared libraries: libaio
MySQL高可用架构之PXC实践
cuanzhutang8944的博客
08-18 295
近期想对MySQL高可用架构做些研究并总结下来，本文是对PXC安装部署做个记录。 1、PXC简介 官方介绍： Percona XtraDB Cluster is High Availability ...
Mysql PXC 问题从节点不能启动问题
由俱有理
06-03 5736
mysql pxc 主节点可以正常启动，从节点启动报错：WSREP_SST: [ERROR] Cleanup after exit with status:1
基于centos封装mysql8的 PXC集群的docker镜像
最新发布
03-30
mysql --user=root --password=$MYSQL_ROOT_PASSWORD -e "SET GLOBAL wsrep_sst_method='xtrabackup-v2';" mysql --user=root --password=$MYSQL_ROOT_PASSWORD -e "SET GLOBAL wsrep_cluster_name='PXC';" # ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值