Monitoring and maintenance of MySQL replication in the database

(1) Cleanup log:

PURGE, delete the binary files, can not be directly physically deleted, or restart the MySQL service may cause a variety of exceptions, need to use the command PURGE to delete, because after MySQL restart, will not find the corresponding files, using the PURGE command to delete automatically update the index file, The next time MySQL restarts, it will not find the corresponding binary file

PURGE {BINARY | MASTER} LOGS {to ' log_name ' | Before datetime_expr};

To indicates that the binary file, excluding the binary, will be erased

Or, before a time, the binary log file that precedes that time is deleted, and if that happens to be a point in the middle of a binary file, the log before that point in time will be deleted, but the log will not be cleared after that point in time.

Binary log must not be deleted, used to recover data, if must be deleted, it is recommended to copy the binary files to other machines, and then use the Purge command to delete the binary files on the computer

Find the current binary file contents

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 clear master-log.000003 is cleared master-log.000002 and master-log.000001 binary log, the command is as follows

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

(2) Copy Monitoring command

To view the copy-related status and file commands

MASTER:

See which binaries are currently used, and where the current binaries are

SHOW MASTER STATUS;

To view the records of a binary file

SHOW BINLOG EVENTS;

View all binary files and file sizes

SHOW BINARY LOGS;

SLAVE:

View status data from the server

SHOW SLAVE Status\g;

Determine if the time from the server is behind the master server:

seconds_behind_master:0

(3) How to determine the master-slave node data is consistent?

With the table's checksum check, the table can be created with the checksum feature enabled, but once this feature is enabled, each change to the table will calculate the check code of the table and consume resources;

It is recommended to use the function Pt-table-checksum of Percona-toolkit in Percona-tools, calculate the checksum of the table on the master and slave server and compare it. Percona-toolkit can also check various system indicators

(4) The repair method when the master-slave data is inconsistent?

Re-copy;

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

Note that the tool PXC bitwise copy, the probability of inconsistent data is small

Monitoring and maintenance of MySQL replication in the database