Mysql13 Copy 2, mysql13 Copy 2

Mysql13 Copy 2, mysql13 Copy 2
Replication management monitoring

Show master logs;
Check the second-level logs of the master database. The logname is the input parameter of other commands, and the file_size is the offset and the input parameter.

Assume that we know the log offset (from the preceding command) and use:
Show binlog events in 'mysql-bin.0000023 'FROM 13634;
View the last executed SQL statement.

Measure slave database latency

The show slave status command has the following problems:

• Use the current server date and the timestamp in the binary file
• Large events will cause latency fluctuations
  A better solution is to use heart record. This is the timestamp that is updated every second on the master database.

Check whether the master and slave databases are consistent.

Pt-table-checksum

Synchronize the slave database from the master database again

Remove the slave database and synchronize it again.
Use the mysqldump command. This command needs to be locked and then operated
Pt-table-sync Tool

Change master database

Plan

Unplanned
This will cause data loss and non-synchronization of the master database. The slave database is not synchronized either.

Copy problematic data corruption or loss

The primary database goes down unexpectedly.
If sync_binlog is not set, the first few binary logs may crash and are not flushed into the disk. Restart the standby database thread to connect again. The master database will tell him that the offset does not exist.
The solution is to allow the slave database to read from the beginning of the next binary log, and then use a tool to view master-slave consistency. Or enable sync_binlog to avoid loss, but it may cause performance loss.
... There are many more details.

Use non-transactional tables

Make sure that the stop slave operation is run before the master database is restarted. Otherwise, data may be inconsistent.

Uncertain statement

It is mainly statement-based replication, which requires attention during development. Consider the possible causes of this phenomenon.

Lock contention caused by lock reading using the unique server IDInnoDB

INSERT... The SELECT Operation will cause the read lock and serialize the data.
Can be split into small commands
Use select into outfile, and load data infile instead of INSERT... SELECT. is faster without locking.

High replication latency

Note: latency must be allowed in application design.
Latency usually occurs suddenly, but it is not easy to monitor.

You can use some methods to improve the performance of the slave database:

• If log_slows_slave_statements is enabled after latency is found, you can check the problem.
• Disable binary logs of the standby Database
• Set innodb_flush_log_at_trx_commit to refresh the disk.
• Cost-effective part of Non-repeated write operations
  For example, an operation to update a statistical table can be optimized. Create a new database in the master database. The statistics structure updates this database. Then, select into outfile and load data infile are used to write the DATA back to the master database. In this way, the slave database will not perform this operation synchronously.
  Based on the same idea, you can also put this operation on the application layer for statistics, and then the application layer shows that the database update operation is called.
• Operations outside of replication mainly solve the problem that the slave database is serial.
  There are two common types: archive databases. Archive operation in binary files, and then perform these archive queries on the master database and slave database separately.
  You can also process some special tables separately. You can use the application to manually synchronize these objects. This may improve data performance.

The size and configuration of the Master/Slave database package are inconsistent.

If the max_allowed_packet of the master and slave databases does not match, a large package may be sent from the master database. It may cause errors or log corruption.

Insufficient bandwidth

You can enable the slave_compressed_protocol option of the slave database to compress and decompress the data during transmission.

Replication speed

Test:

Insert into lag_test (now_usec) VALUES (NOW_USEC () // ensure the time synchronization between the master and slave databases. // note that the database must be a varchar column, because the accuracy of the time column may be seconds.

Then, use the TIMESTAMPDIFF method to query the time difference.
You can insert a value of 1000 to the input, group the data by the order of magnitude, or calculate the average value. How long is the normal delay?
Generally, it is within 0. A few milliseconds.

Some advanced features

• 5.1 introduced row Replication
• 5.5 introduced semi-sync.
  After the food is submitted, ensure that the binary log is transmitted to at least one slave database before the result is returned to the client, so as to ensure better master-slave synchronization. However, it will delay the client transaction commit a little bit. Semi-synchronization also has a little performance improvement, because of semi-synchronization, You can boldly disable bin-log. The local write disk is converted to the remote write memory. It turns out that the remote write memory is faster.
• 5.5 added replication heartbeat monitoring
• 5.6 introduced parallel replication for partial Parallel Processing

Other replication Behaviors

Percona Toolkit and Percona Xtrabackup both provide the copy-based or help copy function.
Tunststen Java open source middleware replication product. Provides automatic data sharding, concurrent execution, data replication, platform replication, multi-source replication, and other functions. He is very practical and has some advantages:
-Built-in consistency check
-Plug-in features
-Global Transaction ID, no need to match the daily fatal and offset
-Fast upgrade of the slave database to the master database
-Heterogeneous replication, such as Mysql to PostgreSQL
-Between Different Versions
-Parallel
The disadvantage is that learning is more complex and less efficient.

Summary:

• Significantly increased Mysql functionality and availability
• It does not provide monitoring, configuration, management, and other functions. It can be optimized by other authorities, such as Percona Toolkit and XtraBackup.
• Before copying configurations, you must use the following tools for comparison.
• Monitoring will not lag behind the master database
• Application Design should avoid dirty data with Master/Slave latency
• Slave database system and add permissions, do not write in multiple places.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.