Shulou(Shulou.com)06/01 Report--

The following is mainly about how to build Docker-based MySQL master-slave replication. I hope these contents can bring you practical use, which is also the main purpose of my editor how to build Docker-based MySQL master-slave replication. All right, don't talk too much nonsense, let's just read the following.

Why is it based on Docker? Setting up virtual machines with limited resources requires machine configuration, and the steps of installing mysql are tedious. Multiple Docker containers can be run on one machine. Docker containers are independent of each other, have independent ip, and do not conflict with each other. Docker is easy to use. Start the container and use Docker to build a master-slave cloud server at the second level.

First, pull the docker image. We use mysql version 5.7 here:

Docker pull mysql:5.7

Then use this image to start the container. Here, you need to start the master and slave containers respectively.

Master (Master):

Docker run-p 3339 MYSQL_ROOT_PASSWORD=123456 3306-- name mysql-master-e MYSQL_ROOT_PASSWORD=123456-d mysql:5.7

Slave (from)

Docker run-p 3340 MYSQL_ROOT_PASSWORD=123456 3306-- name mysql-slave-e MYSQL_ROOT_PASSWORD=123456-d mysql:5.7

The port for external mapping of Master is 3339 and the port for external mapping of Slave is 3340. Because docker containers are independent of each other, and each container has its own independent ip, there is no conflict between different containers using the same port. Here we should try to use the default port 3306 of mysql, otherwise there may be a problem of not being able to connect to the mysql in the docker container through ip.

Use the docker ps command to view the running container:

At this point, you can use tools such as Navicat to test the connection to mysql

Configure Master (Master)

Enter the Master container through the docker exec-it 5ddad8c2f368 / bin/bash command, or you can enter it through the docker exec-it mysql-master / bin/bash command. 5ddad8c2f368 is the id of the container, and mysql-master is the name of the container.

Cd / etc/mysql/mysql.conf.d changes to the / etc/mysql/mysql.conf.d directory, and vim mysqld.cnf edits the my.cnf. At this point, bash: vim: command not found will be reported, requiring us to install vim inside the docker container. Use the apt-get install vim command to install vim

The following problems occur:

Execute apt-get update, and then execute apt-get install vim again to successfully install vim. Then we can edit the my.cnf using vim and add the following configuration to the my.cnf:

[mysqld] # # pay attention to unique server-id=100 # # enable binary log function in the same local area network, and you can take (key) log-bin=mysql-bin at will

After the configuration is complete, you need to restart the mysql service for the configuration to take effect. Use service mysql restart to complete the reboot. Restarting the mysql service will stop the docker container, and we also need docker start mysql-master to start the container.

The next step is to create a data synchronization user in the Master database and grant the user slave REPLICATION SLAVE and REPLICATION CLIENT permissions to synchronize data between master and slave libraries.

First connect to the mysql database

Root@5ddad8c2f368:/# mysql-uroot-p123456

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

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'slave'@'%'

REPLICATION CLIENT permission is granted to the replication account, and replication users can use SHOW MASTER STATUS, SHOW SLAVE STATUS and SHOW BINARY LOGS to determine the replication status.

REPLICATION SLAVE permission is granted to copy the account so that replication can really work.

Configure Slave (from)

As with configuring Master (master), add the following configuration to the Slave configuration file my.cnf:

[mysqld] # # set server_id, pay attention to unique server-id=101 # # enable binary log function, in case Slave is used as the Master of other Slave, use log-bin=mysql-slave-bin # # relay_log to configure relay log relay_log=edu-mysql-relay-bin

After the configuration is completed, you also need to restart the mysql service and the docker container, and the operation is consistent with the configuration Master (master).

Relay log is similar to binary log in many ways. The difference is that the SQL thread reads the binary log of the master CVM and records it to the local file of the slave CVM, and then the CVM thread reads the contents of the relay-log log and applies it to the slave CVM, thus keeping the data of the slave CVM consistent with that of the master CVM.

Link Master (master) and Slave (slave)

Enter mysql at Master and execute show master status

The values of the File and Position fields will be used later. Before the later operation is completed, you need to ensure that the Master library cannot do anything, otherwise it will cause a state change, a change in the values of the File and Position fields.

Enter mysql in Slave and execute

CHANGE MASTER TO master_host = '172.17.0.2 master calendar user =' slave',master_password = '123456 master calendar port = 3306 master master logbook file =' mysql-bin.000001',master_log_pos = 617 masterconnectcalendar retry = 30

Command description:

Master_host: the address of Master, which refers to the independent ip of the container. You can query the container's ip via docker inspect-- format=' {{.NetworkSettings.IPAddress}} 'container name | Container id

The port number of master_port:Master, which refers to the port number of the container

Master_user: user for data synchronization

Master_password: password of the user used for synchronization

Master_log_file: specifies the log file from which Slave starts copying data, that is, the value of the File field mentioned above

Master_log_pos: which Position to read from, that is, the value of the Position field mentioned above

Master_connect_retry: if the connection fails, the interval between retries (in seconds). Default is 60 seconds.

Execute show slave status\ G in the mysql terminal in Slave; used to view the master-slave synchronization status.

Normally, both SlaveIORunning and SlaveSQLRunning are No because we haven't started the master-slave replication process yet. Use start slave to start the master-slave replication process, and then query the master-slave synchronization status show slave status\ G; again.

Both SlaveIORunning and SlaveSQLRunning are Yes, indicating that master / slave replication has been enabled. At this point, you can test whether the data synchronization is successful.

Master-slave replication troubleshooting:

After the master-slave replication process is enabled with start slave, if SlaveIORunning has been Connecting, the master-slave replication has been connected all the time. This situation is generally caused by the following reasons, which can be excluded according to the Last_IO_Error prompt.

The network is not connected.

Check ip. The port password is incorrect.

Check whether the user created for synchronization and the user password are correct and pos is not correct

Check the Position test master-slave replication of Master

There are many ways to test master-slave replication, and the simplest thing is to create a database in Master, and then check to see if the database exists in Slave.

Master:

Slave:

Complete master and standby flow chart

Finally, let's take a look at the complete flowchart of a update statement executed on node An and then synchronized to node B.

As you can see: after receiving the update request from the client, the main library executes the update logic of the internal transaction and writes to the binlog at the same time.

A long connection is maintained between standby library B and main library A. There is a thread inside the main library A that is dedicated to this persistent connection to service slave B.

The complete process of a transaction log synchronization is as follows:

1. Use the change master command on slave B to set the IP, port, user name and password of master library A, and the location from which to request binlog, which contains the file name and log offset. 2. Execute the start slave command on slave B, and the standby will start two threads, io_thread and sql_thread. Among them, io_thread is responsible for establishing a connection with the main library. 3. After the main library A verifies the user name and password, it starts to read the binlog locally and send it to B according to the location passed by slave database B. 4. After getting the binlog, slave B writes to the local file, which is called transit log (relay log). 5. Sql_thread reads the transit log, parses the commands in the log, and executes them.

For the above about how to build Docker-based MySQL master-slave replication, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.