Mysql – MariaDB / MySQL galera cluster nodes won’t join

mariadbMySQLperconapercona-xtradb-cluster

I'm setting up a MariaDB Galera cluster and I can’t seem to get the nodes to join each other. They both start up without errors but never seem to join, the status is always disconnected:

MariaDB [(none)]> show status like 'wsrep%';
+--------------------------+----------------------+
| Variable_name            | Value                |
+--------------------------+----------------------+
| wsrep_cluster_conf_id    | 18446744073709551615 |
| wsrep_cluster_size       | 0                    |
| wsrep_cluster_state_uuid |                      |
| wsrep_cluster_status     | Disconnected         |
| wsrep_connected          | OFF                  |
| wsrep_local_index        | 18446744073709551615 |
| wsrep_provider_name      |                      |
| wsrep_provider_vendor    |                      |
| wsrep_provider_version   |                      |
| wsrep_ready              | ON                   |
+--------------------------+----------------------+

first node is started with: "sudo service mysql start —wsrep-new-cluster" (also tried passing in the cluster address just as —wsrep_cluster_address=gcomm://) as well as putting the empty address list in my.cnf

second node is started with: "sudo service mysql start" with cluster address in my.cnf as gcomm://172.16.56.130,172.16.56.131

no matter what the cluster status is always “Disconnected” and cluster size is 0.

I had some settings for SST to use xtrabackup, iptables firewall was on, etc but in order to try and find the issue I turned everything off and went with the most simple config & still the same result.

Here's the my.cnf

# MariaDB database server configuration file.

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
bind-address        = 0.0.0.0

# * Fine Tuning
max_connections     = 100
connect_timeout     = 5
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = 128
sort_buffer_size    = 4M
bulk_insert_buffer_size = 16M
tmp_table_size      = 32M
max_heap_table_size = 32M


# * MyISAM
myisam_recover          = BACKUP
key_buffer_size     = 128M
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M

# * Query Cache Configuration
query_cache_limit       = 128K
query_cache_size        = 64M
log_warnings        = 2
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity  = query_plan
log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
expire_logs_days    = 10
max_binlog_size         = 100M

# * InnoDB
innodb_buffer_pool_size = 1G
#innodb_log_file_size = 100M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT


[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M


# MariaDB / Galera Cluster Settings
wsrep_provider         = /usr/lib/galera/libgalera_smm.so
wsrep_provider_options = "gcache.size=32G"
wsrep_cluster_address  = "gcomm://172.16.56.130,172.16.56.131"
wsrep_cluster_name     = 'my_galera_cluster'
wsrep_node_address     = '172.16.56.131'
wsrep_node_name        = 'lou-dev-sc-db2'
wsrep_sst_method       = xtrabackup
wsrep_sst_auth         = root:rootPa$$
wsrep_slave_threads    = 16

binlog_format                  = ROW
default_storage_engine         = InnoDB
innodb_autoinc_lock_mode       = 2
innodb_locks_unsafe_for_binlog = 1
innodb_flush_log_at_trx_commit = 2

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
!includedir /etc/mysql/conf.d/

I also realize that the xtrabackup needs access rights made available so I have also made a GRANT entry for that user from the remote servers and tested via manual login, still no dice.

Any ideas?

Best Answer

From the show status output your servers don't load any wsrep provider (Galera), and probably don't even attempt to. As little as I understand in how mysql configuration file works, I think you need to put [mysqld] before MariaDB/Galera settings (or put them immediately after InnoDB settings). Otherwise they seem to be in the [isamcheck] section and simply are not parsed by the server.

Related Topic