MySQL binlog常用命令及设置清理时间
MySQL binlog常用命令及设置清理时间
- 1 binlog 基本概念
- 2 binlog常用命令
- 3 清理MySQL的binlog日志
- 3.1 自动清理
- 3.2 手动清理
文章参考：
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';