管理mysql二進位制日誌

lsq_008發表於2015-08-17
MySql
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章