MariaDB Galera Cluster Node cannot start after going down

clustergaleramariadb

My Environtment :

Two Nodes – CentOS 6.4 x86_64

Node1 : 10.10.201.3

Node2 : 10.10.201.4
MariaDB-server-10.2.0-1.el6.x86_64

Both nodes are running normally, but after restarting mysql on Node1, it'll be failed to start again whilst mysql on Node2 is continuing running without problem.

Configuration on Node1 :

#/etc/my.cnf.d/server.cnf

node1
bind-address=10.10.201.3
datadir=/opt/mysql
socket=/opt/mysql/mysql.sock
handlersocket_address="10.10.201.3"
handlersocket_port="9998"
handlersocket_port_wr="9999"
open_files_limit = 25600
log-error=/opt/mysql/log/mysqld.log
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.201.4,10.10.201.3"
wsrep_cluster_name='galera_cluster_www'
wsrep_node_address='10.10.201.3'
wsrep_node_name='www-node1'
wsrep_sst_method=rsync
wsrep_sst_auth=sst_user:password
wsrep-slave-threads=16
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_log_conflicts=ON
wsrep_provider_options="cert.log_conflicts=ON"
wsrep_debug=ON
wsrep_max_ws_size = 2G
binlog_row_image = minimal

Configuration on Node2 :

#/etc/my.cnf.d/server.cnf

node2
bind-address=10.10.201.4
datadir=/opt/mysql
socket=/opt/mysql/mysql.sock
handlersocket_address="10.10.201.4"
handlersocket_port="9998"
handlersocket_port_wr="9999"
open_files_limit = 25600
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.201.3,10.10.201.4"
wsrep_cluster_name='galera_cluster_www'
wsrep_node_address='10.10.201.4'
wsrep_node_name='www-node2'
wsrep_sst_method=rsync
wsrep_sst_auth=sst_user:password
wsrep-slave-threads=16
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_log_conflicts=ON
wsrep_provider_options="cert.log_conflicts=ON"
wsrep_debug=ON
wsrep_max_ws_size = 2G
binlog_row_image = minimal

And finally, Cluster Information on Node2 after failing mysql on the first node (Node1) :

MariaDB [(none)]> show status like 'wsrep%';

+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_apply_oooe             | 0.017353                             |
| wsrep_apply_oool             | 0.000050                             |
| wsrep_apply_window           | 1.021550                             |
| wsrep_causal_reads           | 0                                    |
| wsrep_cert_deps_distance     | 24.564685                            |
| wsrep_cert_index_size        | 48                                   |
| wsrep_cert_interval          | 0.021750                             |
| wsrep_cluster_conf_id        | 69                                   |
| wsrep_cluster_size           | 1                                    |
| wsrep_cluster_state_uuid     | c07f825f-132f-11e6-b219-d7e841605104 |
| wsrep_cluster_status         | Primary                              |
| wsrep_commit_oooe            | 0.000000                             |
| wsrep_commit_oool            | 0.000034                             |
| wsrep_commit_window          | 1.005403                             |
| wsrep_connected              | ON                                   |
| wsrep_evs_delayed            |                                      |
| wsrep_evs_evict_list         |                                      |
| wsrep_evs_repl_latency       | 0/0/0/0/0                            |
| wsrep_evs_state              | OPERATIONAL                          |
| wsrep_flow_control_paused    | 0.000000                             |
| wsrep_flow_control_paused_ns | 0                                    |
| wsrep_flow_control_recv      | 0                                    |
| wsrep_flow_control_sent      | 0                                    |
| wsrep_gcomm_uuid             | 401f6755-71da-11e6-8244-9e88079ed6c4 |
| wsrep_incoming_addresses     | 10.10.201.4:3306                     |
| wsrep_last_committed         | 2364263                              |
| wsrep_local_bf_aborts        | 116                                  |
| wsrep_local_cached_downto    | 2221069                              |
| wsrep_local_cert_failures    | 23                                   |
| wsrep_local_commits          | 729390                               |
| wsrep_local_index            | 0                                    |
| wsrep_local_recv_queue       | 0                                    |
| wsrep_local_recv_queue_avg   | 0.004725                             |
| wsrep_local_recv_queue_max   | 6                                    |
| wsrep_local_recv_queue_min   | 0                                    |
| wsrep_local_replays          | 112                                  |
| wsrep_local_send_queue       | 0                                    |
| wsrep_local_send_queue_avg   | 0.000335                             |
| wsrep_local_send_queue_max   | 2                                    |
| wsrep_local_send_queue_min   | 0                                    |
| wsrep_local_state            | 4                                    |
| wsrep_local_state_comment    | Synced                               |
| wsrep_local_state_uuid       | c07f825f-132f-11e6-b219-d7e841605104 |
| wsrep_protocol_version       | 7                                    |
| wsrep_provider_name          | Galera                               |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>    |
| wsrep_provider_version       | 25.3.15(r3578)                       |
| wsrep_ready                  | ON                                   |
| wsrep_received               | 1376816                              |
| wsrep_received_bytes         | 630752657                            |
| wsrep_repl_data_bytes        | 303429595                            |
| wsrep_repl_keys              | 3039261                              |
| wsrep_repl_keys_bytes        | 41097380                             |
| wsrep_repl_other_bytes       | 0                                    |
| wsrep_replicated             | 729452                               |
| wsrep_replicated_bytes       | 391211903                            |
| wsrep_thread_count           | 17                                   |
+------------------------------+--------------------------------------+

Best Answer

I had the same problem, and finally after fixing the issue ( on CentOS 7 - MariaDB-server-10.2.0-1 ), I wrote a documentation on how to set it up correctly and I hope it will fix yours too. Follow the instructions below and try to build your Galera nodes from scratch. Notice that I will just use the mandatory configuration, you can add yours to it later. I guess you have missed the fifth step or you haven't done it correctly. Anyway, I will write all the steps so anyone else can find it easier to follow.

The problem arises when you do not use the galera_new_cluster command on the master node, and you do not use the appropriate address for wsrep_cluster_address - gcomm. So when the master fails, other nodes cannot come back to the peer. (not even the master can come back in cluster)

Consider 3 servers named GLR{1,2,3} and we are going to setup Galera Cluster on each. - I will explain how to avoid failure on two-node cluster in the seventh step.

STEP 1

For installation:

Open /etc/yum.repos.d/mariadb.repo with your favourite editor and add the following lines into it:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

STEP 2

If you do not know how to manage/configure SELinux, set it to permissive mode and check your log files after finishing the installation to do the required steps for managing it. You might also be in need of having setroubleshoot-server and setools-console packages installed to better understand your SELinux logs.

But if you have SELinux enabled and do not want to set it to permissive mode, you should note that it may block mysqld from carrying out required operations. So you should configure it to allow mysqld to run external programs and open listen sockets on unprivileged ports—that is, things that an unprivileged user can do.

Teaching how to manage SELinux is beyond the scope of this answer, but you can set it in permissive mode only for mysql requests by doing the following command:

semanage permissive -a mysql_t

STEP 3

After installation using yum, Add the following lines to the end of /etc/my.cnf.d/server.cnf as shown below on each GLR server respectively:

[GLR1] ↴

$ vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://{GLR1 IP},{GLR2 IP},{GLR3 IP}'
wsrep_cluster_name='galera'
wsrep_node_address='{GLR1 IP}'
wsrep_node_name='GLR1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

[GLR2] ↴

$ vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://{GLR1 IP},{GLR2 IP},{GLR3 IP}'
wsrep_cluster_name='galera'
wsrep_node_address='{GLR2 IP}'
wsrep_node_name='GLR2'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

[GLR3] ↴

$ vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://{GLR1 IP},{GLR2 IP},{GLR3 IP}'
wsrep_cluster_name='galera'
wsrep_node_address='{GLR3 IP}'
wsrep_node_name='GLR3'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

STEP 4

Reboot all servers.

STEP 5

Use the following command on GLR1 only, and then restart mariadb.service on GLR2 and GLR3:

$ galera_new_cluster
$ sevice mysql start

STEP 6

As you noticed in your question, you can test connectivity between servers by using the following command:

$ mysql -u root -p -e "SHOW STATUS LIKE 'wsrep%'"

Or just check the cluster size:

$ mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';"

STEP 7

On the other hand, after finishing all the steps above, you can use this Article provided by galeracluster website on how to a avoid a single-node failure causing the other to stop working if you wanna use a TWO-NODE cluster.

There are two solutions available to you:

You can bootstrap the surviving node to form a new Primary Component, using the pc.boostrap wsrep Provider option. To do so, log into the database client and run the following command:

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

This bootstraps the surviving node as a new Primary Component. When the other node comes back online or regains network connectivity with this node, it will initiate a state transfer and catch up with this node.

