Detailed tutorial on installing pxc cluster with docker

Here are some commands related to the firewall under Linux

# Query the firewall status systemctl status firewalld
# Query the firewall status firewall-cmd --state
# Check whether port 8080 is open firewall-cmd --query-port=8080/tcp
# Open port 80 firewall-cmd --permanent --add-port=80/tcp
# Remove port firewall-cmd --permanent --remove-port=8080/tcp
Clear the firewall iptables -F

2. Turn off SELINUX and mysql

Permanently closed:

vi /etc/selinux/config

Set SELINUX to disable, then reboot the machine

Temporary closure:

setenforce 0

Shutdown mysql

systemctl status mysql
systemctl stop mysql

3. Create a docker swarm cluster

Swarm also requires some ports to be open, of course if you turn off the firewall, there is no need to say more

I am using 172.16.9.40 as the master node

docker swarm init Initialize the master node docker swarm join --token xxxx xxxx Join other nodes

40After the master node is init, the console will show docker swarm join --token xxxx xxxx

Then machines 41 and 42 call the corresponding commands to join the swarm cluster

docker node ls

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.dcmp.database.m1 Ready Active Leader 20.10.3
4s0pj57d43hm71wipnnbckfkt srig.dcmp.database.m2 Ready Active 20.10.3
ub1fe2qms2rlhmj9zlap20bsq srig.dcmp.database.s1 Ready Active

docker node rm -f xxx Forced deletion of nodes docker swarm leave -f The master node is forced to leave the swarm cluster docker swarm leave The slave node leaves the swarm cluster

4. Create a virtual network

docker network create -d overlay --attachable xxxxx

Other related commands

docker network inspect xxxx View network information docker network ls View all network information docker network rm xxxx Delete a network

The network name here is swarm_mysql . After the network is created, docker network inspect swarm_mysql to view it (in my case, after the nodes are established, you can see that there are three machines)

5. Create directory and cert certificate

If you are 8.0+ and not using the same certificate, you will definitely get an SSL related error

"error:0407008A:rsa routines:RSA_padding_check_PKCS1_type_1:invalid padding"

This is because after 8.0, SSL is used for connection, and the three machines must maintain the consistency of the key to communicate.

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

Create a Directory

Generally speaking, it is best to check the partitioning of the system disk and then put the mysql data on a large disk.

df -h 

My data is all in /home /home so it is the largest file system.

! Note that the directory here needs to be created in the same way on all three machines.

cd /home
mkdir -m 777 pxc_cert certificate mkdir -m 777 pxc_config MySQL custom configuration file mkdir -m 777 pxc_data data

Note: You need to give permissions here, otherwise many places will report errors

Create custom.cnf

cd /home/pxc_config
vi custom.cnf

Enter content here

[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

I need to set the database to be case insensitive and group by can be used after 8.0

Create cert certificate

docker run --name pxc-cert --rm -v /home/pxc_cert:/cert \
percona/percona-xtradb-cluster:8.0 mysql_ssl_rsa_setup -d /cert

You can create a certificate file in the /home/pxc_cert directory

! Note that after the certificate is created, it needs to be copied to the corresponding directories on the other two machines.

scp -r [email protected]:/home/pxc_cert /Users/jafir/downloads/pxc_cert

Download to local

scp -r /Users/jafir/Downloads/pxc_cert [email protected]:/home/
scp -r /Users/jafir/Downloads/pxc_cert [email protected]:/home/

Upload to 41 42 other two machines

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

cd /home/pxc_cert
chmod 777 *

Building a pxc cluster

1. Install the image

docker pull percona/percona-xtradb-cluster

The name is a bit long, you can rename it with tag

docker tag percona/percona-xtradb-cluster pxc

Delete the original

docker rmi percona/percona-xtradb-cluster

2. Create containers on multiple machines

I have 40 master nodes here, and the others are cluster nodes, so 40 will be created 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

Command interpretation:

docker run -d 
-p 3306:3306 3306 port mapping --net=swarm_mysql virtual network name -e MYSQL_ROOT_PASSWORD=asdw@123 database initial 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 mapping -v /home/pxc/config/:/etc/percona-xtradb-cluster.conf.d mysql configuration file path mapping --privileged grant permissions --name=pxc1 pxc

You can run docker logs pxc1 to see if the log reports any errors.

If successful, you can use Navicat to connect to see if mysql is started successfully, and then install the slave node after it is started.

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

Compared with the above, there is one more sentence here -e CLUSTER_JOIN=pxc1 , which means joining pxc1. How can the second machine know pxc1? It is because of the establishment of the swarm cluster that they can 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

Note: If you are on 8.0+ then you will definitely encounter an SSL related error

"error:0407008A:rsa routines:RSA_padding_check_PKCS1_type_1:invalid padding"

This is because after 8.0, SSL is used for connection, and the three machines must maintain the consistency of the key to communicate.

This is the official solution to generate certificates and everyone uses the same set. I keep it simple. I just extract the file from the host machine and transfer it to the other two machines. After overwriting, I restart them.

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

scp -r [email protected]:/home/pxc /Users/jafir/Downloads/pxc

Copy the data from node 40 and delete all files except the 5 files.

scp -r /Users/jafir/Downloads/pxc [email protected]:/home

Upload it to 41 and 42 to overwrite, then restart.

success

If all three are successful, confirm again.

The master node enters the container and then enters mysql to view

docker exec -it pxc1 sh

mysql -uroot -p

show status like 'wsrep%'; 

As expected, the cluster size here is 3

docker network inspect xxx 

There are also 3 networks

check

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

nginx load

I put nginx on 172.16.9.48

If there is no nginx.conf configuration file, you can create one first and then test the configuration

Self-built/nginx/log /nginx/etc/nginx.conf etc.

docker run -d -name nginx nginx
docker cp nginx:/etc/nginx/nginx.conf copy it docker rm -f nginx

Configuration modification of nginx.conf

Add it in the last line, which is the same level as http

stream {
 upstream pxc {
  server 172.16.9.40:3306;
  server 172.16.9.41:3306;
  server 172.16.9.42:3306;
 }
 server {
  listen 3306;
  proxy_pass pxc;
 }
}

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

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

This is the end of this article about installing pxc cluster in docker. For more information about installing pxc cluster in docker, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!