Shulou(Shulou.com)05/31 Report--

This article introduces what the PXC practice of MySQL high availability architecture is like, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

1. Introduction to PXC

Official introduction:

Percona XtraDB Cluster is High Availability and Scalability solution for MySQL Users.

Main features:

Synchronous replication. Transaction either committed on all nodes or none.

Multi-master replication. You can write to any node.

Parallel applying events on slave. Real "parallel replication".

Automatic node provisioning.

Data consistency. No more unsynchronized slaves.

Official manual:

Https://www.percona.com/doc/percona-xtradb-cluster/5.6/index.html

2. PXC architecture

Refer to http://blog.csdn.net/signmem/article/details/17379427 for this picture

The following picture is from the official document:

3. PXC installation and deployment

This experiment adopts 3 nodes, centos6.4_x86_64 operating system, and the PXC version is 5.6 Galera Murray 3.

1) download the required software packages

Cmake-2.8.12.2.tar.gz http://www.linuxfromscratch.org/blfs/view/7.5/general/cmake.html

Libev-4.22.tar.gz http://dist.schmorp.de/libev/Attic/

Percona-XtraDB-Cluster-5.6.30-76.3.tar.gz https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/LATEST/

DBD-mysql-4.033_02.tar.gz http://www.filewatcher.com/d/FreeBSD/distfiles/Other/DBD-mysql-4.018.tar.gz.133427.html

Percona-xtrabackup-2.2.9.tar.gz

Socat-2.0.0-b9.tar.gz http://www.dest-unreach.org/socat/

DBI-1.636.tar.gz http://www.cpan.org/modules/by-module/DBI/

Percona-XtraDB-Cluster-5.6.30-25.16-raa929cb-el6-x86_64-bundle.tar

Zlib-1.2.3.tar.gz http://www.zlib.net/

2) extract the installation package

For i in `ls`; do tar-xzvf $I; tar-xvf $I; done

3) install dependency packages

Configure the local yum source:

[root@node3 PXC] # mkdir / media/cdrom

[root@node3 PXC] # mount CentOS-6.4-x86_64-bin-DVD1.iso / media/cdrom/-o loop

[root@node3 PXC] # rm-rf / etc/yum.repos.d/*.repo

[root@node3 PXC] # vi / etc/yum.repos.d/CentOS6.repo

[Base]

Name=CentOS6 ISO Base

Baseurl= file:///media/cdrom

Enabled=1

Gpgcheck=0

Dependent package check installation:

Yum install-y git scons gcc gathers + gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel

Rpm-q git scons gcc gathers + gcc-c++ openssl check cmake bison libaio libboost-all-dev libasio-dev libaio-dev libncurses5-dev libreadline-dev libpam-dev ncurses-devel

Cmake installation:

[root@node3 PXC] # cd cmake-2.8.12.2

[root@node3 cmake-2.8.12.2] #. / bootstrap

[root@node3 cmake-2.8.12.2] # make- j 8

[root@node3 cmake-2.8.12.2] # make install

Socat installation:

[root@node3 PXC] # cd socat-2.0.0-b9

[root@node3 socat-2.0.0-b9] #. / configure

[root@node3 socat-2.0.0-b9] # make-j 8

[root@node3 socat-2.0.0-b9] # make install

[root@node3 socat-2.0.0-b9] # ln-s / usr/local/bin/socat / usr/bin/

DBI installation:

[root@node3 PXC] # cd DBI-1.636

[root@node3 DBI-1.636] # perl Makefile.PL

[root@node3 DBI-1.636] # make-j 8

[root@node3 DBI-1.636] # make install

DBD installation:

[root@node3 PXC] # cd DBD-mysql-4.033_02

[root@node3 DBD-mysql-4.033_02] # perl Makefile.PL-- mysql_config=/usr/local/mysql/bin/mysql_config

[root@node3 DBD-mysql-4.033_02] # make-j 8

[root@node3 DBD-mysql-4.033_02] # make install

Percona Toolkit installation:

[root@node3 PXC] # for i in `rpm-qa | grep mysql`; do rpm-e $I-- nodeps; done

[root@node3 PXC] # cd percona-xtrabackup-2.2.9

[root@node3 percona-xtrabackup-2.2.9] # cmake. /

[root@node3 percona-xtrabackup-2.2.9] # make-j 8

