CentOS 6.7 Linux compilation configure a multi-master MariaDB Galera Cluster for HA.

Multi-master MariaDB Galera Cluster

Summary
Galera is essentially a wsrep provider (provider), relies on the wsrep API interface. Wsrep API defines a set of applications and replication callback call library to implement transaction database synchronization write set (writeset) replication, and similar applications. The purpose is to achieve the abstract, isolated copied from the application details. While the main objective of the interface is based on the certification of multi-master replication, but the same applies to asynchronous and synchronous master-slave replication.
Personal feeling MariaDB Galera across the room to do a multi-master is still very convenient, than direct shots from good use MySQL. Like with other clusters, in order to avoid node split brain and destroy data, we recommend a minimum Galera Cluster add three nodes. Galera Cluster in the case of high concurrency, may occur when multiple simultaneous write transaction main conflict, this time only one transaction request will be successful, all others failed. You can write / update fails, the automatic retry, and then returns the result. Node status of each node is equal, there is no primary and secondary, to read any one node effect is the same. Can actually fit VIP / LVS or HA uses to achieve high availability. If all the machines in the cluster restart, such as room off, the first start of the server must be started with an empty address.
Galera Cluster is a set of synchronous multi-master MySQL replication cluster solution, easy to use, there is no single point of failure, high availability, can be very good at all times to ensure the safety and the expansion of our data when business is growing.
First, compile and install MariaDB Galera Here to talk about multi-master cluster

cluster1.rmohan.com       Centos6.7   192.168.1.60    MariaDB
cluster2.rmohan.com       Centos6.7   192.168.1.62    MariaDB
cluster3.rmohan.com       Centos6.7   192.168.1.63    MariaDB

Hereinafter, if the command is not preceded by PS1 is to represent the three nodes are required to perform the same action, if it means the implementation of the action on the specified node

1, install ntpdate to set timing on node cluster1 cluster2 cluster3 each other synchronization and ssh

yum install -y ntpdate
yum install openssh-askpass -y
yum -y install openssh-client

echo “*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1″ >> /var/spool/cron/root

/usr/sbin/ntpdate pool.ntp.org

cat >>/etc/hosts<< EOF
192.168.1.60  cluster1.rmohan.com  cluster1
192.168.1.62  cluster2.rmohan.com  cluster2
192.168.1.63  cluster3.rmohan.com  cluster3
EOF

Here is the production of a key ###

ssh-keygen  -t rsa -f ~/.ssh/id_rsa  -P ”
### The following are performed on each node, so that the public key of the machine onto the other nodes
awk ‘{if ($0!~/'”$(hostname)”‘|localhost/)print $NF}’ /etc/hosts |xargs -i ssh-copy-id -i ~/.ssh/id_rsa.pub root@{}

2, installed base build environment

Add EPEL Repository which is provided from Fedora project.
yum -y install epel-release
# set [priority=5]

sed -i -e “s/\]$/\]\npriority=5/g” /etc/yum.repos.d/epel.repo
# for another way, change to [enabled=0] and use it only when needed
sed -i -e “s/enabled=1/enabled=0/g” /etc/yum.repos.d/epel.repo
# if [enabled=0], input a command to use the repository
yum –enablerepo=epel install [Package]

Add RPMforge Repository which provides many useful packages.

yum -y install http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
# set [priority=10]
sed -i -e “s/\]$/\]\npriority=10/g” /etc/yum.repos.d/rpmforge.repo
# for another way, change to [enabled=0] and use it only when needed
sed -i -e “s/enabled = 1/enabled = 0/g” /etc/yum.repos.d/rpmforge.repo
# if [enabled=0], input a command to use the repository
yum –enablerepo=rpmforge install [Package]

rpm -ivh http://mirrors.aliyun.com/epel/epel-release-latest-6.noarch.rpm
rpm –import http://mirrors.dwhd.org/epel/RPM-GPG-KEY-EPEL-6
rpm -ivh http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
rpm –import http://mirrors.dwhd.org/repoforge/RPM-GPG-KEY.dag.txt

