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

It is believed that many inexperienced people don't know what to do about how to quickly build PXC clusters and Mycat fragments in mysql. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Set up PXC cluster

Refer to the percona official documentation:

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

1 preparation 1.1 download the software

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

Download a separate software package: qpress-11-1.el7.x86_64.rpm

Delete MariaDByum-y remove mari*1.3 open port firewall-cmd-- zone=public-- add-port=3306/tcp-- permanentfirewall-cmd-- zone=public-- add-port=4444/tcp-- permanentfirewall-cmd-- zone=public-- add-port=4567/tcp-- permanentfirewall-cmd-- zone=public-- add-port=4568/tcp-- permanentfirewall-cmd-- reload

1.4 close SELinuxvi / etc/selinux/config# and set the SELINUX property value to disabledreboot2 installation software (install PXC) 2.1 online installation $sudo yum install Percona-XtraDB-Cluster-572.2 local installation

Extract the downloaded installation package, put the qpress package together, then enter the directory, and issue the following command

$sudo yum localinstall * .rpm

[^ description]: You need to have root access on the node where you will be installing Percona XtraDB Cluster (either logged in as a user with root privileges or be able to run commands with sudo

2.3 prohibit initialization of self-booting chkconfig mysqld off2.4 database

Start the service

$sudo service mysql start

View MySQL initial password

Cat / var/log/mysqld.log | grep "A temporary password"

Log in with your root account

Mysql-u root-p

Modify the password

Mysql_secure_installation

Create a remote administrator account

CREATE USER 'YXC_admin'@'%' IDENTIFIED BY' Yxc_123456';GRANT all privileges ON*. * TO 'YXC_admin'@'%';FLUSH PRIVILEGES;# or as follows: CREATE USER' YXC'@'localhost' IDENTIFIED BY 'passw0rd';GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON*. * TO 'YXC'@'localhost';mysql > FLUSH PRIVILEGES

Out of Service

$sudo service mysql stop3 configuration file

Configure all nodes, configuration file content:

# vim / etc/ my.cnf [client] socket=/var/lib/mysql/ mysql.sock [mysqld] server-id=31#server-id=1 # the unique ID of the MySQL instance in the PXC cluster, which cannot be repeated. Must be the number datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidlog-binlog_slave_updatesexpire_logs_days=7character_set_server = utf8bind-address = 0.0.0.0.The DNS parsing skip-name-resolve# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0## cluster part wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_name=pxc3 must be skipped -cluster # name of PXC cluster wsrep_cluster_address=gcomm://192.168.56.31192.168.56.32192.168.56.33wsrep_node_name=pxc31 # name of current node wsrep_node_address=192.168.56.31 # IPwsrep_sst_method=xtrabackup-v2 # synchronization method of current node (mysqldump, Rsync, xtrabackup) wsrep_sst_auth= YXC_admin:Yxc_123456 # account used synchronously # SST means State Snapshot Transferpxc_strict_mode=ENFORCING # synchronous strict mode binlog_format=ROW # based on ROW replication (secure and reliable) default_storage_engine=InnoDB # default engine innodb_autoinc_lock_mode=2 # Primary key self-growth does not lock table 4 node start and stop

Boot Node start and stop (Bootstrap the first node)

Systemctl start mysql@bootstrap.servicesystemctl stop mysql@bootstrap.servicesystemctl restart mysql@bootstrap.service

Non-boot node start and stop (add other nodes)

Service start mysqlservice stop mysqlservice restart mysql

Judge the boot node

# # cat / var/lib/mysql/grastate.dat# GALERA saved stateversion: 2.1uuid: 4580a102-bd96-11e9-a653-2a1e935fbf67seqno:-1safe_to_bootstrap: 0#safe_to_bootstrap: 1, then boot the startup node

If the last shut-down PXC node exits safely, the node should be started first next time and should be started with the boot node.

If the last closed PXC node does not exit safely, modify the / var/lib/mysql/grastate.dat file first, set the safe_to_bootstrap attribute value to 1, and then start according to the boot node

5 status information 5.1 confirm status show status like'% wsrep%';## wsrep:write set replication#Percona XtraDB Cluster is based on Percona Server running with the XtraDB storage engine. It uses the Galera library, which is an implementation of the write set replication (wsrep) API developed by Codership Oy. The default and recommended data transfer method is via Percona XtraBackup.

5.2 status description (pictures from the Internet, for reference only)

6 synchronization principle (pictures from the Internet, for reference only)

7. Mycat sharding 7.1Mycat installation

(1) install JDK

# search JDK version yum search jdk # install JDK1.8 development version yum install java-1.8.0-openjdk-devel.x86_64

(2) configure environment variables

# check the JDK installation path ls-lrt / etc/alternatives/javavi / etc/profile# and add the JDK path to the end of the file, such as export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64/source / etc/profile

(3) download MyCat

Http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar. Gz

(4) upload MyCat package to virtual machine

(5) install the unzip package and extract the MyCat

Yum install unzipunzip MyCAT package name

(6) Open firewall ports 8066 and 9066 and shut down SELINUX

(7) modify the permissions in the bin directory of MyCat

Chmod-R 777. / * .sh

(8) MyCat startup and shutdown

# bin directory of cd MyCat. / startup_nowrap.sh # launch MyCatps-aux # View system process kill-9 MyCat process number 7.2 Mycat configuration 7.2.1 schema.xml

Select user () Select user () 7.2.2 server.xml Yxc_123456 ecs 7.2.3 rule.xml sharding_id customer-hash-int customer-hash-int.txt8. Haproxy + keepalived load balancing

Deploy Haproxy with a virtual machine instance

Install Haproxy

Yum install-y haproxy

Edit configuration file

Vi / etc/haproxy/haproxy.cfgglobal log 127.0.0.1 local2 chroot / var/lib/haproxy pidfile / var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon # turn on stats unix socket stats socket / var/lib/haproxy/statsdefaults mode httplog global option httplog Option dontlognull option http-server-close option forwardfor except 127.0.0.0/8 option redispatch retries 3 timeout http-request 10s timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout http-keep-alive 10s timeout check 10s maxconn 3000listen admin_stats bind 0.0.0.0 statistics stats auth admin:abc123456listen proxy-mysql bind 4001 mode http stats uri / dbs stats realm Global\ log format server mycat_1 192.168.99.131 3000listen admin_stats bind 3306 check port 8066 maxconn 2000 server mycat_2 192.168.99.132 3306 check port 8066 Maxconn 2000 option tcpka # using keepalive to detect dead chains

Start Haproxy

Service haproxy start

Access the Haproxy monitoring screen

Http://192.168.99.131:4001/dbs

Use another virtual machine to install Haproxy as described above

Deploy Keepalived on a Haproxy virtual machine instance

VRRP protocol with firewall turned on

# enable VRRPfirewall-cmd-- direct-- permanent-- add-rule ipv4 filter INPUT 0-- protocol vrrp-j ACCEPT# Application Settings firewall-cmd-- reload

Install Keepalived

Yum install-y keepalived

Edit configuration file

Vim / etc/keepalived/keepalived.confvrrp_instance VI_1 {state MASTER interface ens33 virtual_router_id 51 priority 100 advert_int 1 authentication {auth_type PASS auth_pass 123456} virtual_ipaddress {192.168.99.133}}

Start Keepalived

Service keepalived start

Ping 192.168.99.133

On another Haproxy virtual machine, deploy Keepalived as described above

Connect to 192.168.99.133 using the MySQL client

After reading the above, have you mastered how to quickly build PXC clusters and Mycat sharding in mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.