MySQL High-availability PXC (Percona XtraDB Cluster)

MySQL High-availability PXC (Percona XtraDB Cluster)

Reference

Http://www.cnblogs.com/xiaoboluo768/p/5135619.html

The server environment information is as follows:

Node1 192.168.0.100

Node2 192.168.0.101

Node3 192.168.0.102

1. Install the Software dependency package (add Repl source, install dependencies, and 3 server operations. ）

[Email protected] ~]# RPM-IVH http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

[email protected] ~]# Yum install perl-io-socket-ssl perl-dbd-mysql perl-time-hires socat NC libaio rsync-y

2. Install xtrabackup (PXC Sync data required, why use Xtrabackup? Check the official documents yourself)

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.11/binary/redhat/6/x86_64/ percona-xtrabackup-2.2.11-1.el6.x86_64.rpm

RPM-IVH percona-xtrabackup-2.2.11-1.el6.x86_64.rpm

3. Download and install Percona-xtradb-cluster

wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.24-25.11/binary/ tarball/percona-xtradb-cluster-5.6.24-rel72.2-25.11. Linux.x86_64.tar.gz

If you cannot download the package, please FQ or go to the official website to find the latest version.

Tar XF percona-xtradb-cluster-5.6.24-rel72.2-25.11. Linux.x86_64.tar.gz-c/usr/local/services/

cd/usr/local/services/

Ln-s percona-xtradb-cluster-5.6.24-rel72.2-25.11. Linux.x86_64/mysql

CD MySQL

Groupadd MySQL

Useradd-r-G MySQL MySQL

Chown-r MySQL.

Chgrp-r MySQL.

CP SUPPORT-FILES/MY-DEFAULT.CNF/ETC/MY.CNF

Mkdir-p/data/mysql/data

Chown-r Mysql.mysql/data/mysql/data

CP Support-files/mysql.server/etc/init.d/mysqld

4, the above installation steps three nodes are installed, if at the time of initialization libssl.so.6 and libcrypto.so.6 two dynamic library file does not exist, do the following link:

Yum Install-y libssl.so.6

Ln-s/usr/lib64/libssl.so/usr/lib64/libssl.so.6

Ln-s/usr/lib64/libcrypto.so/usr/lib64/libcrypto.so.6

./scripts/mysql_install_db--basedir=/usr/local/services/mysql--datadir=/data/mysql/data/--user=mysql

#################################################################### Node1 ###################################### ######################################################

5, Node1 Configuration and start the following:

Modify the MY.CNF configuration file as follows:

/ETC/MY.CNF Add the following content:

[Mysqld]

Basedir =/usr/local/services/mysql

DataDir =/data/mysql/data

and add the following parameters to the [MYSQLD] paragraph:

Wsrep_provider=/usr/local/services/mysql/lib/libgalera_smm.so #库文件

wsrep_cluster_address=gcomm://192.168.0.100,192.168.0.101,192.168.0.102 #节点中所有ip

wsrep_node_address=192.168.0.100 #节点的ip

wsrep_slave_threads=2 #开启的复制线程数, CPU core count

Binlog_format=row #binlog格式必须为row

Default_storage_engine=innodb #暂时不支持其他存储引擎, only support InnoDB, of course, can support MyISAM, need additional parameters to open

innodb_autoinc_lock_mode=2 #自增锁的优化

Wsrep_cluster_name=pxc-xiaoboluo #集群名字

Wsrep_sst_auth=sst:xiaoboluo #sst模式需要的用户名和密码

Wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据. also supports Mysqldump,rsync

Start, authorize the operation, for the first node must be started in a special way, as follows:

View startup options:/etc/init.d/mysqld--help

usage:mysql {START|STOP|RESTART|RESTART-BOOTSTRAP|RELOAD|FORCE-RELOAD|STATUS|BOOTSTRAP-PXC} [MySQL (Percona XtraDB Cluster) options]

Start:

[Email protected] mysql]#/etc/init.d/mysqld BOOTSTRAP-PXC

Bootstrapping PXC (Percona XtraDB Cluster) starting MySQL (p[OK]tradb Cluster) ...

[Email protected] mysql]#

To view, you can find the boot two ports

[Email protected] ~]# NETSTAT-NLTP | grep mysqld

TCP 0 00.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld

TCP 0 00.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld

[Email protected] ~]#

