Linux Nine Yin canon of nine yin Bones claw fragment 12 (log function)

One, the 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

 MariaDB [school]> SHOW VARIABLES like  "  innodb_log%   " ;  +---------------------------+------------+| variable_name | Value |+---------------------------+------------+| Innodb_log_block_size | 512  | -- block size  | Innodb_log_buffer_size | 8388608  | -- cache size  | Innodb_log_file_size | 1073741824  | -- per log file size  | Innodb_log_files_in_group | 3  | -- Number of log group members, that is, there are several files  | Innodb_log_group_home_dir |./| -- transaction file path, relative to data directory  +---------------------------+------------+ 

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

Second, error log

Entries are logged in the 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_error ' ;  #错误日志文件的路径+---------------+------------------------------+| variable_name | Value                        |+---------------+------------------------------+| log_error     |/var/log/mariadb/ Mariadb.log |+---------------+------------------------------+

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

Third, query log

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

MariaDB [school]> SHOW VARIABLES like'general_log%';+------------------+-------------+| variable_name | Value |+------------------+-------------+| General_log | OFF | --Turn off universal logging on, off by default| General_log_file | Centos7.log | --The file name of the common log file,/var/lib/mysql/HOSTNAME.log+------------------+-------------+MariaDB [School]> SHOW VARIABLES like'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

Four, slow query log

Records actions that execute a query longer than the specified length

1, slow query related variables

MariaDB [school]> 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+---------------------+------------------+MariaDB [School]> 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+-----------------+-----------+MariaDB [School]> 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)+---------------------+-------+MariaDB [School]> 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+-------------------------------+-------+

Log_slow_filter: Filtering based on query results

• Admin
• Filesort
• Filesort_on_disk
• Full_join
• Full_scan
• Query_cache
• Query_cache_miss
• Tmp_table
• Tmp_table_on_disk

2. Supplement: Use profilingVerbose time used to track query statements

MariaDB [school]> SHOW VARIABLES like'Profiling';+---------------+-------+| variable_name | Value |+---------------+-------+| Profiling | OFF | --On or off, default off+---------------+-------+MariaDB [School]> SET profiling=On ; #开启查询语句跟踪功能MariaDB [School]>SHOW profiles; #查询语句执行的时间列表+----------+------------+------------------------------------------------------------+| query_id | Duration | Query |+----------+------------+------------------------------------- -----------------------+|1|0.00024497| SELECT * FROM Students | |2|0.00038528| SELECT stuid,name,age from students WHERE Stuid =2|+----------+------------+------------------------------------------------------------+MariaDB [School]> SHOW Profiles for Query2; #查询指定编号的SQL语句的详细执行过程+----------------------+----------+| Status | Duration |+----------------------+----------+| Starting |0.000035|| Opening Tables |0.000003|| After opening tables |0.000006|| Query End |0.000003|| Closing Tables |0.000002|| Freeing items |0.000011|| Updating status |0.000006|| Cleaning Up |0.000001|+----------------------+----------+

Five or two 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.

Binary logs are 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, mariadb-bin.000001 binary log data file, mariadb-bin.index binary Log index file

1. Three ways to log 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 +---------------+-----------+SET binlog_format='row| Statement| MIXED'; --Modify the binary logging method

2. Correlation variables of binary log

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 specified content in a binary mariadb [(none)]> SHOW VARIABLES like'Sql_log_bin'; --whether to log the binary log, default onmariadb [(none)]> SHOW VARIABLES like'Log_bin'; --specifies the file location; default off, which means that the binary logging feature is not enabled, both of the above are turned on to mariadb [(none)]> SHOW VARIABLES like'max_binlog_size';+-----------------+------------+| variable_name | Value |+-----------------+------------+| Max_binlog_size |1073741824| --maximum volume of a single binary log file, reaching the maximum will automatically scroll, default to 1G+-----------------+------------+MariaDB [(none)]> SHOW VARIABLES like'Sync_binlog'; +---------------+-------+| variable_name | Value |+---------------+-------+| Sync_binlog |0| --set whether to start the binary Log Instant Sync disk feature, default 0, the operating system is responsible for synchronizing logs to disk+---------------+-------+MariaDB [(none)]> SHOW VARIABLES like'expire_logs_days';+------------------+-------+| variable_name | Value |+------------------+-------+| Expire_logs_days |0| --the number of days that binary logs can be automatically deleted. The default is 0, which is not automatically deleted+------------------+-------+

3. Mysqlbinlog command

Client command tools for binary logs

• --start-position=# Specify the starting 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 end time
• --base64-output=decode-row
• -V |-VV |-vvv |-VVVV Show more information

[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

4. 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

VI. Relay Logs

Relay log: In the master-slave replication schema, from the server used to hold events read from the primary server's binary log

Linux Nine Yin canon of nine yin Bones claw fragment 12 (log function)