Shulou(Shulou.com)06/01 Report--

The following is about the mainstream enterprise MySQL high-availability cluster architecture application tool PXC, the secret of the text is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading PXC, the mainstream enterprise MySQL high availability cluster architecture application tool.

PXC architecture diagram:

Pxc belongs to a set of near-perfect MySQL high availability cluster architecture.

Summary of advantages:

It can achieve real-time synchronization without delay.

Fully compatible with MySQL

For the addition of new nodes in the cluster, it is easy to maintain

Strong consistency of data

Summary of inadequacies:

Only Innodb storage engine is supported

There is a multi-node update update problem, that is, the write magnification problem.

Online DDL statement, table locking problem

Sst aims at the problem that the transmission cost of new nodes is too high.

Actual combat process:

Environment introduction:

192.168.56.100 node1192.168.56.101 node2192.168.56.102 node3

Considerations before installation:

The firewall iptables of all three machines should be turned off, and the server-id of the three machines cannot be the same.

Download the PXC package:

Https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/LATEST/

Here I download: Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64.tar.gz

The next three machines need to be installed for the basic software package, and you can install it using yum to resolve the dependencies.

Perl-IO-Socket-SSL.noarch perl-DBD-MySQL.x86_64 perl-Time-HiRes openssl openssl-devel socat

Perform the operation on Node 1 (192.168.56.100):

Extract the package and grant permissions first

Cd / usr/local/tar-zxvf Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64.tar.gzln-s Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64 mysqlchown mysql:mysql-R mysqlmkdir-p / data/mysqlchown mysql:mysql-R / data/mysql

Configure the parameter file of pxc. Here, there are several more points than ordinary MySQL parameter file:

One note to note: here the format of binlog must be row

# pxcdefault_storage_engine=Innodbinnodb_autoinc_lock_mode=2wsrep_cluster_name=pxc_zs-name of the cluster wsrep_cluster_address=gcomm://192.168.56.100192.168.56.101192.168.56.102 (IP of nodes in the cluster) wsrep_node_address=192.168.56.100- ip address of the current machine wsrep_provider=/usr/local/mysql/lib/libgalera_smm.sowsrep_sst_method=xtrabackup -v2 (sst transfer method) wsrep_sst_auth=sst:zs (account permissions)

Initialization data:

/ usr/local/mysql/scripts/mysql_install_db-basedir=/usr/local/mysql-datadir=/data/mysql/-defaults-file=/etc/my.cnf-user=mysql

Start the service of the first node:

Cd / usr/local/mysql/support-filescp mysql.server / etc/init.d/mysql [root@node1 support-files] # / etc/init.d/mysql bootstrap-pxcBootstrapping PXC (Percona XtraDB Cluster) MySQL (Percona Xt [OK] ster) running (4740)

After successful startup, maintain the database environment and create users

Mysql > delete from mysql.user where username created root' or hostworthy 'localhostworthy' MySQL > grant all privileges on *. * to 'zs'@'%' identified by' zs';mysql > grant all privileges on *. * to 'sst'@'localhost' identified by' zs';mysql > flush privileges

Perform the operation on Node 2 (192.168.56.101):

Cd / usr/local/tar-zxvf Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64.tar.gzln-s Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64 mysqlchown mysql:mysql-R mysqlmkdir-p / data/mysqlchown mysql:mysql-R / data/mysql

Edit the configuration file for pxc:

# pxcdefault_storage_engine=Innodbinnodb_autoinc_lock_mode=2wsrep_cluster_name=pxc_zs-name of the cluster wsrep_cluster_address=gcomm://192.168.56.100192.168.56.101192.168.56.102 (IP of nodes in the cluster) wsrep_node_address=192.168.56.101- ip address of the current machine wsrep_provider=/usr/local/mysql/lib/libgalera_smm.sowsrep_sst_method=xtrabackup -v2 (sst transfer method) wsrep_sst_auth=sst:zs (account permissions)

Initialization data:

/ usr/local/mysql/scripts/mysql_install_db-basedir=/usr/local/mysql-datadir=/data/mysql/-defaults-file=/etc/my.cnf-user=mysql

Start the service for the second node:

Cd / usr/local/mysql/support-filescp mysql.server / etc/init.d/mysql [root@node2 mysql] # / etc/init.d/mysql startStarting MySQL (Percona XtraDB Cluster).. [OK]

Execute on the third node (192.168.56.102):

Cd / usr/local/tar-zxvf Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64.tar.gzln-s Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64 mysqlchown mysql:mysql-R mysqlmkdir-p / data/mysqlchown mysql:mysql-R / data/mysql

Edit the configuration file for pxc:

# pxcdefault_storage_engine=Innodbinnodb_autoinc_lock_mode=2wsrep_cluster_name=pxc_zs-name of the cluster wsrep_cluster_address=gcomm://192.168.56.100192.168.56.101192.168.56.102 (IP of nodes in the cluster) wsrep_node_address=192.168.56.102- ip address of the current machine wsrep_provider=/usr/local/mysql/lib/libgalera_smm.sowsrep_sst_method=xtrabackup -v2 (sst transfer method) wsrep_sst_auth=sst:zs (account permissions)

Initialization data:

/ usr/local/mysql/scripts/mysql_install_db-basedir=/usr/local/mysql-datadir=/data/mysql/-defaults-file=/etc/my.cnf-user=mysql

Start the service for the third node:

Cd / usr/local/mysql/support-filescp mysql.server / etc/init.d/mysql [root@node3 mysql] # / etc/init.d/mysql startStarting MySQL (Percona XtraDB Cluster).. [OK]

Note: the other two nodes are also installed and initialized.

But the startup mode is not / etc/init.d/mysql bootstrap-pxc, but / etc/init.d/mysql start.

All three nodes have been started successfully so that the execution of a DML statement on any one node is synchronized to the other two nodes.

Is there anything you don't understand about the above enterprise mainstream MySQL high availability cluster architecture application tool PXC? Or if you want to know more about it, you can continue to follow our industry information section.

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.