Running a Percona XtraDB Cluster on RHEL 7

To operate a PXC cluster under RHEL / CentOS 6, you pass the appropriate argument to a service script:

• service mysql start to start this cluster’s node
• service mysql bootstrap-pxc to start this node as the first node of the cluster
• service mysql status to check the status of this cluster’s node
• service mysql stop to stop this cluster’s node

and so on. This was explained in a previous blog post.

Under RHEL / CentOS 7, things are a bit different. In the transition from System V to systemd, the modes of operation of a Percona cluster were split among two different scripts.

One is

which simply starts PXC with mysqld_safe, and therefore is used to start/stop/restart a simple node.

The other is

which passes to PXC an environment file which, in turn, passes additional arguments (EXTRA_ARGS) to mysqld_safe.

To start a simple node, you would use

To bootstrap the cluster from this node, however, you must use

This will read the /etc/sysconfig/mysql.bootstrap file and pass the additional argument

to mysqld_safe.

Therefore there might be either service running on a node, and you must interrogate the correct one.
For instance, let’s say that you run systemctl status mysql on a node and it returns an error:

Apr 22 09:48:17 sqlnode1 mysql-systemd[918]: ERROR! mysqld_safe with PID 917 has already exited: FAILURE
Apr 22 09:48:17 sqlnode1 systemd[1]: mysql.service: control process exited, code=exited status=1
Apr 22 09:48:17 sqlnode1 mysql-systemd[3355]: WARNING: mysql pid file /var/lib/mysql/mysql.pid empty or not readable
Apr 22 09:48:17 sqlnode1 mysql-systemd[3355]: ERROR! mysql already dead
Apr 22 09:48:17 sqlnode1 systemd[1]: mysql.service: control process exited, code=exited status=2
Apr 22 09:48:17 sqlnode1 mysql-systemd[3371]: WARNING: mysql pid file /var/lib/mysql/mysql.pid empty or not readable
Apr 22 09:48:17 sqlnode1 mysql-systemd[3371]: WARNING: mysql may be already dead
Apr 22 09:48:17 sqlnode1 systemd[1]: Failed to start Percona XtraDB Cluster.
Apr 22 09:48:17 sqlnode1 systemd[1]: Unit mysql.service entered failed state.
Apr 22 09:48:17 sqlnode1 systemd[1]: mysql.service failed.

If you ever try to start MySQL by hand, the command fails:

Spoiler alert: this happens because there’s already a mysql daemon running and the socket is already in use. In fact, if you try to connect to the MySQL service you’ll see that it is working:

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, 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>

What is happening? Well, this node was used to bootstrap the cluster, and therefore the service to interrogate is mysql@bootstrap and not mysql:

Apr 22 09:51:44 sqlnode1 systemd[1]: Starting Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap…
Apr 22 09:51:44 sqlnode1 mysql-systemd[13524]: State transfer in progress, setting sleep higher
Apr 22 09:51:44 sqlnode1 mysqld_safe[13523]: 160422 09:51:44 mysqld_safe Logging to ‘/var/log/mysql/mysqld.log’.
Apr 22 09:51:44 sqlnode1 mysqld_safe[13523]: 160422 09:51:44 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Apr 22 09:51:44 sqlnode1 mysqld_safe[13523]: 160422 09:51:44 mysqld_safe WSREP: Running position recovery with –log_error=’/var/lib/mysql/wsrep_recovery.2NTvKc’ –pid-file=’/var/lib/mysql/sqlnode1-recover.pid’
Apr 22 09:51:47 sqlnode1 mysqld_safe[13523]: 160422 09:51:47 mysqld_safe WSREP: Recovered position 9b5e2916-07cc-11e6-879b-532e4a0da8ef:9
Apr 22 09:51:54 sqlnode1 mysql-systemd[13524]: SUCCESS!
Apr 22 09:51:54 sqlnode1 systemd[1]: Started Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap.
Hint: Some lines were ellipsized, use -l to show in full.

The output of systemctl shows the difference between mysql units:

On any other node of the cluster but the bootstrapping node, it is the usual mysql service which is active. Note: mysql@bootstrap intervenes only at bootstrap. If the bootstrapping node is later rebooted, it’ll be running the mysql service afterwards.

Apr 22 09:52:59 sqlnode2 systemd[1]: Starting Percona XtraDB Cluster…
Apr 22 09:53:00 sqlnode2 mysqld_safe[7712]: 160422 09:53:00 mysqld_safe Logging to ‘/var/log/mysql/mysqld.log’.
Apr 22 09:53:00 sqlnode2 mysqld_safe[7712]: 160422 09:53:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Apr 22 09:53:00 sqlnode2 mysqld_safe[7712]: 160422 09:53:00 mysqld_safe Skipping wsrep-recover for 9b5e2916-07cc-11e6-879b-532e4a0da8ef:9 pair
Apr 22 09:53:00 sqlnode2 mysqld_safe[7712]: 160422 09:53:00 mysqld_safe Assigning 9b5e2916-07cc-11e6-879b-532e4a0da8ef:9 to wsrep_start_position
Apr 22 09:53:01 sqlnode2 mysql-systemd[7713]: SUCCESS!
Apr 22 09:53:01 sqlnode2 systemd[1]: Started Percona XtraDB Cluster.

We’ve been testing the behavior of the latest version of SeveralNines ClusterControl (v1.3.0, which installs Percona XtraDB Cluster v5.6) and we’ve seen that it handles correctly the different cases. A few things worth noting on this version:

• The MySQL service is enabled at startup (via systemd) only on the node from which the cluster has been originally bootstrapped; ClusterControl then takes care of reviving mysqld on all other nodes.
• The ClusterControl host runs an instance of MariaDB, not MySQL, for its CMON database.