[root@node3 percona-xtrabackup-2.2.9] # make install

[root@node3 percona-xtrabackup-2.2.9] # ln-s / usr/local/xtrabackup/bin/* / usr/bin/

[root@node3 PXC] # rpm-ivh Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm-- nodeps

[root@node3 PXC] # rpm-ivh Percona-XtraDB-Cluster-devel-56-5.6.30-25.16.1.el6.x86_64.rpm

[root@node3 PXC] # rpm-ivh Percona-XtraDB-Cluster-galera-3-3.16-1.rhel6.x86_64.rpm-- nodeps

4) installation and configuration of PXC database

Percona-XtraDB-Cluster installation:

[root@node3 PXC] # tar-xzvf Percona-XtraDB-Cluster-5.6.30-76.3.tar.gz

[root@node3 PXC] # useradd mysql-s / sbin/nologin

[root@node3 PXC] # cd Percona-XtraDB-Cluster-5.6.30-76.3

[root@node3 Percona-XtraDB-Cluster-5.6.30-76.3] # cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1-DENABLED_LOCAL_INFILE=1-DMYSQL_DATADIR=/usr/local/mysql/data /-DMYSQL_USER=mysql-DENABLE_DOWNLOADS=1-DWITH_WSREP=1-DWITH_EDITLINE=0

[root@node3 Percona-XtraDB-Cluster-5.6.30-76.3] # make-j 4

[root@node3 Percona-XtraDB-Cluster-5.6.30-76.3] # make install

Initialize the database:

[root@node3 PXC] # cd / usr/local/mysql

[root@node3 mysql] # / scripts/mysql_install_db-- user=mysql-- basedir=/usr/local/mysql-- datadir=/usr/local/mysql/data/

[root@node3 mysql] # cp support-files/mysql.server / etc/init.d/mysqld

[root@node3 mysql] # chkconfig-- add mysqld

[root@node3 mysql] # chkconfig mysqld on

[root@node3 mysql] # chown-R mysql.mysql / usr/local/mysql

[root@node3 mysql] # vi ~ / .bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

[root@node3 mysql] # source ~ / .bash_profile

Configure my.cnf

Node1 node:

[client]

Socket=/usr/local/mysql/mysql.sock

[mysqld]

Datadir=/usr/local/mysql/data

User=mysql

Log-bin=mysql-binlog

Binlog_format=ROW

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Innodb_locks_unsafe_for_binlog=1

Innodb_autoinc_lock_mode=2

Wsrep_provider=/usr/lib64/libgalera_smm.so

Wsrep_cluster_address=gcomm://IP1,IP2,IP3

Wsrep_slave_threads=2

Wsrep_cluster_name=my_centos_cluster

# wsrep_sst_method=rsync

# wsrep_sst_method=mysqldump

Wsrep_sst_method=xtrabackup

Wsrep_node_name=node1

Wsrep_node_address=IP1

Wsrep_sst_auth= "repuser:userpasswd"

Node2 node

[client]

Socket=/usr/local/mysql/mysql.sock

[mysqld]

Datadir=/usr/local/mysql/data

User=mysql

Log-bin=mysql-binlog

Binlog_format=ROW

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Innodb_locks_unsafe_for_binlog=1

Innodb_autoinc_lock_mode=2

Wsrep_provider=/usr/lib64/libgalera_smm.so

Wsrep_cluster_address=gcomm://IP1,IP2,IP3

Wsrep_slave_threads=2

Wsrep_cluster_name=my_centos_cluster

# wsrep_sst_method=rsync

# wsrep_sst_method=mysqldump

Wsrep_sst_method=xtrabackup

Wsrep_node_name=node2

Wsrep_node_address=IP2

Wsrep_sst_auth= "repuser:userpasswd"

Node3 node

[client]

Socket=/usr/local/mysql/mysql.sock

[mysqld]

Datadir=/usr/local/mysql/data

User=mysql

Log-bin=mysql-binlog

Binlog_format=ROW

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Innodb_locks_unsafe_for_binlog=1

Innodb_autoinc_lock_mode=2

Wsrep_provider=/usr/lib64/libgalera_smm.so

Wsrep_cluster_address=gcomm://IP1,IP2,IP3

Wsrep_slave_threads=2

Wsrep_cluster_name=my_centos_cluster

# wsrep_sst_method=rsync

# wsrep_sst_method=mysqldump

Wsrep_sst_method=xtrabackup

Wsrep_node_name=node3

Wsrep_node_address=IP3

Wsrep_sst_auth= "repuser:userpasswd"

Start the Node1 database

[root@node1 mysql] # service mysqld bootstrap-pxc

Create a PXC replication account and authorize

Mysql > show status like 'wsrep%'

Mysql > delete from mysql.user where user =''

Mysql > delete from mysql.user where user = 'root' and host='::1'

Mysql > delete from mysql.user where user = 'root' and host='node1'

Mysql > delete from mysql.user where user = 'root' and host='127.0.0.1'

Mysql > CREATE USER 'repuser'@'localhost' IDENTIFIED BY' userpasswd'

Mysql > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *. * TO 'repuser'@'localhost'

Mysql > FLUSH PRIVILEGES

Start the node2 node database

(first, set the wsrep_sst_method parameter value of my.cnf to rsync. After the node is joined, you can set it back to xtrabackup and restart the database)

[root@node2 mysql] # service mysqld start

Start the node3 node database

(first, set the wsrep_sst_method parameter value of my.cnf to rsync. After the node is joined, you can set it back to xtrabackup and restart the database)

[root@node3 mysql] # service mysqld start

4. installation error message and solution

Error message 01:

[root@node3 PXC] # rpm-ivh Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm

Warning: Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Error: Failed dependencies:

Libcrypto.so.10 (libcrypto.so.10) (64bit) is needed by Percona-XtraDB-Cluster-shared-56-1 64bit 5.6.30-25.16.1.el6.x86_64

Libssl.so.10 (libssl.so.10) (64bit) is needed by Percona-XtraDB-Cluster-shared-56-1 64bit 5.6.30-25.16.1.el6.x86

Solution:

Check that the corresponding openssl is installed, and if you have installed and found the appropriate library files, you can ignore the dependencies.

[root@node3 PXC] # rpm-qa | grep openssl

Openssl-1.0.0-27.el6.x86_64

Openssl098e-0.9.8e-17.el6.centos.2.x86_64

Openssl-devel-1.0.0-27.el6.x86_64

[root@node3 PXC] # ls-l / usr/lib64/libssl.so.10

Lrwxrwxrwx. 1 root root 15 Aug 17 17:41 / usr/lib64/libssl.so.10-> libssl.so.1.0.0

[root@node3 PXC] # rpm-ivh Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm-- nodeps

Error message 02:

Which: no socat in (/ usr/sbin:/sbin:/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/mysql/bin)

WSREP_SST: [ERROR] socat not found in path: / usr/sbin:/sbin:/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/mysql/bin (20160818 14 socat not found in path 08.903)

2016-08-18 14:45:08 7131 [ERROR] WSREP: Failed to read 'ready' from: wsrep_sst_xtrabackup-- role 'joiner'-- address' IP2'-- datadir'/ usr/local/mysql/data/'-defaults-file'/ etc/my.cnf'-defaults-group-suffix'- parent '7131''

Read:'(null)'

2016-08-18 14:45:08 7131 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-- role 'joiner'-- address' IP2'-- datadir'/ usr/local/mysql/data/'-- defaults-file'/ etc/my.cnf'-- defaults-group-suffix'--parent '7131'': 2 (No such file or directory)

2016-08-18 14:45:08 7131 [ERROR] WSREP: Failed to prepare for 'xtrabackup' SST. Unrecoverable.

2016-08-18 14:45:08 7131 [ERROR] Aborting

Solution:

Install socat and do soft connection ln-s / usr/local/bin/socat / usr/bin/

Error message 03:

Node2 node

Tar: This does not look like a tar archive

Tar: Exiting with failure status due to previous errors

WSREP_SST: [ERROR] Error while getting data from donor node: exit codes: 0 2 (20160818 15 Error while getting data from donor node 52 2.658)

WSREP_SST: [ERROR] Cleanup after exit with status:32 (20160818, 15, 15, 32, 52. 660)

2016-08-18 15:32:52 12825 [Warning] WSREP: 0.0 (node1): State transfer to 1.0 (node2) failed:-22 (Invalid argument)

2016-08-18 15:32:52 12825 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg (): Will never receive state. Need to abort

