How to realize MySQL master replication in Docker

It is believed that many inexperienced people have no idea about how to achieve MySQL master replication in Docker. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Install Docker

Install Docker Engine on CentOS

MySQL configuration

Master

Create a new directory data (XXXX/master/data) to map the docker database directory

New configuration (XXXX/master/my.cnf)

[mysqld] skip-host-cacheskip-name-resolvedatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksecure-file-priv=/var/lib/mysql-filesuser=mysqlpid-file=/var/run/mysqld/mysqld.pid# replication # server id unique server_id=1# STATEMENT, ROW, or MIXEDbinlog_format=ROWlog_bin=/var/lib/mysql/master-binauto-increment-increment=2auto-increment-offset=1slave-skip-errors=all# database replicate-ignore-db=information_schemareplicate-ignore-db=mysqlreplicate-ignore-db=performance_schemareplicate-ignore-db=sys that does not replicate

Replica

Create a new directory data (XXXX/rep/data) to map the docker database directory

New configuration (XXXX/rep/my.cnf)

[mysqld] skip-host-cacheskip-name-resolvedatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksecure-file-priv=/var/lib/mysql-filesuser=mysqlpid-file=/var/run/mysqld/mysqld.pid# replication# server id unique server_id=2# STATEMENT, ROW Or MIXEDbinlog_format=ROWlog_bin=/var/lib/mysql/rep-binauto-increment-increment=2auto-increment-offset=1slave-skip-errors=all# does not replicate the database replicate-ignore-db=information_schemareplicate-ignore-db=mysqlreplicate-ignore-db=performance_schemareplicate-ignore-db=sys adds Bridge type network docker network create mysql_net

The mysql service connects to the new network mysql_net

Mysql services can access each other through hostnames

Start the MySQL service

Master

Docker container run-- name mysql_master\-- network mysql_net\-- hostname mysql_master\-- publish 33306 network mysql_net 3306\-- env MYSQL_ROOT_PASSWORD=123456\-- mount type=bind,src=/xxxx/master/my.cnf,dst=/etc/my.cnf\-- mount type=bind,src=/xxxx/master/data Dst=/var/lib/mysql\-d mysql/mysql-server:8.0-- character-set-server=utf8-- collation-server=utf8_general_ci-- explicit_defaults_for_timestamp

Replica

Docker container run-- name mysql_rep\-- network mysql_net\-- hostname mysql_rep\-- publish 43306 network mysql_net 3306\-env MYSQL_ROOT_PASSWORD=123456\-- mount type=bind,src=/xxx/repl/my.cnf,dst=/etc/my.cnf\-- mount type=bind,src=/xxx/repl/data Dst=/var/lib/mysql\-d mysql/mysql-server:8.0-- character-set-server=utf8-- collation-server=utf8_general_ci-- explicit_defaults_for_timestamp

Parameter description

XXX: replace with a valid path

Network: designated as the new network mysql_net

Hostname: service hostname

Publish: Port mapping ([docker host Port]: [container Port])

MYSQL_ROOT_PASSWORD: MySQL default password

Mount: Mount configuration files and database files directory

Check whether the MySQL service starts normally

# View Docker container process docker container ps-a | grep mysql# view startup log docker container logs mysql_masterdocker container logs mysql_rep database configuration

New users with replication permissions

Log in to the master database

# Log in to the mysql_masterdocker container exec-it mysql_master bash# connection database with the password of 123456mysql-u root-p# specified in the configuration file and grant replication permission to CREATE USER 'dev'@'%' IDENTIFIED BY' 123456GRANT ALL ON *. * TO 'dev'@'%'

Log in to the standby database

# Log in to the mysql_repdocker container exec-it mysql_rep bash# connection database with the password of 123456mysql-u root-p# specified in the configuration file and grant replication permission to CREATE USER 'dev'@'%' IDENTIFIED BY' 123456GRANT ALL ON *. * TO 'dev'@'%'

View the status of the master-slave service database separately

# Master database mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | master-bin.000005 | 445 | +- -+ # standby database mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | rep-bin.000002 | 155 | +- -+

Configure database replication

Configure standby database mysql_rep

# MASTER_HOST: master database service name # MASTER_LOG_FILE: log file name # MASTER_LOG_POS: current location of log file mysql > CHANGE MASTER TO MASTER_HOST='mysql_master', MASTER_USER='dev', MASTER_PASSWORD='123456', MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=692

Configure the primary database mysql_master

# MASTER_HOST: from database service name # MASTER_LOG_FILE: log file name # MASTER_LOG_POS: current location of log file mysql > CHANGE MASTER TO MASTER_HOST='mysql_rep', MASTER_USER='dev', MASTER_PASSWORD='123456', MASTER_LOG_FILE='rep-bin.000001', MASTER_LOG_POS=155

Start replication

Standby database

Mysql > start slave

Master database

Mysql > start slave

View database status

Mysql > show master status;mysql > show slave status; after reading the above, have you mastered how to achieve MySQL master replication in Docker? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!