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

Common binlog log operation commands

1) View a list of all binlog logs

Mysql > show master logs

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000020 | 1233973852 | |

| | mysql-bin.000021 | 1098889933 | |

| | mysql-bin.000022 | 1073742725 | |

| | mysql-bin.000023 | 1076857649 | |

2 rows in set (0.00 sec)

2) check the master status, that is, the numbered name of the last (latest) binlog log and the pos end point (Position) value of the last action event

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000023 | 4102 | |

+-+

1 row in set (0.00 sec)

3) flush refreshes the log log and generates a newly numbered binlog log file from now on

Mysql > flush logs

Query OK, 0 rows affected (0.13 sec)

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 149 | |

| | mysql-bin.000002 | 4145 | |

| | mysql-bin.000003 | 106 | |

+-+ +

3 rows in set (0.00 sec)

Note:

Whenever the mysqld service is restarted, this command is automatically executed to refresh the binlog log; adding the-F option to mysqldump backup data will also refresh the binlog log.

4) reset (empty) all binlog logs

Mysql > reset master

Query OK, 0 rows affected (0.12 sec)

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 106 | |

+-+ +

1 row in set (0.00 sec)

5.1) clear binlog:

Delete files before binlog.000022

/ / Delete the binlog log file in the log index of the specified log file

Mysql > purge master logs to 'binlog.000022'

Query OK, 0 rows affected (0.04 sec)

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | binlog.000022 | 185770450 | |

| | binlog.000023 | 209780654 | |

| | binlog.000024 | 210483780 | |

| | binlog.000025 | 210304097 | |

| | binlog.000026 | 210346860 | |

| | binlog.000027 | 209954750 | |

| | binlog.000028 | 210094938 | |

5.2) specify a time to delete:

Purge master logs before'2018-10-02 21 40 00; / / Delete the binlog log file in the log index before the specified date

5.3) remove binlog considerations:

Note that the time and the file name must not be misspelled, especially the year in the time and the sequence number in the file name, to prevent the binlog in use from being deleted, and do not delete the binlog in use.

Using this syntax, the corresponding file and the corresponding path in mysql-bin.index are deleted.

5.4) make the system delete binlog files automatically by setting the expiration time of binlog:

Mysql > show variables like 'expire_logs_days'

+-+ +

| | Variable_name | Value |

+-+ +

| | expire_logs_days | 0 | |

+-+ +

Mysql > set global expire_logs_days = 14; # set how many days binlog expires

Note:

The expiration time should be set appropriately. For master-slave replication, it depends on the delay of the slave database to determine the expiration time, especially for some busy business systems, to avoid deleting the master database binlog before it is transferred to the slave database.

Cause the master to be inconsistent and reserve enough binlog logs for recovery use.

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.