2016-08-18 15:32:52 12825 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-- role 'joiner'-- address' IP2'-- datadir'/ usr/local/mysql/data/'-- defaults-file'/ etc/my.cnf'-- defaults-group-suffix'--parent '12825'': 32 (Broken pipe)

2016-08-18 15:32:52 12825 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.

2016-08-18 15:32:52 12825 [ERROR] WSREP: SST script aborted with error 32 (Broken pipe)

2016-08-18 15:32:52 12825 [ERROR] WSREP: SST failed: 32 (Broken pipe)

2016-08-18 15:32:52 12825 [ERROR] Aborting

Node1 node

160818 15:32:52 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/usr/local/mysql/mysql.sock' as' repuser' (using password: YES).

Innobackupex: got a fatal error with the following stacktrace: at / usr/bin/innobackupex line 2999

Main::mysql_connect ('abort_on_error', 1) called at / usr/bin/innobackupex line 1539

Innobackupex: Error: Failed to connect to MySQL server as DBD::mysql module is not installed at / usr/bin/innobackupex line 2999.

Solution:

Install DBI and DBD packages, and you need to install Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64 and Percona-XtraDB-Cluster-devel-56-5.6.30-25.16.1.el6.x86_64

Error message 04:

WSREP_SST: [ERROR] xtrabackup process ended without creating'/ usr/local/mysql/data//xtrabackup_galera_info' (20160818 16 xtrabackup process ended without creating'/ 33.614)

WSREP_SST: [ERROR] Cleanup after exit with status:32 (20160818 1630 Cleanup after exit with status:32 33.628)

WSREP_SST: [INFO] Removing the sst_in_progress file (20160818 1630 Removing the sst_in_progress file 33.629)

2016-08-18 16:30:33 15314 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-- role 'joiner'-- address' IP2'-- datadir'/ usr/local/mysql/data/'-- defaults-file'/ etc/my.cnf'-- defaults-group-suffix'--parent '15314'': 32 (Broken pipe)

2016-08-18 16:30:33 15314 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.

2016-08-18 16:30:33 15314 [ERROR] WSREP: SST script aborted with error 32 (Broken pipe)

2016-08-18 16:30:33 15314 [ERROR] WSREP: SST failed: 32 (Broken pipe)

2016-08-18 16:30:33 15314 [ERROR] Aborting

Solution:

For the initial data synchronization problem, you can complete the data synchronization by setting the node2,node3 parameter wsrep_sst_method=rsync, and then change it back to wsrep_sst_method=xtrabackup.

5. Appendix

1) openssl source code installation

Wget ftp://ftp.openssl.org/source/openssl-1.0.0c.tar.gz

Tar-zxf openssl-1.0.0c.tar.gz

Cd openssl-1.0.0c/

. / config-prefix=/usr/local-openssldir=/usr/local/ssl

Make & & make install

. / config shared-prefix=/usr/local-openssldir=/usr/local/ssl

Make clean

Make & & make install

2) gcc upgrade

Four installation packages are required, which are: boost_1_60_0.tar.gz gcc-4.8.0.tar.gz gmp-5.1.3.tar.gz mpc-1.0.3.tar.gz mpfr-3.1.3.tar.gz

Gmp installation

. / configure-- prefix=/usr/local/gmp

Make

Make test

Make install

Mpfr installation

. / configure-- with-gmp-include=/usr/local/gmp/include-- with-gmp-lib=/usr/local/gmp/lib-- prefix=/usr/local/mpfr

Make

Make install

Mpc installation

. / configure-with-mpfr-include=/usr/local/mpfr/include-with-mpfr-lib=/usr/local/mpfr/lib-with-gmp-include=/usr/local/gmp/include-with-gmp-lib=/usr/local/gmp/lib

Make

Make install

Gcc installation

. / configure-- with-gmp-include=/usr/local/gmp/include-- with-gmp-lib=/usr/local/gmp/lib-- with-mpfr-include=/usr/local/mpfr/include-- with-mpfr-lib=/usr/local/mpfr/lib-- with-mpc-include=/usr/local/mpc/include-- with-mpc-lib=/usr/local/mpc/lib-- enable-languages=c,c++-- enable-threads=posix-- disable-multilib

3) reference command

Show status like'% wsrep%'

Show variables like 'wsrep_sst_method'

This is the end of the PXC practice on the MySQL high-availability architecture. I hope the above content can be of some help and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.