Galera Cluster for MySQL

MySQL Galera Introduction

Main functions :

• Synchronous replication
• True multi-master thatall nodes can read and write to the database at the same time
• Automatic node member control, failed nodes are automatically cleared
• New node joins data automatic replication
• True parallel replication, row-level
• Users can connect directly to the cluster, using the same feeling on MySQL exactly

Advantages :

• Because it is multi-master, there is no Slave lag ( delay )
• There is no loss of transactions
• Ability to expand both read and write
• Smaller client latency
• Data between nodes is synchronous , and master/slave mode is asynchronous , and binlog on different Slave may be different

Environment

IP Address         hostname         Description 10.0.  0.10       node1       first start 10.0.  0.11       node210.0.  0.12       Node3

Version Information

[Email protected] ~]#  cat/etc/redhat-7.1.  1503~]# uname-3.10. 0-229. el7.x86_64 #16each:2015 x86_64 x86_64 x86_64 gnu/linux

shutting down firewalls and SELinux

[[Email protected] ~0

clear the original MySQL

[email protected] ~]# Yum erase MySQL mysql-devel mysql-libs-~]# rpm-qa |grep MySQL

Components

http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/http://  releases.galeracluster.com/galera-3/centos/7/x86_64/

Install the components (Yum installation, install in sequence, if it fails, and so on after other installation packages are installed, reinstall them again)

[email protected] ~]# Yum install-y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-client-5.6-5.6.38-25.21.el7.x86_64.rpm[email protected] ~]# Yum install-y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-devel-5.6-5.6.38-25.21.el7.x86_64.rpm[email protected] ~]# Yum install-y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-shared-5.6-5.6.38-25.21.el7.x86_64.rpm[email protected] ~]# Yum install-y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-test-5.6-5.6.38-25.21.el7.x86_64.rpm[email protected] ~]# Yum install-y http://releases.galeracluster.com/galera-3/centos/7/x86_64/galera-3-25.3.22-2.el7.x86_64.rpm[email protected] ~]# Yum install-y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-server-5.6-5.6.38-25.21.el7.x86_64.rpm

MySQL First boot

[[Email protected] ~]# service MySQL start--skip-grant-tables

Enter MySQL login, add user name, password and corresponding database user name, password permissions

mysql> use mysql;mysqlset password=password ("123456") where user="root"mysql> quit

Restart Database

[[Email protected] ~]# service MySQL restart

Restart the MySQL service and log in with the user name and password you created. When you re-enter, you are prompted to set the password

Mysql> SET Password=password ("123456")

to the corresponding need to access MySQL user name and password authorization, if not authorized, is unable to remotely connect MySQL (two Chitian two selected one)

authorizes the specified port user to connect to the MySQL database command mysql>grant all privileges on *. * to ' root ' @ ' 10.0.0.10 ' identified by ' 123456 ' with    GRANT OPTION; Authorized User Login MySQL database mysql>grant all privileges on * * to ' root ' @ '% ' identified by ' 123456 ' with GRANT OPTION;

Create a Sync account

' Wsrep '@'%'nx111111'; Mysql> quit

setting up MySQL for boot

[[email protected] ~]# chkconfig MySQL on

remove Postfix, this may conflict with MySQL configuration

[email protected] ~]# Yum Remove postfix-y

turn off MySQL

[[Email protected] ~]# service MySQL stop

configuration file

[Email protected] ~]# vim/etc/!includedir/etc/my.cnf.d/

Copy the swrep.conf file to/etc/my.cnf.d/

[Email protected] ~]# cp-r/usr/share/doc/mysql-wsrep-server-5.6-5.6. /wsrep.cnf/etc/my.cnf.d/

Configure The following configuration for the original

vim/etc/my.cnf.d/wsrep.conf[mysqld]server_id=Ten# Each node is different, respectivelyTen、 One、 ADefault_storage_engine=Innodbdatadir=/var/lib/mysql/Binlog_format=Rowlog-bin = mysql-Binlog-slave-updates =1Innodb_autoinc_lock_mode=2Lower_case_table_names=1# Modifying this configuration must restart Log_bin_trust_function_creators=1Wsrep_provider=/usr/lib64/galera-3/Libgalera_smm.sowsrep_sst_auth=Wsrep:nx111111wsrep_sst_method=Xtrabackupwsrep_cluster_name='Nx_wsrep'# Cluster Name, must be consistent. Wsrep_node_name=Node1 #本节点主机名wsrep_cluster_address= Gcomm://10.0.0.11,10.0.0.12 # Other node IP Wsrep_node_address = 10.0.0.10# this node Ipwsrep_sst_donor=node2,node3, #name of the other node Wsrep_sst_method=rsyncwsrep_slave_threads=8innodb_buffer_pool_size=10737418240innodb_log_file_size=134217728Innodb_flush_log_at_trx_commit=2#innodb_buffer_pool_dump_at_shutdown=1#innodb_buffer_pool_load_at_startup=1#innodb_log_file_buffer_size=16777216Skip-name-Resolve[client]user=Wsreppassword= nx111111

Start Mysql-wsrep

The first node in the cluster, starting with an empty address

[Email protected] ~]# mysqld_safe--wsrep_cluster_address=gcomm://  >/dev/null &

Other nodes, starting mode

[[email protected] ~]# mysqld_safe >/dev/null &

View cluster status

Confirm that MySQL 3306 port and Wsrep 4567 Port are in listening state

4567 3306 TCP         0      0 0.0. 0.0:4567            0.0.  0.0:*               LISTEN      5036/mysqld         tcp6       0      0 :: :3306                 :::*                    LISTEN      5036/mysqld

Whether the cluster function is turned on

' Wsrep_ready ' +---------------+-------+| variable_name | Value |+---------------+-------+| Wsrep_ready   | On    |+---------------+-------+1inset (0.00 sec)

Cluster ID number

mysql> SHOW  GLOBAL  STATUS  like  'wsrep_cluster_state_uuid'  ; +--------------------------+--------------------------------------+| Variable_name            | Value                                |+--------------------------+--------------------------------------+| wsrep_cluster_state_uuid | 176A8D84-E0BA-11E7-9E7B-D7B3A39130FF |+--------------------------+--------------------------------------+1  inset (0.00 sec)

Number of nodes in the cluster

mysql> SHOW  GLOBAL  STATUS  like  'wsrep_cluster_size'  ; +--------------------+-------+| Variable_name      3     |+--------------------+-------+1inset (  0.00 sec)

Cluster (other) node address

' wsrep_cluster_address ' ; +-----------------------+----------+| Variable_name         | Value    |+-----------------------+----------+| wsrep_cluster_address | gcomm://  | +-----------------------+----------+1inset (0.00 sec)

Reference

http://blog.csdn.net/yangchuan_csdn91/article/details/52689981?locationnum=2

Galera Cluster for MySQL