Setup of MySQL/MariaDB Galera cluster in Linux

Setup of MySQL/MariaDB Galera cluster in Linux

MariaDB Introduction

MariaDB is a MySQL Branch maintained by the open-source community. It is developed by MySQL founder Michael Widenius and uses a GPL license.

MariaDB is designed to be fully compatible with MySQL, including APIs and command lines, so that it can easily become a substitute for MySQL.

For more information, see:

Http://mariadb.org/(official website)

Introduction to Galera Cluster

Galera Cluster is a master-slave database Cluster that supports "Multi-master synchronization" based on the secondary development of MySQL/innodb. It is highly available and easy to expand.

For more information, see:

Http://galeracluster.com/(official website)

This article uses the Linux release: CentOS6.7: https://wiki.centos.org/Download

1. Add yum Source

[Root @ localhost ~] # Vi/etc/yum. repos. d/CentOS-MariaDB.repo

Add the following lines:

[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/5.5/rhel6-amd64gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck = 1

2. Install the mariadb galera package

[Root @ localhost ~] # Yum install MariaDB-Galera-server MariaDB-client galera

3. Modify firewall configurations

[Root @ localhost ~] # Vi/etc/sysconfig/iptables

Add the following lines:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT-A INPUT -m state --state NEW -m tcp -p tcp --dport 4444 -j ACCEPT-A INPUT -m state --state NEW -m tcp -p tcp --dport 4567 -j ACCEPT-A INPUT -m state --state NEW -m tcp -p tcp --dport 4568 -j ACCEPT

4. Restart the firewall Function

[Root @ localhost ~] # Service iptables restart

5. Install selinux management tool

[Root @ localhost ~] # Yum provides/usr/sbin/semanage
[Root @ localhost ~] # Yum-y install policycoreutils-python

6. Modify selinux Security Policy

[Root @ localhost ~] # Semanage port-a-t mysqld_port_t-p tcp 4567
[Root @ localhost ~] # Semanage port-a-t mysqld_port_t-p tcp 4568
[Root @ localhost ~] # Semanage permissive-a mysqld_t

7. Start the mysql Service

[Root @ localhost ~] # Service mysql start

8. Execute mysql Security Settings

[Root @ localhost ~] # Mysql_secure_installation

(Set the root account password first, and then keep "y)

9. Create an account for node synchronization

[Root @ localhost ~] # Mysql-uroot-p
MariaDB [(none)]> grant usage on *. * to sst @ '%' identified by '123 ';
MariaDB [(none)]> flush privileges;

10. Modify the default mysql Character Set

MariaDB [(none)]> show variables like 'character % ';
MariaDB [(none)]> set character_set_server = utf8;
MariaDB [(none)]> set character_set_database = utf8;

11. Modify cluster node configuration

[Root @ localhost ~] # Cp/usr/share/mysql/wsrep. cnf/etc/my. cnf. d/
[Root @ localhost ~] # Vi/etc/my. cnf. d/wsrep. cnf

Modify the following lines:

Wsrep_provider =/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address = "gcomm: //" # address of cluster node N (delete the previous !) Wsrep_sst_auth = sst: 123456 # database account and password of node N

• Parameter description

"Gcomm: //" is a special address, which is only used when the galera cluster is initialized and started.
If the first node is disabled after the cluster is started, you must first change "gcomm: //" to the cluster address of other nodes, for example, wsrep_cluster_address = "gcomm: // 192.168.0.152 ".

Check whether/etc/my. cnf exists! Includedir/etc/my. cnf. d/line. If no line exists, add it.

[Root @ localhost ~] # Vi/etc/my. cnf

By now, the configuration of the 1st nodes is complete, and then proceed to Step 1 ~ 11. to configure 2nd nodes, you only need to modify wsrep_cluster_address of Node 2 as the IP address of Node 1, and so on.

12. Start the cluster node

• Check the mysql process: [root @ localhost ~] # Ps aux | grep mysql
• Stop mysql service: [root @ localhost ~] # Service mysql stop
• Start up 1st nodes: [root @ localhost ~] # Service mysql bootstrap

• Start nodes 2nd, 3,...: [root @ localhost ~] # Service mysql start

(Note: Before starting mysql, check whether the service has been started. Do not start it again. If the current mysql service cannot be stopped, manually kill the mysql process)

13. Check the cluster running status

[Root @ localhost ~] # Mysql-uroot-p
MariaDB [(none)]> show status like 'wsrep % ';

If wsrep_connected = ON and wsrep_ready = ON, the node is successfully connected to the cluster.

14. Configure the cluster's arbitration Node

Like other cluster software, the galera cluster with only two nodes must be in the "Split-brain" state in extreme circumstances. To avoid this problem, galera introduced "arbitrator )".
There is no data on the "arbitrator" node. Its role in the cluster is to conduct arbitration when the cluster is split. The cluster can have multiple "arbitrator" nodes. The method for adding an "arbitrator" node to a cluster is simple. Run the following command:
[Root @ localhost ~] # Garbd-a gcomm: // <node IP>-g my_wsrep_cluster-d

• Parameter description

-Cluster a address
-G cluster name
-D run in daemon mode

15. Check whether the database meets the requirements

Before deploying it to a cluster, we recommend that you check whether the database meets galera requirements. For example, the storage engine must be innodb and the data table must have a primary key. Otherwise, the record will not be replicated across multiple databases.

Select the specified database and execute the following SQL statements to output tables that do not meet the requirements and their causes. Modify the statements based on the corresponding causes:

select distinct concat( t.table_schema, '.', t.table_name ) as tbl, t. engine,     if ( isnull(c.constraint_name), 'nopk', '' ) as nopk,     if ( s.index_type = 'fulltext', 'fulltext', '' ) as ftidx,     if ( s.index_type = 'spatial', 'spatial', '' ) as gisidx from information_schema. tables as t left join information_schema.key_column_usage as c on ( t.table_schema = c.constraint_schema and t.table_name = c.table_name and c.constraint_name = 'primary' ) left join information_schema.statistics as s on ( t.table_schema = s.table_schema and t.table_name = s.table_name and s.index_type in ('fulltext', 'spatial')) where t.table_schema not in ( 'information_schema', 'performance_schema', 'mysql' ) and t.table_type = 'base table' and ( t. engine <> 'innodb' or c.constraint_name is null or s.index_type in ('fulltext', 'spatial')) order by t.table_schema, t.table_name;

16. FAQs

1) An ERROR occurred while starting mysql: SST in progress, setting sleep higher. ERROR!

• Make sure that rsync is installed on the local machine: [root @ localhost ~] # Yum list | grep rsync
• Make sure that ports 4444, 4567, and 4568 used by galera sst are allowed to pass the firewall and restart the firewall function.
• Make sure that selinux has enabled port 4444: [root @ localhost ~] # Semanage port-a-t mysqld_port_t-p tcp 4444

2) when viewing the galera cluster status, the values of wsrep_connected and wsrep_ready are both OFF!

Open/etc/my. cnf. d/wsrep. in the cnf file, locate wsrep_cluster_address = "gcomm: //" and check whether "#" exists. If yes, delete and restart mysql.

MariaDB Galera Cluster deployment (how to quickly deploy MariaDB Cluster)

MariaDB Galera Cluster details: click here
MariaDB Galera Cluster: click here

This article permanently updates the link address: