Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL replication and tuning principle of example analysis, the article is very detailed, has a certain reference value, interested friends must read!

I. profile

MySQL comes with a replication scheme, which brings benefits such as:

Data backup.

Load Balancer.

Distributed data.

Concept introduction:

Master: The database being replicated.

Slave: A database that replicates host data.

Copy steps:

(1). The master records the details of the changes and stores them in the binary log.

(2). The master sends synchronization messages to the slave.

(3). When slave receives the message, it copies the master binary log to the local relay log.

(4). slave reproduces messages from relay logs, thereby altering the data in the database.

Here's a classic picture to illustrate the process:

II. implementing replication

To achieve replication, there are the following steps:

1. Set up the MySQL main library binary log and server-id

MySQL configuration files are generally stored in/etc/my.cnf

#Add configuration option [mysqld]server-id=1log-bin=mysql-bin.log under [mysqld]

Server-id is the unique identifier of the database in the whole database cluster and must remain unique.

Restart MySQL.

Note: If this file is already configured in the MySQL configuration file, you can skip this step.

2. Create a copy account

Create an account in the master database to copy master database data from the slave database, and grant copy permission.

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

3. Set MySQL master server-id

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

If you do not need a slave as a master for another slave, you do not need to configure binary logging. Many times replication does not require copying the entire database of the master repository (especially mysql's information repository). Therefore, you can configure replicate_do_db to specify the replicated database 4. Initialize the database of the master database from the database

If the amount of data is not large, you can use the mysqldump tool to export the master database data and then import it into the slave database.

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

If the data volume is large, you should use Xtrabackup to export the database, which is not described here.

Some students may ask, why not use binary log initialization directly?

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. It will take time and performance to initialize the slave library directly using the binary log. More often than not, the binary log entry for the master library is not turned on, so there is no binary log for previous operations. 5. Open copy

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, indicating which binary file in the library to read from and where the offset starts, which can be found in the SQL we imported.

Open copy

start slave;

At this time, the replication is completed, and the results can be queried in the slave database for updating a data or adding new data in the master database.

You can also query the status of replication threads on the master repository.

III. Log format for replication

MySQL replication log format has three, according to the main database storage data different ways have the following three:

Copy method Features Advantages Disadvantages Row format copy based on rows, record the data information of each row that needs to be modified. If a SQL modifies 2w rows of data, then the log format of 2w rows will be recorded to ensure strong consistency of the data, and since the record is the result after execution, the restore performed on the slave library will be faster. The number of log records is large, and the transfer between master and slave takes more time. statement is copied based on the log format of the segment, that is, the SQL record that records the changes, not the record of the changed rows. The minimum amount of logging. For some functions with uncertain output results, problems may occur when executing them once on the slave library, such as uuid. When the slave library restores the master database data according to the log, it needs to execute SQL once, which is relatively slow. mixed The above two log formats record logs, and MySQL itself decides when to use which log method. You can balance the pros and cons of the above two log formats.

Before mysql 5.7, statement format was used by default.

Setting method can be set in the configuration file (preferred):

binlog_format=ROW

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

view log format mysql > show variables like 'binlog_format'; set log format mysql > set binlog_format=' row';

Since two master and slave servers are generally placed in the same computer room, the synchronization speed between the two will be faster. To ensure strong consistency, the log format of rows should be preferred, and the mixed mode can be selected to ensure the transmission speed.

There are three types of log formats:

Record method characteristics minimal records only the data of modified columns full records the data of all columns of modified rows noblob characteristics are the same as above, except that if the columns of blob and text type are not modified, the data of these columns will not be recorded (i.e. large data columns)

mysql is full by default, it is best to modify it to minimal.

binlog_row_image=minimal four. master-slave replication delay

Since the master and slave libraries are not on the same host, there is inevitably a delay between data synchronization. The solution includes adding cache and waiting for jump at the business layer. If you have to slow down the delay problem from the database level, you can start from the three steps of replication (generating logs in the master library, transferring logs from the master to the slave, and restoring log contents in the slave library):

1. The rate at which the master library writes to the log

Control the transaction size of the master library, splitting large transactions into multiple small transactions.

For example, insert 20w data, change to insert 5000 lines multiple times (you can use the idea of paging)

2. Binary log transfer time between master and slave

As far as possible between the master and slave in the same room or region.

The log format is changed to MIXED, and the log format of the setting line is not minimal. For the principle, see the log format description above.

3. Reduce log restore time from repository

SQL multithreading can be allocated using logical clocks after MySQL version 5.7.

Set logical clock: slave_parallel_type='logical_clock';

Set the number of replication threads: slave_parallel_workers=4;

v. places to pay attention

Restart MySQL It is best to switch MySQL users before operating, otherwise there will be permission problems after the file is started. After setting up the MySQL environment, set the log-bin option in the configuration, so that if the database needs to copy from the database in the future, there is no need to restart the database and interrupt the business. You need to open the mysql port corresponding to the firewall of the main database. Because the slave synchronizes with the master by listening to messages sent by the master instead of polling, if there is a communication failure, the slave will not synchronize data if the master does not perform data change operations after reconnection, so you can synchronize data by inserting null transactions.

The above is "MySQL replication and tuning principle of example analysis" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.