MySQL binlog常用命令及设置清理时间

文章参考：
http://www.360doc.com/content/22/0418/08/65840191_1027038859.shtml
https://www.cnblogs.com/kiko2014551511/p/11532426.html

1 binlog 基本概念

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

2 binlog常用命令

说明	命令
查看binlog日志是否开启	show variables like ‘log_bin’;
查看日志配置信息	show variables like ‘log_%’;
查看详细的日志配置信息	show variables like ‘%log_%’;
查看binlog过期时间	show variables like ‘expire_logs_days’;
查看最新一个binlog日志文件名称和位置	show master status;
查看当前服务器使用的binlog文件及大小	show binary logs;
设置binlog过期时间	set global expire_logs_days = 15;

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)mysql>
mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------+
| Variable_name                          | Value                            |
+----------------------------------------+----------------------------------+
| log_bin                                | ON                               |
| log_bin_basename                       | /data/mysql/data/mysql-bin       |
| log_bin_index                          | /data/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                              |
| log_bin_use_v1_row_events              | OFF                              |
| log_builtin_as_identified_by_password  | OFF                              |
| log_error                              | /var/log/mariadb/mariadb.log     |
| log_error_verbosity                    | 3                                |
| log_output                             | FILE                             |
| log_queries_not_using_indexes          | OFF                              |
| log_slave_updates                      | OFF                              |
| log_slow_admin_statements              | OFF                              |
| log_slow_slave_statements              | OFF                              |
| log_statements_unsafe_for_binlog       | ON                               |
| log_syslog                             | OFF                              |
| log_syslog_facility                    | daemon                           |
| log_syslog_include_pid                 | ON                               |
| log_syslog_tag                         |                                  |
| log_throttle_queries_not_using_indexes | 0                                |
| log_timestamps                         | UTC                              |
| log_warnings                           | 2                                |
+----------------------------------------+----------------------------------+
21 rows in set (0.00 sec)mysql>
mysql> show variables like '%log_%';
+--------------------------------------------+---------------------------------------------+
| Variable_name                              | Value                                       |
+--------------------------------------------+---------------------------------------------+
| binlog_cache_size                          | 32768                                       |
| binlog_checksum                            | CRC32                                       |
| binlog_direct_non_transactional_updates    | OFF                                         |
| binlog_error_action                        | ABORT_SERVER                                |
| binlog_format                              | ROW                                         |
| binlog_group_commit_sync_delay             | 0                                           |
| binlog_group_commit_sync_no_delay_count    | 0                                           |
| binlog_gtid_simple_recovery                | ON                                          |
| binlog_max_flush_queue_time                | 0                                           |
| binlog_order_commits                       | ON                                          |
| binlog_row_image                           | FULL                                        |
| binlog_rows_query_log_events               | OFF                                         |
| binlog_stmt_cache_size                     | 32768                                       |
| binlog_transaction_dependency_history_size | 25000                                       |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER                                |
| expire_logs_days                           | 15                                          |
| general_log_file                           | /data/mysql/data/i-A8C12E4F.log             |
| innodb_flush_log_at_timeout                | 1                                           |
| innodb_flush_log_at_trx_commit             | 1                                           |
| innodb_log_buffer_size                     | 16777216                                    |
| innodb_log_checksums                       | ON                                          |
| innodb_log_compressed_pages                | ON                                          |
| innodb_log_file_size                       | 50331648                                    |
| innodb_log_files_in_group                  | 2                                           |
| innodb_log_group_home_dir                  | ./                                          |
| innodb_log_write_ahead_size                | 8192                                        |
| innodb_max_undo_log_size                   | 1073741824                                  |
| innodb_online_alter_log_max_size           | 134217728                                   |
| innodb_undo_log_truncate                   | OFF                                         |
| innodb_undo_logs                           | 128                                         |
| log_bin                                    | ON                                          |
| log_bin_basename                           | /data/mysql/data/mysql-bin                  |
| log_bin_index                              | /data/mysql/data/mysql-bin.index            |
| log_bin_trust_function_creators            | OFF                                         |
| log_bin_use_v1_row_events                  | OFF                                         |
| log_builtin_as_identified_by_password      | OFF                                         |
| log_error                                  | /var/log/mariadb/mariadb.log                |
| log_error_verbosity                        | 3                                           |
| log_output                                 | FILE                                        |
| log_queries_not_using_indexes              | OFF                                         |
| log_slave_updates                          | OFF                                         |
| log_slow_admin_statements                  | OFF                                         |
| log_slow_slave_statements                  | OFF                                         |
| log_statements_unsafe_for_binlog           | ON                                          |
| log_syslog                                 | OFF                                         |
| log_syslog_facility                        | daemon                                      |
| log_syslog_include_pid                     | ON                                          |
| log_syslog_tag                             |                                             |
| log_throttle_queries_not_using_indexes     | 0                                           |
| log_timestamps                             | UTC                                         |
| log_warnings                               | 2                                           |
| max_binlog_cache_size                      | 18446744073709547520                        |
| max_binlog_size                            | 1073741824                                  |
| max_binlog_stmt_cache_size                 | 18446744073709547520                        |
| max_relay_log_size                         | 0                                           |
| relay_log_basename                         | /data/mysql/data/i-A8C12E4F-relay-bin       |
| relay_log_index                            | /data/mysql/data/i-A8C12E4F-relay-bin.index |
| relay_log_info_file                        | relay-log.info                              |
| relay_log_info_repository                  | FILE                                        |
| relay_log_purge                            | ON                                          |
| relay_log_recovery                         | OFF                                         |
| relay_log_space_limit                      | 0                                           |
| slow_query_log_file                        | /data/mysql/data/i-A8C12E4F-slow.log        |
| sql_log_bin                                | ON                                          |
| sql_log_off                                | OFF                                         |
| sync_relay_log_info                        | 10000                                       |
+--------------------------------------------+---------------------------------------------+
66 rows in set (0.00 sec)mysql>
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 15    |
+------------------+-------+
1 row in set (0.01 sec)mysql>
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 30589365 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)mysql>
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 750131872 |
| mysql-bin.000002 |  30651375 |
+------------------+-----------+
2 rows in set (0.00 sec)mysql>

3 清理MySQL的binlog日志

3.1 自动清理

	MySQL中修改	配置文件my.cnf中修改
设置命令	set global expire_logs_days = 7	expire_logs_days=7
时效性	临时生效，重启失效	永久生效，重启生效

• MySQL中修改

# 查看binlog的过期时间，默认值为0，即不自动清理
mysql> show variables like 'expire_logs_days'; +------------------------+-------+ | Variable_name  | Value | +------------------------+-------+ | expire_logs_days |   0  | +------------------------+-------+ # 设置MySQL的binlog日志7天过期自动清理
mysql> set global expire_logs_days = 7;

• 配置文件my.cnf中修改，并保存

# 编辑配置文件
vim /etc/my.cnf
# 在文件中追加binlog自动删除时间，如7天
expire_logs_days=7

3.2 手动清理

手动删除前需要先确认主从库当前在用的binlog文件：
主库：show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 30589365 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)mysql>

从库：show slave status\G

mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.200.66Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 30591705Relay_Log_File: i-E97ED6FB-relay-bin.000004Relay_Log_Pos: 30591918Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes....（此处省略其他信息）....
1 row in set (0.00 sec)mysql>

删除语句：

# 删除指定日期以前的日志索引中binlog日志文件
purge master logs before '2016-09-01 17:20:00';# 或 删除指定日志文件的日志索引中binlog日志文件
purge master logs to'mysql-bin.000022';