Get ready

I use docker deployment on CentOS, so I need to prepare docker related operations in advance

docker

CentOS installation

To configure

mapping

A host has multiple virtual machines, so it needs to use different named configuration folders, such as / etc/mysql/my.cnf.d/master01, to distinguish the configuration of startup items of different MySQL docker instances. In addition, there are differences in data document configuration: / var/lib/mysql/master01

Generally, the default data and startup configuration of MySQL docker correspond to / var/lib/mysql and / etc/my.cnf.d respectively. The corresponding relationship is as follows:

/etc/mysql/my.cnf.d/master01:/etc/my.cnf.d
/var/lib/mysql/master01:/var/lib/mysql

Folder creation may be involved

mkdir -p /etc/mysql/my.cnf.d/master01 /var/lib/mysql/master01
# Set permissions strictly, otherwise some configuration files, such as my.cnf, will fail
chmod 645 -R /etc/mysql/my.cnf.d/master01
chmod 777 -R /var/lib/mysql/master01

port

The default is 3306, and I map to 6306

image

According to the situation, build PXC(Percona XtraDB Cluster) cluster here, then select percona:5.7.23

Jurisdiction

This refers to the problem of how to define and set the initial password of root user. There are three options: one of three:

1. No password: MySQL? Allow? Empty? Password = true

2. Specify password: MySQL? Root? Password = XXX

3. Random password: MySQL? Random? Root? Password = true

   Not recommended! Because this means that you need to reset your password, and you need to configure skip grant tables under [mysqld] of my.cnf in advance. After you define your password, you need to restart it, which is troublesome

start-up

Master node

Create container

The above preparations outline the container creation commands:

docker create --name percona-master01 -v /var/lib/mysql/master01:/var/lib/mysql -v /etc/mysql/my.cnf.d/master01:/etc/my.cnf.d -p 6306:3306 -e MYSQL_ROOT_PASSWORD=master01 percona:5.7.23

my.cnf

To cluster, you need to configure at least the following:

1. Open log bin
2. Set server ID

So create my.cnf in the corresponding directory / etc/mysql/my.cnf.d/master01 and write:

[mysqld]
log-bin=mysql-bin
server-id=1
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The SQL mode here is to solve the possible problems 1055 mistake Of, regardless of whether the cluster is configured

Provide the following easy script for initialization:

cat << EOF > /etc/mysql/my.cnf.d/master01/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

Startup mirroring

Run master01 and view the log:

docker start percona-master01  && docker logs -f percona-master01

Verification

After the startup is completed, you can first check whether the corresponding configuration is effective on the host, such as log Bin:

ll /var/lib/mysql/master01 | grep mysql-bin

Or directly log in for authentication. You can connect to the host port or the docker virtual machine, but you need to query its IP address first

1. Host computer

# - h127.0.0.1 cannot be omitted, otherwise it will be considered as localhost and login will be denied
mysql -h127.0.0.1 -P6306 -uroot -pmaster01 -Dmysql

1. virtual machine

docker exec -it percona-master01 mysql -uroot -pmaster01 -Dmysql 

To grant authorization

At this time, the server is set up and connected, and some initialization operations need to be done, such as creating user pxc and granting permissions

It can be connected and operated on the server or the client. The actual sql operation will not change either

Take the core sql for creating cluster users, granting permissions and viewing cluster status as an example:

use mysql;
create user 'pxc'@'%' identified by 'pxc';
grant replication slave on *.* to 'pxc'@'%';
flush privileges;

Execute after restart:

# View cluster status
show master status;
# View binary log related configuration items
show global variables like 'binlog%';
# View server related configuration items
show global variables like 'server%';

Summary

No matter how connected, as long as it's a command, you can use shell scripts to implement one by one, such as creating users

# -D database name - e SQL script to be executed
mysql -h127.0.0.1 -P6306 -uroot -pmaster01 -Dmysql -e"create user 'pxc'@'%' identified by 'pxc';"

Slave node

It is roughly the same as the configuration of the primary node, and only the differences between the key configurations are listed here

Set permissions strictly, otherwise some configuration files, such as my.cnf, will fail

