preface

Now there are a variety of mysql self-built cluster solutions, Keepalived, MHA, PXC, mysql master/standby, etc., but currently according to their own situation and conditions, choose to use PXC put to build, the biggest benefit is, multiple master and multiple standby, that is, master and slave as one, no synchronization delay problem, easy to use.

I have used PXC and Docker container installation directly. Personally, I think docker installation is more convenient and easier to maintain, so I recommend you to use this method.

Set up the environment

The environment
centos7
PXC mirroring: the latest version, 8.0+

Host IP	The deployment of	swarm
172.16.9.40	pxc1	manager
172.16.9.41	pxc2	worker
172.16.9.42	pxc3	worker
172.16.9.48	Nginx do load

preparation

Linux needs to turn off the firewall or enable some required ports. PXC will come with mysql, version is corresponding to the same, so the machine does not need mysql; Better to turn off SELINUX, Linux’s built-in security enhancements.

Pay attention to these configurations. You have to do it on all three machines.

1. Open ports required by PXC

port	function
3306	The mysql database
4567	PXC Cluster communication port
4444	SST full transmission
4568	Ist Incremental Transmission

Here are some commands related to the firewall under Linux

Systemctl status firewalld # Check firewall status firewall-cmd --state # Check whether port 8080 is open firewall-cmd --query-port=8080/ TCP # Firewall - CMD --permanent --add-port=80/ TCP # Remove port firewall- CMD --permanent --remove-port=8080/ TCP -FCopy the code

2. Close SELINUX and mysql

Permanently closed:

vi /etc/selinux/config
Copy the code

Set SELINUX to disable, then reboot the machine

Temporary closure:

setenforce 0
Copy the code

Close the mysql

systemctl status mysql
systemctl stop mysql
Copy the code

Docker swarm docker swarm

Swarm also requires some port opening, of course if you are turning off the firewall needless to say

port	function
2377	For cluster communication
4789	Container overlay network
7946	Container network discovery

I have 172.16.9.40 as the primary node

Docker swarm init Docker swarm join --token XXXX XXXXCopy the code

Docker swarm join –token XXXX XXXX docker swarm join –token XXXX XXXX

Swarm swarm swarm swarm swarm swarm swarm swarm swarm

docker node ls 
Copy the code

You can view the current node information as follows

root@srig config]# docker node ls ID HOSTNAME STATUS AVAILABILITY MANAGER STATUS ENGINE VERSION Vk3kzrob1b8jvjq9bxia8lwa7 * srig in DCMP. Database. M1 Ready Active Leader s0pj57d43hm71wipnnbckfkt 20.10.3 4 Srig) DCMP) database. M2 Ready Active 20.10.3 ub1fe2qms2rlhmj9zlap20bsq srig. DCMP. Database. S1 Ready Active 20.10.3Copy the code

Docker node rm -f XXX Forcibly deleted the docker swarm leave -f The active docker swarm leave forcibly left the swarm docker swarm leave The secondary docker swarm leave the swarmCopy the code

Create a virtual network

docker network create -d overlay --attachable xxxxx
Copy the code

Other related commands

Docker network inspect XXXX Docker network ls Docker network rm XXXX Delete a networkCopy the code

Docker network inspect swarm_mysql after the network is created

5. Create a directory and cert certificate

If you are 8.0+ and not using the same certificate, then you are sure to encounter an SSL-related error

"Error: 0407008 a: rsa routines: RSA_padding_check_PKCS1_type_1: invalid padding"Copy the code

This is because after 8.0, SSL to connect, three machines, it must keep the key consistency can communicate.

This is the official solution, generate certificates, everyone uses the same set.

Create a directory

In general, it is better to take a look at the partition of the system disk and put mysql data on a larger disk

df -h
Copy the code

I have the largest value under /home, so all my data is under /home

! Note that directories are created the same way on all three machines

CD /home/mkdir -m 777 pxc_cert certificate mkdir -m 777 pxc_config User-Defined mysql configuration file mkdir -m 777 pxc_data DataCopy the code

Note: permissions are required here, otherwise many errors will be reported

Create custom. CNF

cd /home/pxc_config
vi custom.cnf
Copy the code

Input content here we

