mysql-Log Records

Log

Transaction log: Transaction log

Trunk log: Reley log error log: Error logfile

General log:

Slow query log: Slow query log

Binary logs: Binary log

Transaction log

Transaction log: Transactional storage engine self-managing and using

The transaction log is logged when a transaction has not yet been saved to disk, this time if the system loses power, the committed transaction redo (redo log) is automatically written to disk, and if the transaction has not yet been committed, the undo log is performed when the power is restarted.

Related Variables

Innodb_log_file_size default size is 5m,innodb_log_files_in_group number of default 2, strongly recommended to increase these two values;

If the data for a transactional operation is greater than the total size of the transaction log file, when executing rollback: can be undone, but the data file is occupied and the data space needs to be freed using the Optimize table command;

Using Truncate to delete table records can reduce space consumption, and the Delete command will not free up disk space, and if necessary, use the optimize command to free up space by using the new defragmentation.

Note: You need to delete the original log file before modifying innodb_log_file_size

An item is logged in the error log error log:

1. Mysqld event information that is output during startup and shutdown
2. MYSQLD error messages generated in the run
3. Event Scheduler Log information generated when an event is run
4. Information generated when starting a server thread from a server in a master-slave replication schema

Related variables

log_warnings=1|0Default value 1 (yes): whether to log warning messages to the error log file

Query log

Logging the user's action log, generally not recommended to enable

' Log_output ' ;   +---------------+-------+| variable_name | Value |+---------------+-------+| Log_output    | FILE  | # How to store the log, (table| file| NONE) +---------------+-------+

Mysql.general_log: The table holds the query log, if it is stored as a table

Slow Query Log

Records actions that execute a query longer than the specified length

