MySQL Replication and tuning

Original: MySQL Replication and tuning

I. INTRODUCTION

The benefits of MySQL's own replication scheme are:

Data backup.

Load Balancing.

Distributed data.

Concept Introduction:

Host (Master): the database being replicated.

Slave (slave): The database that replicates the host data.

Copy steps:
(1). master records the details of the changes, deposited into the binary log (binary logs).
(2). master sends a sync message to slave.
(3). Slave When the message is received, copy the binary log of master to the local trunk log (relay log).
(4). Slave Reproduce the message in the relay log, thereby altering the database's data.

Here is a classic picture to illustrate the process:

Two. Implementing Replication

Implementing replication has the following steps:

1. Set up binary logs for MySQL main library and Server-id

MySQL configuration files are generally stored in/ETC/MY.CNF

# 在[mysqld]下面添加配置选项[mysqld]server-id=1log-bin=mysql-bin.log

Server-id is the unique identifier of the database throughout the database cluster and must remain unique.
Restart MySQL.
注：如果MySQL配置文件中已经配置过此文件，则可以跳过此步。

2. Create a new copy account

Create a new account in the main library to copy the Master library data from the library and grant replication permissions.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO [email protected]'host' IDENTIFIED BY 'password';

3. Set up MySQL Main library Server-id

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

1. You do not need to configure a binary log if you do not need to use the library as the main library from the library.
2. Many times replication does not need to replicate the entire database of the main library (especially the MySQL Information configuration library). Therefore, you can configure replicate_do_db to specify the replicated database

4. Initializing the master library's data from the library

If the amount of data is not large, you can use the Mysqldump tool to export the main library data and then import it into the library.

mysqldump --single-transaction --triggers --master-data databasename > data.sql

If the data volume is large, the xtrabackup should be used to export the database, not described here.
There may be classmates asking, why not just use binary log for initialization?

1. If our main library is running for a long time, it is not very suitable to replicate data from the library based on binary logs, and using binary logs directly to initialize from the library can be time-consuming and performance-intensive.
2. In more cases, the configuration entry for the primary repository's binary log is not open, so there is no binary log for the previous operation.

5. Turn on 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;

Note the last two commands: Master_log_file and Master_log_pos, which indicate from which binary file the library starts reading, where the offset starts, and these two parameters can be found from the SQL we import.

Turn on replication

start slave;

This completes the replication, updating a data in the main library or adding data to the results from the library.

The state of the replication thread can also be queried on the main library.

Three. Copy the log format

There are three log formats for MySQL replication, and there are three different ways to put data according to the master inventory:

How to copy	features	Advantages	Disadvantages
Row	Row-based format replication that records data information for each row that needs to be modified. If a SQL modifies the data of a 2w row, the log format of the 2w row is recorded	Ensure strong consistency of data, and because the record is the result of execution, it will be faster to perform a restore from the library	The number of log records is many, and the transfer between Master and slave takes more time.
Statement	Segment-based log format replication, that is, records of changed SQL Records, instead of changed rows.	Minimum number of log records.	For some functions that are not deterministic in output, it is likely that there will be a problem when executing from the library, such as the UUID, which needs to be executed once SQL is taken from the library according to the log to restore the Master library data, and the time is relatively slow.
Mixed	Mix the above two log formats to record logging, as to when to use which log mode is determined by MySQL itself.	You can balance the pros and cons of both of the above log formats.

mysql5.7 previously used the statement format by default.
Settings can be set in configuration file (preferred):

binlog_format=ROW

or temporarily set global variables (current MySQL connection is valid):

查看日志格式mysql > show variables like 'binlog_format';设置日志格式mysql > set binlog_format='row';

Since two master-slave servers will generally be placed in the same room, the synchronization between the speed will be faster, in order to ensure strong consistency, should be preferred row log format record (row), to ensure that the transmission speed can be selected mixed mode (mixed).
The log format of the row has the following three kinds of recording methods:

Recording Method	features
Minimal	Records only the data of the modified column
Full	Record data for all columns of the row being modified
Noblob	Features above, except that if you do not modify the Blob and text type columns, the data for those columns is not recorded (that is, Big data columns)

MySQL default is full, preferably modified into minimal.

binlog_row_image=minimal

Four. master-slave replication delay

Because the main library and from the library is not on the same host, the data synchronization can not avoid the delay between the resolution of the method has to add the cache, the business layer of the jump wait, if you have to slow down from the database level delay problem, can be copied from the time of the three major steps (the main library to generate logs, Master and slave transfer Restore the log content from the library) start with:
1. The speed at which the main library writes to the log
Control the transaction size of the main library, splitting large transactions into smaller transactions.
such as inserting 20w of data, to insert multiple 5000 lines (you can use the idea of paging)

2. Binary log transfer time between Master and slave
Between master and slave as far as possible in the same room or region.
The log format is changed to mixed, and the log format of the set line is not minimal, as described in the log format above.

3. Reduce the time to restore logs from the library
SQL multithreading can be allocated using logical clocks after the MySQL5.7 version.
Set logic clock: slave_parallel_type= ' logical_clock ';
Set Number of replication threads: slave_parallel_workers=4;

Five. Places to be aware of

1. Restarting MySQL is the best way to switch not to MySQL users, or the file will have permission problems when it starts.
2. After setting up the MySQL environment, set up the Log-bin option in the configuration, so that if the database needs to be copied from the library, there is no need to restart the database to interrupt the business.
3. You need to open the corresponding MySQL port of the main library's firewall.
4. Because of the way the main library is synchronized from the library, listening to the information sent by the main library, rather than polling, so if there is a communication failure, if the main library does not have data changes after reconnection, the data will not be synchronized from the library, so you can synchronize the data by inserting a null transaction.

Welcome to my Blog to view this article

MySQL Replication and tuning