Header Shadow Image


[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last oneave the cluster and may not contain all the updates.

Getting this?

Mar 15 18:28:12 mysql03.nix.mds.xyz mysqld[17578]: 2020-03-15T22:28:12.453960Z 0 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last oneave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .

This was due to MySQL running out of space on mysql03, the primary node.  Because the service was up, no failover occurred.  Handle the situation by checking the grastate.dat file while the other two nodes are still up in this MySQL Galera Cluster:  

[root@mysql01 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    751c8bd5-224f-11ea-b912-c7063c41e2a2
seqno:   -1
safe_to_bootstrap: 0
[root@mysql01 ~]#

[root@mysql02 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    751c8bd5-224f-11ea-b912-c7063c41e2a2
seqno:   -1
safe_to_bootstrap: 0
[root@mysql02 ~]#

[root@mysql03 audit]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    751c8bd5-224f-11ea-b912-c7063c41e2a2
seqno:   -1
safe_to_bootstrap: 0
[root@mysql03 audit]#

Notice how the safe_to_bootstrap is set to 0 on all the nodes, despite mysql01, mysql02 being up and the primary node, mysql03 being down.  Proceed to shut down all the nodes. 

mysql03: systemctl stop mysqld
mysql01: systemctl stop mysqld
mysql02: systemctl stop mysqld

And check the grastate.dat file once more:

[root@mysql02 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    751c8bd5-224f-11ea-b912-c7063c41e2a2
seqno:   23620163
safe_to_bootstrap: 1
[root@mysql02 ~]#

[root@mysql01 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    751c8bd5-224f-11ea-b912-c7063c41e2a2
seqno:   23620163
safe_to_bootstrap: 0
[root@mysql01 ~]#

[root@mysql03 log]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    751c8bd5-224f-11ea-b912-c7063c41e2a2
seqno:   -1
safe_to_bootstrap: 0
[root@mysql03 log]#

Notice how the last node to be stopped has the safe_to_bootstrap flag set to 1?  This is the node we'll bootstrap.  To do so, we'll call the following code ensure we run systemctl start mysqld on the remainder of the nodes:

mysql02: /usr/bin/mysqld_bootstrap
mysql01: systemctl start mysqld
mysql02: systemctl start mysqld

The commands have to be typed within roughly 30 seconds of each other.  Otherwise startup won't work.  So go ahead and execute them.

After execution, the same error message persisted however.  So we'll try to the same method but bootstrapping mysql03 instead.  To do that successfully, edit the /var/lib/mysql/grastate.dat file, verify the UUID is identical across all nodes then change the safe_to_bootstrap option to 1:

[root@mysql03 lib]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    751c8bd5-224f-11ea-b912-c7063c41e2a2
seqno:   -1
safe_to_bootstrap: 1
[root@mysql03 lib]#

Now start up the nodes in this sequence:

mysql03: /usr/bin/mysqld_bootstrap
mysql01: systemctl start mysqld
mysql02: systemctl start mysqld

This time they should start!  To ensure the DB doesn't run out of space, set the bin log retention like this:

[root@mysql03 audit]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10087901
Server version: 5.7.25-log MySQL Community Server – (GPL), wsrep_25.17

Copyright (c) 2000, 2019, 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> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| galera_test        |
| mysql              |
| opennebula         |
| performance_schema |
| sys                |
| zabbix             |
+——————–+
7 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> show global expire_logs_days
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us                                e near 'expire_logs_days' at line 1
mysql> select global expire_logs_days;
ERROR 1054 (42S22): Unknown column 'global' in 'field list'
mysql>
mysql>
mysql> show variables like 'expire_logs_days';
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| expire_logs_days | 0     |
+——————+——-+
1 row in set (0.01 sec)

mysql>
mysql>
mysql>
mysql> SET GLOBAL expire_logs_days = 20;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show variables like 'expire_logs_days';
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| expire_logs_days | 20    |
+——————+——-+
1 row in set (0.01 sec)

mysql>
mysql>
mysql>
mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
+——————–+—————+
| table_schema       | MB            |
+——————–+—————+
| information_schema |    0.15625000 |
| mysql              |    0.86142254 |
| opennebula         |   11.90625000 |
| performance_schema |    0.00000000 |
| sys                |    0.01562500 |
| zabbix             | 4668.21875000 |
+——————–+—————+
6 rows in set (0.23 sec)

mysql> \q
Bye
[root@mysql03 audit]#

This will ensure you aren't keeping too many of these:

[root@mysql03 mysql]# ls -altri
total 31253596
134299846 drwxr-xr-x. 41 root  root        4096 Mar 17  2019 ..
   523777 -rw——-.  1 mysql mysql         42 Jun  8  2019 .lesshst
   544181 -rw——-.  1 mysql mysql       2048 Jun  8  2019 .bash_history
   104729 -rw-r—–.  1 mysql mysql         56 Dec 19 06:02 auto.cnf
   230925 -rw-r—–.  1 mysql mysql        177 Dec 19 06:02 mysql_binary_log.000001
   104733 -rw——-.  1 mysql mysql       1679 Dec 19 06:02 ca-key.pem
   114749 -rw-r–r–.  1 mysql mysql       1107 Dec 19 06:02 ca.pem
   292828 -rw——-.  1 mysql mysql       1675 Dec 19 06:02 server-key.pem
   292830 -rw-r–r–.  1 mysql mysql       1107 Dec 19 06:02 server-cert.pem
   299833 -rw——-.  1 mysql mysql       1679 Dec 19 06:02 client-key.pem
   299856 -rw-r–r–.  1 mysql mysql       1107 Dec 19 06:02 client-cert.pem
   104730 -rw——-.  1 mysql mysql       1675 Dec 19 06:02 private_key.pem
   104732 -rw-r–r–.  1 mysql mysql        451 Dec 19 06:02 public_key.pem
   299860 -rw-r—–.  1 mysql mysql        146 Dec 19 06:02 mysql_binary_log.000002
   523766 -rw-r—–.  1 mysql mysql        146 Dec 19 06:03 mysql_binary_log.000003
   523775 -rw-r—–.  1 mysql mysql        146 Dec 19 06:03 mysql_binary_log.000004
   523765 -rw-r—–.  1 mysql mysql        146 Dec 19 06:05 mysql_binary_log.000005
   523769 -rw-r—–.  1 mysql mysql        177 Dec 19 06:08 mysql_binary_log.000006
   523767 -rw-r—–.  1 mysql mysql        278 Dec 19 06:08 ib_buffer_pool
   523780 -rw-r—–.  1 mysql mysql        146 Dec 19 06:08 mysql_binary_log.000007
   523768 -rw-r—–.  1 mysql mysql        146 Dec 19 06:09 mysql_binary_log.000008
   523783 -rw-r—–.  1 mysql mysql        146 Dec 19 06:10 mysql_binary_log.000009
   523770 -rw-r—–.  1 mysql mysql        146 Dec 19 06:12 mysql_binary_log.000010
   523771 -rw——-.  1 mysql mysql          6 Dec 19 06:12 mysql.sock.lock
   523772 srwxrwxrwx.  1 mysql mysql          0 Dec 19 06:12 mysql.sock
   523639 drwxr-x—.  2 mysql mysql         19 Dec 19 06:39 galera_test
201777909 drwxr-x—.  2 mysql mysql       4096 Dec 19 06:39 mysql
 68168280 drwxr-x—.  2 mysql mysql       4096 Dec 19 06:39 opennebula
   227648 drwxr-x—.  2 mysql mysql       8192 Dec 19 06:39 performance_schema
   523773 -rw-r—–.  1 mysql mysql 1074061080 Dec 19 07:00 mysql_binary_log.000011
135036980 drwxr-x—.  2 mysql mysql      12288 Dec 19 07:02 zabbix
 68168287 drwxr-x—.  2 mysql mysql      12288 Dec 19 07:02 sys
   248927 -rw-r—–.  1 mysql mysql 1073745981 Dec 21 22:56 mysql_binary_log.000012
   248926 -rw-r—–.  1 mysql mysql 1073756739 Dec 24 16:47 mysql_binary_log.000013
   250211 -rw-r—–.  1 mysql mysql 1073752660 Dec 27 08:13 mysql_binary_log.000014
   250213 -rw-r—–.  1 mysql mysql 1073753727 Dec 29 20:26 mysql_binary_log.000015
   250209 -rw-r—–.  1 mysql mysql 1073748868 Jan  1 10:14 mysql_binary_log.000016
   250214 -rw-r—–.  1 mysql mysql 1073742815 Jan  3 23:08 mysql_binary_log.000017
   250210 -rw-r—–.  1 mysql mysql 1073741937 Jan  6 12:41 mysql_binary_log.000018
   248925 -rw-r—–.  1 mysql mysql 1073755918 Jan  9 02:04 mysql_binary_log.000019
   250212 -rw-r—–.  1 mysql mysql 1073742596 Jan 11 15:31 mysql_binary_log.000020
   250216 -rw-r—–.  1 mysql mysql 1073753377 Jan 14 07:28 mysql_binary_log.000021
   250215 -rw-r—–.  1 mysql mysql 1073744978 Jan 17 01:35 mysql_binary_log.000022
   250218 -rw-r—–.  1 mysql mysql 1073745134 Jan 19 17:52 mysql_binary_log.000023
   250217 -rw-r—–.  1 mysql mysql 1073743661 Jan 22 07:41 mysql_binary_log.000024
   250219 -rw-r—–.  1 mysql mysql 1073746724 Jan 25 01:48 mysql_binary_log.000025
   250221 -rw-r—–.  1 mysql mysql 1073742123 Jan 28 15:29 mysql_binary_log.000026
   250220 -rw-r—–.  1 mysql mysql 1073741913 Feb  1 09:27 mysql_binary_log.000027
   250250 -rw-r—–.  1 mysql mysql 1073743471 Feb  5 22:58 mysql_binary_log.000028
   250252 -rw-r—–.  1 mysql mysql 1073742622 Feb 10 12:19 mysql_binary_log.000029
   250253 -rw-r—–.  1 mysql mysql 1073742499 Feb 15 00:23 mysql_binary_log.000030
   250251 -rw-r—–.  1 mysql mysql 1073742228 Feb 19 13:57 mysql_binary_log.000031
   250254 -rw-r—–.  1 mysql mysql 1073743350 Feb 24 03:03 mysql_binary_log.000032
   250258 -rw-r—–.  1 mysql mysql 1073742457 Feb 28 16:35 mysql_binary_log.000033
   250248 -rw-r—–.  1 mysql mysql 1073742544 Mar  3 16:43 mysql_binary_log.000034
   250259 -rw-r—–.  1 mysql mysql 1073752856 Mar  7 03:19 mysql_binary_log.000035
   250260 -rw-r—–.  1 mysql mysql 1073749612 Mar 10 11:15 mysql_binary_log.000036
   250264 -rw-r—–.  1 mysql mysql 1073743755 Mar 12 15:06 mysql_binary_log.000037
   250262 -rw-r—–.  1 mysql mysql 1073743529 Mar 14 19:12 mysql_binary_log.000038
   250267 -rw-r—–.  1 mysql mysql       1521 Mar 14 19:12 mysql_binary_log.index
   250261 -rw-r—–.  1 mysql mysql        264 Mar 15 02:03 gvwstate.dat
    81124 drwxr-x–x.  8 mysql mysql       4096 Mar 15 02:03 .
   104731 -rw-r—–.  1 mysql mysql        113 Mar 15 02:04 grastate.dat
   523764 -rw-r—–.  1 mysql mysql   12582912 Mar 15 17:52 ibtmp1
   113330 -rw-r—–.  1 mysql mysql   79691776 Mar 15 17:52 ibdata1
   114721 -rw-r—–.  1 mysql mysql  536870912 Mar 15 17:52 ib_logfile1
   113353 -rw-r—–.  1 mysql mysql  536870912 Mar 15 17:52 ib_logfile0
   114746 -rw-r—–.  1 mysql mysql  314574120 Mar 15 17:54 galera.cache
   250266 -rw-r—–.  1 mysql mysql  457420800 Mar 15 18:12 mysql_binary_log.000039
[root@mysql03 mysql]#

Once your retention is set accordingly, the space will be freed by mysqld:

[root@mysql03 mysql]# df -h
Filesystem                      Size  Used Avail Use% Mounted on
/dev/mapper/centos-root          41G   18G   23G  45% /
devtmpfs                        1.9G     0  1.9G   0% /dev
tmpfs                           1.9G     0  1.9G   0% /dev/shm
tmpfs                           1.9G  209M  1.7G  12% /run
tmpfs                           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/mapper/centos-home          20G   33M   20G   1% /home
/dev/sda1                       497M  298M  200M  60% /boot
tmpfs                           379M     0  379M   0% /run/user/155601104
nfs-c01.nix.mds.xyz:/n/mds.xyz  128G   11G  119G   8% /n/mds.xyz
[root@mysql03 mysql]#

Hope this helps!  

Thx,
SD


     
  Copyright © 2003 - 2013 Tom Kacperski (microdevsys.com). All rights reserved.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License