Install Mysql Galera Cluster - Install MySQL Galera Cluster Offline offline

0. preparation

• Operating System: CentOS 7.2-7.4
• Mysql-with-wsrep-patch: 5.7.21, official download address: MySQL 5.7.21 extended with wsrep patch
• Galera: 3. Official download address: Galera 3 replication library source
• Galera Cluster Documentation : GALERA CLUSTER DOCUMENTATION
• Linux Packages Search : Linux Packages Search

1. Prepare the server

Official documents: Preparing The Server

1.1. Disable mysqld SELinux

SELinux(SELinux and MySQL There are two ways to configure SELinux, one is to ban SELinux only for mysqld, the other is to shut down SELinux directly.

1.1.1. disable SELinux for mysql

The first method is the method in the official document, but you need to install a management tool Yum install policy coreutils-python first. I don't use this method. I feel very empty and unstable.

#Check SELinux status
sestatus | grep 'SELinux status' | awk '{print $3}'
# output>>>>>>>>>
# enable
# output>>>>>>>>>

# If enable
# Semanagement needs to be installed (policy coreutils-python)
semanage permissive -a mysqld_t

This command switches SELinux into permissive mode when it registers activity from the database server.1

1.1.2. Disable SELinux directly

#Check SELinux status
sestatus | grep 'SELinux status' | awk '{print $3}'
# output>>>>>>>>>
# enable
# output>>>>>>>>>

# If enable
vi /etc/selinux/config
# Modify SELINUX = som_value_not_disabled - > SELINUX = disabled
reboot

The above method is very good, but to restart the machine, if you can't restart, for example, there are other services running on the machine, you can use the following method

# command switches off SELinux enforcing until the next reboot
# SELinux will be shut down before the next reboot
setenforce 0

# Then modify / etc/selinux/config to set SELINUX=disabled
# So even if you restart, SELinux will be disabled

# View SELinux status
getenforce

For more information, please refer to: SELinux and MySQL

1.2. Firewall settings

Next, you need to update the firewall settings on each node so that they can communicate with the cluster.2

1.2.1. TCP mutual trust

If the system is relatively strict and can not open the ports freely, then TCP mutual trust to other hosts can be configured on the hosts that make up the cluster. Assuming that the IP of the three hosts constituting the cluster is 192.168.0.11 192.168.0.12 192.168.0.13 respectively, this setting is required on 192.168.0.11:

iptables --append INPUT --protocol tcp --source 192.168.0.12 --jump ACCEPT
iptables --append INPUT --protocol tcp --source 192.168.0.13 --jump ACCEPT
iptables-save > /etc/sysconfig/iptables

The advantage of this approach is that the cluster is relatively safer and the official documents do so, but this configuration is not conducive to adding or deleting nodes. Every additional node has to modify the firewall on each node. I did not use this method.

1.2.2. Open the necessary ports

iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
iptables -I INPUT -p tcp --dport 4444 -j ACCEPT
iptables -I INPUT -p tcp --dport 4567 -j ACCEPT
iptables -I INPUT -p tcp --dport 4568 -j ACCEPT

iptables-save > /etc/sysconfig/iptables

3306 is the port of mysql, and the other ports are needed by wsrep or galera. This configuration does not need to modify the firewall settings of nodes already in the cluster when adding or deleting nodes.

1.3. Disable AppArmor

By default, some servers—for instance, Ubuntu—include AppArmor, which may prevent mysqld from opening additional ports or running scripts. You must disable AppArmor or configure it to allow mysqld to run external programs and open listen sockets on unprivileged ports.3

# Check for Apparmor
whereis apparmor
service apparmor status

# If there is
ln -s /etc/apparmor.d/usr /etc/apparmor.d/disable/.sbin.mysqld
service apparmor restart

Not tested on Ubuntu or other systems with AppArmor installed, just a record.

2. Preparing Off-line Installation Package

Prepare a server that can connect to the public network, configure yum sources, such as alibaba or souhu Netease. All operations are completed under CentOS 7.4 + mirrors.sohu.com + repotrack. Repotrack needs to be installed in advance. If yum can not be installed, you can find and download Linux Packages Search in Part 0, and use RPM - ivrphU xx. m to install manually.

repotrack is a program for keeping track of a particular package and its dependencies. It will download one or more packages and all dependencies.4

You can use repotrack instead like this:
repotrack -a x86_64 -p /repos/Packages [packages]
Unfortunately there is a bug with the -a flag (arch). It will download i686 and x86_645

# Create temporary directories
mkdir ~/mysql-wsrep-galera
cd ~/mysql-wsrep-galera

# Download mysql-wsrep
wget http://releases.galeracluster.com/mysql-wsrep-5.5/binary/mysql-wsrep-5.5.59-25.23-linux-x86_64.tar.gz

# Download galera
wget http://releases.galeracluster.com/galera-3/source/galera-3-25.3.23.tar.gz

# Download rpm dependency packages
# Annotation structure [packageName/module ToUse]
# libaio/mysql
repotrack -p ~/mysql-wsrep-galera/libaio libaio
# rsync/wsrep
repotrack -p ~/mysql-wsrep-galera/rsync rsync
# lsof/wsrep
repotrack -p ~/mysql-wsrep-galera/lsof lsof
# gcc/galera
repotrack -p ~/mysql-wsrep-galera/gcc gcc
# gcc-c++/galera
repotrack -p ~/mysql-wsrep-galera/gcc-c++ gcc-c++
# boost-devel/galera
repotrack -p ~/mysql-wsrep-galera/boost-devel boost-devel
# check-devel/galera
repotrack -p ~/mysql-wsrep-galera/check-devel check-devel
# openssl-devel/galera
repotrack -p ~/mysql-wsrep-galera/openssl-devel openssl-devel
# scons/galera
repotrack -p ~/mysql-wsrep-galera/scons scons

# Create a new folder to merge all rpm packages into one folder
mkdir yum-source
# -n denotes duplicate uncovering
cp -n libaio/* yum-source/
cp -n rsync/* yum-source/
...
...
...
cp -n openssl-devel/* yum-source/
cp -n scons/* yum-source/

# Because of the bug in the repotrack-a option (footnote 5), all the architecture packages are downloaded. We only need x86_64's
# So delete all i686 packages
cd /yum-source
rm -rf *.i686.*

# Finally, pack galera, mysql-wsrep, yum-source
cd ~/mysql-wsrep-galera
tar -cvzf mysql-wsrep-5.7.21-galera-3-linux-x86_64.tar.gz galera-3-25.3.23.tar.gz mysql-wsrep-5.7.21-25.14-linux-x86_64.tar.gz yum-source

The final mysql-wsrep-5.7.21-galera-3-linux-x86_64.tar.gz is the complete offline installation package. In fact, the whole process is to package mysql-wsrep+galera+yum source together, which requires Yum packages and corresponding dependencies to be downloaded in the form of rpm packages, and finally manually installed on the target machine, which is called "offline".
Generally speaking, the target machine operating system corresponding to cluster is the same. It is possible to put the compiled galera plug-in in mysql-wsrep installation package beforehand, so as to avoid the scons compilation process, yum-source will be much fewer, such as gcc/scons, which has not been verified.
There may be a lack of different Yum sources for different environments. The packages listed here are only the smallest packages installed in the streamlined version of CentOS 7.2. They do not guarantee the perfect operation of other environments and systems.

3. Install MySQL Galera Cluster

3.1. Unload Mysql

If mysql is not installed on the target machine, or if multiple mysqls are allowed to be installed on the same machine, this step is eliminated.

# Find mysql installed before
rpm -qa|grep -i mysql

# Stop mysql service
service mysqld stop

# Unloading sequence is very important
rpm -ev mysql-community-server-5.7.19-1.el7.x86_64
rpm -ev mysql-community-client-5.7.19-1.el7.x86_64
rpm -ev mysql-community-libs-5.7.19-1.el7.x86_64
rpm -ev mysql-community-common-5.7.19-1.el7.x86_64

# If you have dependencies, you can execute the following commands
rpm -e --nodeps mysql-community-common-5.7.19-1.el7.x86_64

# Delete mysql folders and files
find / -name mysql
# See if you can delete

# Delete/etc/my.cnf
rm -rf /etc/my.cnf 

# Verify unloading
rpm -qa|grep -i mysql 

# Delete base-dir
# Here you can see / etc/my.cnf to find base-dir
# The default is / var/lib/mysql
rm -rf /var/lib/mysql

3.2. Install MySQL (the first node)

# decompression
tar -zvxf mysql-wsrep-5.7.21-galera-3-linux-x86_64.tar.gz

# Installation dependency
cd yum-source
# Because it's not installed in yum, it's impossible to detect the sequential installation of dependency packages, so -- nodeps skips dependency checks directly
# For installed packages, upgrade replacepkgs directly
rpm -ivhU *.rpm --nodeps --replacepkgs

# Unzip the database
cd ../
tar -zvxf mysql-wsrep-5.7.21-25.14-linux-x86_64.tar.gz
mv mysql-wsrep-5.7.21-25.14-linux-x86_64 /usr/local/mysql

# Install the Galera plug-in
tar -zvxf galera-3-25.3.23.tar.gz
cd galera-3-25.3.23
# scons process is slightly longer
scons
cp garb/garbd /usr/local/mysql/bin/
cp libgalera_smm.so /usr/local/mysql/lib/plugin/

# Prepare initialization file
cd /usr/local/mysql
vi init-root-pass.sql
# alter root for change password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
ESC :wq

# Create mysql users and user groups (if not)
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql .

# Configure Galera/Mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
mkdir -p /var/lib/mysql
# Create error log file, otherwise start error report can not find file, said to be mysql bug
echo '' > /var/lib/mysql/mysql.log
chown -R mysql:mysql /var/lib/mysql
vi /etc/my.cnf
# >>>>>>>>>>>>>>>>>>>>>>>>>>file>>>>>>>>>>>>>>>>>>>>>>>>
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld_safe]
log-error = /var/lib/mysql/mysql.log
pid-file = /var/lib/mysql/mysql.pid

[mysqld]
wsrep_node_name = dbaas_galera_node1
wsrep_provider = /usr/local/mysql/lib/plugin/libgalera_smm.so
#wsrep_provider_options ='gcache.size=1G;socket.ssl_key=my_key;socket.ssl_cert=my_cert'
#wsrep_slave_threads=16
wsrep_sst_method = rsync
#wsrep_sst_auth=root:

port = 3306
socket = /var/lib/mysql/mysql.sock
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data

default_storage_engine=InnoDB
#innodb_buffer_pool_size=1G
#innodb_log_file_size=256M
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_flush_log_at_trx_commit=0
innodb_doublewrite=0
innodb_file_per_table=1

binlog_format=ROW
log-bin=mysql-bin
server-id=101
relay-log=mysql-relay-bin
#read_only=1
log-slave-updates=1

ESC :wq
# >>>>>>>>>>>>>>>>>>>>>>>>>>file>>>>>>>>>>>>>>>>>>>>>>>>

# Start for the first time, change the root password
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --init-file=/usr/local/mysql/init-root-pass.sql

# Close mysql
service mysql stop

# Start the first galera node
# Because it is the first node, wsrep_cluster_address is empty
service mysql start --wsrep_cluster_address=gcomm://

# Verify whether to start
# You can put / usr/local/mysql/bin in PATH
/usr/local/mysql/bin/mysql -uroot -proot
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.01 sec)

# A value of 1 indicates that the first node of the cluster started successfully.

# Allow remote connections to root (or other users)
mysql> use mysql;
mysql> UPDATE user SET host = '%' WHERE user = 'root';
# UPDATE user SET host = '%' WHERE user IS NOT NULL;

3.3 Adding other nodes

Installation is the same, but personalized configuration such as node name and start-up mode are different.

...
...
vi /etc/my.cnf
# >>>>>>>>>>>>>>>>>>>>>>>>>>file>>>>>>>>>>>>>>>>>>>>>>>>
...
wsrep_node_name = dbaas_galera_node2
...
# >>>>>>>>>>>>>>>>>>>>>>>>>>file>>>>>>>>>>>>>>>>>>>>>>>>
...
...
# Close mysql
service mysql stop

# Join nodes in a cluster
# Suppose that the other two IPS in the cluster are 192.168.0.166 and 192.168.0.117.
service mysql start --wsrep_cluster_address="gcomm://192.168.0.166:4567,192.168.0.117:4567"

# Verify success
/usr/local/mysql/bin/mysql -uroot -proot
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.01 sec)

# The value of 2 indicates that the second node of the cluster joined successfully, and 3 indicates that the third node joined successfully.
# The wsrep_cluster_size value of any node should be the same

# Verify Cluster Function
# Create a database at any node to see if other nodes are synchronizing the library
# Create a table at any node to see if other nodes are synchronizing the table
# Insert a data in any node to see if other nodes synchronize the data
# Update a data at any node to see if other nodes update the data synchronously

4. Restart the cluster

4.1. Normal Restart

To restart an entire Galera Cluster, complete the following steps:

1. Identify the node with the most advanced node state ID.
2. Start the most advanced node as the first node of the cluster.
3. Start the rest of the node as usual.6

# In a running cluster, service mysql stop is executed at each node in any order
# This simulates a normally closed cluster
# Find the largest seqno on each node, which represents the transaction ID of the last submission. The larger the data, the newer the data, the more it should be started as the first node in the cluster.

# Find the maximum seqno, which is recorded at $data-dir/grastate.dat
cat /usr/local/mysql/data/grastate.dat | grep seqno
# Suppose node1:5000, node2:5005, and node3:5003.
# So you should start node2 first

# Assuming that each node has the same seqno, refer to safe_to_bootstrap again.
# The node safe_to_bootstrap=1 should be started as the first node
# Otherwise, an error will be reported: [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1
cat /usr/local/mysql/data/grastate.dat | grep safe_to_bootstrap
# Assuming that it is still safe_to_bootstrap=1 for node2
# So you should still start node2 first

# Switch to node2
# Start the first node
service mysql start --user=mysql --wsrep_cluster_address=gcomm://
# Start node1
service mysql start --user=mysql --wsrep_cluster_address="gcomm://192.168.0.166:4567,192.168.0.117:4567"
# Start node3
service mysql start --user=mysql --wsrep_cluster_address="gcomm://192.168.0.166:4567,192.168.0.116:4567"

4.2 Abnormal Restart

When the seqno of each node is -1 and the safe_to_bootstrap=0 of each node, it may be caused by simultaneous downtime of all nodes of the server, such as power outage in the computer room. At this point, the startup method is slightly more complex, and the following steps are approximate:
1. Start a database of a node in a non-clustered way;
2. Restore binary log records;
3. Start with this node as the first node in the cluster.
4. Other nodes join in turn.

Refer to specific methods: Galera Cluster mysql+keepalived cluster deployment Exceptional cluster restart

5. Records of some problems

1. Starting MySQL... ERROR! The server quit without updating PID file(/path/to/pid/file). This error is usually found by looking at the error log, but if the error is reported and there is no error log, the head will be large. The first problem is to get the error log out. No error log may modify the value of log-error under [mysql-safe] in / etc/my.cnf and specify a path to the error log, but MySQL does not have access to the folder or file. When this configuration is commented out, the error log will appear in / path/to/mysql/data/dir/host_name.err. Qut without updating PID file caused by other problems will look at the error log or here: mysql startup error: Starting MySQL... ERROR! The server quit without updating PID file Find inspiration.
2. I tried to install Galera Cluster with rpm, but after the installation is completed, there is always a wrong state after the start-up cluster, and it is not convenient to find the problem. It is better to use the install-free version to find the problem.