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)