[mysqld]
lower_case_table_names=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ssl-ca = /cert/ca.pem
ssl-cert = /cert/server-cert.pem
ssl-key = /cert/server-key.pem

[client]
ssl-ca = /cert/ca.pem
ssl-cert = /cert/client-cert.pem
ssl-key = /cert/client-key.pem

[sst]
encrypt = 4
ssl-ca = /cert/ca.pem
ssl-cert = /cert/server-cert.pem
ssl-key = /cert/server-key.pem
Copy the code

I need to make the database case insensitive and use group by after 8.0

Creating a CERT Certificate

Docker run --name pxc-cert --rm -v /home/pxc_cert:/cert \ percona/percona-xtradb-cluster:8.0 mysql_ssl_rsa_setup -d /certCopy the code

You can create a certificate file in /home/pxc_cert

! Note that once the certificate is created, you need to copy it to the corresponding directory on the other two machines

SCP - r [email protected]: / home/pxc_cert/Users/jafir/downloads/pxc_certCopy the code

Download to local

SCP - r/Users/jafir/Downloads/pxc_cert [email protected]: / home/SCP - r/Users/jafir/Downloads/pxc_cert [email protected]: / home /Copy the code

Upload it to 41, 42 and the other two

! Note: all three machines need to give you certificate file permissions

cd /home/pxc_cert
chmod 777 *
Copy the code

Set up a PXC cluster

1. Install the image

docker pull percona/percona-xtradb-cluster
Copy the code

The name is a bit long, so you can tag it

docker tag percona/percona-xtradb-cluster pxc
Copy the code

Delete the original

docker rmi percona/percona-xtradb-cluster
Copy the code

2, multiple machines to create containers

I have 40 primary nodes, and the others are cluster nodes, so 40 starts first

172.9.16.40 master node

docker run -d -p 3306:3306 --net=swarm_mysql  \
-e MYSQL_ROOT_PASSWORD=asdw@123  \
-e CLUSTER_NAME=pxc_cluster \
-e XTRABACKUP_PASSWORD=asdw@123  \
-v /home/pxc_data:/var/lib/mysql  \
-v /home/pxc_cert:/cert \
-v /home/pxc_config/:/etc/percona-xtradb-cluster.conf.d  \
--privileged --name=pxc1  pxc
Copy the code

Command interpretation:

Docker run -d -p 3306:3306 3306 Port mapping --net=swarm_mysql virtual network name -e MYSQL_ROOT_PASSWORD=asdw@123 Initial database password -e CLUSTER_NAME=pxc_cluster Cluster name -e XTRABACKUP_PASSWORD=asdw@123 Backup password -v /home/pxc_cert:/cert Certificate path mapping -v / home/PXC: / var/lib/mysql PXC path map - v/home/PXC/config / : / etc/percona xtradb - cluster. Conf., d mysql configuration file path map - ring to give permission  --name=pxc1 pxcCopy the code

You can docker logs pxc1 to see if there is an error

If successful, you can use Navicat to see if mysql has been successfully started and then install the slave node.

172.9.16.41 node

docker run -d -p 3306:3306 --net=swarm_mysql  \
-e MYSQL_ROOT_PASSWORD=asdw@123  \
-e CLUSTER_NAME=pxc_cluster \
-e XTRABACKUP_PASSWORD=asdw@123  \
-v /home/pxc_data:/var/lib/mysql  \
-v /home/pxc_cert:/cert \
-v /home/pxc_config/:/etc/percona-xtradb-cluster.conf.d  \
-e CLUSTER_JOIN=pxc1 \
--privileged --name=pxc2  pxc
Copy the code

-e CLUSTER_JOIN=pxc1 CLUSTER_JOIN= pxC1 What about pXC1 for the second machine? Swarm was built to allow each other to communicate with each other.

172.9.16.42 node

docker run -d -p 3306:3306 --net=swarm_mysql  \
-e MYSQL_ROOT_PASSWORD=asdw@123  \
-e CLUSTER_NAME=pxc_cluster \
-e XTRABACKUP_PASSWORD=asdw@123  \
-v /home/pxc_data:/var/lib/mysql  \
-v /home/pxc_cert:/cert \
-v /home/pxc_config/:/etc/percona-xtradb-cluster.conf.d  \
-e CLUSTER_JOIN=pxc1 \
--privileged --name=pxc3  pxc
Copy the code

