MySQL Learning notes log management (1/2)

One, log type:

MySQL has several different log files that can help you find out what's going on inside Mysqld:

The type of information that the log file is logged into the file
The error log records issues that occur when you start, run, or stop.
query log records established client connections and executed statements.
The binary log records all statements that change data. Primarily for replication and point-in-time recovery.
A slow log records all queries that have an execution time exceeding long_query_time seconds, or queries that do not use indexes.
A log that is generated when transactions are performed by a transaction log that supports transactions such as InnoDB.

By default, all logs are created in the MYSQLD data directory. By refreshing the log, you can force mysqld to close and reopen the log file (or, in some cases, switch to a new log). When you execute a FLUSH logs statement or perform a mysqladmin flush-logs or mysqladmin refresh, a log refresh occurs. If you are using the MySQL replication feature, more log files will be maintained from the replication server, known as the replacement log.
Second, error log :
The error log mainly records the following types of logs:

Information during server startup and shutdown
Error message during server run
Event Scheduler running a time is the information generated
The information generated from the server process being started from the server

Error log Definition:

You can use the--log-error[=file_name option to specify where MYSQLD saves the error log file. If the file_name value is not given, MYSQLD uses the error log name Host_name.err and writes to the log file in the data directory. If you perform flush LOGS, the error log renames the suffix with-old and mysqld creates a new empty log file. (If the--LOG-ERROR option is not given, it will not be renamed).

To view the current error log configuration:

The code is as follows	Copy Code
Mysql> show GLOBAL VARIABLES like '%log_error% '; +---------------+---------------------------------------+ | variable_name | Value | +---------------+---------------------------------------+ | Log_error | /data/mysql/localhost.localdomain.err | +---------------+---------------------------------------+ 1 row in Set (0.45 sec)

Whether to log warning logs:

The code is as follows	Copy Code
Mysql> show GLOBAL VARIABLES like '%log_warnings% '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | log_warnings | 1 | +---------------+-------+ 1 row in Set (0.00 sec)

Third, the general query log

Start switch: general_log={on| OFF}
Log file variable: general_log_file[=/path/to/file]
Global Log switch: log={on| OFF} All logs will be enabled when the switch is turned on
Record type: log_output={table| file| NONE}

Therefore, to enable the common query log, you need to configure at least general_log=on,log_output={table| FILE}. and general_log_file if not specified, the default name is Host_name.log.

Take a look at the default configurations for the above several values:

The code is as follows

Copy Code

Mysql> show GLOBAL VARIABLES like '%general_log% ';     +------------------+---------------------------+     | variable_name      | value                                 |     +------------------+---------------------------+     | general_log            | off                                   |     | general_log_file     |/data/mysql/localhost.log |     +------------------+---------------------------+ Mysql> show GLOBAL VARIABLES like '%log_output% '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Log_output | FILE | +---------------+-------+

Four, slow query log:

MySQL if the Slow_query_log=on option is enabled, queries that run longer than Long_query_time are logged (the time at which the table is locked initially does not count as execution time). Log file is Slow_query_log_file[=file_name], if no file_name value is given, the default is host name and suffix is-slow.log. If a file name is given, but not an absolute pathname, the file is written to the data directory.

Default and slow query related variables:

The code is as follows	Copy Code
Mysql> show GLOBAL VARIABLES like '%slow_query_log% '; +----------------------------+--------------------------------+ | variable_name | Value | +----------------------------+--------------------------------+ | Slow_query_log | Off | | Slow_query_log_file | /data/mysql/localhost-slow.log | +----------------------------+--------------------------------+

The server parameter setting method is the same as the common query log, without explanation.

Slow queries are not enabled by default and are recommended for server tuning.

The code is as follows	Copy Code
mysql> SET GLOBAL Slow_query_log=on; Query OK, 0 rows affected (1.45 sec)

# #如果要长久生效, you need to define it in the configuration file.

So how long is it slow?

If the query is longer than the defined value of Long_query_time (default 10 seconds), it is a slow query:

The code is as follows	Copy Code
Mysql> show GLOBAL VARIABLES like ' long_query_time '; +-----------------+-----------+ | variable_name | Value | +-----------------+-----------+ | Long_query_time | 10.000000 | +-----------------+-----------+

five or two in-process log:
binary log start switch: log-bin [= file_name]

Must be manually specified in version 5.6 and above. 5.6 The following version default file_name is $datadir/mysqld-binlog

Binary logs are used to record all statements that change data. Primarily for replication and point-in-time recovery.

The tool for viewing binary logs is: Mysqlbinlog

The binary log contains all the statements that have updated the data or have potentially updated the data (for example, a delete that does not match any of the rows). Statement is saved as an "event," which describes the data changes. The binary log also contains execution time information about each statement that updates the database. It does not contain statements that do not modify any data.

The primary purpose of binary logging is to be able to update the database (that is, point-in-time recovery) most likely when a database fails, because the binary log contains all the updates that were made after the backup. The binary log is also used to record all statements that will be sent to the server from the primary replication server.
So does the binary log record the executed statement or the result data after execution?

In the first case:

Adding a table has 100,000 rows of data, and now executes a statement that adds the value of the Amount field to 1000 on its original basis:

The code is as follows	Copy Code
UPDATE sales.january SET amount=amount+1000;

At this point, if you want to record the result data after execution, the log will be very large.

Therefore, the execution statement should be recorded in this case. This approach is based on the binary log of the statement.

In the second case:

What if the current time is inserted into a field? As follows:

The code is as follows	Copy Code
INSERT into TB SET birthdate=current_time ();

The statement cannot be logged at this time, because the results of execution at different times are not the same. This is the value that should be recorded for this line, which is a binary log based on rows (row).

In some cases, it may be possible to combine two ways to record this binary log, called a hybrid method.
Binary Logging Time:

By default, the binary logs are not synchronized with the hard disk every time you write. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the binary log is lost. To prevent this, you can use the Sync_binlog global variable (1 is the safest value, but also the slowest) so that the binary log synchronizes with the hard disk after every n binary log write.

The update to the non-transaction table is saved to the binary log as soon as it finishes executing. For a transaction table, such as a bdb or InnoDB table, all changes to the table's update (update, delete, or insert) are cached until the server receives a commit statement. At that point, Mysqld writes the entire transaction to the binary log before executing the commit. When the thread that handles the transaction starts, it allocates binlog_cache_size memory for the buffered query. If the statement is greater than this value, the thread opens the temporary file to save the transaction. Temporary files are deleted after the thread ends.
Management of binary logs:

Log scrolling:

Set max_binlog_size = 200M in my.cnf to limit the maximum binary log size to 200M and scroll after 200M. MySQL scrolling is not the same as other logs, scrolling will create a new number 1 log to record the latest log, and the original log name will not be changed.

Every time you restart the MySQL service, the log will scroll automatically.

In addition, if you need to scroll manually, use the command:

The code is as follows	Copy Code
Mysql> FLUSH LOGS;

View of log:

To see which binary log files are available:

code is as follows	copy code
mysql> Show BINARY LOGS;     +----------------------+-----------+     | log_name                    | file_size    |     +----------------------+-----------+     | mysqld-binlog.000001 |        143   |     | mysqld-binlog.000002 |       120   |     +----------------------+-----------+

To see which binary log file is currently in use:

The code is as follows

Copy Code

Mysql> show MASTER STATUS; +----------------------+----------+--------------+------------------+-------------------+ | File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set | +----------------------+----------+--------------+------------------+-------------------+ |      mysqld-binlog.000002 |                       120 |                            |                              | | +----------------------+----------+--------------+------------------+-------------------+ # #做个操作后再次查看: mysql> use Jiaowu; Database changed Mysql> INSERT into students (name,age) VALUES (' stu1 ', 24); Query OK, 1 row affected (0.07 sec) Mysql> show MASTER STATUS; +----------------------+----------+--------------+------------------+-------------------+ | File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set | +----------------------+----------+--------------+------------------+-------------------+ |      mysqld-binlog.000002 |                      394 |                             |                              | | +----------------------+----------+--------------+------------------+-------------------+ 1 row in Set (0.00 sec) # #可以看到Position (position) has changed.

To view binary log content:

The code is as follows

Copy Code

Mysql> show Binlog EVENTS in ' mysqld-binlog.000002 '; +----------------------+-----+-------------+-----------+-------------+----------------------------------------- -------------------------+ | Log_name | Pos | Event_type | server_id | End_log_pos | Info | +----------------------+-----+-------------+-----------+-------------+----------------------------------------- -------------------------+ |   mysqld-binlog.000002 | 4 |         Format_desc |         1 | 120 | Server ver:5.6.13-log, Binlog ver:4 | | mysqld-binlog.000002 | 120 |         Query |         1 | 203 | BEGIN | | mysqld-binlog.000002 | 203 |         Intvar |         1 | 235 | insert_id=11 | | mysqld-binlog.000002 | 235 |         Query |         1 | 363 | Use ' Jiaowu '; INSERT into students (name,age) VALUES (' stu1 ', 24) | | mysqld-binlog.000002 | 363 |         Xid |         1 | 394 | COMMIT/* Xid=13 * | +----------------------+-----+-------------+-----------+-------------+----------------------------------------- -------------------------+ 5 rows in Set (0.01 sec) # #该语句还可以加上Position (position), specify which position (position) to show to start with: Mysql> Show Binlog the EVENTS in ' mysqld-binlog.000002 ' from 203; +----------------------+-----+------------+-----------+-------------+------------------------------------------ ------------------------+ | Log_name | Pos | Event_type | server_id | End_log_pos | Info | +----------------------+-----+------------+-----------+-------------+------------------------------------------ ------------------------+ | mysqld-binlog.000002 | 203 |         Intvar |         1 | 235 | insert_id=11 | | mysqld-binlog.000002 | 235 |         Query |         1 | 363 | Use ' Jiaowu '; INSERT into students (name,age) VALUES (' stu1 ', 24) | | mysqld-binlog.000002 | 363 |         Xid |         1 | 394 | COMMIT/* Xid=13 * | +----------------------+-----+------------+-----------+-------------+------------------------------------------ ------------------------+ 3 Rows in Set (0.00 sec)

Home 1 2 last page