The 'mysqld_safe --wsrep-recover' command not only passes this option to mysqld but also makes it easy to check each failed node position and shows results in user-friendly way (no need to view/parse error log).
IMHO this command should always do force GTID recovery. If grastate.dat is OK and has the information (like after graceful shutdown) you can just read it, no need to involve mysqld_safe.
Or maybe add another option to mysqld_safe, like --force-wsrep-recover which would do the position recovery regardless of grastate.dat presence.
percona20 mysql> show status like "wsrep_last_committed";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| wsrep_last_committed | 6 |
+----------------------+-------+
1 row in set (0.02 sec)
[root@percona20 ~]# killall -9 mysqld
[root@percona20 ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[root@percona20 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 33fbfa96-f619-11e3-abd0-faf333aa6b5f
seqno: -1
cert_index:
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:12:34 mysqld_safe Logging to '/var/lib/mysql/percona20_error.log'.
140618 13:12:34 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140618 13:12:34 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.1LJ4Xu' --pid-file='/var/lib/mysql/percona20-recover.pid'
140618 13:12:38 mysqld_safe WSREP: Recovered position 33fbfa96-f619-11e3-abd0-faf333aa6b5f:6
140618 13:12:42 mysqld_safe mysqld from pid file /var/lib/mysql/percona20.pid ended
And when gracefully stopped:
[root@percona20 ~]# /etc/init.d/mysql stop
Shutting down MySQL (Percona XtraDB Cluster)...... SUCCESS!
[root@percona20 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 33fbfa96-f619-11e3-abd0-faf333aa6b5f
seqno: 6
cert_index:
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:24:13 mysqld_safe Logging to '/var/lib/mysql/percona20_error.log'.
140618 13:24:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140618 13:24:13 mysqld_safe Skipping wsrep-recover for 33fbfa96-f619-11e3-abd0-faf333aa6b5f:6 pair
140618 13:24:13 mysqld_safe Assigning 33fbfa96-f619-11e3-abd0-faf333aa6b5f:6 to wsrep_start_position
140618 13:24:17 mysqld_safe mysqld from pid file /var/lib/mysql/percona20.pid ended
[root@percona20 ~]# rm -f /var/lib/mysql/grastate.dat
[root@percona20 ~]#
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:24:49 mysqld_safe Logging to '/var/lib/mysql/percona20_error.log'.
140618 13:24:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140618 13:24:49 mysqld_safe Skipping wsrep-recover for empty datadir: /var/lib/mysql
140618 13:24:49 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position
140618 13:24:53 mysqld_safe mysqld from pid file /var/lib/mysql/percona20.pid ended
[root@percona20 ~]# touch /var/lib/mysql/grastate.dat
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:35:14 mysqld_safe Logging to '/var/lib/mysql/percona20_error.log'.
140618 13:35:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe: line 228: [: -ne: unary operator expected
140618 13:35:14 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.3Q8KWm' --pid-file='/var/lib/mysql/percona20-recover.pid'
140618 13:35:17 mysqld_safe WSREP: Recovered position 33fbfa96-f619-11e3-abd0-faf333aa6b5f:6
140618 13:35:21 mysqld_safe mysqld from pid file /var/lib/mysql/percona20.pid ended
Btw. for me the 'mysqld --user=mysql --wsrep-recover' does not print anything to stderr nor stdout, the position has to be read from error log (CentOS 6.5, PXC 5.5.37 and PXC 5.6.19).
The 'mysqld_safe --wsrep-recover' command not only passes this option to mysqld but also makes it easy to check each failed node position and shows results in user-friendly way (no need to view/parse error log). wsrep-recover which would do the position recovery regardless of grastate.dat presence.
IMHO this command should always do force GTID recovery. If grastate.dat is OK and has the information (like after graceful shutdown) you can just read it, no need to involve mysqld_safe.
Or maybe add another option to mysqld_safe, like --force-
This is how it works for me in PXC 5.6.19:
percona20 mysql> select @@version, @@version_ comment; ------- ------- +------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --+ ------- ------- +------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --+ ------- ------- +------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --+
+------
| @@version | @@version_comment |
+------
| 5.6.19-67.0-56-log | Percona XtraDB Cluster (GPL), Release rel67.0, Revision 796, WSREP version 25.6, wsrep_25.6.r4096 |
+------
1 row in set (0.00 sec)
percona20 mysql> show status like "wsrep_ last_committed" ; ------- ------- --+---- ---+ ------- ------- --+---- ---+ committed | 6 | ------- ------- --+---- ---+
+------
| Variable_name | Value |
+------
| wsrep_last_
+------
1 row in set (0.02 sec)
[root@percona20 ~]# killall -9 mysqld mysql/mysql. sock' (111) mysql/grastate. dat f619-11e3- abd0-faf333aa6b 5f mysql/percona20 _error. log'. '/var/lib/ mysql/wsrep_ recovery. 1LJ4Xu' --pid-file= '/var/lib/ mysql/percona20 -recover. pid' f619-11e3- abd0-faf333aa6b 5f:6 mysql/percona20 .pid ended
[root@percona20 ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/
[root@percona20 ~]# cat /var/lib/
# GALERA saved state
version: 2.1
uuid: 33fbfa96-
seqno: -1
cert_index:
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:12:34 mysqld_safe Logging to '/var/lib/
140618 13:12:34 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140618 13:12:34 mysqld_safe WSREP: Running position recovery with --log_error=
140618 13:12:38 mysqld_safe WSREP: Recovered position 33fbfa96-
140618 13:12:42 mysqld_safe mysqld from pid file /var/lib/
And when gracefully stopped:
[root@percona20 ~]# /etc/init.d/mysql stop mysql/grastate. dat f619-11e3- abd0-faf333aa6b 5f mysql/percona20 _error. log'. f619-11e3- abd0-faf333aa6b 5f:6 pair f619-11e3- abd0-faf333aa6b 5f:6 to wsrep_start_ position mysql/percona20 .pid ended mysql/grastate. dat mysql/percona20 _error. log'. 0000-0000- 0000-0000000000 00:-1 to wsrep_start_ position mysql/percona20 .pid ended
Shutting down MySQL (Percona XtraDB Cluster)...... SUCCESS!
[root@percona20 ~]# cat /var/lib/
# GALERA saved state
version: 2.1
uuid: 33fbfa96-
seqno: 6
cert_index:
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:24:13 mysqld_safe Logging to '/var/lib/
140618 13:24:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140618 13:24:13 mysqld_safe Skipping wsrep-recover for 33fbfa96-
140618 13:24:13 mysqld_safe Assigning 33fbfa96-
140618 13:24:17 mysqld_safe mysqld from pid file /var/lib/
[root@percona20 ~]# rm -f /var/lib/
[root@percona20 ~]#
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:24:49 mysqld_safe Logging to '/var/lib/
140618 13:24:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140618 13:24:49 mysqld_safe Skipping wsrep-recover for empty datadir: /var/lib/mysql
140618 13:24:49 mysqld_safe Assigning 00000000-
140618 13:24:53 mysqld_safe mysqld from pid file /var/lib/
[root@percona20 ~]# touch /var/lib/ mysql/grastate. dat mysql/percona20 _error. log'. mysqld_ safe: line 228: [: -ne: unary operator expected '/var/lib/ mysql/wsrep_ recovery. 3Q8KWm' --pid-file= '/var/lib/ mysql/percona20 -recover. pid' f619-11e3- abd0-faf333aa6b 5f:6 mysql/percona20 .pid ended
[root@percona20 ~]# mysqld_safe --wsrep-recover
140618 13:35:14 mysqld_safe Logging to '/var/lib/
140618 13:35:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/
140618 13:35:14 mysqld_safe WSREP: Running position recovery with --log_error=
140618 13:35:17 mysqld_safe WSREP: Recovered position 33fbfa96-
140618 13:35:21 mysqld_safe mysqld from pid file /var/lib/
Btw. for me the 'mysqld --user=mysql --wsrep-recover' does not print anything to stderr nor stdout, the position has to be read from error log (CentOS 6.5, PXC 5.5.37 and PXC 5.6.19).