Pull the mirror

Get a server and install the Docker input

Docker pull mysql: 5.7.29Copy the code

Mysql 5.7.29 is used as an example

Preparing configuration Files

You are advised to mount the mysql configuration file to facilitate viewing and unified management

Create a.cnf file as the configuration file for the main library and write the following

! includedir /etc/mysql/conf.d/ ! includedir /etc/mysql/mysql.conf.d/ [mysqld]## Be unique within the same LAN
server-id=001
## enable the binary log function, can be arbitrary (key)
log-bin=mysql-bin

gtid_mode=on
enforce_gtid_consistency=on

relay-log=relay-log.log
binlog_format=ROW
#In MySQL5.7, the gtid_executed table is used to record synchronous replication information. This reduces the secondary disk I/O by avoiding double writes to relay logs and binlogs
#log_slave_updates=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
slave_parallel_workers=2
binlog_checksum=CRC32
master_verify_checksum=1
slave_sql_verify_checksum=1
binlog_rows_query_log_events=1
#replicate_do_db=tt
#Mysql > create table based group commit parallel replication. Mysql > create table based group commit parallel replication. Mysql > create table based group commit parallel replication
slave_parallel_type=logical_clock
#Set the timeout duration of the mysql connection when it is not used. Default is 8 hours. Set this parameter to 24 hours, in seconds
wait_timeout=86400
Copy the code

The server-id cannot be repeated in a mysql cluster

Then we will create another.cnf file as the configuration file for the standby library and write the following

! includedir /etc/mysql/conf.d/ ! includedir /etc/mysql/mysql.conf.d/ [mysqld]#Set server_id to unique
server-id=010
#The binary log function is enabled for the Slave to function as the Master of another Slave
log-bin=mysql-slave-bin
## relay_log Configures relay logs

gtid_mode=on
enforce_gtid_consistency=on

relay-log=relay-log.log
binlog_format=ROW
#log_slave_updates=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
slave_parallel_workers=2
binlog_checksum=CRC32
master_verify_checksum=1
slave_sql_verify_checksum=1
binlog_rows_query_log_events=1
#replicate_do_db=tt
slave_parallel_type=logical_clock
#Set the timeout duration of the mysql connection when it is not used. Default is 8 hours. Set this parameter to 24 hours, in seconds
wait_timeout=86400
Copy the code

Save the configuration file, so that we are ready for the mysql master/standby configuration file

Start the container

Start the main library

docker run -p 3339:3306 --name gtid-master -e MYSQL_ROOT_PASSWORD=123456 --restart="always" -v / home/dockermysqlconf/masterconf. CNF: / etc/mysql/my CNF -v hosting storage path: / var/lib/mysql - v/etc/localtime: / etc/localtime - d Mysql: 5.7.29Copy the code

After starting the slave library

docker run -p 3340:3306 --name gtid-slave -e MYSQL_ROOT_PASSWORD=123456 --restart="always" -v / home/dockermysqlconf/slaveconf. CNF: / etc/mysql/my CNF -v hosting storage path: / var/lib/mysql - v/etc/localtime: / etc/localtime - d Mysql: 5.7.29Copy the code

Port 3339 is the primary library, port 3340 is the secondary library. Now you can use NavICat to connect to the database, confirm whether the database service is normal

A master-slave configuration

Configure the main library

Check the main library container ID, enter the container, enter mysql

Docker ps docker exec it mysql -uroot -p 123456/Copy the code

Then execute the following two commands to create a data synchronization user on the Master database and grant slave REPLICATION and REPLICATION CLIENT privileges to the user to synchronize data between the Master and slave databases

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Copy the code

% of the two words can also be replaced with the library’s id, so that this account will allow you to specify only from a trip to the library, the advantage is that account is safe, the disadvantage is that and we also need to create another synchronous now account for the main library to library from synchronization, and if like this write directly % does not limit access to the IP, as long as we build a sync account

If you are using mysql8.0 or higher, you need to specify the password plug-in

CREATE USER slave@' % 'IDENTIFIED WITH 'mysql_native_password' BY '123456'; Grant replication slave on *.* to slave@' % '; flush privileges;Copy the code

Gets the primary library container IP

Before configuring the slave library, we need to query the container IP of the master library and execute it on the host machine

Docker inspect - format = '{{. NetworkSettings. IPAddress}}' containers name/idCopy the code

We know the IP address of the specified container, which will be used when configuring the slave library. Of course, since we are deploying a dual master, we can look up the IP address of both containers

Configuration from the library

Now enter the slave library, the same process as above into the container after entering mysql, execute

Change master to master_host='172.18.0.5', master_user='slave', master_password='123456', master_port=3306, master_auto_position=1, master_connect_retry=30;Copy the code

Then we execute from the library

show slave status \G;
Copy the code

View the master/slave synchronization status

It’s all No because we haven’t turned on the master-slave synchronization yet

start slave;
Copy the code

Start synchronization and execute it again

show slave status \G;
Copy the code

As you can see, normal master/slave synchronization has started

At this point, the gTID primary/secondary deployment is complete

With double main

If you created an account with a restricted login IP address, now you need to create another account for the primary database to log in to the secondary database. Note that the creation of an account is performed in the primary database. Do not write data to the slave database so that the master and slave database will automatically synchronize the account after the master database creates a new account. However, if you write % directly like me, this problem will not occur

Execute in the primary library

Change master to master_host='172.18.0.6', master_user='slave', master_password='123456', master_port=3306, master_auto_position=1, master_connect_retry=30;Copy the code

Specifies the current slave library as the master library of the current master library

As before, execute

show slave status \G;
Copy the code

Check the synchronization status

Synchronization has not started yet

start slave;
Copy the code

Start synchronization and execute it again

show slave status \G;
Copy the code

As you can see, master/slave synchronization has started

At this point, Docker deployment of Mysql GTID mode is completed

You can create some databases, tables, write some data into what to test

Error handling

If the picture below appears

– Slave_IO_Running is always in connection, usually because of setting the master library’s IP

At this point, let’s stop syncing

stop slave;
Copy the code

Then execute the command again to set the master database. Check the parameters carefully before executing the command, including the master database IP address, master database port, master database synchronization account, master database synchronization account password, and enable automatic location

Change master to master_host='172.18.0.6', master_user='slave', master_password='123456', master_port=3306, master_auto_position=1, master_connect_retry=30;Copy the code

Correct the parameters before executing

start slave;
Copy the code

Restart master/slave synchronization and you are done

Refer to the article

• MySQL5.7 Dual Master architecture setup (based on GTID)
• Docker deploys Mysql master/slave