In the event that you want the node to continue to operate, you can use the pc.ignore_sb wsrep Provider option. To do so, log into the database client and run the following command:

SET GLOBAL wsrep_provider_options='pc.ignore_sb=TRUE';

The node resumes processing updates and it will continue to do so, even in the event that it suspects a split-brain situation.

Note Warning: Enabling pc.ignore_sb is dangerous in a multi-master setup, due to the aforementioned risk for split-brain situations. However, it does simplify things in master-slave clusters, (especially in cases where you only use two nodes).

In addition to the solutions provided above, you can avoid the situation entirely using Galera Arbitrator. Galera Arbitrator functions as an odd node in quorum calculations. Meaning that, if you enable Galera Arbitrator on one node in a two-node cluster, that node remains the Primary Component, even if the other node fails or loses network connectivity.

Related Solutions

Debian autostart MariaDB Galera cluster

I've struggled with this on Debian 8 and have found that the init.d script ignores the --wsrep-new-cluster option.

I was able to start the first cluster node manually with /usr/bin/mysqld_safe --wsrep-new-cluster. The second can then be started with the normal init script `service mysql start'. Once a second node is up, the mysqld_safe process can be stopped and the first node can be started normally.

MariaDB Galera Cluster, Force Sync

I was not able to find an official way to handle this, so I went with the idea of dumping the tables individually and reimporting them. Not wanting to do it by hand, I whipped a PHP script to do it for me. I'm posting it here in case anyone else finds this useful.

/*
 * InnoDB Convert
 * Converts existing non-InnoDB tables to InnoDB, then re-imports the
 * data so that it's replicated across the cluster.
 */

// Configuration
    $_config['db'] = array(
        'type'     => 'mysql',
        'host'     => 'localhost',
        'username' => 'user',
        'password' => 'password'
    );

// Establish database connection
    try {
        $pdo = new PDO(
            $_config['db']['type'] . ':host=' . $_config['db']['host'],
            $_config['db']['username'],
            $_config['db']['password']
        );
    } catch ( PDOException $e ) {
        echo 'Connection failed: ' . $e->getMessage();
    }

// Get list of databases
    $db_query = <<<SQL
  SHOW DATABASES
SQL;
    $db_result = $pdo->prepare( $db_query );
    $db_result->execute();

    while ( $db_row = $db_result->fetch( PDO::FETCH_ASSOC )) {
    // Look through databases, but ignores the ones that come with a
    // MySQL install and shouldn't be part of the cluster
        if ( !in_array( $db_row['Database'], array( 'information_schema', 'mysql', 'performance_schema', 'testdb' ))) {
            $pdo->exec( "USE {$db_row['Database']}" );

            $table_query = <<<SQL
  SHOW TABLES
SQL;
            $table_result = $pdo->prepare( $table_query );
            $table_result->execute();

            while ( $table_row = $table_result->fetch( PDO::FETCH_ASSOC )) {
            // Loop through all tables
                $table = $table_row["Tables_in_{$db_row['Database']}"];

                $engine_query = <<<SQL
  SHOW TABLE STATUS WHERE Name = :table
SQL;
                $engine_result = $pdo->prepare( $engine_query );
                $engine_result->execute( array(
                    ':table' => $table
                ));

                $engine_row = $engine_result->fetch( PDO::FETCH_ASSOC );

                if ( $engine_row['Engine'] != 'InnoDB' ) {
                // Engine is not equal to InnoDB, let's convert it
                    echo "Converting '$table' on '{$db_row['Database']}' from '{$engine_row['Engine']}' to InnoDB:\n";

                    echo     "Modifying engine...";

                    $change_query = <<<SQL
  ALTER TABLE $table ENGINE=InnoDB
SQL;
                    $change_result = $pdo->prepare( $change_query );
                    $change_result->execute();

                    echo "done!\n";

                    echo "    Exporting table...";

                    exec( "mysqldump -h {$_config['db']['host']} -u {$_config['db']['username']} -p{$_config['db']['password']} {$db_row['Database']} $table > /tmp/dump-file.sql" );

                    echo "done!\n";

                    echo "    Re-importing table...";

                    exec( "mysql -h {$_config['db']['host']} -u {$_config['db']['username']} -p{$_config['db']['password']} {$db_row['Database']} < /tmp/dump-file.sql" );

                    echo "done!\n";

                    unlink( '/tmp/dump-file.sql' );

                    echo "done!\n";
                }
            }
        }
    }

I successfully used it to convert hundreds of tables across a couple dozen databases in about two minutes.