yum clean all && yum makecache
yum groupinstall “Development tools” “Server Platform Development” -y
yum install libxml2-devel lz4 lz4-devel libpcap nmap lsof socat -y

3, Node1 compile install MariaDB Galera

### Download Source Package cmake
[root@cluster1 ~]# wget http://cmake.org/files/v3.4/cmake-3.4.0-rc1.tar.gz
### Download mariadb-galera source package
[root@cluster1 ~]#  wget http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/source/mariadb-galera-10.0.21.tar.gz
### Download Source Package kytea
[root@cluster1 ~]# wget http://www.phontron.com/kytea/download/kytea-0.4.7.tar.gz
### Will cmake, mariadb-galera, kytea Source Package spread to other nodes

[root@cluster1 ~]#  awk ‘{if ($0!~/'”$(hostname)”‘|localhost/)print $NF}’ /etc/hosts |xargs -i scp cmake-3.4.0-rc1.tar.gz mariadb-galera-10.0.21.tar.gz kytea-0.4.7.tar.gz root@{}:/root

3, Cluster1 compile install MariaDB Galera

### Compile and install cmake
[root@cluster1 ~]# tar xf cmake-3.4.0-rc1.tar.gz
[Root @ cluster1 ~] # cd cmake-3.4.0-rc1
[root@cluster1 cmake-3.4.0-rc1]# ./bootstrap
[root@cluster1 cmake-3.4.0-rc1]# make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ../
### Compiler installation kytea
[root@cluster1 ~]# tar xf kytea-0.4.7.tar.gz
[root@cluster1 ~]# cd kytea-0.4.7/
[root@cluster1 ~/kytea-0.4.7]# ./configure && make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ..
### Installation Galera
[root@cluster1 ~]# rpm –import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
[root@cluster1 ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
### Compile and install MariaDB Galera
[root@cluster1 ~]# mkdir -p /data/mariadb-galera-10.0.21
[root@cluster1 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21
[root@cluster1 ~]# tar -xvf mariadb-galera-10.0.21.tar.gz
[root@cluster1 ~]# cd mariadb-10.0.21/
[root@cluster1 ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \
-DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \
-DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \
-DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \
-DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1
[root@cluster1 ~/mariadb-10.0.21]# make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ..
[root@cluster1 ~]#

4, cluster2 compile install MariaDB Galera
### Compile and install cmake
[root@cluster2 ~]# tar xf cmake-3.4.0-rc1.tar.gz
[Root @ cluster2 ~] # cd cmake-3.4.0-rc1
[root@cluster2 cmake-3.4.0-rc1]# ./bootstrap
[root@cluster2 cmake-3.4.0-rc1]# make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ../
### Compiler installation kytea
[root@cluster2 ~]# tar xf kytea-0.4.7.tar.gz
[root@cluster2 ~]# cd kytea-0.4.7/
[root@cluster2 ~/kytea-0.4.7]# ./configure && make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ..
### Installation Galera
[root@cluster2 ~]# rpm –import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
[root@cluster2 ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
### Compile and install MariaDB Galera
[root@cluster2 ~]# mkdir -p /data/mariadb-galera-10.0.21
[root@cluster2 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21
[root@cluster2 ~]# tar -xvf mariadb-galera-10.0.21.tar.gz
[root@cluster2 ~]# cd mariadb-10.0.21/
[root@cluster2 ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \
-DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \
-DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \
-DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \
-DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1
[root@cluster2 ~/mariadb-10.0.21]# make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ..
[root@cluster2 ~]#

5, Cluster3 compile install MariaDB Galera

### Compile and install cmake
[root@cluster3 ~]# tar xf cmake-3.4.0-rc1.tar.gz
[Root @ cluster3 ~] # cd cmake-3.4.0-rc1
[root@cluster3 cmake-3.4.0-rc1]# ./bootstrap
[root@cluster3 cmake-3.4.0-rc1]# make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ../
### Compiler installation kytea
[root@cluster3 ~]# tar xf kytea-0.4.7.tar.gz
[root@cluster3 ~]# cd kytea-0.4.7/
[root@cluster3 ~/kytea-0.4.7]# ./configure && make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ..
### Installation Galera
[root@cluster3 ~]# rpm –import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
[root@cluster3 ~]# rpm -ivh http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
http://mirrors.dwhd.org/SQL/MariaDB/mariadb-galera-10.0.21/galera-25.3.9/rpm/galera-25.3.9-1.rhel6.el6.x86_64.rpm
### Compile and install MariaDB Galera
[root@cluster3 ~]# mkdir -p /data/mariadb-galera-10.0.21
[root@cluster3 ~]# chown -R mysql.mysql /data/mariadb-galera-10.0.21
[root@cluster3 ~]# tar -xvf mariadb-galera-10.0.21.tar.gz
[root@cluster3 ~]# cd mariadb-10.0.21/
[root@cluster3 ~/mariadb-10.0.21]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb \
-DMYSQL_DATADIR=/data/mariadb-galera-10.0.21 \
-DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_SPHINX_STORAGE_ENGINE=1 -DWITH_ARIA_STORAGE_ENGINE=1 \
-DWITH_XTRADB_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATEDX_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all \
-DWITH_EMBEDDED_SERVER=1 -DWITH_READLINE=1 -DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_WSREP=1 -DWITH_INNODB_DISALLOW_WRITES=1
[root@cluster3 ~/mariadb-10.0.21]# make -j $(awk ‘/processor/{i++}END{print i}’ /proc/cpuinfo) && make install && cd ..
[root@cluster3 ~]#

6, configure all nodes startup scripts, environment variables on

cd /usr/local/mariadb/
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
cp /usr/local/mariadb/support-files/my-large.cnf /etc/my.cnf
sed -i ‘/query_cache_size/a datadir = /data/mariadb-galera-10.0.21’ /etc/my.cnf
echo “export PATH=/usr/local/mariadb/bin:\$PATH” > /etc/profile.d/mariadb_galera.sh
source /etc/profile.d/mariadb_galera.sh
sed -i “$(awk ‘$1==”MANPATH”{i=NR}END{print i}’ /etc/man.config)a \MANPATH\tMANPATH /usr/local/mariadb/man” /etc/man.config

7, Configuration node Cluster1
[root@cluster1 profile.d]# cd /usr/local/mariadb
[root@cluster1 mariadb]# /usr/local/mariadb/scripts/mysql_install_db –user=mysql –datadir=/data/mariadb-galera-10.0.21/
[root@cluster1 mariadb]# cd && service mysqld start
Starting MySQL.. SUCCESS!
[root@cluster1 ~]# ss -tnl | grep :3306
LISTEN     0      128                      :::3306                    :::*
[root@cluster1  ~]# mysql -uroot -p <<< “USE mysql;
update user set password=PASSWORD(‘test123′) WHERE USER=’root’;
DELETE FROM user WHERE User=”;
GRANT ALL PRIVILEGES ON *.* TO ‘cluster’@’%’ IDENTIFIED BY ‘test123’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT USER,PASSWORD,HOST FROM user;”
Enter password: #### Note that the password is blank, just press Enter enough
USER    PASSWORD        HOST
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       localhost
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       cluster1.rmohan.com
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       127.0.0.1
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       ::1
cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C        %
[root@cluster1 ~]#

[root@cluster1~]# service mysqld stop
Shutting down MySQL… SUCCESS!
[root@cluster1~]# chkconfig mysqld on
[root@cluster1 ~]# sed -i ‘/binlog_format/d’ /etc/my.cnf
[root@cluster1 ~]# sed -i ‘/log_bin/d’ /etc/my.cnf
[root@cluster1 ~]# sed -i ‘/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\
default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\
wsrep_cluster_address = “gcomm://”\nwsrep_cluster_name = LegionMariadbGaleraCluster1\nwsrep_node_address = node1\
wsrep_sst_method = rsync\nwsrep_sst_auth = cluster:test123’ /etc/my.cnf
[root@cluster1 ~]# service  mysqld start
Starting MySQL.. SUCCESS!
[root@cluster1 ~]# ss -tnl | grep -E ‘:(3306|4567)’
LISTEN     0      128                      :::3306                    :::*
LISTEN     0      128                       *:4567                     *:*

vi /etc/my.cnf

[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
datadir = /data/mariadb-galera-10.0.21/
query_cache_size = 0
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = “gcomm://cluster1,cluster2,cluster3”
wsrep_cluster_name = LegionMariadbGaleraCluster1
wsrep_node_address = 192.168.1.60
wsrep_sst_method = rsync
wsrep_sst_auth = cluster1:test123
query_cache_size = 0
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = “gcomm://cluster1,cluster2,cluster3”
wsrep_cluster_name = LegionMariadbGaleraCluster1
wsrep_node_address=’192.168.1.60′
wsrep_node_name=’cluster1′
wsrep_sst_method=rsync
wsrep_sst_auth= cluster:lookback
thread_concurrency = 8
log-bin=mysql-bin
server-id       = 1
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

### When I tested the rules, see the highlighted lines
[root@cluster1 ~]# iptables -t filter -A INPUT -s 192.168.1.60 -p tcp -m state –state NEW -m tcp –dport 3306 -j ACCEPT
[root@cluster1 ~]# iptables -t filter -A INPUT -s 192.168.1.60 -p tcp -m state –state NEW -m tcp –dport 4444 -j ACCEPT
[root@cluster1 ~]# iptables -t filter -A INPUT -s 192.168.1.60 -p tcp -m state –state NEW -m tcp –dport 4567 -j ACCEPT
[root@cluster1 ~]# iptables -t filter -A INPUT -s 192.168.1.60 -p udp -m udp –dport 4567 -j ACCEPT
[root@cluster1 ~]# service iptables save
[root@cluster1 ~]#iptables -t filter -L INPUT -n –line-numbers

8, the configuration node Node2
[root@cluster2 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db –user=mysql –datadir=/data/mariadb-galera-10.0.21/
[root@cluster2 /usr/local/mariadb]# cd && service mysqld start
Starting MySQL.. SUCCESS!
[root@cluster2 ~]# mysql -uroot -p <<< “USE mysql;
update user set password=PASSWORD(‘test123′) WHERE USER=’root’;
DELETE FROM user WHERE User=”;
GRANT ALL PRIVILEGES ON *.* TO ‘cluster’@’%’ IDENTIFIED BY ‘test123’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT USER,PASSWORD,HOST FROM user;”
[root@cluster2 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@cluster2 ~]# chkconfig mysqld on
[root@cluster2 ~]# sed -i ‘/binlog_format/d’ /etc/my.cnf
[root@cluster2 ~]# sed -i ‘/log.bin/d’ /etc/my.cnf
[root@cluster2 ~]# sed -ri ‘s/^(server-id).*/\1 = 2/’ /etc/my.cnf
[root@cluster2 ~]# sed -i ‘/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\
default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\
wsrep_cluster_address = “gcomm://cluster1,cluster2,cluser3″\nwsrep_cluster_name = LegionMariadbGaleraCluster1\n\
wsrep_node_address = node2\nwsrep_sst_method = rsync\nwsrep_sst_auth = cluster:test123’ /etc/my.cnf
[root@cluster2 ~]# service mysqld start
Starting MySQL… SUCCESS!
[root@cluster2 ~]# ss -tnl | grep -E ‘:(3306|4567)’
LISTEN     0      128                      :::3306                    :::*
LISTEN     0      128                       *:4567                     *:*
[root@cluster2 ~]#
[root@cluster2 ~]# grep -v ‘^$\|^\s*\#’ /etc/my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
datadir = /data/mariadb-galera-10.0.21
query_cache_size = 0
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = “gcomm://cluster1,cluster2,cluster3”
wsrep_cluster_name = LegionMariadbGaleraCluster1
wsrep_node_address = cluster2
wsrep_sst_method = rsync
wsrep_sst_auth = cluster2:test123
thread_concurrency = 8
server-id = 2
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

9, the configuration node Node3

[root@cluster3 /usr/local/mariadb]# /usr/local/mariadb/scripts/mysql_install_db –user=mysql –datadir=/data/mariadb-galera-10.0.21/
[root@cluster3 /usr/local/mariadb]# cd && service mysqld start
Starting MySQL.. SUCCESS!
[root@cluster3 ~]# mysql -uroot -p <<< “USE mysql; > update user set password=PASSWORD(‘test123′) WHERE USER=’root’;
> DELETE FROM user WHERE User=”;
> GRANT ALL PRIVILEGES ON *.* TO ‘cluster’@’%’ IDENTIFIED BY ‘test123’ WITH GRANT OPTION;
> FLUSH PRIVILEGES;
> SELECT USER,PASSWORD,HOST FROM user;”
Enter password:
USER    PASSWORD        HOST
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       localhost
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       cluster3.ovh.net
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       127.0.0.1
root    *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C       ::1
cluster *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C        %
[root@cluster3 ~]# service mysqld stop
Shutting down MySQL… SUCCESS!
[root@cluster3 ~]# chkconfig mysqld on
[root@cluster3 ~]# sed -i ‘/binlog_format/d’ /etc/my.cnf
[root@cluster3 ~]# sed -i ‘/log.bin/d’ /etc/my.cnf
[root@cluster3 ~]# sed -ri ‘s/^(server-id).*/\1 = 3/’ /etc/my.cnf
[root@cluster3 ~]# sed -i ‘/^datadir = /a \\n\nquery_cache_size = 0\nlog_bin = mysql-bin\nbinlog_format = ROW\nexpire_logs_days = 30\
> default_storage_engine = InnoDB\ninnodb_autoinc_lock_mode = 2\nwsrep_provider = /usr/lib64/galera/libgalera_smm.so\
> wsrep_cluster_address = “gcomm://cluster1,cluster2,cluster3″\nwsrep_cluster_name = LegionMariadbGaleraCluster1\n\
> wsrep_node_address = node3\nwsrep_sst_method = rsync\nwsrep_sst_auth = cluster:test123’ /etc/my.cnf
[root@cluster3 ~]# service mysqld start
Starting MySQL..SST in progress, setting sleep higher.. SUCCESS!

[root@cluster3 ~]# ss -tnl | grep -E ‘:(3306|4567)’
LISTEN     0      128                      :::3306                    :::*
LISTEN     0      128                       *:4567                     *:*
[root@cluster3 ~]#
[root@cluster3 mariadb-galera-10.0.21]# grep -v ‘^$\|^\s*\#’ /etc/my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
datadir = /data/mariadb-galera-10.0.21/
query_cache_size = 0
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = “gcomm://cluster1,cluster2,cluster3”
wsrep_cluster_name = LegionMariadbGaleraCluster1
wsrep_node_address=’192.168.1.63′
wsrep_node_name = ‘cluster3’
wsrep_sst_method = rsync
wsrep_sst_auth = cluster3:test123
thread_concurrency = 8
server-id = 3
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

ISSUES

Disable selinux
Iptables off

1018 16:36:28 [Warning] WSREP: (531d16f9, ‘tcp://0.0.0.0:4567’) address ‘tcp://192.168.1.60:4567’ points to own listening address, blacklisting
151018 16:36:31 [Warning] WSREP: no nodes coming from prim view, prim not possible
151018 16:36:31 [Note] WSREP: view(view_id(NON_PRIM,531d16f9,1) memb {
531d16f9,0
} joined {
} left {
} partitioned {
})
151018 16:36:31 [Warning] WSREP: last inactive check more than PT1.5S ago (PT3.5051S), skipping check
151018 16:37:01 [Note] WSREP: view((empty))
151018 16:37:01 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
at gcomm/src/pc.cpp:connect():161
151018 16:37:01 [ERROR] WSREP: gcs/src/gcs_core.cpp:long int gcs_core_open(gcs_core_t*, const char*, const char*, bool)():206: Failed to open backend connection: -110 (Connection timed out)
151018 16:37:01 [ERROR] WSREP: gcs/src/gcs.cpp:long int gcs_open(gcs_conn_t*, const char*, const char*, bool)():1379: Failed to open channel ‘LegionMariadbGaleraCluster1’ at ‘gcomm://192.168.1.60,192.168.1.62,192.168.1.63’: -110 (Connection timed out)
151018 16:37:01 [ERROR] WSREP: gcs connect failed: Connection timed out
151018 16:37:01 [ERROR] WSREP: wsrep::connect() failed: 7
151018 16:37:01 [ERROR] Aborting

151018 16:37:01 [Note] WSREP: Service disconnected.
151018 16:37:02 [Note] WSREP: Some threads may fail to exit.
151018 16:37:02 [Note] /usr/local/mariadb/bin/mysqld: Shutdown complete

151018 16:37:02 mysqld_safe mysqld from pid file /data/mariadb-galera-10.0.21//cluster1.rmohan.com.pid ended

[ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)

When node1 starts MySQL, it tries to join an existing cluster. But because both nodes are currently down, there is no primary node available (see this page for a good and short explanation).

So when a Galera Cluster must be started from “zero” again, the first node must be started with the “wsrep-new-cluster” command (exactly during the set up of a new cluster):

service mysql start –wsrep-new-cluster
* Starting MariaDB database server mysqld                               [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.

10, modify the node configuration Cluster1

[root@cluster1 ~]# sed -ri ‘s/^(wsrep_cluster_address).*/\1 = “gcomm:\/\/cluster1,cluster2,cluster3″/’ /etc/my.cnf
[root@cluster1 ~]# service mysqld restart
Shutting down MySQL…. SUCCESS!
Starting MySQL… SUCCESS!
[root@cluster1 ~]#

11, the test cluster multi-master synchronization
[root@cluster1 ~]# mysql -uroot -ptest123 <<< ‘USE mysql;
CREATE DATABASE LegionTestDataBase;
USE LegionTestDataBase;
CREATE TABLE equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
INSERT INTO equipment (type, quant, color) VALUES (“slide”, 2, “blue”);
FLUSH PRIVILEGES;
SELECT * FROM LegionTestDataBase.equipment;’
id      type    quant   color
2       slide   2       blue
[root@cluster1 ~]#

INSERT FROM Cluster2

root@cluster2 ~]# mysql -uroot -ptest123 <<< “USE LegionTestDataBase;
SELECT * FROM LegionTestDataBase.equipment;”
id      type    quant   color
2       slide   2       blue
[root@cluster2 ~]# mysql -uroot -ptest123 <<< “USE LegionTestDataBase;
INSERT INTO equipment (type, quant, color) VALUES (‘swing’, 10, ‘yellow’);
SELECT * FROM LegionTestDataBase.equipment;”
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
[root@cluster2 ~]#

INSERT FROM CLUSTER3

[root@cluster3 ~]# mysql -uroot -ptest123 <<< “USE LegionTestDataBase;
SELECT * FROM LegionTestDataBase.equipment;”
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
[root@cluster3 ~]# mysql -uroot -ptest123 <<< “USE LegionTestDataBase;
INSERT INTO equipment (type, quant, color) VALUES (‘Legion’, 20, ‘red’);
SELECT * FROM LegionTestDataBase.equipment;”
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
6       Legion  20      red
[root@cluster3 ~]#

[root@cluster1 ~]# mysql -uroot -ptest123 <<< “USE LegionTestDataBase; SELECT * FROM LegionTestDataBase.equipment;”
id      type    quant   color
2       slide   2       blue
4       swing   10      yellow
6       Legion  20      red
[root@cluster1 ~]#

After the above round of testing, multi-node cluster-wide primary MariaDB is OK. .