MySQL operation log

In any database, there are a variety of logs. MySQL is no exception, there are 4 different logs, error logs, binary logs, query logs, and slow query logs in MySQL. These logs record the different aspects of the MySQL database. These 4 different log roles and uses are described below.

I. Error log

The error log is important in the MySQL database, which records information about mysqld start and stop, and any errors that occur during the operation of the server.

1. Configuration information

--log-error=[file-name] is used to specify where the error log is stored.

If [File-name] is not specified, the default hostname.err is the file name, which is stored by default in the DataDir directory.

You can also configure Log-error to the My.cnf file, which saves you from manually specifying--log-error each time you start mysqld. For example:

[[Email protected]]$ vi/etc/my.cnf

# The MySQL server

[Mysqld]

....

Log-error =/var/lib/mysql/test2_mysqld.err

.....

2. Error message Template

080313 05:21:55 Mysqld started

080313 5:21:55 innodb:started; Log sequence number 0 43655

080313 5:21:55 [Note]/usr/local/mysql/bin/mysqld:ready for connections.

Version: ' 5.0.26-standard-log ' socket: '/var/lib/mysql/mysql.sock ' port:3306 mysql Community edition-standard (GPL)

080313 5:24:13 [Note]/usr/local/mysql/bin/mysqld:normal shutdown

080313 5:24:13 innodb:starting shutdown ...

080313 5:24:16 Innodb:shutdown completed; Log sequence number 0 43655

080313 5:24:16 [Note]/usr/local/mysql/bin/mysqld:shutdown complete

080313 05:24:16 Mysqld Ended

080313 05:24:47 Mysqld started

080313 5:24:47 innodb:started; Log sequence number 0 43655

080313 5:24:47 [Note]/usr/local/mysql/bin/mysqld:ready for connections.

Version: ' 5.0.26-standard-log ' socket: '/var/lib/mysql/mysql.sock ' port:3306 mysql Community edition-standard (GPL)

080313 5:33:49 [Note]/usr/local/mysql/bin/mysqld:normal shutdown

Three. Query log

The query log records all of the clinet statements.

Note: Because the log log records all operations on the database, this log can have a performance impact on frequently accessed systems and is recommended to be closed.

1. Configuration information

--log=[file-name] is used to specify where the error log is stored.

If [File-name] is not specified, the default host name (hostname) is the file name, which is stored by default in the DataDir directory.

You can also configure the log to be in the My.cnf file, which saves you from manually specifying--log each time you start mysqld. For example:

# The MySQL server

[Mysqld]

......

#query-log

Log =/var/lib/mysql/query_log.log

......

2. Read the query log

The query log is plain text and can be opened directly using the OS text Reading tool. For example:

[Email protected]]$ tail-n Query_log.log

080313 7:58:28-Query Show tables

080313 8:07:45-Quit

080313 10:01:48 Connect [email protected] on

080313 10:02:38 Query SELECT DATABASE ()

Init DB Test

080313 10:02:42 Query Show tables

080313 10:03:07 Query select * FROM pet

080313 10:06:26 Query INSERT INTO pet values (' Hunter ', ' yxyup ', ' cat ', ' f ', ' 1996-04-29 ', null)

080313 10:06:39 Query select * FROM pet

080313 10:07:13 Query Update pet set sex= ' m ' where name= ' hunter '

080313 10:07:38 Query Delete from pet where name= ' hunter '

080313 10:13:48 Query desc test8

080313 10:14:13 Query CREATE TABLE t1 (ID int,name char (10))

080313 10:14:41-Query ALTER TABLE t1 add sex char (2)

[[Email protected]]$

Four. Slow query log

The slow query log is the SQL statement log that records the value set by the execution time over the parameter long_query_time (in seconds).

Note: The slow query log is useful for finding SQL that has a problem with performance, and is recommended and often analyzed

1. Configuration information

--log-slow-queries=[file-name] is used to specify where the error log is stored.

If [File-name] is not specified, the default is hostname-slow.log as the file name, which is stored by default in the DataDir directory.

You can also configure Log-slow-queries to the My.cnf file, which saves you from manually specifying--log-slow-queries each time you start mysqld. For example:

# The MySQL server

[Mysqld]

......

#slow-query-log

Log-slow-queries =/var/lib/mysql/slow_query_log.log

......

2. Read the slow query log

