Galera Cluster for MySQL

First, MySQL Galera introduction

1. Galera characteristics

Mysql/galera is a multi-master cluster of Mysql/innodb with the following features:

1) Synchronous replication

2) active-active multi-Master topology

3) Any node in the cluster can read and write

4) Automatic identity control, failure node automatically separated from the cluster

5) Automatic node access

6) True parallel replication based on "row" level and ID checks

7) No single point of failure, easy to expand

2. Architecture diagram

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/41/04/wKioL1PQbDHwWT1rAAFTq8JqiSA437.jpg "title=" Clipboard1.png "alt=" wkiol1pqbdhwwt1raaftq8jqisa437.jpg "/>650" this.width=650; "src="/e/u261/themes/default/ Images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px Solid #ddd; "alt=" Spacer.gif "/>

Second, Galera cluster installation

1, pre-installation preparation

1) need at least three MySQL server, if only two to do cluster, if there is inconsistent data situation, it is possible to have a brain fissure, need an arbitration server, if the direct three do cluster, then no need to arbitrate

2) If you are doing a galera cluster for an existing environment, you need to check the existing MySQL table, including the table, the table engine, whether there is no primary key, whether there is a full-text index, whether there is a spatial index:

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 are NULL OR s.index_type in (' Fulltext ', ' SPATIAL '))  ORDER by T.table_schema,t.table_name;

2. Experimental environment:

mysql1:172.16.5.156

mysql2:172.16.5.157

mysql3:172.16.5.158

3. Install dependent packages:

Yum groupinstall-y "Development Tools" "Server Platform Development"

4. Install the MySQL version with Wsrep patch

Yum Install-y libaio-devel

wget https://launchpad.net/codership-mysql/5.6/5.6.16-25.5/+download/mysql-5.6.16_wsrep_25.5-linux-x86_64.tar.gz

Tar XF mysql-5.6.16_wsrep_25.5-linux-x86_64.tar.gz

MV Mysql-5.6.16_wsrep_25.5-linux-x86_64/usr/local/mysql

Useradd-s/sbin/nologin-m MySQL

Mkdir/data/mydata

./scripts/mysql_install_db--no-defaults--datadir=/data/mydata--user=mysql

Chown-r mysql.mysql/data/mydata/

Chown Root.mysql/usr/local/mysql

Cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld

5. Install Galera Copy Plugin

wget https://launchpad.net/galera/3.x/25.3.5/+download/galera-25.3.5-src.tar.gz

Tar XF galera-25.3.5-src.tar.gz

CD GALERA-25.3.5-SRC

SCons

CP Garb/garbd/usr/local/mysql/bin

CP libgalera_smm.so/usr/local/mysql/lib/plugin/

6. Edit MySQL configuration file my.cnf

[Mysqld] Basedir =/usr/local/mysql DataDir =/data/mydata Port = 3306 server-id=101 Socket =/tmp/ Mysql.sock Pid-file=/data/mydata/mysql.pid Sql_mode=no_engine_substitution,strict_trans_tables Wsrep_node_name = mysql1 Wsrep_provider =/usr/local/mysql/lib/plugin/libgalera_smm.so Wsrep_sst_method = rsync #wsrep_sst_auth =sst:sstpass     #使用sst的用户和密码, if it is on, you need to create the user on MySQL and grant them sufficient permissions Default_storage_engine=innodb innodb_autoinc_lock_mode=2 Innodb_locks_unsafe_for_binlog=1 Innodb_flush_log_at_trx_commit=1 Innodb_file_per_table=1 Binlog_format=row Log-bin=mysql-bin Relay-log=mysql-relay-bin Log-slave-updates=1

7. Start MySQL

Service mysqld Start--wsrep_cluster_address=gcomm://

Note:

1) "gcomm://" is a special address, it is only used when the Galera cluster is initialized, it needs to use the specific IP address when starting up again.

2) Wsrep default to 4567 port, after MySQL boot, in addition to check the 3306 port of MySQL, also check this port

8. Add a new MySQL node to Galera

In Galera cluster, the newly-accessed node is called Joiner, and the node that provides replication to joiner is called donor.

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m01/41/04/wkiol1pqbgprbe8iaad3za3jwfw940.jpg "title=" Clipboard2.png "alt=" Wkiol1pqbgprbe8iaad3za3jwfw940.jpg "/>

In a production environment, it is recommended to set up a dedicated donor that does not perform any SQL requests from the client, which has the following benefits:

1) Consistency of data:

Because donor itself does not execute any client-side SQL, the likelihood of transaction collisions on this node is minimal, so if you find that the cluster has inconsistent data, the data on donor should be the most accurate in the entire cluster.

2) Data security:

Because the dedicated donor itself does not execute any client-side SQL, the likelihood of a catastrophic event on this node is minimal, so when the entire cluster goes down, it should be the best node for recovering the cluster.

3) High Availability:

The dedicated donor can be used as a dedicated state snapshot donor. Because the node does not serve the client, it does not affect the user experience when using this node for SST, and the front-end load balancer device does not need to be reconfigured.

The new nodes that you add need to meet the following criteria:

1) Install the MySQL version with Wsrep patch

2) Install Galera copy Plugin

3) Configure MySQL for the new node (refer to Donor's my.cnf)

4) The address of the gcomm://configured or started requires the use of donor IP.

From this, you can refer to 172.16.5.156 configuration 172.16.5.157, start MySQL as follows:

Service mysqld start--wsrep_cluster_address= "gcomm://172.16.5.156:4567"

Note:

There are three ways to specify a cluster IP:

1) as shown above, when MySQL is started, the add--wsrep_cluster_address parameter specifies

2) Configure the wsrep_cluster_address in MY.CNF

3) Modify global variables directly: Set global wsrep_cluster_address= "gcomm://172.16.5.156:4567"

It is also necessary to note that the value of gcomm://can have multiple, separated from each other by commas, for example: gcomm://172.16.5.156:4567, 172.16.5.158:4567

9. Adding the Quorum node

In a Galera cluster with only two database nodes, in order to solve the problem of brain fissure, we need to introduce the quorum node, which can have multiple quorum nodes in the cluster. There is no data on the quorum node, it simply arbitrations when the cluster is cracked. The quorum node joins the cluster in the following ways:

Garbd-a gcomm://172.16.5.156:4567-g my_wsrep_cluster-d

-A: Specify the cluster address

-D: Run as Daemon

-G: Cluster name

Third, Galera monitoring

1. Common Query Instructions

View Wsrep version: Mysql> SHOW GLOBAL STATUS like ' wsrep_provider_version ';

See all variables related to Wsrep: mysql> SHOW VARIABLES like ' wsrep% ' \g

View Galera cluster Status: mysql> Show status like ' wsrep% ';

2. Parameter description

1) Cluster integrity check:

Wsrep_cluster_state_uuid: The values for all nodes in the cluster should be the same, with different values for the nodes, indicating that they are not connected to the cluster.

WSREP_CLUSTER_CONF_ID: Normally, this value is the same on all nodes. If the value is different, the node is temporarily "partitioned". The same value should be restored when the network connection between nodes is restored.

Wsrep_cluster_size: If this value is consistent with the expected number of nodes, all cluster nodes are connected.

Wsrep_cluster_status: The state of the cluster. If it is not "Primary", a "Partition" or "Split-brain" condition appears.

2) node status check:

Wsrep_ready: This value is on, which indicates that the SQL payload can be accepted. If off, you need to check for wsrep_connected.

Wsrep_connected: If the value is off and the value of Wsrep_ready is off, then the node is not connected to the cluster. (This may be caused by a misconfiguration such as wsrep_cluster_address or Wsrep_cluster_name.) The error log is required for specific errors

Wsrep_local_state_comment: If wsrep_connected is on, but Wsrep_ready is off, you can see the reason from that item.

3) Copy Health check:

Wsrep_flow_control_paused: Indicates how long replication has stopped. That is, the extent to which the cluster is slow due to slave latency. A value of 0~1, the closer to 0, the better, and a value of 1 means that replication stops completely. Optimize the value of wsrep_slave_threads to improve .

Wsrep_cert_deps_distance: How many transactions can be processed in parallel. The value set by wsrep_slave_threads should not be higher than the value.

Wsrep_flow_control_sent: Indicates how many times the node has stopped replicating.

Wsrep_local_recv_queue_avg: Represents the average length of the slave transaction queue. Slave the bottleneck.

The wsrep_flow_control_sent and wsrep_local_recv_queue_avg of the slowest nodes are the highest. These two values are relatively better if they are lower.

4) Detect slow network problems:

Wsrep_local_send_queue_avg: A harbinger of network bottlenecks. If this value is higher, there may be a network bottle

5) Number of conflicts or deadlocks:

wsrep_last_committed: Number of last committed transactions

Wsrep_local_cert_failures and Wsrep_local_bf_aborts: Rollback, number of conflicts detected

Iv. Galera State Snapshot Transfer (SST)

SST allows newly-accessed nodes to use a custom method to obtain the initial data, and currently MySQL supports three SST methods:

1, mysqldump

This requires the receiving server to fully initialize and prepare the receive connection before the transfer. This method prevents the duration of modification of its own state transitions by defining blocking. This is also the slowest way to potentially bring high loads to the problem.

2. rsync

The quickest way is the way Galera is used by default. The rsync script runs on the send and receive side. On the receiving side, turn on the Rsync service mode and wait for the send side to connect. On the sending side, turn on the rsync client mode and send the MySQL data directory contents to the connection node. This method can also block, but faster than mysqldump.

3, Xtrabackup

Also quickly, but requires additional installation.

This article is from the "Nobody" blog, please be sure to keep this source http://breezey.blog.51cto.com/2400275/1529503