This article demonstrates a simple MySQL master-slave replication example.

0. Show

  1. This example has a master – slave architecture.
  2. To demonstrate, start two MySQL containers using Docker, make sure you have Docker installed and understand the basic use of Docker.

1. Create and start a container

The main library:

docker run --name=mysql-dn-m --env=MYSQL_ROOT_PASSWORD=admin123 -p 3316:3306 -p 33160:33060 -d mysql:latest
Copy the code

From the library

docker run --name=mysql-dn-s --env=MYSQL_ROOT_PASSWORD=admin123 -p 3326:3306 -p 33260:33060 --link mysql-dn-m:master -d mysql:latest
Copy the code

Instructions for the above operations:

  1. Start two MySQL containers (image details can be found inhereView), the primary library container is namedmysql-dn-m, the slave library container is namedmysql-dn-s.
  2. Set up therootThe password of the user isadmin123(for demonstration purposes only).
  3. Map the corresponding port number.
  4. Is used when the slave library container is created--link mysql-dn-m:masterCreate a slave library container to the master library

Use the docker ps command to view the current running container to ensure that the above operation is normal.

2. Configure the primary database

Run the docker exec -it mysql-dn-m /bin/bash command to go to the command line of the primary library container. Edit the /etc/mysql/my.cnf configuration file.

First, under [mysqld], add the following configuration (following the # symbol is the comment) :

Log-bin =mysql-bin =mysql-bin =mysql-binCopy the code

Then add the following at the end of the configuration file:

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
Copy the code

This ignores copying the four built-in MySQL databases.

If you want to replicate a custom library, you can use the binlog-do-db parameter to configure it. Otherwise, all libraries are replicated by default.

Restart the primary library container after the operation.

You can connect to the primary database and run an SQL statement to check the log status.

show variables like '%log_bin%';
Copy the code

If the value of log_bin is ON, there is no problem.

3. Configure the slave library

Edit the /etc/mysql-my.cnf configuration file in the slave library container using the same method as the master library. To configure the slave library, just add a line below [mysqld] :

Select * from server where server id=2Copy the code

Restart the slave library container after the operation.

4. Create a user account for data synchronization on the primary database

Enter the primary repository.

Run the following command to create a user. Set the user name, host, and password as required.

CREATE USER 'repl'@The '%' IDENTIFIED BY 'slavepass';
Copy the code

Then, authorize the user you just created:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@The '%'
Copy the code

Last refresh permission:

flush privileges;
Copy the code

Once you’re done, you can verify this step with the following query:

show grants for 'repl';
Copy the code

5. Configure the primary/secondary replication

Connect to the primary database and execute the query:

show master status\G;
Copy the code

The query result is similar to the following:

***************************[ 1. row ]***************************
File | mysql-bin.000009
Position | 435
Binlog_Do_DB |
Binlog_Ignore_DB | mysql,information_schema,performance_schema,sys
Executed_Gtid_Set |
Copy the code

Note the values of File and Position.

Connect to the slave library, use the user you created earlier, and the two values you just noted, and do the following to create the copy:

change master to MASTER_HOST = 'master', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'slavepass', MASTER_LOG_FILE = 'mysql-bin.000009', MASTER_LOG_POS = 435;
Copy the code

Parameter description:

  1. MASTER_HOST: The host of the master library. It can be an IP address or a host name. Enter the one we configured earliermaster.
  2. MASTER_PORT: Specifies the port number of the master library. This parameter can be omitted if the default port number is used.
  3. MASTER_USER: previously configured user name for replication.
  4. MASTER_PASSWORD: previously configured password for configuration.
  5. MASTER_LOG_FILE: the name of the master library log file recorded earlier.
  6. MASTER_LOG_POS: The location of the master database log previously recorded.

After executing the above statement, the creation is successful. You can verify the status of the master/slave replication by executing the following query statement on the slave:

show slave status\G;
Copy the code

If the values of both Slave_IO_Running and Slave_SQL_Running are Yes, success is indicated.

If no, failure information is displayed in corresponding fields in the query result for troubleshooting. Reported error: Authentication plugin ‘caching_sha2_password’ reported error: Error: Authentication requires secure connection ALTER USER ‘repl’@’%’ IDENTIFIED WITH mysQL_native_password BY ‘slavepass’; ALTER USER ‘repl’@’%’ IDENTIFIED WITH mysql_native_password BY ‘slavepass’; A mismatch in the validation mode caused the secondary library to fail to connect to the primary library.

Other errors that cause the connection failure include network failure between containers, incorrect user name, password, or permission configuration, and incorrect log file and location configuration. If an error occurs, check for these errors.

6. Test verification

Now that the configuration is complete, you can verify that the configuration is successful by creating databases, tables, or inserting, modifying, or deleting data from the primary database.

7. What if a database dies?

If the slave fails, the contents of the master are changed, and the changed data is automatically synchronized after the slave starts up again.

This section describes how to re-establish data synchronization after the primary database fails.

First, re-view the master database log file and location: show master status\G.

Then perform the following steps in the slave library:

  1. Stop replication:stop slave.
  2. Reconfigure the parameters of the primary and secondary replication:change master to MASTER_HOST = 'master', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'slavepass', MASTER_LOG_FILE = 'mysql-bin.000009', MASTER_LOG_POS = 435;
  3. To start replication:start slave;.