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

This article mainly gives you a brief description of what kinds of log files there are in 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 which kinds of log files there are in MySQL can bring you some practical help.

MySQL mainly has the following log types:

Error log-information and other errors and warnings during the startup and shutdown of the MySQL service. The default is under the data directory.

General query log-the log used to record select query statements. General_log and general_log_file are disabled by default. It is recommended to disable them.

Slow log-log-slow-queries records all SQL statements that exceed the long_query_time time

Binary log-records any operations that cause changes in data for backup and restore. It is stored in the data directory by default, and the binary log is scrolled during refresh and service restart.

Relay log-events copied from the binaries of the primary cloud server and saved as binaries in the same format as binary logs.

Transaction log-ensures the consistency of transactions.

1 slow query log:

Mysql > show variables like "long%"; # View slow query log information

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 3.000000 | |

+-+ +

1 row in set (0.13 sec)

Mysql > show variables like "slow%"; # View slow log settings

+-- +

| | Variable_name | Value |

+-- +

| | slow_launch_time | 2 | |

| | slow_query_log | OFF |

| | slow_query_log_file | / data/mysql/slave2-slow.log |

+-- +

3 rows in set (0.06 sec)

# slow query time setting. Permanent change requires changing the configuration file. Slow queries exceeding 5 seconds will be recorded in the slow query log file.

Mysql > set long_query_time=5

Query OK, 0 rows affected (0.25 sec)

Mysql > set global slow_query_log=1; # enable slow query. Configuration file needs to be modified if it is enabled permanently.

Mysql > show global status like'% Slow_queries%'; # View the number of slow query logs

Mysqldumpslow-s c-t 10 / database/mysql/mysql06_slow.log # View the 10 most visited SQL

Mysqldumpslow slow-query.log # classifies and summarizes slow queries

2 binary log:

Mysql > show global variables like "% log%"; # View log related variables

Mysql > show variables like "% log_bin%"; # View binary variables

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | OFF |

| | log_bin_basename |

| | log_bin_index |

| | log_bin_trust_function_creators | OFF |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+ +

[root@slave02 mysql] # vim / etc/my.cnf

Log_bin # uncomment and open binary log

[root@slave02 mysql] # service mysqld restart

Mysql > show binary logs; # View all binary log files

+-+ +

| | Log_name | File_size |

+-+ +

| | slave2-bin.000001 | 168 | |

| | slave2-bin.000002 | 120 | |

+-+ +

Mysql > show master status; # View the currently used binary log

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | slave2-bin.000002 | 120 | |

+-+

1 row in set (0.03 sec)

Mysql > flush logs; # refresh binary log

Query OK, 0 rows affected (0.17 sec)

[root@slave02 mysql] # mysqlbinlog mysql-bin.00001; # displays the contents of binary files

(3) deletion of logs-the capacity of the disk is limited, while the growth of logs is unlimited.

Delete all binary logs in 1:reset master mode

Mysql > reset master

Query OK, 0 rows affected (0.02 sec)

View the deleted binary log

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 120 Apr 29 22:59 www-bin.000001

-rw-rw----. 1 mysql mysql 17 Apr 29 22:59 www-bin.index

Delete logs before 000002 by 2:purge master logs to 'www-bin.000002';

View the log

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 165 Apr 29 23:04 www-bin.000001

-rw-rw----. 1 mysql mysql 165 Apr 29 23:04 www-bin.000002

-rw-rw----. 1 mysql mysql 165 Apr 29 23:04 www-bin.000003

-rw-rw----. 1 mysql mysql 120 Apr 29 23:04 www-bin.000004

-rw-rw----. 1 mysql mysql 68 Apr 29 23:04 www-bin.index

Delete Log

Mysql > purge master logs to 'www-bin.000002'

Query OK, 0 rows affected (0.03 sec)

Check the log again

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 165 Apr 29 23:04 www-bin.000002

-rw-rw----. 1 mysql mysql 165 Apr 29 23:04 www-bin.000003

-rw-rw----. 1 mysql mysql 120 Apr 29 23:04 www-bin.000004

-rw-rw----. 1 mysql mysql 51 Apr 29 23:06 www-bin.index

Mode: 3:purge master logs before '2017-04-29 23: 11 virtual; delete logs before a certain point in time

View the log

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 165 Apr 29 23:04 www-bin.000002

-rw-rw----. 1 mysql mysql 165 Apr 29 23:04 www-bin.000003

-rw-rw----. 1 mysql mysql 165 Apr 29 23:11 www-bin.000004

-rw-rw----. 1 mysql mysql 120 Apr 29 23:11 www-bin.000005

-rw-rw----. 1 mysql mysql 68 Apr 29 23:11 www-bin.index

Delete Log

Mysql > purge master logs before '2017-04-29 2315 11purl 00'

Query OK, 0 rows affected (0.74 sec)

Check the log again

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 165 Apr 29 23:11 www-bin.000004

-rw-rw----. 1 mysql mysql 120 Apr 29 23:11 www-bin.000005

-rw-rw----. 1 mysql mysql 34 Apr 29 23:13 www-bin.index

Method 4: add a parameter to the configuration file:-- the meaning of the expire_logs_days=# parameter is to set the number of days the log expires

1) modify the configuration file: vim / etc/my.cnf

Expire_logs_days=60 # add a line

[root@www mysql] # service mysqld restart

2) View the log before deletion

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 165 Apr 29 23:11 www-bin.000004

-rw-rw----. 1 mysql mysql 120 Apr 29 23:11 www-bin.000005

-rw-rw----. 1 mysql mysql 34 Apr 29 23:13 www-bin.index

3) modify the system time

[root@www mysql] # date-s "2017-05-30"

4) refreshing the log triggers the log update. Since it is less than 60 days, the log will not be deleted.

Mysql > flush logs

Query OK, 0 rows affected (0.64 sec)

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 165 Apr 29 23:11 www-bin.000004

-rw-rw----. 1 mysql mysql 143 Apr 29 23:25 www-bin.000005

-rw-rw----. 1 mysql mysql 165 May 30 00:02 www-bin.000006

-rw-rw----. 1 mysql mysql 165 May 30 00:02 www-bin.000007

-rw-rw----. 1 mysql mysql 165 May 30 00:03 www-bin.000008

-rw-rw----. 1 mysql mysql 120 May 30 00:03 www-bin.000009

-rw-rw----. 1 mysql mysql 102 May 30 00:03 www-bin.index

5) after changing the system time to 60 days, refresh the log again to trigger the log file update, and you can find that the log of 60 days ago has been deleted.

[root@www mysql] # date-s "2017-08-30"

Mysql > flush logs

Query OK, 0 rows affected (0.06 sec)

Mysql > system ls-ltr www-bin*

-rw-rw----. 1 mysql mysql 165 Aug 30 00:00 www-bin.000010

-rw-rw----. 1 mysql mysql 120 Aug 30 00:00 www-bin.000011

-rw-rw----. 1 mysql mysql 34 Aug 30 00:00 www-bin.index

What are the main log files of MySQL? let's stop here. If you want to know about other related issues, you 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.