mysqlbinlog 命令--> 解析mysqlbinlog日志的mysql官网自带工具
binlog日志的作用是什么？
用来记录mysql内部 增删改等对mysql数据库有更新的内容(查是不会有记录的)
如何开启log_bin
[mysqld]
log-bin=mysql-bin(名字可以随便起)
验证是否开启:
|
1
|
mysql> show variables
like
'%log_bin%'
;
|
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON
|
1
|
/etc/init
.d
/mysqld
restart
|
重启mysql后(为了刷新滚动log日志文件)执行如下mysql语句
|
1
2
3
4
5
6
7
|
create
database
oldboy;
use oldboy;
create
table
student(id
int
(4) auto_increment
primary
key
,
name
varchar
(16)
not
null
);
insert
into
student (
name
)
values
(
'andy'
);
insert
into
student (
name
)
values
(
'jack'
);
select
*
from
student;
\q
|
对应的binlog文件内容:
|
1
|
[root@master mysql]
# mysqlbinlog mysql-bin.000046
|
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160411 21:00:35 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 160411 21:00:35 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
858LVw8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADznwtXEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#160411 21:01:16 server id 1 end_log_pos 194 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1460379676/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database oldboy
/*!*/;
# at 194
#160411 21:03:34 server id 1 end_log_pos 344 Query thread_id=2 exec_time=0 error_code=0
use oldboy/*!*/;
SET TIMESTAMP=1460379814/*!*/;
create table student(id int(4) auto_increment primary key, name varchar(16) not null)
/*!*/;
# at 344
#160411 21:04:53 server id 1 end_log_pos 414 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1460379893/*!*/;
BEGIN
/*!*/;
# at 414
#160411 21:04:53 server id 1 end_log_pos 442 Intvar
SET INSERT_ID=1/*!*/;
# at 442
#160411 21:04:53 server id 1 end_log_pos 549 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1460379893/*!*/;
insert into student (name) values ('andy')
/*!*/;
# at 549
#160411 21:04:53 server id 1 end_log_pos 576 Xid = 13
COMMIT/*!*/;
# at 576
#160411 21:04:57 server id 1 end_log_pos 646 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1460379897/*!*/;
BEGIN
/*!*/;
# at 646
#160411 21:04:57 server id 1 end_log_pos 674 Intvar
SET INSERT_ID=2/*!*/;
# at 674
#160411 21:04:57 server id 1 end_log_pos 781 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1460379897/*!*/;
insert into student (name) values ('jack')
/*!*/;
# at 781
#160411 21:04:57 server id 1 end_log_pos 808 Xid = 14
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
可能遇到的报错:
|
1
|
[root@master mysql]
# mysqlbinlog mysql-bin.000045
|
mysqlbinlog: unknown variable 'default-character-set=utf8'
产生这个问题的原因是因为我在my.cnf中的client选项组中添加了：default-character-set=utf8
这个是mysqlbinlog的一个bug
解决方法:
使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf，而不是服务器已经加载进内存的配置文件。
暂时注释掉即可