Authorization, the Grant method is recommended

MySQL Login:

Mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' sst ' @ ' localhost ' identified by ' Xiaoboluo ';

Query OK, 0 rows affected (0.03 sec)

mysql> FLUSH privileges;

Query OK, 0 rows affected (0.03 sec)

Here our first node is done, the remaining two node configuration is slightly different, the installation method is the same, no longer repeat

######################################## #node2 node3########################################################### ###########################################

6, the Node2 configuration is as follows:

[Mysqld]

Basedir =/usr/local/services/mysql

DataDir =/data/mysql/data

Wsrep_provider=/usr/local/services/mysql/lib/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.0.100,192.168.0.101,192.168.0.102

Wsrep_node_address=192.168.0.101

wsrep_slave_threads=2

Binlog_format=row

Default_storage_engine=innodb

innodb_autoinc_lock_mode=2

Wsrep_cluster_name=pxc-xiaoboluo

Wsrep_sst_auth=sst:xiaoboluo

Wsrep_sst_method=xtrabackup-v2

7, the NODE3 configuration is as follows:

[Mysqld]

Basedir =/usr/local/services/mysql

DataDir =/data/mysql/data

Wsrep_provider=/usr/local/services/mysql/lib/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.0.100,192.168.0.101,192.168.0.102

wsrep_node_address=192.168.0.102

wsrep_slave_threads=2

Binlog_format=row

Default_storage_engine=innodb

innodb_autoinc_lock_mode=2

Wsrep_cluster_name=pxc-xiaoboluo

Wsrep_sst_auth=sst:xiaoboluo

Wsrep_sst_method=xtrabackup-v2

8, NODE2,NODE3 nodes are configured to complete, we start, for the 2nd, 3 nodes start the same as our normal mode of MySQL boot.

Node2 Boot:

[[email protected] ~]#/etc/init.d/mysqld start

MySQL (Percona XtraDB Cluster) isn't running, but lock Fil[failed]lock/subsys/mysql) exists

Starting MySQL (Percona XtraDB Cluster) ..... ..... State transfer in progress, setting sleep higher

.............

[OK]

View Node2 Logs

[Email protected] mysql]# tail-f/data/mysql/3306/data/node2.err

2017-01-08 09:15:25 6036 [Note] Wsrep:repl protocols:7 (3, 2)

2017-01-08 09:15:25 6036 [Note] wsrep:service thread queue flushed.

2017-01-08 09:15:25 6036 [Note] wsrep:assign initial position for Certification:2, protocol Version:3

2017-01-08 09:15:25 6036 [Note] wsrep:service thread queue flushed.

2017-01-08 09:15:27 6036 [Note] Wsrep:member 2.0 (NODE3) Requested State transfer from ' *any* '. Selected 0.0 (Node1) (synced) as donor.

2017-01-08 09:15:28 6036 [note] Wsrep: (B21bada5, ' tcp://0.0.0.0:4567 ') turning message relay requesting off

2017-01-08 09:15:43 6036 [Note] wsrep:0.0 (node1): State Transfer to 2.0 (NODE3) complete.

2017-01-08 09:15:43 6036 [Note] wsrep:member 0.0 (node1) synced with group.

2017-01-08 09:15:48 6036 [Note] wsrep:2.0 (NODE3): State transfer from 0.0 (node1) complete.

2017-01-08 09:15:48 6036 [Note] Wsrep:member 2.0 (NODE3) synced with group.

[[email protected] mysql]#/etc/init.d/mysqld start

Starting MySQL (Percona XtraDB Cluster) ... State transfer in progress, setting sleep higher

... [OK]

######################################## #测试 ####################################################################

9. Testing

The Node1 node creates the table T1, inserts the data, and at the other two nodes you can see that it's not tested.

This is basically the end of the building. 3 nodes Support Write/read operation at the same time. The maximum number of nodes is not recommended for more than 8, more detailed introduction and details of the recommendations you refer to the official documents.

Summarize:

Percona XtraDB Cluster now has a business in use, I'm just a simple build and test, if the online need to use we also need to carry out the relevant stress test. such as using Sysbench, or Tpcc-mysql.

Usually we use Percona XtraDB cluster without any problems, if used by the combination of harproxy, or lvs+keepalived way to use will be more perfect.

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1893619

MySQL High-availability PXC (Percona XtraDB Cluster)