A brief introduction.

MySQL has its own replication solution, which brings the following benefits:

Data backup. Load balancing. Distributed data.

Concept introduction:

Master: the database to be replicated. Slave: replicates the data of the host.

(1). Master records the details of the changes and stores them in the binary log. (2). The master sends a synchronization message to the slave. (3). After receiving the message, the slave copies the binary log of the master to the local relay log. (4). Slave reproduces the messages in the relay logs to change the data of the database.

Here’s a classic picture to illustrate the process:

Implement replication

There are the following steps to implement replication:

1. Set the binary logs and the server ID of the primary MySQL database

The MySQL configuration file is stored in /etc/my.cnf

[mysqld] server-id=1 log-bin=mysql-bin.logCopy the code

The server ID must be unique in the entire database cluster. Restart the MySQL. Note: If this file is already configured in the MySQL configuration file, skip this step.

2. Create a replication account

Create an account for the secondary database to copy data from the primary database and grant replication permission on the primary database.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO user_name@'host' IDENTIFIED BY 'password';Copy the code

3. Set the primary MySQL database server-id

As with the second configuration, there are two things to note:

  1. There is no need to configure binary logging if the slave library is not required to act as the master of another slave library.
  2. Most of the time replication does not require copying the entire database of the master library (especially mysql’s information configuration library). Therefore, replicate_do_DB can be configured to specify the replicated database

4. Initialize the data of the primary database from the secondary database

If the data volume is not large, you can use the mysqldump tool to export the primary database data and then import it to the secondary database.

mysqldump --single-transaction --triggers --master-data databasename > data.sqlCopy the code

If there is a large amount of data, Xtrabackup should be used to export the database, which is not described here. Some students may ask, why not just use binary log initialization?

  1. If our master library has been running for a long time, it is not suitable to use the slave library to copy data from the binary log. Using the binary log directly to initialize the slave library can be time consuming and performance consuming.
  2. More often than not, the binary log configuration items for the main library are not turned on, so there are no binary logs of previous operations.

5. Enable replication

Execute the following command from the library

mysql> CHANGE MASTER TO MASTER_HOST='host',
-> MASTER_USER='user',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;Copy the code

Note that the last two commands, MASTER_LOG_FILE and MASTER_LOG_POS, indicate the binary from which the library is read and the offset from which it is read. These two parameters can be found in the SQL we imported.

Open the copy

start slave;Copy the code

At this point, the replication is complete, updating data in the master database or adding data in the slave database can be queried.



The status of the replication thread can also be queried on the master library.

3. Log format for replication

MySQL replicates logs in three formats, depending on how the main inventory stores data:

Copy the way The characteristics of advantages disadvantages
row Row-based format replication records data information for each row that needs to be modified. If a SQL modifies 2W rows of data, 2W rows of log format will be recorded Strong data consistency is guaranteed, and since the results are recorded after execution, restoring from the repository is also faster The number of log records is large, and the transfer between master and slave takes more time.
statement Segment-based log format replication, which is an SQL record that records the changes, not the changed rows. The minimum amount of logs is recorded. For some functions whose output is uncertain, a single run from the library, such as uUID, requires a slower run of SQL from the secondary library to return the master library data from the log.
mixed Mix the two logging formats to record logging. It is up to MySQL to decide when to use which logging method. You can balance the advantages and disadvantages of the two logging formats above.

Mysql5.7 used the statement format by default. Setting mode, which can be set in configuration file (preferred) :

binlog_format=ROWCopy the code

Or temporarily set global variables (valid for current mysql connection) :

Mysql > show variables like 'binlog_format'; Mysql > set binlog_format='row';Copy the code

Since the two primary and secondary servers are generally located in the same machine room, the synchronization speed between them is relatively fast. To ensure strong consistency, the log format of row should be preferred, and the mixed mode can be selected to ensure the transmission speed. The line log format has the following three recording modes:

Record the way The characteristics of
minimal Only the modified column data is logged
full Records data for all columns of the row being modified
noblob Same as above, except that blob and TEXT columns are not logged (i.e. big data columns) unless they are modified

Mysql is full by default, you’d better change it to minimal.

binlog_row_image=minimalCopy the code

4. Master/slave replication delay

Due to the main library and not on the same host between libraries, data synchronization between can’t inevitably has the delay, the solution is to add the cache, the business layer of jump to wait, if from the database level to reduce latency issue, can from the replication of the three steps (main library generate log, master-slave transport logs, from the content of library reduction) : 1. The rate at which the master database writes to the log controls the transaction size of the master database, splitting large transactions into smaller transactions. For example, insert 20W of data. Instead, insert 5000 rows more than once.

2. Transmission time between the primary and secondary logs The primary and secondary logs must be in the same equipment room or area. Change the log format to MIXED, and set the line log format is not minimal, see the log format introduction above.

3. Reduce the time it takes to restore logs from the library. With MySQL5.7, SQL multithreading can be allocated using logical clock mode. Set the logical clock: slave_parallel_type= ‘logical_clock’; Set the number of replicated threads: slave_parallel_workers=4;

Five. Need to pay attention to

  1. If you restart MySQL, switch to another MySQL user. Otherwise, the file may have permissions after it is started.
  2. After setting up the MySQL environment, set the log-bin option in the configuration. In this way, if the database needs to be copied from the database, there is no need to restart the database and interrupt services.
  3. The corresponding mysql port of the master library firewall needs to be opened.
  4. Because of the way that the slave database synchronizes the master database, it listens to the information sent by the master database rather than polling, so if there is a communication failure, the slave database will not synchronize the data if the master database does not change the data after reconnection, so the data can be synchronized by inserting an empty transaction.