MYSQL/MARIADB galera Cluster construction process "turn" under Linux

MARIADB Introduction

MARIADB is a MySQL branch maintained by the open source community, developed by Michael Widenius, the founder of MySQL, with a GPL licensed license.

MARIADB is designed to be fully compatible with MySQL, including APIs and command lines, making it easy to be a replacement for MySQL.

For more information, please refer to the link:

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

Http://baike.baidu.com/link?url=dFJ-My-I52YFc1mx26K804LPwZrcEWCwB4IqfA4-soYx6295BZLIe7bEFgOtt3CWZ8AYpkp1P342L4S-R4x4CK

Galera Cluster Introduction

Galera Cluster is a two-time development based on MYSQL/INNODB, a database master-slave cluster which supports "multi-master Synchronization", which has the features of high availability and easy expansion.

For more information, please refer to the link:

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

Http://www.gpfeng.com/?p=603&utm_source=tuicool&utm_medium=referral

Linux distributions used in this article: CentOS6.7 : Https://wiki.centos.org/Download

1. Add Yum Source

[Email protected] ~]# 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. Installing the MARIADB Galera package

[email protected] ~]# Yum install mariadb-galera-server mariadb-client galera

3. Modify the firewall configuration

[Email protected] ~]# 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 Firewall function

[Email protected] ~]# service iptables restart

5. Installing the SELinux management tool

[email protected] ~]# Yum provides/usr/sbin/semanage
[Email protected] ~]# yum-y install Policycoreutils-python

6. Modifying the SELinux security policy

[[email protected] ~]# semanage port-a-t mysqld_port_t-p TCP 4567
[[email protected] ~]# semanage port-a-t mysqld_port_t-p TCP 4568
[Email protected] ~]# semanage permissive-a mysqld_t

7. Start the MySQL service

[[Email protected] ~]# service MySQL start

8. Perform MySQL security settings

[Email protected] ~]# mysql_secure_installation

(first set the root account password, and then always "Y" down)

9. Create an account for node synchronization

[Email protected] ~]# mysql-uroot-p
MariaDB [(None)]> grant usage on * * to [e-mail protected] '% ' identified by ' 123456 ';
MariaDB [(None)]> flush privileges;

10. Modify the MySQL default character set

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

11. Modify the cluster node configuration

[Email protected] ~]# cp/usr/share/mysql/wsrep.cnf/etc/my.cnf.d/
[Email protected] ~]# VI/ETC/MY.CNF.D/WSREP.CNF

Modify the following lines:

wsrep_provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address= "gcomm://"    #集群节点N的地址 (pay attention to the "#" in front of the delete!) ) wsrep_sst_auth=sst:123456    #节点N的数据库账户和密码

• Parameter description

"gcomm://" is a special address that is used only when the Galera cluster is initialized for startup.
If we shut down the first node after the cluster is started, we must first modify "gcomm://" to the cluster address of the other node, such as wsrep_cluster_address= "gcomm://192.168.0.152".

Check that there is no!includedir/etc/my.cnf.d/in the/ETC/MY.CNF line, no Add.

[Email protected] ~]# VI/ETC/MY.CNF

Here, the configuration of the 1th node is complete, and then on the other host to follow the steps 1~11 Configuration 2nd node, just modify Node 2 wsrep_cluster_address is the IP of Node 1, and so on.

12. Start the cluster node

• Check the MySQL process: [[email protected] ~]# PS aux|grep MySQL
• Stop MySQL service: [[email protected] ~]# service MySQL stop
• Start the 1th node: [[email protected] ~]# service MySQL Bootstrap

• Start 2nd, 3 、... nodes: [[email protected] ~]# service MySQL start

(Note: Before starting MySQL, check that the service has been started, do not start repeatedly, if you cannot stop the current MySQL service, kill the MySQL process manually)

13. Check Cluster run status

[Email protected] ~]# mysql-uroot-p
MariaDB [(None)]> show status like ' wsrep% ';

If Wsrep_connected=on and wsrep_ready=on indicate that the node is successfully connected to the cluster.

14. Configure the quorum node for the cluster

For a 2-node Galera cluster and other cluster software, you need to face the "brain crack" state in extreme situations. In order to avoid this problem, Galera introduced the "Arbitrator (Arbitrator)".
There is no data on the Arbiter node, and its role in the cluster is to arbitrate when the cluster splits, and there can be multiple "arbiter" nodes in the cluster. The method of joining the moderator node to the cluster is simple, run the following command:
[Email protected] ~]# garbd-a gcomm://< node ip>-G my_wsrep_cluster-d

• Parameter description

-A cluster address
-G Cluster Name
-d runs in daemon mode

15. Check whether the database meets the requirements

Before deploying to a cluster, it is recommended to check that the database meets Galera requirements, such as that the storage engine must be INNODB, the data table must have a primary key, and so on, otherwise the records will not be replicated in multiple units.

Select the specified database, execute the following SQL output table that does not meet the requirements, and why, modify it for the appropriate reason:

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 joins 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.ta Ble_schema and t.table_name = S.table_name and S.index_type in (' Fulltext ', ' spatial ')) where T.table_schema not in (' INF Ormation_schema ', ' performance_schema ', ' MySQL ') and t.table_type = ' base table ' and (t.engine <> ' InnoDB ' or c.co Nstraint_name is null or s.index_type in (' Fulltext ', ' spatial ')) Order by T.table_schema, T.table_name;

16. Frequently Asked Questions

1) Error starting MySQL: SST in progress, setting sleep higher. error!

• Make sure the machine is installed Rsync:[[email protected] ~]# yum List|grep rsync
• Ensure that ports 4444, 4567, and 4568 that are used by the Galera SST are allowed to restart the firewall function through the firewall
• Ensure that SELinux has open access to port 4444: [[email protected] ~]# semanage port-a-t mysqld_port_t-p TCP 4444

2) wsrep_connected and Wsrep_ready values are off! when viewing the Galera cluster status

Open the/etc/my.cnf.d/wsrep.cnf file, find the wsrep_cluster_address= "gcomm://" line, check the front for "#", if any, delete and restart MySQL.

Turn from

The construction process of MYSQL/MARIADB galera cluster under Linux--The blog garden
Http://www.cnblogs.com/liujiduo/p/5066803.html

MariaDB Galera cluster Clustering advantages and disadvantages _mailrun_ Sina Blog
Http://blog.sina.com.cn/s/blog_548c8a830102vrgw.html

MYSQL/MARIADB galera Cluster construction process "turn" under Linux