1

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 <[email protected]>    |
| 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                                   |
+------------------------------+--------------------------------------+
Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer
7
  • Not sure if MariaDB 10.2 is just too different or something, but every set of these instructions I follow fail after the .cnf file update. The server never starts after that, and I'm not sure what's wrong...
    – Brian Leishman
    Jun 30, 2017 at 17:32
  • @BrianLeishman Let me know what version you are using, and I will check in two days and will keep you informed.
    – user356895
    Jun 30, 2017 at 17:34
  • 1
    @BrianLeishman no much difference between CentOS and RHEL, that should work on either, but let me try a bit and will let you know. If you could try that sooner, please come up with an update here and I would highly appreciate. BTW, thanks for giving a feedback.
    – user356895
    Jun 30, 2017 at 17:39
  • 1
    Okay I got it started. I found "Disabling SELinux" a possible solution. Now I have no idea if there are repercussions (because I have no clue what SELinux is), but it definitely started after disabling. access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/…
    – Brian Leishman
    Jun 30, 2017 at 17:58
  • 1
    @BrianLeishman well done, SELinux is an amazing Linux kernel security module which supports access control security policies and controls MAC (Mandatory Access Control) permissions and etc. Yes, your MariaDB and Galera would start working after disabling SELiunx, but I recommend everyone learns to use it since this is so important and vital. I'm working on a tutorial for SELinux, and I will share it here when it's out. I will try my best to come up with an update as soon as possible to tell you guys how to use Galera and SELinux together. BTW, thanks for your feedback, I do really appreciate.
    – user356895
    Jul 1, 2017 at 4:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .