如何查询MySQL日志

您好，很高兴为您解答。

开启MySQL日志：

找到my.ini文件

打开 log-bin="D:\log\mysql_bin"

就会生成形如：

mysql_bin.000001

mysql_bin.index

这样的文件。

查看MySQL日志：

D:\mysql\bin>mysqlbinlog mysql_bin.000001

导出MySQL日志到文本文件：

D:\mysql\bin>mysqlbinlog D:\mysql\data\mysql_bin.000002 >D:\b.txt

更新MySQL日志：

mysqladmin -u root -p password flush-logs

就会生成新的日志文件，序号是连接起来的，比如上一个日志文件名为：

mysql_bin.000001

执行命令后就会生成新的日志文件：

mysql_bin.000002

如若满意，请点击右侧【采纳答案】，如若还有问题，请点击【追问】

希望我的回答对您有所帮助，望采纳！

1.首先确认你日志是否启用了

MySQL>show variables like 'log_bin'

2.如果启用了，即ON那日志文件就在MySQL的安装目录的data目录下

3.怎样知道当前的日志

MySQL>show master status

4.看二进制日志文件用MySQLbinlog

shell>MySQLbinlog mail-bin.000001

或者

shell>MySQLbinlog mail-bin.000001 | tail 因为mail-bin.000001是二进制的日志，所以想看日志就需要用mysqlbinlog命令，将二进制文件转换为日志文件，下面详细说说如何使用：上面已经说过如果启用了日志文件，那么默认的日志文件就在data目录下（如果你没有更改的话）进入存放日志文件目录，使用mysqlbinlog localhost-bin.000202 >new_file_name.log

命令，将目标文件保存为日志文件，可指定保存路径下

有个小技巧跟大家介绍下，我在准备转换的时候发现日志文件有2G多，寻思着为什么mysql为什么不把日志按日志定期的分多个文件放呢。结果发现mysql有个更好的方法，可以通过时间参数获取某个时间段的数据，例子如下：mysqlbinlog--start-datetime="2010-11-20 00:00:00" --stop-datetime="2010-11-21 00:00:00"

[hx@localhost data]$ mysqlbinlog

mysqlbinlog Ver 3.0 for pc-linux-gnu at i686

By Monty and Sasha, for your professional use

This software comes with NO WARRANTY: This is free software,

and you are welcome to modify and redistribute it under the GPL licenseDumps a MySQL binary log in a format usable for viewing or for piping to

the mysql command line clientUsage: mysqlbinlog [options] log-files

-d, --database=name List entries for just this database (local log only).

-D, --disable-log-bin

Disable binary log. This is useful, if you enabled

--to-last-log and are sending the output to the same

MySQL server. This way you could avoid an endless loop.

You would also like to use it when restoring after a

crash to avoid duplication of the statements you already

have. NOTE: you will need a SUPER privilege to use this

option.

-f, --force-readForce reading unknown binlog events.

-?, --help Display this help and exit.

-h, --host=name Get the binlog from server.

-o, --offset=# Skip the first N entries.

-p, --password[=name]

Password to connect to remote server.

-P, --port=#Use port to connect to the remote server.

-j, --position=#Deprecated. Use --start-position instead.

--protocol=name The protocol of connection (tcp,socket,pipe,memory).

-r, --result-file=name

Direct output to a given file.

-R, --read-from-remote-server

Read binary logs from a MySQL server

--open_files_limit=#

Used to reserve file descriptors for usage by this

program

-s, --short-formJust show the queries, no extra info.

-S, --socket=name Socket file to use for connection.

--start-datetime=name

Start reading the binlog at first event having a datetime

equal or posterior to the argumentthe argument must be

a date and time in the local time zone, in any format

accepted by the MySQL server for DATETIME and TIMESTAMP

types, for example: 2004-12-25 11:25:56 (you should

probably use quotes for your shell to set it properly).

--stop-datetime=name

Stop reading the binlog at first event having a datetime

equal or posterior to the argumentthe argument must be

a date and time in the local time zone, in any format

accepted by the MySQL server for DATETIME and TIMESTAMP

types, for example: 2004-12-25 11:25:56 (you should

probably use quotes for your shell to set it properly).

--start-position=# Start reading the binlog at position N. Applies to the

first binlog passed on the command line.

--stop-position=# Stop reading the binlog at position N. Applies to the

last binlog passed on the command line.

-t, --to-last-log Requires -R. Will not stop at the end of the requested

binlog but rather continue printing until the end of the

last binlog of the MySQL server. If you send the output

to the same MySQL server, that may lead to an endless

loop.

-u, --user=name Connect to the remote server as username.

-l, --local-load=name

Prepare local temporary files for LOAD DATA INFILE in the

specified directory.

-V, --version Print version and exit.Variables (--variable-name=value)

and boolean options {FALSE|TRUE} Value (after reading options)

--------------------------------- -----------------------------

database (No default value)

disable-log-bin FALSE

force-readFALSE

host (No default value)

offset0

port 3306

position 4

read-from-remote-server FALSE

open_files_limit 64

short-formFALSE

socket(No default value)

start-datetime(No default value)

stop-datetime (No default value)

start-position4

stop-position 18446744073709551615

to-last-log FALSE

user (No default value)

local-load(No default value)2009.09.30 检查一个应用的问题的时候，发现通过 oracle 的 dblink 连接 mysql 进行更新等 *** 作的时候，mysql 不会把 *** 作的 sql 语句记录到日志文件里

有时候我们会不小心对一个大表进行了 update，比如说写错了 where 条件......

此时，如果 kill 掉 update 线程，那回滚 undo log 需要不少时间。如果放置不管，也不知道 update 会持续多久。

那我们能知道 update 的进度么？

实验

我们先创建一个测试数据库：

快速创建一些数据：

连续执行同样的 SQL 数次，就可以快速构造千万级别的数据：

查看一下总的行数：

我们来释放一个大的 update：

然后另起一个 session，观察 performance_schema 中的信息：

可以看到，performance_schema 会列出当前 SQL 从引擎获取的行数。

等 SQL 结束后，我们看一下 update 从引擎总共获取了多少行：

可以看到该 update 从引擎总共获取的行数是表大小的两倍，那我们可以估算：update 的进度 = (rows_examined) / (2 * 表行数)