[Email protected]]$ cat Slow_query_log.log

/usr/local/mysql/bin/mysqld, Version:5.0.26-standard-log. Started with:

TCP port:3306 Unix Socket:/var/lib/mysql/mysql.sock

Time Id Command Argument

# time:080313 5:41:46

# [email protected]: root[root] @ localhost []

# query_time:108 lock_time:0 rows_sent:0 rows_examined:8738

Use test;

Select COUNT (1) from T1 A, T1 b,t1 C where a.id=b.id and b.name=c.name;

# time:080313 5:52:04

# [email protected]: root[root] @ localhost []

# query_time:583 lock_time:0 rows_sent:0 rows_examined:508521177

Select COUNT (1) from T1 A, T1 b where a.id=b.id;

/usr/local/mysql/bin/mysqld, Version:5.0.26-standard-log. Started with:

TCP port:3306 Unix Socket:/var/lib/mysql/mysql.sock

Time Id Command Argument

# time:080313 10:39:59

# [email protected]: root[root] @ localhost []

# query_time:11 lock_time:0 rows_sent:4537467 rows_examined:4537467

Use test;

Select id from tail;

If you have a lot of slow query logging, you can use Mysqldumpslow for subtotals

[Email protected]]$ mysqldumpslow Slow_query_log.log

Reading MySQL slow query log from Slow_query_log.log

Count:1 time=583.00s (583s) lock=0.00s (0s) rows=0.0 (0), Root[root] @localhost

Select COUNT (N) from T1 A, T1 b where a.id=b.id

Count:1 time=108.00s (108s) lock=0.00s (0s) rows=0.0 (0), Root[root] @localhost

Select COUNT (N) from T1 A, T1 b,t1 C where a.id=b.id and B.name=c.name

Count:1 time=11.00s (11s) lock=0.00s (0s) rows=4537467.0 (4537467), Root[root] @localhost

Select id from tail;

MySQL has the following types of logs:
Error log:-log-err
Query log:-log
Slow query log:-log-slow-queries
Update log:-log-update
Binary log:-log-bin

In the MySQL installation directory, open My.ini, followed by the above parameters, save and restart the MySQL service on the line.
For example:
#Enter a name for the binary log. Otherwise a default name would be used.
#log-bin=
#Enter a name for the query log file. Otherwise a default name would be used.
#log =
#Enter a name for the error log file. Otherwise a default name would be used.
Log-error=
#Enter a name for the update log file. Otherwise a default name would be used.
#log-update=

View Date to:
1. First verify that your log is enabled
Mysql>show variables like ' log_bin ';
If enabled, on
The log file is in the data directory of the MySQL installation directory.
Cat/tail log file name

2. How to know the current log
Mysql> Show master status;
3. View logs from a certain period of time to a certain period of time

Mysqlbinlog--start-datetime= ' 2008-01-19 00:00:00 '--stop-datetime= ' 2008-01-30 00:00:00 '/var/log/mysql/ mysql-bin.000006 > Mysqllog1.log

Appendix:

Show all binary logs on this computer
mysql> SHOW MASTER LOGS;
Delete all binary logs on this computer
Mysql> RESET MASTER;
Delete all of the binary logs created before binary-log.xxx
Mysql> PURGE MASTER LOGS to ' binary-log.xxx ';
Keep only the last 6 days of the log, delete the previous
Find/var/intra-type f-mtime +6-name "*.log"-exec rm-f {};
Use the key in the upper-left corner of the keyboard (that is, ESC below) to enclose it, indicating the command. -1D was yesterday, and so -1m was last month and so on
Day= '/bin/date-v -1d +%y%m%d ';
Renaming a file
MV Xxx.log Xxx-${day}.log;
Here also add the database user name password, the role is to update the log (including binary logs and query logs, etc.)
Mysqladmin Flush-logs

Binary logs are also commonly referred to as Binlog, which are written in front of all DDL and DML, but do not include data query statements.

1. Configuration information

--log-bin=[file-name] is used to specify where the error log is stored.

If [File-name] is not specified, the default host name is followed by-bin as the file name, which is stored by default in the DataDir directory.

You can also configure Log-bin to the My.cnf file, which saves you from manually specifying--log-bin each time you start mysqld. For example:

# The MySQL server

[Mysqld]

......

Log-bin =/var/lib/mysql/log-bin

......

2. View Blnlog

