每天进步一点点mysqlmysqlbinlog_MySQL

php中文网

发布时间：2016-06-01 12:59:48

1212人浏览过

来源于php中文网

原创

一、简介

mysqlbinlog：用于查看服务器生成的二进制日志的工具。

二、命令格式

mysqlbinlog 选项日志文件1 日志文件2

三、常用参数

-d--database=name：指定数据库名称，只列出指定数据库相关操作

-o--offset=#：忽略掉日志中前N行命令

-r--result-file=name：将输出的文本格式日志输出到指定文件

-s--short-form：显示简单格式忽略掉一些信息

--set-charset=char-name：在输出为文本格式时，在文件第一行加上set names char-name，这个选项在某些情况下装载数据时非常有用。

--start-datetime=date --stop-datetime=date指定日期间各内的所有日志

--start-position=# --stop-position=#：指定位置时间间隔内的所有日志

四、常用例子

1. 查看当前正在使用的二进制日志文件

mysql>show master status;

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

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

| mysql.000007 | 120 | | | |

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

1 row in set (0.00 sec)

2. 不加任何参数，显示所有日志

[root@localhostdata]# mysqlbinlog mysql.000007

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET@@session.max_insert_delayed_threads=0*/;

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

DELIMITER /*!*/;

# at 4

#150717 11:01:43 server id 1 end_log_pos 120 CRC32 0xc3bad8fd Start: binlog v 4, server v 5.6.12-logcreated 150717 11:01:43

# Warning: this binlog is either in use orwas not closed properly.

BINLOG '

F3CoVQ8BAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf3Y

usM=

'/*!*/;

# at 120

#150717 11:05:54 server id 1 end_log_pos 219 CRC32 0x79fabcbc Query thread_id=24910 exec_time=0 error_code=0

use`test2`/*!*/;

SET TIMESTAMP=1437102354/*!*/;

SET @@session.pseudo_thread_id=24910/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET@@session.collation_database=DEFAULT/*!*/;

createtable t6(id int)

/*!*/;

# at 219

#150717 11:06:09 server id 1 end_log_pos 300 CRC32 0xd883c511 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102369/*!*/;

BEGIN

/*!*/;

# at 300

#150717 11:06:09 server id 1 end_log_pos 399 CRC32 0x4845ae22 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102369/*!*/;

insertinto t6 value(1)

/*!*/;

# at 399

#150717 11:06:09 server id 1 end_log_pos 430 CRC32 0x254bd95b Xid = 75156

COMMIT/*!*/;

# at 430

#150717 11:06:12 server id 1 end_log_pos 511 CRC32 0x400701db Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102372/*!*/;

BEGIN

/*!*/;

# at 511

#150717 11:06:12 server id 1 end_log_pos 610 CRC32 0x4d494d54 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102372/*!*/;

insertinto t6 value(2)

/*!*/;

# at 610

#150717 11:06:12 server id 1 end_log_pos 641 CRC32 0xc0160921 Xid = 75157

COMMIT/*!*/;

# at 641

#150717 11:06:14 server id 1 end_log_pos 722 CRC32 0x722e009d Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102374/*!*/;

BEGIN

/*!*/;

# at 722

#150717 11:06:14 server id 1 end_log_pos 821 CRC32 0x46f08ed1 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102374/*!*/;

insertinto t6 value(3)

/*!*/;

# at 821

某个性淘客站整站打包

1、演示：以截图为准 2、安装说明: 1,运行你的域名/install/index.php填写相关信息进行安装, 2,安装后运行你的域名/dede进入后台--系统--数据备份/还原---点击右上数据还原---开始还原数据 3, 系统--系统基本设置--站点设置--域名改为你的域名(否则显示不正常) 这一步很重要更新主页HTML 更新栏目HTML 更新文档HT

下载

#150717 11:06:14 server id 1 end_log_pos 852 CRC32 0x6eccabd7 Xid = 75158

COMMIT/*!*/;

# at 852

#150717 11:06:36 server id 1 end_log_pos 933 CRC32 0x1d97f072 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102396/*!*/;

BEGIN

/*!*/;

# at 933

#150717 11:06:36 server id 1 end_log_pos 1034 CRC32 0xca5d7197 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102396/*!*/;

deletefrom t6 where id=3

/*!*/;

# at 1034

#150717 11:06:36 server id 1 end_log_pos 1065 CRC32 0xc36e2552 Xid = 75159

COMMIT/*!*/;

# at 1065

#150717 11:21:20 server id 1 end_log_pos 1166 CRC32 0x937167ae Query thread_id=24910 exec_time=0 error_code=0

use `jiaowu`/*!*/;

SET TIMESTAMP=1437103280/*!*/;

createtable t7(id int)

/*!*/;

