ITPub博客

首页 > 数据库 > MySQL > 管理mysql二进制日志

管理mysql二进制日志

原创 MySQL 作者:lsq_008 时间:2015-08-17 17:05:32 0 删除 编辑
1,查看是否打开二进制日志文件的功能

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| log_bin                         | ON                         |
| log_bin_basename                | /u01/mysql/mysql-bin       |
| log_bin_index                   | /u01/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                        |
| log_bin_use_v1_row_events       | OFF                        |
| sql_log_bin                     | ON                         |
+---------------------------------+----------------------------+
6 rows in set (0.00 sec)

2.查看二进制文件的情况
mysql> show master log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'log' at line 1
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       749 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       120 |
| mysql-bin.000005 |       120 |
| mysql-bin.000006 |      1351 |
| mysql-bin.000007 |       333 |
| mysql-bin.000008 |      7044 |
| mysql-bin.000009 |       120 |
| mysql-bin.000010 |       143 |
| mysql-bin.000011 |       987 |
| mysql-bin.000012 |       143 |
| mysql-bin.000013 |       143 |
| mysql-bin.000014 |       592 |
| mysql-bin.000015 |      4026 |
| mysql-bin.000016 |       253 |
| mysql-bin.000017 |       120 |
+------------------+-----------+
17 rows in set (0.00 sec)

3.切换二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       749 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       120 |
| mysql-bin.000005 |       120 |
| mysql-bin.000006 |      1351 |
| mysql-bin.000007 |       333 |
| mysql-bin.000008 |      7044 |
| mysql-bin.000009 |       120 |
| mysql-bin.000010 |       143 |
| mysql-bin.000011 |       987 |
| mysql-bin.000012 |       143 |
| mysql-bin.000013 |       143 |
| mysql-bin.000014 |       592 |
| mysql-bin.000015 |      4026 |
| mysql-bin.000016 |       253 |
| mysql-bin.000017 |       120 |
+------------------+-----------+
17 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       749 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |       120 |
| mysql-bin.000005 |       120 |
| mysql-bin.000006 |      1351 |
| mysql-bin.000007 |       333 |
| mysql-bin.000008 |      7044 |
| mysql-bin.000009 |       120 |
| mysql-bin.000010 |       143 |
| mysql-bin.000011 |       987 |
| mysql-bin.000012 |       143 |
| mysql-bin.000013 |       143 |
| mysql-bin.000014 |       592 |
| mysql-bin.000015 |      4026 |
| mysql-bin.000016 |       253 |
| mysql-bin.000017 |       167 |
| mysql-bin.000018 |       120 |
+------------------+-----------+
18 rows in set (0.00 sec)

4.删除或者初始化二进制文件

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

5.删除指定的二进制文件
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |       167 |
| mysql-bin.000003 |       167 |
| mysql-bin.000004 |       120 |
+------------------+-----------+
4 rows in set (0.00 sec)


mysql> purge binary logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.03 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       167 |
| mysql-bin.000004 |       120 |
+------------------+-----------+
2 rows in set (0.00 sec)

6,指定二进制日志失效期
mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 0     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

mysql> set global expire_logs_days=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 5     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-1774299/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1244073