错误日志
–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/datamysql> 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;