Since binlog is accessed in binary mode and cannot be viewed directly, it needs to be viewed with the Mysqlbinlog tool provided by MySQL.

3. Delete Binlog

(1). Delete all logs with the Reset Master command, and new log numbering starts at 000001

(2). Use purge master logs to ' mysq-bin.****** ' command to delete all logs before the specified number

(3). Use purge master logs to before ' yyyy-mm-dd HH24:MI:SS ' command to delete all logs generated before ' Yyyy-mm-dd HH24:MI:SS '

(4). You can specify--expire_logs_days=# in MY.CNF, this parameter sets the number of days that the Binlog log expires

4. Test Case

[[Email protected]]$ mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or G.

Your MySQL Connection ID is the server version:5.0.26-standard-log

Type ' help ', ' or ' h ' for help. Type ' C ' to clear the buffer.

mysql> use test;

Database changed

Mysql> select * from pet;

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

| name | Owner | Species | sex | Birth | Death |

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

| Fluffy | Harold | Cat | f | 1993-02-04 | NULL |

| Claws | Gwen | Cat | m | 1994-03-17 | NULL |

| Buffy | Harold | Dog | f | 1989-05-13 | NULL |

| Fang | Benny | Dog | m | 1990-08-27 | NULL |

| Bowser | Diane | Dog | m | 1979-08-31 | 1995-07-29 |

| Chirpy | Gwen | Bird | f | 1998-09-11 | NULL |

| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |

| Slim | Benny | Snake | m | 1996-04-29 | NULL |

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

8 rows in Set (0.06 sec)

Mysql> INSERT into pet values (' Hunter ', ' yxyup ', ' cat ', ' f ', ' 1996-04-29 ', null);

Query OK, 1 row affected (0.03 sec)

Mysql> select * from pet;

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

| name | Owner | Species | sex | Birth | Death |

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

| Fluffy | Harold | Cat | f | 1993-02-04 | NULL |

| Claws | Gwen | Cat | m | 1994-03-17 | NULL |

| Buffy | Harold | Dog | f | 1989-05-13 | NULL |

| Fang | Benny | Dog | m | 1990-08-27 | NULL |

| Bowser | Diane | Dog | m | 1979-08-31 | 1995-07-29 |

| Chirpy | Gwen | Bird | f | 1998-09-11 | NULL |

| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |

| Slim | Benny | Snake | m | 1996-04-29 | NULL |

| Hunter | Yxyup | Cat | f | 1996-04-29 | NULL |

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

9 Rows in Set (0.00 sec)

Mysql> Update pet set sex= ' m ' where name= ' hunter ';

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

Mysql> Delete from pet where name= ' hunter ';

Query OK, 1 row Affected (0.00 sec)

[Email protected]]$ mysqlbinlog log-bin.000002

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

/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;

# at 4

#080313 7:52:47 Server ID 1 end_log_pos 98 start:binlog v 4, Server v 5.0.26-standard-log created 080313 7:52:47

# Warning:this Binlog is not closed properly. Most probably mysqld crashed writing it.

# at 98

#080313 10:06:26 Server ID 1 end_log_pos 229 Query thread_id=18 exec_time=0 error_code=0

Use test;

SET timestamp=1205373986;

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=8;

INSERT into pet values (' Hunter ', ' yxyup ', ' cat ', ' f ', ' 1996-04-29 ', null);

# at 229

#080313 10:07:13 Server ID 1 end_log_pos 334 Query thread_id=18 exec_time=0 error_code=0

SET timestamp=1205374033;

Update pet set sex= ' m ' where name= ' hunter ';

# at 334

#080313 10:07:38 Server ID 1 end_log_pos 432 Query thread_id=18 exec_time=0 error_code=0

SET timestamp=1205374058;

Delete from pet where name= ' hunter ';

# at 432

#080313 10:14:13 Server ID 1 end_log_pos 532 Query thread_id=18 exec_time=0 error_code=0

SET timestamp=1205374453;

CREATE TABLE T1 (ID int,name char (10));

# at 532

#080313 10:14:41 Server ID 1 end_log_pos 625 Query thread_id=18 exec_time=0 error_code=0

SET timestamp=1205374481;

ALTER TABLE t1 add sex char (2);

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

[[Email protected]]$

As you can see, three DML operations and two DDL are recorded in Binlog, and select is not logged

MySQL operation log