How to build a Percona XtraDB Cluster and perconaxtradb

How to build a Percona XtraDB Cluster and perconaxtradb

I. Environment preparation

Host IP host name operating system version PXC

192.168.244.146 node1 CentOS7.1 Percona-XtraDB-Cluster-56-5.6.30

192.168.244.147 node2 CentOS7.1 Percona-XtraDB-Cluster-56-5.6.30

192.168.244.148 node3 CentOS7.1 Percona-XtraDB-Cluster-56-5.6.30

Disable the firewall or allow connections from ports 3306,444, 4567, and 4568.

Disable SElinux

2. Download PXC

Install PXC yum Source

# Yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

This will generate a percona-release.repo file under/etc/yum. repos. d

Install PXC

# Yum install Percona-XtraDB-Cluster-56

The final version downloaded is the Percona-XtraDB-Cluster-56-5.6.30

Note: install these three nodes.

 

3. Configure nodes

Configure Node 1

Modify/etc/my. cnf of node1

[mysqld]datadir=/var/lib/mysqluser=mysql# Path to Galera librarywsrep_provider=/usr/lib64/galera3/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #1 addresswsrep_node_address=192.168.244.146# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST methodwsrep_sst_auth="sstuser:s3cret"

Start node1

# Systemctl start mysql@bootstrap.service

Note: This is the CentOS 7 startup method. If it is CentOS 6, the startup method is #/etc/init. d/mysql bootstrap-pxc.

The bootstrap startup is used to tell the database that this is the first node without data synchronization.

This method is equivalent to setting wsrep_cluster_address to gcomm ://.

 

You can log on to the client to view the database status.

Mysql> show status like 'wsrep % ';

Focus on the status of the following parameters

+------------------------------+--------------------------------------+| Variable_name                | Value                                |+------------------------------+--------------------------------------+| wsrep_local_state_uuid       | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c | ...| wsrep_local_state            | 4   | wsrep_local_state_comment    | Synced                               | ...| wsrep_cluster_size           | 1 ...| wsrep_cluster_status         | Primary                              || wsrep_connected              | ON                                   | ...| wsrep_ready                  | ON                                   |

 

In the preceding configuration file, there is a wsrep_sst_auth parameter. This parameter is used to add other nodes to the cluster and execute State Snapshot Transfer (similar to full synchronization) using XtraBackup.

Therefore, the next step is to authorize

mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret';mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';mysql> FLUSH PRIVILEGES;

Configure Node 2

Modify/etc/my. cnf of node2

[mysqld]datadir=/var/lib/mysqluser=mysql# Path to Galera librarywsrep_provider=/usr/lib64/galera3/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #2 addresswsrep_node_address=192.168.244.147# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST methodwsrep_sst_auth="sstuser:s3cret"

Start node2

# Systemctl start mysql

For CentOS 6, the start mode is #/etc/init. d/mysql start.

If a problem occurs during the startup process, you can view the mysql error log. For RPM installation, the default value is/var/lib/mysql/Host Name. err.

After the cluster is started, run the mysql> show status like 'wsrep % 'command to view the cluster information.

Configure Node 3

Modify/etc/my. cnf of node3

[mysqld]datadir=/var/lib/mysqluser=mysql# Path to Galera librarywsrep_provider=/usr/lib64/galera3/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #3 addresswsrep_node_address=192.168.244.148# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST methodwsrep_sst_auth="sstuser:s3cret"

Start node3

# Systemctl start mysql

Log on to the database and view the cluster status.

+------------------------------+--------------------------------------+| Variable_name                | Value                                |+------------------------------+--------------------------------------+| wsrep_local_state_uuid       | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c | ...| wsrep_local_state            | 4   | wsrep_local_state_comment    | Synced                               | ...| wsrep_cluster_size           | 3 ...| wsrep_cluster_status         | Primary                              || wsrep_connected              | ON                                   | ...| wsrep_ready                  | ON        

Wsrep_cluster_size shows that the cluster has three nodes.

Iv. Test

Next, let's test one. Create a table in node3 and insert records to check whether the query results in node1 and node2.

Create a test table in node3 and insert records

root@node3 > create table test.test(id int,description varchar(10));Query OK, 0 rows affected (0.18 sec)root@node3 > insert into test.test values(1,'hello,pxc');Query OK, 1 row affected (0.01 sec)

Query in node1 and node2

root@node1 > select * from test.test;+------+-------------+| id   | description |+------+-------------+|    1 | hello,pxc   |+------+-------------+1 row in set (0.00 sec)

root@node2 > select * from test.test;+------+-------------+| id   | description |+------+-------------+|    1 | hello,pxc   |+------+-------------+1 row in set (0.05 sec)

 

So far, Percona XtraDB Cluster has been set up ~

 

Summary:

1. When node2 is started, the startup fails. The following message is displayed in the error log:

2016-06-15 20:06:09 4937 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)         at gcomm/src/pc.cpp:connect():1622016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1387: Failed to open channel 'my_centos_cluster' at 'gcomm://192.168.244.146,192.168.244.147,192.168.244.148': -110 (Connection timed out)2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs connect failed: Connection timed out2016-06-15 20:06:09 4937 [ERROR] WSREP: wsrep::connect(gcomm://192.168.244.146,192.168.244.147,192.168.244.148) failed: 72016-06-15 20:06:09 4937 [ERROR] Aborting

2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.244.147' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5870'  ''        Read: '(null)'2016-06-15 20:27:03 5870 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.244.147' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5870'  '' : 2 (No such file or directory)2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.2016-06-15 20:27:03 5870 [ERROR] Aborting

In particular, the following error message is.

Later, we found that SElinux was not disabled...

In addition, in the process of adding nodes to the cluster, if the error about the xtrabackup-v2 is reported, you may wish to set the wsrep_sst_method method to rsync or mysqldump, to see if it is successful.

2. nodes started with the systemctl start mysql@bootstrap.service must be closed with the systemctl stop mysql@bootstrap.service, and if it is closed with the systemctl stop mysql, no effect.

 

Reference:

1. http://www.cnblogs.com/zejin2008/p/5475285.html

2. PXC official documentation

3. http://galeracluster.com/documentation-webpages/