MySQL master-slave replication-the main library has data solutions

In the previous article, we introduced the MySQL master-slave building based on Docker, and the construction process of a master multi-slave is to repeat a master-one from the library configuration process, it is necessary to pay attention to ensure that the master-slave library my.cnf server-id uniqueness. After the build is complete, you can show slave hosts see what from the Library node in the main library.

Let's take a brief look at the MySQL master-slave replication process:

(1) Master changes the record to binary log (these are called binary log events, binary logs event);

(2) Slave copies the binary log events of master to its trunk logs (relay log);

(3) Slave replay the events in the relay log and change the data to reflect its own.

MySQL master-slave replication-the main library has data solutions

Switching from a stand-alone architecture to one master-one slave or one master-multi-Slave, the main library may have been running for some time before the addition of the library node, a situation that is common in the real business. So how do you deal with scenarios where the main library has data before the master-slave replication?

The first option is to choose to ignore the data before the main library and not do the processing. This scheme only applies to unimportant and dispensable data, and the business can tolerate scenarios where the master-slave database data is inconsistent.

The second scenario is to make a backup of the data from the main library, and then import the exported data from the primary database into the slave database and then turn on master-detail replication to ensure that the master-slave database data is consistent.

Let's take a look at the treatment of the second scenario:

View the database that is already in the primary database

We prepared a TEST1 library in the main database and prepared a data table test and several test data in it.

Use Docker to create a database from

docker run -p 3346:3306 --name mysql-slave4 -e MYSQL_ROOT_PA SSWORD=123456 -d mysql:5.7

Lock the primary database

Locking the primary database, allowing only read not allowed writes, is intended to prevent new data insertions during the backup process or after the backup is complete, resulting in inconsistencies between the backup data and the master data data.

mysql> flush tables with read lock;

Query the primary database state and note the value of file and position

mysql>show master status;

Backing up the primary database

Exit the MySQL terminal and perform the Docker MySQL backup command

docker exec [CONTAINER] /usr/bin/mysqldump -u username --password=xxx [DATABASE] > backup.sql

We only need to back up the TEST1 database here, to back up all the databases, use [Database] --all-databases .

At this point Warning: Using a password on the command line interface can be insecure. , this is because we entered the password in the command line, so there will be a security warning message. The solution is to add the following configuration to the/ETC/MYSQL/MY.CNF:

[mysqldump]user = rootpassword = rootpassword

After modifying the configuration file, executing the backup command again does not require information about the user name password.

Primary database backup data import from database

cat backup.sql | docker exec -i [CONTAINER] /usr/bin/mysql -u username --password=xxx [DATABASE]

As in the previous step, we also need to configure the user name and password information for backup from the database.

Before you can import the primary database backup data, you need to establish a database with the same name from the database. Switch to execute from the database CREATE DATABASE TEST1; , and then import the master data backup data again.

When the backup data import is complete, data validation can be performed from the database.

Configuration from the database

docker exec -it mysql-slave4 /bin/bash

cd /etc/mysql

vi my.cnf, add the following configuration, note that the Server-id to ensure unique:

service mysql restartRestart the MySQL service, which will cause the Docker container where the MySQL service is to stop

docker start mysql-slave4Start the Docker container

Configure Master-Slave links

Switch to from database, execute change master to master_host=‘172.17.0.2‘, master_user=‘slave‘, master_password=‘123456‘, master_port=3306, master_log_file=‘mysql-bin.000001‘, master_log_pos= 4952, master_connect_retry=30; , about this command in the previous blog has detailed introduction.

Initiates a master-slave copy start slave; , viewing from the library state show slave status \G; , if both slaveiorunning and slavesqlrunning are yes, to enable the master-slave copy process to succeed.

Unlocking the primary database

Switch back to the terminal of the primary database and perform a table unlock operation.

unlock tables;

Test Master-slave replication

Inserting a test data into the primary database

Switch to the database and query the test data to indicate the success of the master-slave replication.

Summarize

The process should be optimized as much as possible, reducing the lock table time.

Minimize the lock table range and lock only the relevant database.

The thing about the code.
Public Number:

Source: http://songwenjie.cnblogs.com/
Statement: This article for Bo Master Learning sentiment Summary, the level is limited, if improper, welcome correction. If you think it's good, just click on the "recommend" button below, thanks for your support. Reprint and quote please specify the source.

MySQL master-slave replication-the main library has data solutions