mysql中日志文件_MySQL的日志文件

错误日志

–log-error=file_name来保存错误日志文件的路径

mysql> show variables like ‘%log%error%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| log_error | |

+—————+——-+

1 row in set (0.00 sec)

这里我没有设置log_error，这样错误日志文件会存放到DATADIR指定的目录中，文件名是hostname.err

# ls

ib_logfile0 ib_logfile1 ibdata1 luoxuan mysql primary0.err test

# more primary0.err

090708 11:06:43 mysqld started

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

090708 11:06:43 InnoDB: Setting file ./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait…

090708 11:06:44 InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait…

090708 11:06:44 InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

二进制日志

记录所有的DDL及DML，不包括查询语句。以–log-bin启动

# ./mysqld_safe –defaults-file=/etc/my.cnf –user=mysql –log-bin=logbin &

25188

# Starting mysqld daemon with databases from /opt/mysql/mysql/data

# mysql -uroot -pdev%db

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.83-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show variables like ‘%log%bin%’;

+———————————+——-+

| Variable_name | Value |

+———————————+——-+

| log_bin | ON |

| log_bin_trust_function_creators | OFF |

+———————————+——-+

2 rows in set (0.00 sec)

mysql> insert into t1 value(1);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 value(2);

Query OK, 1 row affected (0.00 sec)

# mysqlbinlog logbin.000001

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

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

DELIMITER /*!*/;

# at 4

#090721 9:38:50 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.83-log created 090721 9:38:50 at startup

# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

ROLLBACK/*!*/;

# at 98

#090721 9:40:15 server id 1 end_log_pos 199 Query thread_id=1 exec_time=0 error_code=0

use luoxuan/*!*/;

SET TIMESTAMP=1248140415/*!*/;

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

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

/*!\C latin1 *//*!*/;

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

DELETE FROM `luoxuan`.`test_memory`

/*!*/;

# at 199

#090721 9:40:15 server id 1 end_log_pos 303 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1248140415/*!*/;

DELETE FROM `luoxuan`.`test_memory_sp`

/*!*/;

# at 303

#090721 9:42:22 server id 1 end_log_pos 392 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1248140542/*!*/;

insert into t1 value(1) ————记录在日志中了

/*!*/;

# at 392

#090721 9:42:30 server id 1 end_log_pos 481 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1248140550/*!*/;

insert into t2 value(2) ————记录在日志中了

/*!*/;

查询日志

以–log启动

# ./mysqld_safe –defaults-file=/etc/my.cnf –user=mysql –log=querylog &

25260

# Starting mysqld daemon with databases from /opt/mysql/mysql/data

mysql> show tables;

+—————————+

| Tables_in_mysql |

+—————————+

| columns_priv |

| db |

| func |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| proc |

| procs_priv |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+—————————+

17 rows in set (0.01 sec)

mysql> select count(*) from time_zone;

+———-+

| count(*) |

+———-+

| 0 |

+———-+

1 row in set (0.00 sec)

查询都会记录下来

# more querylog

/opt/mysql/mysql/bin/mysqld, Version: 5.0.83-log (MySQL Community Server (GPL)). started with:

Tcp port: 0 Unix socket: (null)

Time Id Command Argument

090721 9:48:50 1 Connect root@localhost on

1 Query select @@version_comment limit 1

090721 9:48:56 1 Query show databases

090721 9:48:59 1 Query SELECT DATABASE()

1 Init DB mysql

1 Query show databases

1 Query show tables

1 Field List columns_priv

1 Field List db

1 Field List func

1 Field List help_category

1 Field List help_keyword

1 Field List help_relation

1 Field List help_topic

1 Field List host

1 Field List proc

1 Field List procs_priv

1 Field List tables_priv

1 Field List time_zone

1 Field List time_zone_leap_second

1 Field List time_zone_name

1 Field List time_zone_transition

1 Field List time_zone_transition_type

1 Field List user

090721 9:49:02 1 Query show tables

090721 9:49:14 1 Query select count(*) from time_zone

以–log-slow-queries启动 慢查询日志

# mysqld_safe –defaults-file=/etc/my.cnf –user=mysql –log-slow-queries=sqlog &

25316

# Starting mysqld daemon with databases from /opt/mysql/mysql/data

默认是10秒

mysql> show variables like ‘long%’;

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| long_query_time | 10 |

+—————–+——-+

1 row in set (0.00 sec)

为了方便测试，设置为1秒

mysql> set long_query_time=1;

Query OK, 0 rows affected (0.00 sec)

超过1秒的查询，都会被记录下来

# more sqlog

/opt/mysql/mysql/bin/mysqld, Version: 5.0.83-log (MySQL Community Server (GPL)). started with:

Tcp port: 0 Unix socket: (null)

Time Id Command Argument

# Time: 090721 10:03:46

# User@Host: root[root] @ localhost []

# Query_time: 3 Lock_time: 0 Rows_sent: 2000009 Rows_examined: 2000009

use luoxuan;

select * from test_myisam;

觉得文章有用？立即：

和朋友一起 共学习 共进步！

猜您喜欢