(1)初识mysql binlog

mysqlbinlog 命令--> 解析mysqlbinlog日志的mysql官网自带工具

binlog日志的作用是什么？

用来记录mysql内部 增删改等对mysql数据库有更新的内容(查是不会有记录的)

如何开启log_bin

[mysqld]

log-bin=mysql-bin(名字可以随便起)

验证是否开启:

<code>mysql&gt; show variables </code><code>like</code> <code>'%log_bin%'</code><code>;</code>

+---------------------------------+-------+

| Variable_name                   | Value |

| log_bin                         | ON    

<code>/etc/init</code><code>.d</code><code>/mysqld</code> <code>restart</code>

重启mysql后(为了刷新滚动log日志文件)执行如下mysql语句

<code>create</code> <code>database</code> <code>oldboy;</code>

<code>use oldboy;</code>

<code>create</code> <code>table</code> <code>student(id </code><code>int</code><code>(4) auto_increment </code><code>primary</code> <code>key</code><code>, </code><code>name</code> <code>varchar</code><code>(16) </code><code>not</code> <code>null</code><code>);</code>

<code>insert</code> <code>into</code> <code>student (</code><code>name</code><code>) </code><code>values</code> <code>(</code><code>'andy'</code><code>);</code>

<code>insert</code> <code>into</code> <code>student (</code><code>name</code><code>) </code><code>values</code> <code>(</code><code>'jack'</code><code>);</code>

<code>select</code> <code>* </code><code>from</code> <code>student;</code>

<code>\q</code>

对应的binlog文件内容:

<code>[root@master mysql]</code><code># mysqlbinlog mysql-bin.000046</code>

/*!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

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/*!*/;

# 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

insert into student (name) values ('jack')

# at 781

#160411 21:04:57 server id 1  end_log_pos 808   Xid = 14

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

可能遇到的报错:

<code>[root@master mysql]</code><code># mysqlbinlog mysql-bin.000045</code>

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

产生这个问题的原因是因为我在my.cnf中的client选项组中添加了：default-character-set=utf8

这个是mysqlbinlog的一个bug

解决方法:

使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf，而不是服务器已经加载进内存的配置文件。

暂时注释掉即可

本文转自残剑博客51CTO博客，原文链接http://blog.51cto.com/cuidehua/1762761如需转载请自行联系原作者

cuizhiliang