Fork me on GitHub

MySQL中Binary Log二进制日志文件的基本操作命令小结

前言

MySQL Binary Log也就是常说的bin-log,是mysql执行改动产生的二进制日志文件,其主要作用有两个:

  • 数据恢复
  • 主从数据库。用于slave端执行增删改,保持与master同步。

准备工作(开启binary log功能)

  • 修改mysql的配置文件,添加一句log_bin = mysql_bin即可

二进制日志相关演示

启用二进制日志

当前环境

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.53 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.53-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | AMD64 |
| version_compile_os | Win32 |
+-------------------------+------------------------------+
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON | --该参数用于设定是否启用二进制日志
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+

以下为binary log相关参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+

当前mysql服务器数据文件的缺省位置

1
2
3
4
5
6
mysql> show variables like '%datadir%';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| datadir | D:\phpStudy\MySQL\data\ |
+---------------+-------------------------+

使用命令行工具mysqlbinlog直接提取二进制日志的内容

查看产生的binary log

1
2
3
4
5
6
7
8
9
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 147 |
| mysql_bin.000002 | 147 |
| mysql_bin.000003 | 147 |
| mysql_bin.000004 | 498 |
+------------------+-----------+

利用bin_log恢复数据

1
2
#最常用的就是恢复指定数据端的数据了,可以直接恢复到数据库中
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456
1
2
#亦可导出为sql文件,再导入至数据库中:
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 > d:\000001.sql
1
2
3
#指定开始\结束位置,从上面的查看产生的binary log我们可以知道某个log的开始到结束的位置,我们可以在恢复的过程中指定回复从A位置到B位置的log.需要用下面两个参数来指定:
--start-positon="50" //指定从50位置开始
--stop-postion="100"//指定到100位置结束

bin_log的操作

查看最后一个bin日志文件是那个,现在位置

1
2
3
4
5
6
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000025 | 686 | | |
+------------------+----------+--------------+------------------+

启用新的日志文件,一般备份完数据库后执行

1
2
3
4
5
6
7
8
9
mysql> flush logs;
Query OK, 0 rows affected
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000026 | 107 | | |
+------------------+----------+--------------+------------------+

清空现有的所用bin-log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000026 | 107 | | |
+------------------+----------+--------------+------------------+
mysql> reset master;
Query OK, 0 rows affected
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+

注意事项

MySQL mysqlbinlog 查看binlog时报错unknown variable ‘default-character-set=utf8’解决

1.问题描述

1
mysqlbinlog: unknown variable 'default-character-set=utf8'

2.原因

1
mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令

3.解决方法

1.修改配置文件 —永久生效
1
2
3
4
5
6
windows环境下的my.ini或linux环境下的/etc/my.cnf
[client]
#设置MySQL客户端的字符集
default-character-set=utf8
把client下的default-character-set=utf8修改成character-set-server = utf8
然后重启生效
2.无需重启环境
1
2
使用--no-defaults
mysqlbinlog --no-defaults mysql-bin.000001
-------------本文结束感谢您的阅读-------------
Title - Artist
0:00