Analysis of MySQL replication and optimization principles and methods, mysql replication Optimization Principles

Analysis of MySQL replication and optimization principles and methods, mysql replication Optimization Principles
I. Introduction

MySQL's built-in replication solution brings the following benefits:

Data backup.

Server Load balancer.

Distributed Data.

Concepts:

Master: the database to be copied.

Slave: the database that copies host data.

Copy steps:
(1) The master records the details of the changes and stores them in binary logs ).
(2). The master sends a synchronous message to slave.
(3) After the slave receives the message, it copies the binary log of the master to the local relay log ).
(4). the slave replays the messages in the relay log to change the data in the database.

Here is a classic image to illustrate this process:

Ii. implement replication

Follow these steps to implement replication:

1. Set the binary log and server-id of the MySQL master database

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

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

Server-id is the unique identifier of the database in the entire database cluster and must be unique.
Restart MySQL.

Note: skip this step if this file has been configured in the MySQL configuration file.

2. Create a replication account

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

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

3. Set the MySQL master database server-id

As configured in step 2, note the following two points:

If you do not need the slave database as the master database of another slave database, you do not need to configure binary logs. In many cases, you do not need to copy all the databases in the master database (especially the mysql information configuration library ). Therefore, you can configure replicate_do_db to specify the copied database 4. initialize the data of the master database from the slave Database

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

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

If the data volume is large, Xtrabackup should be used to export the database, which is not described here.
Some may ask, why not directly use binary logs for initialization?

If the master database has been running for a long period of time, it is not suitable to use slave database to copy data based on binary logs. It is time-consuming and performance-consuming to directly use binary logs to initialize slave database. In more cases, the configuration item of the binary log of the master database is not enabled, so there is no binary log of previous operations. 5. enable replication

Run the following command from the database:

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 the binary file from which the database is read and the offset starts from there. These two parameters can be found in the imported SQL.

Enable replication

start slave;

At this time, the replication is completed. You can query the results of updating a data or adding new data in the master database.

The status of the replication thread can also be queried in the master database.

Iii. log format copied

There are three log formats for MySQL replication:

Replication Mode	Features	Advantages	Disadvantages
Row	The row-based format replication records the data information of each row to be modified. If an SQL statement modifies data of 2 million rows, the log format of 2 million rows is recorded.	This ensures strong data consistency. Because the record is the result after execution, it is faster to execute restoration on the slave database.	The number of log records is large, and the transmission between the master and slave nodes takes more time.
Statement	Segment-based log format replication, that is, records the changed SQL records, rather than the records of changed rows.	The minimum number of log records.	For some functions with uncertain output results, it is very likely that errors will occur when executing them again in the slave database, such as uuid. When restoring data in the master database from the slave Database Based on logs, You need to execute SQL again, time is relatively slow.
Mixed	The preceding two log formats are used to record logs. MySQL determines when to use the LOG method.	Balancing the advantages and disadvantages of the preceding two log formats.

The statement format was used by default before MySQL.

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

binlog_format=ROW

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

View the log format mysql> show variables like 'binlog _ format'; set the log format mysql> set binlog_format = 'row ';

Because the two master-slave servers are usually stored in the same data center, the synchronization speed between the two servers is faster. To ensure strong consistency, you should first choose the row log format record (row ), to ensure the transmission speed, you can select the mixed mode (mixed ).
The log format of a row can be as follows:

Record Method	Features
Minimal	Only records the data of the modified Column
Full	Record the data of all columns of the modified row
Noblob	Features are the same as above, except that data in these columns (that is, big data columns) is not recorded if blob and text columns are not modified)

Mysql is full by default. It is best to change it to minimal.

binlog_row_image=minimal

4. Master-slave replication latency

Because the master database and slave database are not on the same host, Data Synchronization cannot avoid latency. The solution is to add cache and wait for the service layer to jump, if you have to reduce the latency at the database level, you can start with three steps during replication (logs are generated in the master database, logs are transmitted from the master database, and logs are restored from the database:

1. Log writing speed of the master database

Controls the transaction size of the master database and separates large transactions into multiple small transactions.

For example, if you insert 20 million data records, you can insert 5000 rows multiple times (you can use the paging method)

2. binary log transmission time between the master and slave

Master/Slave nodes should be in the same data center or region.

The log format is changed to MIXED, and the row log format is not minimal. For details, see the preceding log format.

3. Reduce the time for restoring logs from the database

After MySQL, you can use the logical clock to allocate Multiple SQL threads.

Set the logical clock: slave_parallel_type = 'logical _ clock ';

Set the number of replication threads: slave_parallel_workers = 4;

5. Notes

To restart MySQL, you are advised to switch between non-MySQL users before the operation. Otherwise, the file will have permission issues after startup. 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, you do not need to restart the database and interrupt the business. You need to open the mysql port corresponding to the firewall of the master database. Because the slave database synchronizes the information sent by the master database, instead of polling, if a communication fault occurs, if the master database does not change the data after the connection is reconnected, the slave database does not synchronize data, so data can be synchronized by inserting null transactions.

The above is all the content compiled in this article. Thank you for your support for the help house.