# at 1166

#150717 11:22:03 server id 1 end_log_pos 1261 CRC32 0xb31be6d1 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437103323/*!*/;

truncatetable t7

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

1. 做一些操作

mysql>create table t6(id int);

Query OK, 0 rows affected (0.02 sec)

mysql>insert into t6 value(1);

Query OK, 1 row affected (0.00 sec)

mysql>insert into t6 value(2);

Query OK, 1 row affected (0.01 sec)

mysql>insert into t6 value(3);

Query OK, 1 row affected (0.01 sec)

mysql>delete from t6 where id=3;

Query OK, 1 row affected (0.00 sec)

2. 只看jiaowu库中的信息

[root@localhostdata]# mysqlbinlog mysql.000007 -d jiaowu

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET@@session.max_insert_delayed_threads=0*/;

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

DELIMITER /*!*/;

# at 4

#150717 11:01:43 server id 1 end_log_pos 120 CRC32 0xc3bad8fd Start: binlog v 4, server v 5.6.12-logcreated 150717 11:01:43

# Warning: this binlog is either in use orwas not closed properly.

BINLOG '

F3CoVQ8BAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf3Y

usM=

'/*!*/;

# at 120

# at 219

#150717 11:06:09 server id 1 end_log_pos 300 CRC32 0xd883c511 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102369/*!*/;

SET @@session.pseudo_thread_id=24910/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET@@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 300

# at 399

#150717 11:06:09 server id 1 end_log_pos 430 CRC32 0x254bd95b Xid = 75156

COMMIT/*!*/;

# at 430

#150717 11:06:12 server id 1 end_log_pos 511 CRC32 0x400701db Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102372/*!*/;

BEGIN

/*!*/;

# at 511

# at 610

#150717 11:06:12 server id 1 end_log_pos 641 CRC32 0xc0160921 Xid = 75157

COMMIT/*!*/;

# at 641

#150717 11:06:14 server id 1 end_log_pos 722 CRC32 0x722e009d Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102374/*!*/;

BEGIN

/*!*/;

# at 722

# at 821

#150717 11:06:14 server id 1 end_log_pos 852 CRC32 0x6eccabd7 Xid = 75158

COMMIT/*!*/;

# at 852

#150717 11:06:36 server id 1 end_log_pos 933 CRC32 0x1d97f072 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102396/*!*/;

BEGIN

/*!*/;

# at 933

# at 1034

#150717 11:06:36 server id 1 end_log_pos 1065 CRC32 0xc36e2552 Xid = 75159

COMMIT/*!*/;

# at 1065

#150717 11:21:20 server id 1 end_log_pos 1166 CRC32 0x937167ae Query thread_id=24910 exec_time=0 error_code=0

use `jiaowu`/*!*/;

SET TIMESTAMP=1437103280/*!*/;

createtable t7(id int)

/*!*/;

# at 1166

#150717 11:22:03 server id 1 end_log_pos 1261 CRC32 0xb31be6d1 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437103323/*!*/;

truncatetable t7

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

3. 忽略前三个操作

[root@localhostdata]# mysqlbinlog mysql.000007 -o 3

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

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

DELIMITER /*!*/;

# at 4

#150717 11:01:43 server id 1 end_log_pos 120 CRC32 0xc3bad8fd Start: binlog v 4, server v 5.6.12-logcreated 150717 11:01:43

# Warning: this binlog is either in use orwas not closed properly.

BINLOG '

F3CoVQ8BAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf3Y

usM=

'/*!*/;

# at 300

#150717 11:06:09 server id 1 end_log_pos 399 CRC32 0x4845ae22 Query thread_id=24910 exec_time=0 error_code=0

use`test2`/*!*/;

SET TIMESTAMP=1437102369/*!*/;

SET @@session.pseudo_thread_id=24910/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET@@session.collation_database=DEFAULT/*!*/;

insertinto t6 value(1)

/*!*/;

# at 399

#150717 11:06:09 server id 1 end_log_pos 430 CRC32 0x254bd95b Xid = 75156

COMMIT/*!*/;

# at 430

#150717 11:06:12 server id 1 end_log_pos 511 CRC32 0x400701db Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102372/*!*/;

BEGIN

/*!*/;

# at 511

#150717 11:06:12 server id 1 end_log_pos 610 CRC32 0x4d494d54 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102372/*!*/;

insertinto t6 value(2)

/*!*/;

# at 610

#150717 11:06:12 server id 1 end_log_pos 641 CRC32 0xc0160921 Xid = 75157

COMMIT/*!*/;

# at 641

#150717 11:06:14 server id 1 end_log_pos 722 CRC32 0x722e009d Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102374/*!*/;

BEGIN

/*!*/;

# at 722

