5

How to restart MariaDB Galera cluster?

After all node have been crashed I try to recover the cluster but without success. I have only 2 nodes.

As documentation says I set a parameter on one of the node:

set global wsrep_provider_options="pc.bootstrap=true";

And then try to start first node:

systemctl start mariadb

After that I get an error:

Oct 11 16:11:12 proxy1 sh[2367]: 2016-10-11 16:11:12 140291677038720 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2402 ...
Oct 11 16:11:15 proxy1 sh[2367]: WSREP: Recovered position b6c1dc93-8fa7-11e6-933e-e64cd44e3be0:141
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2434 ...
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Read nil XID from storage engines, skipping position init
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so'
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: wsrep_load(): Galera 25.3.18(r3632) by Codership Oy <info@codership.com> loaded successfully.
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: CRC-32C: using hardware acceleration.
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Found saved state: b6c1dc93-8fa7-11e6-933e-e64cd44e3be0:-1
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.0.41; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false;
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140046790919936 [Note] WSREP: Service thread queue flushed.
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Assign initial position for certification: 141, protocol version: -1
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: wsrep_sst_grab()
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Start replication
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Setting initial position to b6c1dc93-8fa7-11e6-933e-e64cd44e3be0:141
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: protonet asio version 0
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: Using CRC-32C for message checksums.
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: backend: asio
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: gcomm thread scheduling priority set to other:0
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory)
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: restore pc from disk failed
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: GMCast version 0
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: EVS version 0
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: gcomm: connecting to group 'test_cluster', peer '192.168.0.41:,192.168.0.42:'
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') connection established to 30a7b2e6 tcp://192.168.0.41:4567
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Warning] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') address 'tcp://192.168.0.41:4567' points to own listening address, blacklisting
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') connection established to 30a7b2e6 tcp://192.168.0.41:4567
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') connection established to 1ef15511 tcp://192.168.0.42:4567
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: declaring 1ef15511 at tcp://192.168.0.42:4567 stable
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Warning] WSREP: no nodes coming from prim view, prim not possible
Oct 11 16:11:15 proxy1 mysqld[2434]: 2016-10-11 16:11:15 140047023368320 [Note] WSREP: view(view_id(NON_PRIM,1ef15511,2) memb {
Oct 11 16:11:15 proxy1 mysqld[2434]: 1ef15511,0
Oct 11 16:11:15 proxy1 mysqld[2434]: 30a7b2e6,0
Oct 11 16:11:15 proxy1 mysqld[2434]: } joined {
Oct 11 16:11:15 proxy1 mysqld[2434]: } left {
Oct 11 16:11:15 proxy1 mysqld[2434]: } partitioned {
Oct 11 16:11:15 proxy1 mysqld[2434]: })
Oct 11 16:11:18 proxy1 mysqld[2434]: 2016-10-11 16:11:18 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting off
Oct 11 16:11:19 proxy1 mysqld[2434]: 2016-10-11 16:11:19 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.0.42:4567
Oct 11 16:11:20 proxy1 mysqld[2434]: 2016-10-11 16:11:20 140047023368320 [Note] WSREP: forgetting 1ef15511 (tcp://192.168.0.42:4567)
Oct 11 16:11:20 proxy1 mysqld[2434]: 2016-10-11 16:11:20 140047023368320 [Note] WSREP: (30a7b2e6, 'tcp://0.0.0.0:4567') turning message relay requesting off
Oct 11 16:11:20 proxy1 mysqld[2434]: 2016-10-11 16:11:20 140047023368320 [Warning] WSREP: no nodes coming from prim view, prim not possible
Oct 11 16:11:20 proxy1 mysqld[2434]: 2016-10-11 16:11:20 140047023368320 [Note] WSREP: view(view_id(NON_PRIM,30a7b2e6,3) memb {
Oct 11 16:11:20 proxy1 mysqld[2434]: 30a7b2e6,0
Oct 11 16:11:20 proxy1 mysqld[2434]: } joined {
Oct 11 16:11:20 proxy1 mysqld[2434]: } left {
Oct 11 16:11:20 proxy1 mysqld[2434]: } partitioned {
Oct 11 16:11:20 proxy1 mysqld[2434]: 1ef15511,0
Oct 11 16:11:20 proxy1 mysqld[2434]: })
Oct 11 16:11:25 proxy1 mysqld[2434]: 2016-10-11 16:11:25 140047023368320 [Note] WSREP:  cleaning up 1ef15511 (tcp://192.168.0.42:4567)
Oct 11 16:11:46 proxy1 mysqld[2434]: 2016-10-11 16:11:46 140047023368320 [Note] WSREP: view((empty))
Oct 11 16:11:46 proxy1 mysqld[2434]: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
Oct 11 16:11:46 proxy1 mysqld[2434]: at gcomm/src/pc.cpp:connect():162
Oct 11 16:11:46 proxy1 mysqld[2434]: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
Oct 11 16:11:46 proxy1 mysqld[2434]: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1380: Failed to open channel 'test_cluster' at 'gcomm://192.168.0.41,192.168.0.42': -110 (Connection timed out)
Oct 11 16:11:46 proxy1 mysqld[2434]: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: gcs connect failed: Connection timed out
Oct 11 16:11:46 proxy1 mysqld[2434]: 2016-10-11 16:11:46 140047023368320 [ERROR] WSREP: wsrep::connect(gcomm://192.168.0.41,192.168.0.42) failed: 7
Oct 11 16:11:46 proxy1 mysqld[2434]: 2016-10-11 16:11:46 140047023368320 [ERROR] Aborting
Oct 11 16:11:47 proxy1 systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Oct 11 16:11:47 proxy1 systemd[1]: Failed to start MariaDB database server.
-- Subject: Unit mariadb.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mariadb.service has failed.
-- 
-- The result is failed.
Oct 11 16:11:47 proxy1 systemd[1]: Unit mariadb.service entered failed state.
Oct 11 16:11:47 proxy1 systemd[1]: mariadb.service failed.
Oct 11 16:11:47 proxy1 polkitd[570]: Unregistered Authentication Agent for unix-process:2360:148848 (system bus name :1.15, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) (disconnected from bus)

How to recover a cluster?

Improve this question
2
  • Hi, did you ever figure this out?
    – Jonathan
    Mar 26, 2017 at 7:40
  • Hi, see the answer
    – Oleksandr
    Mar 26, 2017 at 9:50

2 Answers 2

Reset to default
14

MariaDB Galera Cluster:
Solution 1:
1) I've changed safe_to_bootstrap parameter to 1 on one of the node in the file /var/lib/mysql/grastate.dat:

safe_to_bootstrap: 1

2) After that I killed all mysql processes:

killall -KILL mysql mysqld_safe mysqld mysql-systemd

3) And started a new cluster:

