[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

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>
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>
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

This entry was posted on Sunday, March 15th, 2020 at 7:15 pm and is filed under NIX Posts. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

You must be logged in to post a comment.

Thoughts and Scribbles | MicroDevSys.com