Web server mysql log analysis _ MySQL

Web server mysql log analysis bitsCN.com

How to check and analyze logs under Freebsd is still a new topic for me. I haven't processed it before, and I don't even know where the logs are stored or how to record them.

There are some good information on the network. mysql manuals can also be used for reference, but some inexplicable problems may occur even if you have never practiced them.

First, let's take a look at the types of mysql logs. Generally, there are five types of logs:

Error log:-log-err (records the information displayed when mysql is started, running, and stopped)

Query log:-log (records the established client connection and executed statements)

Slow query log:-log-slow-queries (records all queries whose execution exceeds long_query_time seconds)

Update log:-log-update (the statement used to record data changes. This log is not supported)

Binary log:-log-bin (records all statements for changing data, also used for copying and restoring the database)

To analyze the running status of mysql, we mainly analyze slow query logs and several simple commands to query the status of the current log records:

Mysql> show variables like 'log _ % '; (whether logs are enabled)

Mysql> show master status; (how to know the current log)

Mysql> show master logs; (displays the number of binary logs)

To enable the slow query log, you must enable it in my. cnf:

Long_query_time = 1 the SQL statement executed for more than 1 second will be logged

Log-slow-queries =/usr/local/db/log/slowquery. log records slow statements returned by the query.

Log-queries-not-using-indexes =/usr/local/db/log/nouseindex. log is a literal query that does not use an index in logs.

Log =/usr/local/db/log/mysql. log records all execution statements.

The binary log file is large. you can set the expiration time of the binary log file in my. cnf so that mysql will automatically delete the expired log file to save disk space:

Expire_logs_days = 5

Restart mysql and you will be able to see the log and slow-log records of mysql. I don't know why the log files I set for log-queries-not-using-indexes are not recorded;

You can directly input mysqldumpslow/usr/local/db/log/slowquery. log through shell to obtain the log summary information, including:

Number of occurrences (Count ),

Maximum execution Time (Time ),

Total Time consumed ),

Lock ),

Total number of Rows sent to the client (Rows ),

Total number of Rows scanned (Rows ),

The user and the SQL statement itself (abstract the format, for example, limit 1, 20 is represented by limit N, N ).

The rest of the work is handed over to the programmer to further optimize the database query statements and reduce the mysql load.

Log update

Mysql logs are recorded all the time. to disconnect the logs, perform the following steps to record them:

1. mv log/backup/log (if backup is required)

2. rm log (delete the log file and mysql stops logging)

3. mysqladmin-uroot-ppassword flush-logs (record started)

This article is from the "Sanzu Wu studio" blog

BitsCN.com