MySQL High-availability PXC (Percona XtraDB Cluster)

Source: Internet
Author: User
Tags install perl mysql login percona rsync

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.