MariaDB [hellodb]>Show variables like 'slow_query%'; +---------------------+------------------+| variable_name | Value |+---------------------+------------------+| Slow_query_log | OFF |--turn slow query log on or off| Slow_query_log_file | Centos7-slow.log |--Slow query log file+---------------------+------------------+2Rowsinch Set(0.00sec) MariaDB [Hellodb]>SHOW VARIABLES like 'long_query_time'; +-----------------+-----------+| variable_name | Value |+-----------------+-----------+| Long_query_time |10.000000|--slow query threshold, per second, query time more than 10 records to slow query log+-----------------+-----------+1Rowinch Set(0.00sec) MariaDB [Hellodb]>SHOW VARIABLES like 'log_slow%'; +---------------------+------------------------------+| variable_name | Value | | Log_slow_rate_limit |1|--How many queries are recorded, mariadb unique| log_slow_verbosity | |--record the verbosity of the content (Query_plan,explain)+---------------------+------------------------------+4Rowsinch Set(0.00sec) MariaDB [Hellodb]>SHOW VARIABLES like 'log_queries_not_using_indexes'; +-------------------------------+-------+| variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF |--A statement that uses a full-text scan without using an index query, which is closed by default, is recommended to open+-------------------------------+-------+1Rowinch Set(0.00Sec

Log_slow_filter: Filter adminfilesortfilesort_on_diskfull_joinfull_scanquery_cachequery_cache_misstmp_tabletmp based on query results _table_on_disk

Add: Use profilingVerbose time used to track query statements

MariaDB [hellodb]> Show variables like'Profiling';+---------------+-------+| variable_name | Value |+---------------+-------+| Profiling | Off |--on or off, default off +---------------+-------+1Rowinch Set(0.00sec) MariaDB [Hellodb]> SET profiling=on ; #开启查询语句跟踪功能Query OK,0Rows Affected (0.00sec) MariaDB [Hellodb]>SHOW profiles; #查询语句执行的时间列表+----------+------------+---------------------------------+| query_id | Duration | Query |+----------+------------+---------------------------------+|1|0.00006851| SET Profiling=on | |2|0.00049469| Show variables like'Profiling'| |+----------+------------+---------------------------------+3Rowsinch Set(0.00Sec

2 ;  #查询指定编号的SQL语句的详细执行过程+----------------------+----------+| Status               | Duration |+----------------------+----------+| Starting             0.000035 | | Opening tables       0.0000030.000006 | | query end            0.000003 | | closing Tables       0.000002 | | Freeing items        0.000011 | | Updating status      0.000006 | | Cleaning up          0.000001< /c19> |+----------------------+----------+

Binary log

A SQL statement that records committed transactions causing data changes or potentially causing data changes, generating copies of the data through events in the replay log file, independent of the storage engine type.

Note: Binary logging is turned on by default, binary logs and data are stored separately

the ability to log binary files is enabled : in my.cnf [mysqld], addlog_bin[=/path/somefile]

Default binary log in database directory, binary log mariadb-bin.000001 data file, binary mariadb-bin.index log index file

Three ways to record binary logs:

• Statement-based recording, each statement that changes the data is recorded as a statement, saving space, the system defaults to this mode, but is not recommended, there will be hidden risks
• With row-based logging, each row changes to a statement that makes it change, and the log volume is large, but the security of the data is very high
• Mixed mode: Mixed, let the system decide in which way

' Binlog_format ' ; +---------------+-----------+| variable_name | Value     |+---------------+-----------+| Binlog_format | STATEMENT |--record mode, system default based on statement mode +---------------+-----------+1inset (0.00 sec)

SET binlog_format= ' row| Statement| MIXED '; --Modify the binary logging method

Correlation variables for binary logs

Open Binary Log

' Sql_log_bin ' ;--whether to log the binary log, defaulton +---------------+-------+| variable_name | Value |+---------------+-------+| Sql_log_bin   | On    |+---------------+-------+1inset (0.00 sec)

Because show VARIABLES like ' log_bin '; dynamic modification is not supported, so edit the configuration file vim/etc/my.cnf

New mkdir/data/mysqllogs/

Modify owner all Groups Chown-g mysql.mysql/data/mysqllogs/

Specifies the file location; The default off, which means that binary logging is not enabled, both of these are turned on to

MariaDB [(None)]> show variables like'max_binlog_size';+-----------------+------------+| variable_name | Value |+-----------------+------------+| Max_binlog_size |1073741824|-the maximum volume of a single binary log file, the maximum value will automatically scroll, the default is 1G+-----------------+------------+1Rowinch Set(0.00sec) MariaDB [(none)]> Show variables like'Sync_binlog';+---------------+-------+| variable_name | Value |+---------------+-------+| Sync_binlog |0|--setting whether to start the binary Log Instant Sync disk feature, default 0, the OS is responsible for synchronizing logs to disk +---------------+-------+1Rowinch Set(0.00sec) MariaDB [(none)]> Show variables like'expire_logs_days';+------------------+-------+| variable_name | Value |+------------------+-------+| Expire_logs_days |0|--binary logs can be automatically deleted for the number of days. The default is 0, which is not automatically deleted +------------------+-------+1Rowinch Set(0.00Sec

MariaDB [(None)]> SHOW master| BINARY LOGS; --View the list of binary log files in MARIADB self-managed use+--------------------+-----------+| Log_name | File_size |+--------------------+-----------+| Mariadb-bin.000002|290|| Mariadb-bin.000003| the|| Mariadb-bin.000004|529038|| Mariadb-bin.000005|245|+--------------------+-----------+MariaDB [(none)]> SHOW MASTER STATUS; --to view binary log files in use+--------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+--------------------+----------+--------------+------------------+| Mariadb-bin.000005|245|                  | |+--------------------+----------+--------------+------------------+MariaDB [(none)]> SHOW BINLOG EVENTS in'mariadb-bin.000004'From1LIMIT2,3\g--View the specified content in a binary file

Mysqlbinlog command

Client command tools for binary logs

--start-position=# Specify start position--stop-position=# specify end position--start-datetime= (yyyy-mm-dd hh:mm:ss) specify start time--stop-datetime= ( YYYY-MM-DD hh:mm:ss) Specify the end time--base64-output=decode-row-v |-vv |-VVV |-VVVV Show Details

[Email protected] mysql]# Mysqlbinlog--start-position=528864--stop-position=529019Mariadb-bin.000004--base64-output=decode-row-v# at528864#180611  -: -: $Server ID1End_log_pos528992Query thread_id= inExec_time=0Error_code=0Use ' School '/*!*/; SET TIMESTAMP=1528721986/*!*/; INSERT students (Stuid,name,age,gender) VALUES ( -,'Tom', A,'M') #改变数据的SQL语句/*!*/; The date and time the event occurred:180611  -: -: $Server ID for event occurrence:1End location of event: End_log_pos528992type of event: The id:thread_id of the thread on which the server executes this event when the query event occurs= intimestamp of the statement and the time difference to write it to the binary file: Exec_time=0error code: Error_code=0Event Content: Gtid:global Transaction id,mysql5.6 with MARIADB10 version exclusive properties: GTID

Use mysqlbinlog mariadb-bin.000001 > file.sql commands to redirect to a SQL file, and then use mysql -uroot -p < file.sql commands to import data directly, enabling backup restore functionality

Management of binary logs

' mariadb.000002 '; -- Delete to 02, note: 02 do not delete mariadb [(none)]'2018-01-23'; --delete2018-23 before the log mariadb [(none)]'2017-03-22 09:25:30'; MariaDB [(None)]> RESET MASTER; -- Delete all binary logs, index file re-count mariadb [(none)]> FLUSH LOGS; --Manually trigger log scrolling

mysql-Log Records