#150717 11:06:14 server id 1 end_log_pos 821 CRC32 0x46f08ed1 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102374/*!*/;

insertinto t6 value(3)

/*!*/;

# at 821

#150717 11:06:14 server id 1 end_log_pos 852 CRC32 0x6eccabd7 Xid = 75158

COMMIT/*!*/;

# at 852

#150717 11:06:36 server id 1 end_log_pos 933 CRC32 0x1d97f072 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102396/*!*/;

BEGIN

/*!*/;

# at 933

#150717 11:06:36 server id 1 end_log_pos 1034 CRC32 0xca5d7197 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102396/*!*/;

deletefrom t6 where id=3

/*!*/;

# at 1034

#150717 11:06:36 server id 1 end_log_pos 1065 CRC32 0xc36e2552 Xid = 75159

COMMIT/*!*/;

# at 1065

#150717 11:21:20 server id 1 end_log_pos 1166 CRC32 0x937167ae Query thread_id=24910 exec_time=0 error_code=0

use `jiaowu`/*!*/;

SET TIMESTAMP=1437103280/*!*/;

createtable t7(id int)

/*!*/;

# at 1166

#150717 11:22:03 server id 1 end_log_pos 1261 CRC32 0xb31be6d1 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437103323/*!*/;

truncatetable t7

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

4. 将输出结果导出到文件

[root@localhostdata]# mysqlbinlog mysql.000007 -o 3 -r /root/test.sql

5. 将指定时间中的内容简单显示

[root@localhostdata]# mysqlbinlog mysql.000007 --start-datetime='2015/07/17 11:06:14'--stop-datetime='2015/07/17 11:12:20'

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET@@session.max_insert_delayed_threads=0*/;

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

DELIMITER /*!*/;

# at 4

#150717 11:01:43 server id 1 end_log_pos 120 CRC32 0xc3bad8fd Start: binlog v 4, server v 5.6.12-logcreated 150717 11:01:43

# Warning: this binlog is either in use orwas not closed properly.

BINLOG '

F3CoVQ8BAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf3Y

usM=

'/*!*/;

# at 641

#150717 11:06:14 server id 1 end_log_pos 722CRC32 0x722e009d Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102374/*!*/;

SET @@session.pseudo_thread_id=24910/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET@@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 722

#150717 11:06:14 server id 1 end_log_pos 821 CRC32 0x46f08ed1 Query thread_id=24910 exec_time=0 error_code=0

use `test2`/*!*/;

SET TIMESTAMP=1437102374/*!*/;

insert into t6 value(3)

/*!*/;

# at 821

#150717 11:06:14 server id 1 end_log_pos 852 CRC32 0x6eccabd7 Xid = 75158

COMMIT/*!*/;

# at 852

#150717 11:06:36 server id 1 end_log_pos 933 CRC32 0x1d97f072 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102396/*!*/;

BEGIN

/*!*/;

# at 933

#150717 11:06:36 server id 1 end_log_pos 1034 CRC32 0xca5d7197 Query thread_id=24910 exec_time=0 error_code=0

SET TIMESTAMP=1437102396/*!*/;

delete from t6 where id=3

/*!*/;

# at 1034

#150717 11:06:36 server id 1 end_log_pos 1065CRC32 0xc36e2552 Xid = 75159

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;=0*/;

注意：--start-datetime包含所选择时间，--stop-datatime不包含所选时间，如果只写前者则表明到日志最后结束，如果只写后者则表明从日志最开始时间开始

本站声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：Mysql导出逗号分隔的csv文件_MySQL 下一篇：MySQL运维之--日常维护操作_MySQL

热门AI工具

DeepSeek

幻方量化公司旗下的开源大模型平台

AI 编程开发 AI 聊天问答

豆包大模型

字节跳动自主研发的一系列大型语言模型

AI 编程开发 AI大模型

通义千问

阿里巴巴推出的全能AI助手

AI 编程开发 Agent智能体

腾讯元宝

腾讯混元平台推出的AI助手

文档处理 AI 聊天问答

文心一言

文心一言是百度开发的AI聊天机器人，通过对话可以生成各种形式的内容。

AI 编程开发 AI 文本写作

讯飞写作

基于讯飞星火大模型的AI写作工具，可以快速生成新闻稿件、品宣文案、工作总结、心得体会等各种文文稿

AI 文本写作中文写作

即梦AI

一站式AI创作平台，免费AI图片和视频生成。

图片拼接

ChatGPT

最最强大的AI聊天机器人程序，ChatGPT不单是聊天机器人，还能进行撰写邮件、视频脚本、文案、翻译、代码等任务。

AI 编程开发 AI 文本写作

智谱清言 - 免费全能的AI助手

AI 编程开发 Agent智能体