Docker setup setup mysql5.7 primary/secondary replication

Recently, I plan to learn database middleware myCAT. Before configuring myCAT read/write separation, I need to configure mysql primary/secondary replication. Today the whole process is said, first of all, the environment server is centos7 system, mysql version 5.7.

Start the container

1. Create mount directories for the mysql primary and secondary containers

Mysql > select * from mysql-master;

//mysql configuration file

mkdir -p /data/mysql-master/conf

// Mysql data file path

The mkdir -p/data/mysql – master/data

// Log file path

mkdir -p /data/mysql-master/logs

Mysql -slave mysql-slave

//mysql configuration file

mkdir -p /data/mysql-slave/conf

// Mysql data file path

The mkdir -p/data/mysql – slave/data

// Log file path

mkdir -p /data/mysql-slave/logs

2. Start the container

(1) the mysql – master

docker run -p 3306:3306 --name mysql-master\
--restart=always \
-v /data/mysql-master/conf:/etc/mysql \
-v /data/mysql-master/logs:/var/log/mysql \
-v /data/mysql-master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.7
Copy the code

(1) the mysql – slave

docker run -p 3307:3306 --name mysql-slave\
--restart=always \
-v /data/mysql-slave/conf:/etc/mysql \
-v /data/mysql-slave/logs:/var/log/mysql \
-v /data/mysql-slave/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.7
Copy the code

2. Master/slave configuration

1. Configure the host mysql-master

Since external mounting has been performed, you can directly go to the mount directory to configure the configuration

Vim /data/mysql-master/my.cnf press I to enter insert mode and add the following content

[mysqld]

server-id=1Log-bin =mysql-bin # you don't write replicate- if you want all databases to replicatedo-db=testdb # Specifies replicate-ignore-db=mysql This configuration specifies an un-synchronized databaseCopy the code

Then restart the mysql-master container and let it reload the configuration file

Docker restart Container ID

2. Create an account that has the primary/secondary replication permission

(1) enter the mysql-master container

 `docker exec -it mysql-master bash`
Copy the code

(2) Login to mysql

 `mysql -u root -proot`
Copy the code

(3) Create a slave account and grant the master/slave replication permission

 CREATE USER 'slave'@The '%' IDENTIFIED BY '123456';

 GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@The '%';
Copy the code

Mysql > query mysql-master status

show master status;

File: binlog Indicates the log location

Position: node number (mysql master-slave replication starts from node, which is different from Redis)

4. Configure the slave mysql-slave

Since external mounting has been performed, you can directly go to the mount directory to configure the configuration

Vim /data/mysql-master/my.cnf press I to enter insert mode and add the following content

 [mysqld]

server-id=2Log-bin =mysql-slave-bin ## relay_log Configures the relay log relay_log=mysql-slave-relay-binCopy the code

Then restart the mysql-master container and let it reload the configuration file

Docker restart Container ID

5. Master-slave links

Access the mysql-slave container of the cluster machine and log in to mysql

Execute the following code

change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos= 1300, master_connect_retry=30;
Copy the code

Master_host: indicates the host IP address, which is the independent IP address of the container. You can query the IP address by running the following command

docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master

Master_port: indicates the host port

Master_log_file: specifies the location of the binlog file, which is obtained by querying the host status

Master_log_pos: indicates the location of the host node

Master_connect_retry: interval for reconnecting to a host

Then start the master/slave replication on the slave machine

start slave;

This completes the master/slave configuration of mysql. Here’s a quick test

Mysql master/slave test

Mysql > create database testdb; mysql > create database testdb; create database testdb;

create database testdb;

Log in to the mysql server using the mysql-slave container

show databases;

You can see that the slave machine also has the testDB database

You can then create a table in the host testdb

create table user(id int,name varcher(20));

Switch to the testdb database use testdb

Then perform

show tables;

Select * from testDB; select * from testDB;

This completes the master/slave replication configuration for mysql.