MySQL 的 Binary Logging 日志文件类似 PostgreSQL 的 WAL 和 Oracle 的 redo ，记录数据库的变化信息，开启 binary logging 比较简单，但仍费了些功夫，记录下。
一 开启 Binary Logging 功能
设置 my.cnf
1
2
3
|
log_bin = on
log_bin_basename = "/database/mysql/data/binlog/bin-log"
binlog_format = "MIXED"
|
重启 mysql 失败
1
2
3
|
[root@db1 ~]# /etc/init.d/mysql restart
Shutting down MySQL.... [OK ]
Starting MySQL.....The server quit without updating PID fil[FAILED]base/mysql/data/db1.pid).
|
db1.err 日志
1
|
2014-08-19 11:09:57 24489 [ERROR] /opt/mysql/bin/mysqld: unknown variable 'log_bin_basename=/database/mysql/data/binlog/bin-log'
|
备注：日志中说 log_bin_basename 设置不对，文档中也没说这个参数不让设置。http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_log_bin_basename ，后来查了下资料，说是更改 log_bin 参数成日志绝对路径即可，但文档中说 log_bin 参数值为 on 或 off,感觉很奇怪，先试试。
重新设置 my.cnf
1
2
3
|
log_bin = "/database/mysql/data/binlog/bin-log"
binlog_format = "STATEMENT"
binlog_format = "MIXED"
|
备注：这里没设置 log_bin_basename。而将 log_bin 设置成日志绝对路径。
重启 mysql
1
2
3
|
[root@db1 ~]# /etc/init.d/mysql restart
MySQL server PID file could not be found! [FAILED]
Starting MySQL..... [OK ]
|
备注：这下可以正常启动了。
查看变量
1
2
3
4
5
6
7
8
9
10
|
root@localhost:(none)>show variables like 'log_bin%';
+---------------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------+
| log_bin | ON |
| log_bin_basename | /database/mysql/data/binlog/bin-log |
| log_bin_index | /database/mysql/data/binlog/bin-log.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------------------------------------------+
|
备注：奇怪，这里 log_bin 显示为 on 了，而 log_bin_basename 变量的值变成之前设置的 log_bin 变量值了。
二 清理 Binary Log
数据库中查看 binary log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
root@localhost:francs>show binary logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| bin-log.000005 | 143 |
| bin-log.000006 | 143 |
| bin-log.000007 | 165 |
| bin-log.000008 | 165 |
| bin-log.000009 | 723 |
| bin-log.000010 | 165 |
| bin-log.000011 | 285 |
| bin-log.000012 | 459 |
| bin-log.000013 | 143 |
| bin-log.000014 | 143 |
| bin-log.000015 | 143 |
| bin-log.000016 | 374 |
| bin-log.000017 | 143 |
| bin-log.000018 | 418 |
| bin-log.000019 | 143 |
| bin-log.000020 | 143 |
| bin-log.000021 | 143 |
| bin-log.000022 | 143 |
| bin-log.000023 | 1587 |
| bin-log.000024 | 165 |
| bin-log.000025 | 165 |
| bin-log.000026 | 385 |
| bin-log.000027 | 143 |
| bin-log.000028 | 143 |
| bin-log.000029 | 120 |
| bin-log.000030 | 120 |
| bin-log.000031 | 120 |
| bin-log.000032 | 120 |
| bin-log.000033 | 120 |
| bin-log.000034 | 120 |
| bin-log.000035 | 143 |
| bin-log.000036 | 10758 |
+----------------+-----------+
32 rows in set (0.01 sec)
|
purge 语法
1
2
3
4
|
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
|
清除 binary log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
root@localhost:francs>purge binary logs to 'bin-log.000010';
Query OK, 0 rows affected (0.11 sec)
root@localhost:francs>show binary logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| bin-log.000010 | 165 |
| bin-log.000011 | 285 |
| bin-log.000012 | 459 |
| bin-log.000013 | 143 |
| bin-log.000014 | 143 |
| bin-log.000015 | 143 |
| bin-log.000016 | 374 |
| bin-log.000017 | 143 |
| bin-log.000018 | 418 |
| bin-log.000019 | 143 |
| bin-log.000020 | 143 |
| bin-log.000021 | 143 |
| bin-log.000022 | 143 |
| bin-log.000023 | 1587 |
| bin-log.000024 | 165 |
| bin-log.000025 | 165 |
| bin-log.000026 | 385 |
| bin-log.000027 | 143 |
| bin-log.000028 | 143 |
| bin-log.000029 | 120 |
| bin-log.000030 | 120 |
| bin-log.000031 | 120 |
| bin-log.000032 | 120 |
| bin-log.000033 | 120 |
| bin-log.000034 | 120 |
| bin-log.000035 | 143 |
| bin-log.000036 | 10758 |
+----------------+-----------+
27 rows in set (0.00 sec)
|
备注: 使用 purge binary logs to ‘log_name’ 命令将会清理除 log_name 以外之前的 binary logs, 命令执行后 binlog 目录里的 bin-log.index 文件会被更新。
三 参考
原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/239599.html
