MariaDB Cluster 10.1 – Unable to Add Extra Node

clusteringmariadbmariadb-10.1MySQLredhat

I was installing a test mariadb cluster but I have a problem when I want to add an extra node. Both nodes use Red Hat Enterprise Linux Server release 7.3 (Maipo)

These were the following steps:

On first node 192.168.1.10. MariaDB-server 10.1.20 installed

1) #vi /etc/my.cnf.d/server.cnf

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.10,192.168.1.20"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# start added lines
wsrep_node_address='192.168.1.10'
wsrep_node_name='galera-db01'
wsrep_cluster_name='cluster-test'
wsrep_sst_method = rsync
# end added lines
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2

2) #galera_new_cluster. No errors.

3) #mysql -uroot -e "show status like 'wsrep_cluster%'"

+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id    | 1                                    |
| wsrep_cluster_size       | 1                                    |
| wsrep_cluster_state_uuid | 0f2debe9-fa32-11e7-9d57-5b2cd22cad06 |
| wsrep_cluster_status     | Primary                              |
+--------------------------+--------------------------------------+

4)
Next. I created a database with one table and one record added.

#mysql –uroot
MariaDB [(none)]> create database dbexample;
MariaDB [(none)]> use dbemaxple
MariaDB [(none)]> create table tblemp(name varchar(50));
MariaDB [(none)]> insert into tblemp values('John');

It looks like it’s working well on first node.

On second node (192.168.1.20). MariaDB-server 10.1.20 installed.

5) #vi /etc/my.cnf.d/server.cnf

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.10,192.168.1.20"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
# start added
wsrep_node_address='192.168.1.20'
wsrep_node_name='galera-db02'
wsrep_cluster_name='cluster-test'
wsrep_sst_method = rsync
# end added
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2

6) #service mysql start

Starting mysql (via systemctl):  Job for mariadb.service failed 
becausethe control process exited with error code. See "systemctl status 
mariadb.service" and "journalctl -xe" for details.
                                                       [FAILED]

7)#systemctl status mariadb.service -l

â mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; 
vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           ââmigrated-from-my.cnf-settings.conf
   Active: failed (Result: exit-code) since lun 2018-01-15 17:37:02 PET;  54s ago
  Process: 18195 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 18116 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 18114 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 18195 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

ene 15 17:37:01 cluster4.local mysqld[18195]: at gcomm/src/pc.cpp:connect():158
ene 15 17:37:01 cluster4.local mysqld[18195]: 2018-01-15 17:37:01 140448459192576 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208:  Failed to open backend connection: -110 (Connection timed out)
ene 15 17:37:01 cluster4.local mysqld[18195]: 2018-01-15 17:37:01 140448459192576 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1380: Failed to open channel 'cluster-test' at 'gcomm://192.168.1.10,192.168.1.20': -110 (Connection timed out)
ene 15 17:37:01 cluster4.local mysqld[18195]: 2018-01-15 17:37:01 140448459192576 [ERROR] WSREP: gcs connect failed: Connection timed out
ene 15 17:37:01 cluster4.local mysqld[18195]: 2018-01-15 17:37:01 140448459192576 [ERROR] WSREP: wsrep::connect(gcomm://192.168.1.10,192.168.1.20) failed: 7
ene 15 17:37:01 cluster4.local mysqld[18195]: 2018-01-15 17:37:01 140448459192576 [ERROR] Aborting
ene 15 17:37:02 cluster4.local systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
ene 15 17:37:02 cluster4.local systemd[1]: Failed to start MariaDB database server.
ene 15 17:37:02 cluster4.local systemd[1]: Unit mariadb.service entered failed state.
ene 15 17:37:02 cluster4.local systemd[1]: mariadb.service failed.

So the question is what is missing in order to add the second node?

Best Answer

It seems the error is related to the connection.

First, on server 2, Check can you ping(ICMP) server 1?

If you can't check your connection.

But if you can, It's related to Firewall restrict.

In MariaDB Galera Cluster these ports must be open on all servers.

22 (ssh) if you want to use rsync
3306 (MySQL)
4444 (Rsync/ SST)
4567 (Galera)
4568 (Galera IST)

Related Solutions

Mysql – wordpress site kills thesql

"Nov 11 09:05:14 localhost kernel: [ 5931.834129] Out of memory: Kill process 6761 (mysqld) score 233 or sacrifice child"

You are crossing system memory and then kernel is killing highest user of memory - in your case that'd be mysqld. Consider revising mysql global and per-thread variables in order to limit total usage under control. You may use this procedure to estimate MySQL memory usage or you might want to fill in here to check the usage details.

Also... Swapping is not good for MySQL, it's better to have swappiness set to 1-10 or 0 depending on your kernel version. Refer here.

MySQL Won’t Start After DB Import and Password Change – Fix Guide

I keep seeing permission denied errors in the log.

You didn't run the chown command recursively

Please do this

chown -R mysql:mysql ./path/to/rh-mysql56

I also see errors like

2016-06-30 14:14:48 9138 [ERROR] InnoDB: Tablespace open failed for '"mysql"."innodb_index_stats"', ignored.

When you installed mysql before, it created 5 InnoDB system tables (See my answers Cannot open table mysql/innodb_index_stats and InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6).

You need to recreate them.

First you need to delete the orphaned .frm files

cd ./path/to/rh-mysql56/mysql
rm -f innodb_index_stats.frm
rm -f innodb_table_stats.frm
rm -f slave_master_info.frm
rm -f slave_relay_log_info.frm
rm -f slave_worker_info.frm
rm -f innodb_index_stats.ibd
rm -f innodb_table_stats.ibd
rm -f slave_master_info.ibd
rm -f slave_relay_log_info.ibd
rm -f slave_worker_info.ibd

Then, you can recreate them with

USE mysql
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

Best Answer

Related Solutions

Mysql – wordpress site kills thesql

MySQL Won’t Start After DB Import and Password Change – Fix Guide

Related Question