galera_new_cluster

4) All other nodes I reconnected to the new one:

systemctl restart mariadb

P.S. to install killall on CentOS use psmisc:

sudo yum install psmisc

Solution 2:
Another way to restart a MariaDB Galera Cluster is to use --wsrep-new-cluster parameter.

1) Kill all mysql processes:

killall -KILL mysql mysqld_safe mysqld mysql-systemd

2) On the most up to date node start a new cluster:

/etc/init.d/mysql start --wsrep-new-cluster

3) Now other nodes can be connected:

service mysql start --wsrep_cluster_address="gcomm://192.168.0.101,192.168.0.102,192.168.0.103" \
--wsrep_cluster_name="my_cluster"

Percona XtraDB Cluster:
Solution 1:
In case you can connect to the most up to date node then you can setup the node to bootstrap with the next SQL:

SET GLOBAL wsrep_provider_options='pc.bootstrap=true';

Solution 2:
In case if all your nodes are dead and can not be started, you can stop the old one cluster and run a new one. You must stop all the cluster nodes because they have an information about old nodes in the old cluster.

1) Kill all the mysql processes on all nodes:

killall -KILL mysql mysqld_safe mysqld mysql-systemd

2) Start a new cluster on the most up to date node:

systemctl start mysql@bootstrap.service

3) Start other nodes:

systemctl start mysql
Improve this answer
1
  • Thanks! Eventually went with --wsrep-new-cluster since I can pass it as a command to percona's docker container.
    – Jonathan
    Mar 27, 2017 at 10:16
0

  1. You just need to check MariaDB is activating or deactivating mode or not using below comamnd

    systemctl status mariadb

  2. If you are unable to stop MariaDB then kill process of MariaDB

     ps -aux | grep -i mariadb
 kill -9 process_id

  3. If MariaDB not stopped yet, then you need to shutdown and start again. (Sometimes reboot not work in this case).

  4. After that you have to set safe_to_bootstrap=1 in /var/lib/mysql/grastate.dat on one of your nodes.

  5. Next run below Galera command on that node

    galera_new_cluster

  6. Now you can check status of your node using below command.

    systemctl status mariadb

  7. If MariaDB is running now you can able to login using below command

    mysql -u root -p

  8. Next you check the Galera cluster size after login to your 1st node. You will see cluster size is 1. Because other node also not running because of that issue.

    show status like 'wsrep_cluster_size';

  9. Next start other node2 and node3 using below command

    systemctl start mariadb

  10. Now if you check from any node you will see cluster size is 3.

    show status like 'wsrep_cluster_size';

I think these steps will solve your problem.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.