Note: If you are 8.0+ then you are sure to encounter an SSL-related error

"Error: 0407008 a: rsa routines: RSA_padding_check_PKCS1_type_1: invalid padding"Copy the code

This is because after 8.0, SSL to connect, three machines, it must keep the key consistency can communicate.

This is the official solution, generate certificates, everyone uses the same set. My side, simple point, directly to the host point of the file test out, and then transfer to the other two machines, after covering, restart can be.

Pem, server-cert.pem, client-key.pem, client-cert.pem, and ca.pem

SCP - r [email protected]: / home/PXC/Users/jafir/Downloads/PXCCopy the code

Copy the data from 40 nodes and delete all files except those 5

SCP - r/Users/jafir/Downloads/PXC [email protected]: / homeCopy the code

Then upload to 41, 42 to overlay, and restart

successful

If all three are successful, check again.

The primary node enters the container and then enters mysql to check

docker exec -it pxc1 sh
Copy the code

mysql -uroot -p 
Copy the code

show status like 'wsrep%';  
Copy the code

Not surprisingly, the cluster size here is 3

docker network inspect xxx
Copy the code

There are also three networks

check

You can create a database, a table, etc. on one of Navicat and see all three are synchronized!

Nginx load

Nginx I put on 172.16.9.48

If you don’t have an nginx.conf configuration file, you can create one and check out the configuration

Nginx /log /nginx/etc/nginx.conf

Docker run - d - name nginx nginx docker cp nginx: / etc/nginx/nginx. Conf copies docker rm -f nginxCopy the code

Configuration changes to nginx.conf

Add it on the last line, which is the same as HTTP

Stream {upstream PXC {server 172.16.9.44:3306; Server 172.16.9.41:3306; Server 172.16.9.42:3306; } server { listen 3306; proxy_pass pxc; }}Copy the code

docker run --net=host  --name nginx -v /nginx/log/:/var/log/nginx -v /nginx/etc/nginx.conf:/etc/nginx/nginx.conf -d nginx
Copy the code

Navicat can also connect to the database by connecting to http://172.16.9.48:3306

About the author

The author is a programmer who loves learning, open source, sharing, spreading positive energy, likes playing basketball, and has a lot of hair. –

Warmly welcome everyone to pay attention to, like, comment exchange!

Jane: www.jianshu.com/u/d234d1569…

github：github.com/fly7632785

CSDN：blog.csdn.net/fly7632785

The Denver nuggets: juejin. Cn/user / 993614…

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Docker installs PXC clusters

preface

Set up the environment

preparation

1. Open ports required by PXC

2. Close SELINUX and mysql

Docker swarm docker swarm

Create a virtual network

5. Create a directory and cert certificate

Create a directory

! Note that directories are created the same way on all three machines

Create custom. CNF

Creating a CERT Certificate

! Note that once the certificate is created, you need to copy it to the corresponding directory on the other two machines

! Note: all three machines need to give you certificate file permissions

Set up a PXC cluster

1. Install the image

2, multiple machines to create containers

172.9.16.40 master node

172.9.16.41 node

172.9.16.42 node

Note: If you are 8.0+ then you are sure to encounter an SSL-related error

successful

check

Nginx load

Configuration changes to nginx.conf

About the author

Docker installs PXC clusters

preface

Set up the environment

preparation

1. Open ports required by PXC

2. Close SELINUX and mysql

Docker swarm docker swarm

Create a virtual network

5. Create a directory and cert certificate

Create a directory

! Note that directories are created the same way on all three machines

Create custom. CNF

Creating a CERT Certificate

! Note that once the certificate is created, you need to copy it to the corresponding directory on the other two machines

! Note: all three machines need to give you certificate file permissions

Set up a PXC cluster

1. Install the image

2, multiple machines to create containers

172.9.16.40 master node

172.9.16.41 node

172.9.16.42 node

Note: If you are 8.0+ then you are sure to encounter an SSL-related error

successful

check

Nginx load

Configuration changes to nginx.conf

About the author

Related Posts

Mysql > select * from ‘Mysql’;

3. Koa.js + mongodb

The Python re module uses regex.