Shulou(Shulou.com)06/01 Report--

The following content mainly brings you a brief analysis of Mysql replication monitoring and maintenance. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

(1) Clean up logs:

PURGE, delete binaries, cannot be physically deleted directly, otherwise restarting the mysql service may cause various exceptions, which need to be deleted by using the command PURGE, because after mysql restart, the corresponding files will not be found. Deleting using the purge command will automatically update the index files, and the next time mysql restarts, the corresponding binary files will not be found.

PURGE {BINARY | MASTER} LOGS {TO 'log_name' | BEFORE datetime_expr}

TO indicates that everything before the binary file, excluding the binary file, will be cleared.

Or, at a certain time in BEFORE, the binary log files before that time will be deleted. If this time happens to be a certain time point in the middle of a binary file, then all logs before that point in time will be deleted, but the logs after that time point in the log will not be deleted.

Binary logs must not be deleted to restore data. If you must delete them, it is recommended to copy the binaries to other machines, and then use the purge command to delete the binaries on this machine.

Find the contents of the current binary file

MariaDB [sunnydb] > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | master-log.000001 | 542 |

| | master-log.000002 | 468 |

| | master-log.000003 | 1045 | |

| | master-log.000004 | 1296 | |

+-+ +

If master-log.000003 is cleared, the binary log of master-log.000002 and master-log.000001 is cleared. The command is as follows

MariaDB [sunnydb] > purge binary logs to 'master-log.000003'

(2) copy monitoring commands

Commands to view copy-related status and files

MASTER:

Check which binaries are currently used and the location of the current binaries

SHOW MASTER STATUS

View the record of the binary file

SHOW BINLOG EVENTS

View all binaries and file sizes

SHOW BINARY LOGS

SLAVE:

View the status data from the CVM

SHOW SLAVE STATUS\ G

Determine whether the slave server lags behind the master server:

Seconds_Behind_Master: 0

(3) how to determine whether the data of master and slave nodes are consistent?

Through the CHECKSUM check of the table, the checksum function can be enabled when the table is created, but once this feature is enabled, the check code of the table will be calculated every time the information of the table is changed, which consumes resources.

It is recommended to use pt-table-checksum;, the function of percona-toolkit in percona-tools, to calculate the checksum of the tables on the master-slave server and compare them. Percona-toolkit can also check various system metrics.

(4) what is the repair method when the master and slave data are inconsistent?

Re-copy

If the amount of non-synchronization is very small, you can directly modify the data manually.

Note that the tool PXC is copied bit by bit, and the probability of data inconsistency is small.

For the above brief analysis of Mysql replication monitoring and maintenance, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.