每天进步一点点mysqlmysqlbinlog_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

每天进步一点点mysqlmysqlbinlog_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 12:59 PM

一、简介

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

#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不包含所选时间，如果只写前者则表明到日志最后结束，如果只写后者则表明从日志最开始时间开始

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How to solve the problem of mysql cannot open shared libraryMar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

Reduce the use of MySQL memory in DockerMar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin)Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overviewMar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

Running multiple MySQL versions on MacOS: A step-by-step guideMar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates

1 months agoBy尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: How To Get Giant Seeds

1 months agoBy尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

4 weeks agoByDDD

R.E.P.O. Save File Location: Where Is It & How to Protect It?

4 weeks agoByDDD

Hot Tools

Dreamweaver Mac version

Visual web development tools

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.