Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to the main log and slow query methods of Mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the main logs and slow query methods of Mysql can bring you some practical help.

There are mainly the following types of mysql logs:

L error log: log_error

L General query log: general_log

L binary log: binlog

L slow query log: log_slow_queries

L Relay log: relay_log

L transaction log: innodb_log

The following directory file directories vary from version to version.

1. Error log

[under Windows] modify the main configuration file my.ini

[mysqld]

Error-bin [= dir\ [filename]]

/ / dir: specify the storage path of the error log; filename: specify the file name of the error file

Delete the error log:

Mysqladmin-u root-p flush-logs

[under Linux]

Modify the main configuration file my.cnf

[mysqld]

Log-error=mysql_error.log (absolute path or direct file name will be stored in the data directory)

Log_warnings= {on | off | 2}: whether to log warning messages

Log_warnings indicates whether the warning message is logged in the error log, 1 and 0, that is, on and off, indicate whether or not to log, and 2 indicates failed and rejected connection information.

View the configuration of error log on mysql CVM:

Mysql > show global variables like'% log%'

2. General query log (very important! )

Mysql all query statements are logged.

This record is turned off by default, which is generally used for debugging and trial. Usually, a large amount of data will be recorded and take up disk space.

Storage location: file, table (table,mysql.general_log)

It is stored in the data directory by default.

Under the Linux environment, modify the main configuration file my.cnf.

[mysqld]

General_log= {on | off}

General_log_file=mysql_general.log (absolute path or direct file name will be stored in the data directory)

Log_output= {file | table | file,table | none}: log output type

Operate the generic query log without restarting the MYSQL service:

Mysql > set global general_log=on; / / on

Mysql > set global general_log=off; / / close

Mysql > mysqladmin-u root-p flush-logs / / Delete the log (delete the old one before creating a new one)

3. Binary log (very important! )

1 "is used to record statements that cause or potentially cause changes in data (statement) or results after changes (row), or a combination of the two.

"2" contains all the updated data or all statements that have potentially updated the data, recording the data changes and the events events and location position of the data changes.

3 "the main purpose is to restore the database as much as possible during recovery, which is turned on by default.

4 "in the Linux environment, modify the main configuration file my.cnf.

Log_bin=/path/to/bin_log_file:

This is a read-only variable that indicates the location of the directory where the logs are stored. You cannot write on or off here. If you do not specify a path, it will be stored in the data directory.

Max_binlog_size=1073741824:

Sets the maximum size of a single binary file, in bytes, beyond which it scrolls automatically.

Sync_binlog= {1 | 0 | N}:

Indicates whether the binary log in memory is synchronized to memory (binlog_cache) immediately after each transaction is committed.

1 means immediate submission; 0 means no submission; N can be any value, representing every N times; different values correspond to different performance, and the performance difference between 0 and 1 can be as high as 5 times. The fdatasync () function is used to write to disk.

Binlog_format= {statement | row | mixed}:

The format of the binlog log

Expire_logs_days=N:

Valid days of binary log

5 "you can use the mysqlbinlog command to view the binary log file.

Mysqlbinlog:

Yyyy-mm-dd hh:mm:ss

-- start-datetime=

-- stop-datetime=

-J,-- start-position=#

-- stop-position=#

-user,-host, password

8 "View binaries in mysql

View a list of binary log files:

Mysql > show master | binary logs

View the binary log files that are currently in use:

Mysql > show master status

View the events in the binary log file:

Mysql > show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count]

View the parameter configuration of the binary log

MariaDB [(none)] > show global variables where variable_name like'% log_bin%' or variable_name like'% binlog%'

9 "clear binary log

Clear all logs (there is no master-slave replication relationship)

Mysql > reset master

Clear all logs before the specified log

Mysql > purge master logs to 'log'

Clear all logs before a certain point in time

Mysql > purge master logs before 'year-month-day hours: minutes: seconds'

Clear all logs from n days ago

Mysql > purge master logs before current_date-interval 10 day

Because of the importance of binary logs, please only determine that the binaries to be deleted are no longer needed

Or after the binary log files have been archived and backed up

Delete only if you have already backed up the database, and do not delete it using the rm command.

10 "pause binary log

Set SQL_LOG_BIN=0; / / pause binary log

Set SQL_LOG_BIN=1; / / start the binary log

4. Query the log slowly (very important! )

Record all sql statements that take more than long_query_time seconds to execute and can be used to find queries that take a long time to execute for optimization.

It is not enabled by default, and the priority of enabling it is higher than that of the query log. By default, it will be recorded only if it takes more than 10 seconds.

Storage location: file, table (table,mysql.slog_log)

In Linux environment, modify the main configuration file / etc/my.cnf, add "long_query_time" and "log-slow-queries= file path name" under [mysqld], and restart the mysqld service.

Log_slow_queries= {on | off}: whether to enable slow log (before 5.5)

Slow_query_log= {on | off}: whether to enable slow log (no difference from above, after 5.6)

Slow_query_log_file=xxxx-slom.log: the location where the slow log is stored. Default is "hostname-slow.log". Relative path, default is under the data directory.

Log_output= {file | table | file,table | none}: indicates how to store logs

Log_query_time=N: indicates how long a query is considered slow. The default is 10 seconds.

Mysql comes with a statistical analysis tool for slow log: mysqldumpslow

Operation slow query log:

Mysql > set global slow_query_log=on; / / enable slow log

Mysql > set global slow_query_log=off; / / close the slow query log

Delete slow log: delete the slow log file directly and regenerate a new slow log through mysqladmin-u root-p flush-logs.

Mysql's main log and slow query methods will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.