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)
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博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-1774299/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
mysql的多实例配置
下一篇：
mysql二进制日志格式介绍
请登录后发表评论
登录
全部评论