bash-4.2$ mysql -uroot -p
mysql: [Warning] World-writable config file '/etc/my.cnf.d/my.cnf' is ignored.
mysql: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.

mkdir -p /etc/mysql/my.cnf.d/slave01 /var/lib/mysql/slave01
chmod 644 -R /etc/mysql/my.cnf.d 
chmod g+w,o+w -R /var/lib/mysql

my.cnf

Still use the same idea to create my.cnf under / etc/mysql/my.cnf.d/slave01 and write:

[mysqld]
# Service ID, not duplicate with master
server-id=2
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

docker create --privileged --name percona-slave01 -v /var/lib/mysql/slave01:/var/lib/mysql -v /etc/mysql/my.cnf.d/slave01:/etc/my.cnf.d -p 6307:3306 -e MYSQL_ROOT_PASSWORD=slave01 percona:5.7.23

docker start percona-slave01  && docker logs -f percona-slave01

mysql -h0.0.0.0 -P6307 -uroot -pslave01 -Dmysql

View the binary log (bin log) information of the primary node (port 6306):

mysql -h0.0.0.0 -P6306 -uroot -pmaster01 -Dmysql -e'show master status;'

The output is as follows:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Associate master

CHANGE MASTER TO
master_host='192.168.0.122',
master_user='pxc',
master_password='pxc',
master_port=6306,
master_log_file='mysql-bin.000001',
master_log_pos=154;
start slave;
show slave status;

Start the slave node and view the slave node status

start slave;
show slave status;

Command waterfall built by master02 and slave02

Here is a record of their complete operation process, do not directly implement

#!/bin/bash

###### master02 ###### 

mkdir -p /etc/mysql/my.cnf.d/master02 /var/lib/mysql/master02
chmod 645 -R /etc/mysql/my.cnf.d/master02
chmod 777 -R /var/lib/mysql/master02

docker create --name percona-master02 -v /var/lib/mysql/master02:/var/lib/mysql -v /etc/mysql/my.cnf.d/master02:/etc/my.cnf.d -p 7306:3306 -e MYSQL_ROOT_PASSWORD=master02 percona:5.7.23

cat << EOF > /etc/mysql/my.cnf.d/master02/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=10
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

docker start percona-master02 && docker logs -f percona-master02

docker exec percona-master02 mysql -uroot -pmaster02 -e"show variables like 'log_bin'"
docker exec percona-master02 mysql -uroot -pmaster02 -e"show master status;"
docker exec percona-master02 mysql -uroot -pmaster02 -e"show global variables like 'server%';"

docker exec percona-master02 mysql -uroot -pmaster02 -Dmysql -e"create user 'pxc'@'%' identified by 'pxc';"
docker exec percona-master02 mysql -uroot -pmaster02 -Dmysql -e"grant replication slave on *.* to 'pxc'@'%';"
docker exec percona-master02 mysql -uroot -pmaster02 -Dmysql -e"flush privileges;"


###### slave02 ######
mkdir -p /etc/mysql/my.cnf.d/slave02 /var/lib/mysql/slave02
chmod 645 -R /etc/mysql/my.cnf.d/slave02
chmod 777 -R /var/lib/mysql/slave02

docker create --name percona-slave02 -v /var/lib/mysql/slave02:/var/lib/mysql -v /etc/mysql/my.cnf.d/slave02:/etc/my.cnf.d -p 7307:3306 -e MYSQL_ROOT_PASSWORD=slave02 percona:5.7.23

cat << EOF > /etc/mysql/my.cnf.d/slave02/my.cnf
[mysqld]
server-id=12
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
EOF

docker start percona-slave02

docker exec percona-slave02 mysql -uroot -pslave02 -e"CHANGE MASTER TO master_host='192.168.0.122', master_user='pxc', master_password='pxc', master_port=7306, master_log_file='mysql-bin.000003', master_log_pos=154;start slave;"
docker exec percona-slave02 mysql -uroot -pslave02 -e"start slave;"
docker exec percona-slave02 mysql -uroot -pslave02 -e"show slave status;"

Reference resources

1. mysql shell script
2. Percona database