Mysql-14-mysql log management, mysql-14-mysql logs

Mysql-14-mysql log management, mysql-14-mysql logs

1. Introduction

The log file records the changes that occur during the running of the mysql database, such as the client connection status of the mysql database, SQL statement execution status, and error information. When the database is accidentally damaged, you can view the causes of file errors in logs, recover data through logs, analyze data through log files, optimize queries, and so on. The Mysql log Management Mechanism is relatively complete. It contains the following common log files: error logs (-log-err) and query logs (-log) binary log (-log-bin), update log (-log-update), and slow query log (-log-slow-queries ).

 

2. Operation Error Log

In the mysql database, the error log records the information during the start and stop processes of the mysql server, the faults and exceptions that occur during the running processes of the server, and the information generated when the event scheduler runs an event, information generated when the server process is started from the server. The error log records not all error information, such as how mysql starts InnoDB tablespace files and how to initialize its storage engine.

(1) Startup Error Log

Error logs are enabled by default and cannot be disabled. You can also configure the error log information by modifying the my. cnf file. The information recorded in the error log can be defined by log-error and log-warnings. log-err defines whether to enable the error log function and the location of the error log, log-warning defines whether warning information is also defined in the error log. -Log-error = [file-name] is used to specify the location where error logs are stored. If [file-name] is not specified, the default hostname. err is used as the file name and is stored in the datadir directory by default.

(2) view error logs

Use the vi or gdit tool in Linux

mysql> show variables like 'log_error';+---------------+---------------------------------+| Variable_name | Value                           |+---------------+---------------------------------+| log_error     | /application/mysql/data/cai.err |+---------------+---------------------------------+1 row in set (0.00 sec)

(3) Delete error logs

The administrator can delete the error logs from a long time ago to ensure the hard disk space on the mysql server. Run the show command to view the location of the error file. You can delete the file after deleting the error log. In the mysql database, you can use the mysqladmin command to enable new error logs. The syntax is as follows:

Mysql> flush logs; Query OK, 0 rows affected (0.02 sec) after executing the preceding command, you can create a new error file.

 

3. Operation query logs

Query logs record all user operations, including adding, deleting, querying, and modifying database information. A large amount of information is generated in environments with many concurrent operations, as a result, unnecessary disk IO will affect mysql performance. If it is not for the purpose of debugging the database, we recommend that you do not enable log query.

The query log contains the date and time, server thread ID, event type, and specific event information columns.

(1) Start querying logs

By default, log query is disabled. To open the query log, modify the my. cnf file to start the query log. Add the log option to the [mysqld] group in the following format.

[Mysqld] log [= path/[filename] the default Host Name (hostname) is used as the file name and placed in the datadir directory. Mysql> show variables like 'General % '; + ------------------ + bytes + | Variable_name | Value | + ---------------------- + ----------------------------- + | general_log | ON | general_log_file |/application/mysql/data/cai. log | + ------------------ + ----------------------------------- +

(2) View query logs

Run the preceding command to view the storage location.

(3) Delete query logs

Because the query log records all user operations. If the database is frequently used, the log query data volume will be very large and will occupy a large disk space. Run the show command to view the location of the error file. You can delete the common log and then delete the file directly.

Flush logs generates logs again.

 

4. Operation binary log

The binary file of the Mysql database is used to record all users' operations on the database. When a database accident occurs, you can use this file to view the operations performed by the user within a certain period of time. Combined with the database backup technology, you can perform user operations and recover the database.

(1) Start binary logs

By default, binary log files are not enabled. You can use the command to view

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+

Enabling method: Under/etc/my. cnf

[Mysqld] Log-bin = [filename] If there is no filename, the default value is hostname-bin as the file name, which is placed under datadir. If only binary files are generated for the specified database, you must add the following statement: Binlog-do-db = db_name (Database Name). If binary logs are not generated for the specified database, you must add the following statement: binlog-ignore-db = db_name

Generate a file in datadir

Every time mysql is restarted, cai-bin is regenerated. If the log length exceeds the upper limit of max_binlog_size (1G = 10737418248 by default), a new log file is created. Run the show command to view the upper limit of binary logs.

Mysql> show variables like 'max _ binlog_size '; + ----------------- + ------------ + | Variable_name | Value | + ----------------- + ------------ + | max_binlog_size | 1073741824 | + ----------------- + ------------ + 1 row in set (0.00 sec) using flush logs will also create a new log file and view the binary information as follows: mysql> show variables like 'Log _ bin % '\ G *************************** 1. row ************************** Variable_name: log_bin Value: ON **************************** 2. row ************************** Variable_name: log_bin_trust_function_creators Value: OFF2 rows in set (0.00 sec)

(2) view binary logs

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)mysql> show binary logs;+----------------+-----------+| Log_name       | File_size |+----------------+-----------+| cai-bin.000001 |      1774 || cai-bin.000002 |       148 || cai-bin.000003 |       126 || cai-bin.000004 |       126 || cai-bin.000005 |       107 |+----------------+-----------+5 rows in set (0.00 sec)mysql> show binlog events in 'cai-bin.000003'\G*************************** 1. row ***************************   Log_name: cai-bin.000003        Pos: 4 Event_type: Format_desc  Server_id: 1End_log_pos: 107       Info: Server ver: 5.5.56-log, Binlog ver: 4*************************** 2. row ***************************   Log_name: cai-bin.000003        Pos: 107 Event_type: Stop  Server_id: 1End_log_pos: 126       Info: 2 rows in set (0.00 sec)

 

Summary: enabling a binary file can implement the following functions:

① Recovery: Binary logs are required for the recovery of some data. For example, after a full backup file of a database is restored, you can use binary logs to recover point-in-time data.

② Replication: similar to restoration, replication and execution of binary logs allow a remote mysql database to be synchronized with another mysql database in real time.

③ Audit (audit): users can audit the information in binary logs to determine whether there is any injection attack to the database.

(3) Delete binary logs

Use the reset master command to delete all logs. The new log starts from 000001.

 

Mysql> reset master; Query OK, 0 rows affected (0.03 sec) mysql> show binary logs; + ---------------- + ----------- + | Log_name | File_size | + ---------------- + ----------- + | cai-bin.000001 | 107 | + ------------------ + --------- + 1 row in set (0.00 sec) can be seen that the deleted

 

(4) restore the database using binary logs

See the backup chapter

 

5. Slow operation log query

The most important part of optimizing mysql is to first determine the "problematic" query statement. You can analyze the cause and optimize the slow SQL query. The slow query log records the queries whose execution time exceeds the specified time. That is, it records all the statements whose execution time exceeds the maximum SQL Execution time (long_query_time) or whose indexes are not used.

(1) Start slow query logs

By default, slow query logs are disabled. You can use the configuration file my. cnf to enable it. The configuration method is as follows:

[Mysqld] Slow_query_log = [filename] Slow_launch_time = ncai-slow.log (under datadir by default) mysql> show variables like 'slow _ % '; + bytes + | Variable_name | Value | + ------------------- + bytes + | slow_launch_time | 10 | slow_query_log | OFF | slow_query_log_file |/application/mysql/data/cai-slow.log | + bytes + -------------------------------------- + 3 rows in set (0.00 sec) mysql> set global slow_query_log = on; Query OK, 0 rows affected (0.02 sec) (the preceding command can also be enabled)

(2) Delete slow query logs

Mysql> set global slow_query_log = 0; Query OK, 0 rows affected (0.00 sec generates a new slow Query log file: mysql> set global slow_query_log = 1; Query OK, 0 rows affected( 0.00 sec)