Hi,
So let’s start with my setup:
- 3 nodes pxc cluster (up to date CentOS 7 and Percona-XtraDB-Cluster-57 5.7.19-29.22.1.el7)
mysql-test-01 10.0.0.51
mysql-test-02 10.0.0.52
mysql-test-03 10.0.0.53 - I have followed all the guidelines for CentOS (setenforce 0, …)
- I bootstraped the cluster from the node “mysql-test-01” 10.0.0.51 using “systemctl start mysql@bootstrap.service”
- Other nodes joined the cluster without problem
- The cluster was working
I did some tests, I gracefully shutdown node “10.0.0.53”, the cluster kept working, I restarted it with “systemctl start mysql”. It rejoined the cluster and recovered.
I did the same thing (graceful shutdown) with node “10.0.0.51”. Everything OK
The problem occured when I “brutally” shutdown the node “10.0.0.51”. I then started again the virtual machine, and then I use “systemctl start mysql.service” to start the node. It should start and automatically recover but it’s not working. I am pretty sure this is related to “systemd” but I am confused because I didn’t touch the default configuration that come with the package. I was hoping a working systemd configuration.
More informations:
configuration of node “10.0.0.51”
---------- /etc/my.cnf ----------
#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
# Please make any edits and changes to the appropriate sectional files
# included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
----------------------------------------
---------- /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ----------
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.51,10.0.0.52,10.0.0.53
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
wsrep_node_address=10.0.0.51
# Cluster name
wsrep_cluster_name=pxc-cluster-test
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=mysql-test-01
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="sstuser:password"
----------------------------------------
---------- /etc/percona-xtradb-cluster.conf.d/mysqld.cnf ----------
# MYSQL-TEST-01
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=51
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config
max-allowed-packet=64M
#
# InnoDB settings
#
innodb_file_per_table
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
--------------------------------------------------
And finally an extract from systemd journalctl
----------------------------------------
[root@mysql-test-01 ~]# systemctl start mysql.service
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
[root@mysql-test-01 ~]# journalctl -u mysql.service
....
Oct 02 17:21:39 mysql-test-01 systemd[1]: Starting Percona XtraDB Cluster...
Oct 02 17:21:40 mysql-test-01 mysqld_safe[2761]: 2017-10-02T15:21:40.081340Z mysqld_safe Logging to '/var/log/mysqld.log'.
Oct 02 17:21:40 mysql-test-01 mysqld_safe[2761]: 2017-10-02T15:21:40.085011Z mysqld_safe Logging to '/var/log/mysqld.log'.
Oct 02 17:21:40 mysql-test-01 mysqld_safe[2761]: 2017-10-02T15:21:40.119964Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Oct 02 17:21:40 mysql-test-01 mysqld_safe[2761]: 2017-10-02T15:21:40.140406Z mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.IG3oHj' --pid-file='/var/lib/mysql/mysql-test-01-recover.pid'
Oct 02 17:21:43 mysql-test-01 mysql-systemd[2762]: /usr/bin/mysql-systemd: line 140: kill: (2761) - No such process
Oct 02 17:21:43 mysql-test-01 mysql-systemd[2762]: ERROR! mysqld_safe with PID 2761 has already exited: FAILURE
Oct 02 17:21:43 mysql-test-01 systemd[1]: mysql.service: control process exited, code=exited status=1
Oct 02 17:21:43 mysql-test-01 mysql-systemd[3332]: WARNING: mysql pid file /var/lib/mysql/mysqld.pid empty or not readable
Oct 02 17:21:43 mysql-test-01 mysql-systemd[3332]: ERROR! mysql already dead
Oct 02 17:21:43 mysql-test-01 systemd[1]: mysql.service: control process exited, code=exited status=2
----------------------------------------
I have read a lot about systemd problems with mysql and pxc, but I can’t find a clue about mine. I am stuck because I have simulated a failure and I can’t restart my cluster, it’s a dev environnement but I won’t continue further until I find a solution.
Any ideas ?
Regards,
