Web server mysql log analysis _ MySQL

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.