MySQL binary logs

Recently, an alarm is triggered when the disk space of the database server is insufficient. In fact, the database file itself is not large, mainly because the binary logs accumulated over the past six months is large, especially databases with master-slave configurations. I checked a lot of information and summarized the commonly used MySQL commands for Binary logs:

1. Binary logs location:

1) view/etc/My. CNF (My. ini in Windows), for example:

Log_bin =/var/mydb/bin-Log

If this setting does not contain a path, place it in datadir =/var/lib/MySQL.

2) check whether the binary log function is enabled in the database:

Mysql> show variables like 'Log _ bin ';
+ --------------- + ------- +
| Variable_name | value |
+ --------------- + ------- +
| Log_bin | on |
+ --------------- + ------- +
1 row in SET (0.00 Sec)

Mysql> show variables like 'Log _ bin ';
+ --------------- + ------- +
| Variable_name | value |
+ --------------- + ------- +
| Log_bin | off |
+ --------------- + ------- +
1 row in SET (0.01 Sec)

If it is off, it will not work as follows :)

2. Check the name and size of the currently running logfile: Show binary logs/show Master logs;

Mysql> show binary logs;
+ ----------------- + ----------- +
| Log_name | file_size |
+ ----------------- + ----------- +
| Bin-log.000011 | 148329 |
+ ----------------- + ----------- +

Mysql> show Master logs;
+ ----------------- + ----------- +
| Log_name | file_size |
+ ----------------- + ----------- +
| Bin-log.000011 | 148329 |
+ ----------------- + ----------- +

Mysql> show binary logs;
Error 1381 (hy000): You are not using binary logging ### binary logs not enabled

3. Clear all binary logs;

1)

Mysql> show Master logs;
+ ----------------- + ----------- +
| Log_name | file_size |
+ ----------------- + ----------- +
| Log-bin.000001 | 259548 |
| Log-bin.000002 | 37200 |
| Log-bin.000003 | 74219 |
+ ----------------- + ----------- +
3 rows in SET (0.00 Sec)

Mysql> Reset master; (on slave, use reset slave. Stop slave before and start slave)
Query OK, 0 rows affected (0.00 Sec)

Mysql> show Master logs;
+ ----------------- + ----------- +
| Log_name | file_size |
+ ----------------- + ----------- +
| Log-bin.000001 | 98 |
+ ----------------- + ----------- +
1 row in SET (0.00 Sec)

4. Clear the specified logs:

Mysql> purge binary logs to 'Log-bin.000012 ';

Clear binary logs before log-bin.000012;

Mysql> purge binary logs before '2017-05-28 12:05:38 ';

Clears the binary logs before the specified time;

5. view the current binary log:

Mysql> show Master status;

6. View binary logs content:

Mysql> show BINLOG events;

Command line:

# Mysqlbinlog/var/log/MySQL/log-bin.000140; or

# Mysqlbinlog -- start-datetime = '2017-07-01 00:00:00 '-- stop-datetime = '2017-07-15 00:00:00'/var/log/MySQL/log-bin.000020>. /tmp. log

7. Set the number of days for Binary logs rollback in my. CNF/My. ini:

Expire_logs_days = 7