Tutorial Series MySQL series: Basic concepts of MySQL relational database
MariaDB-server installation of MySQL series
MySQL Series II Multi-Instance Configuration
MySQL Series 3 Basics
MySQL Series 4 SQL Syntax
MySQL series five views, stored functions, stored procedures, triggers
MySQL series 6 users and authorization
MySQL Series 7 MySQL Storage Engine
MySQL Series 8 MySQL Server Variables
MySQL series 9 MySQL query cache and index
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
MySQL Series 11 Logging
MySQL Series 12 Backup and Recovery
MySQL Series 13 MySQL Replication
MySQL Series 14 MySQL High Availability Implementation
MySQL series 15 MySQL common configuration and performance stress test 1. SQL command history ~/.mysql_history records the command history executed in mysql Transaction Log Transaction log: The transactional storage engine manages and uses itself If a transaction is committed but not yet saved to disk, it will be recorded in the transaction log. If the system loses power at this time, the committed transaction will be automatically redone (redo log) and written to disk after restarting. If the transaction has not been committed, the undo operation (undo log) will be performed after power failure and restart.
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 | -- Size of each log file | innodb_log_files_in_group | 3 | -- Number of log group members, that is, how many files | innodb_log_group_home_dir | ./ | -- Transaction file path, relative to the data directory +---------------------------+------------+ The default value of innodb_log_file_size is 5M, and the default value of innodb_log_files_in_group is 2. It is strongly recommended to increase these two values. If the data of the transaction operation is larger than the total size of the transaction log file, when executing ROLLBACK: it can be undone, but the data file will be occupied and the OPTIMIZE TABLE command needs to be used to free up data space; Using TRUNCATE to delete table records can reduce the occupied space. The DELETE command will not release disk space. If you need to release space, you need to use the OPTIMIZE command to defragment to release space.
Note: When modifying innodb_log_file_size, you need to delete the original log file first. 3. Error log The error log records: - Event information output during mysqld startup and shutdown
- Error messages generated during mysqld operation
- Log information generated when the event scheduler runs an event
- Message generated when starting a slave thread on a slave in a master-slave replication architecture
Related variables
MariaDB [school]> SHOW GLOBAL VARIABLES LIKE 'log_error'; #Path of the error log file+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+ log_warnings=1|0 0Default value 1 (yes): Whether to log warning information to the error log file 4. Query log Records user operation logs. It is generally not recommended to enable
MariaDB [school]> SHOW VARIABLES LIKE 'general_log%';
+------------------+-------------+
| Variable_name | Value |
+------------------+-------------+
| general_log | OFF | -- Turn general log on and off, off by default | general_log_file | centos7.log | -- File name of the general log file, /var/lib/mysql/HOSTNAME.log
+------------------+-------------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE | -- How to store logs, (TABLE|FILE|NONE)
+---------------+-------+ mysql.general_log: The table stores query logs, provided that they are stored in a table
5. Slow query log Records operations that execute queries for longer than the specified time. 1. Slow query related variables
MariaDB [school]> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+------------------+
| Variable_name | Value |
+---------------------+------------------+
| slow_query_log | OFF | -- Enable or disable slow query log | 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 | -- The slow query threshold, in seconds. If the query time exceeds 10, it will be recorded in the slow query log. +-----------------+-----------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| log_slow_rate_limit | 1 | -- How many queries are logged? MariaDB-specific | log_slow_verbosity | | -- The level of detail of the logged content (Query_plan, explain)
+---------------------+-------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF | -- Log statements that do not use index queries but use full-text scans; disabled by default, recommended to enable +-------------------------------+-------+ log_slow_filter : Filter 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 profiling to track the detailed time used by query statements
MariaDB [school]> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF | -- Enable or disable, disabled by default+---------------+-------+
MariaDB [school]> SET profiling=ON; #Turn on the query statement tracking functionMariaDB [school]> SHOW profiles; #Time list of query statement execution+----------+------------+------------------------------------------------------------+
| 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 profile FOR query 2; #Query the detailed execution process of the SQL statement with the specified number+----------------------+----------+
| 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 |
+----------------------+----------+ 6. Binary Log Records SQL statements that have caused data changes or potentially caused data changes in committed transactions, and generates data copies by "replaying" events in log files, independent of the storage engine type. Enable binary logging. It is disabled by default. Binary logging and data are stored separately.
Enable the function of logging binary files : add og_bin[=/path/somefile] under [mysqld] in my.cnf The default binary log is in the database directory. mariadb-bin.000001 is the binary log data file, mariadb-bin.index is the binary log index file. 1. Three ways to record binary logs:- Based on the statement recording method, each statement that changes data is recorded as a statement to save space. The system defaults to this mode, but it is not recommended to use it because there will be hidden risks.
- Based on the line recording method, each change in a line will record the statement that changes it as a statement. The log volume will be large, but the security protection of data is very high.
- Mixed mode: mixed, let the system decide which method to use
MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT | -- Logging mode, the system defaults to statement-based mode+---------------+-----------+
SET binlog_format='ROW|STATEMENT|MIXED'; -- Change the binary log recording mode 2. Binary log related variables
MariaDB [(none)]> SHOW MASTER|BINARY LOGS; -- View the list of binary log files managed by MariaDB. +--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000002 | 290 |
| mariadb-bin.000003 | 264 |
| mariadb-bin.000004 | 529038 |
| mariadb-bin.000005 | 245 |
+--------------------+-----------+
MariaDB [(none)]> SHOW MASTER STATUS; -- View the 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' FROM 1 LIMIT 2,3\G -- View the specified content in the binary file
MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; -- Whether to record binary logs, the default is ON
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin'; --Specify the file location; the default value is OFF, which means that the binary log function is not enabled. Both of the above two items must be enabled. MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 | -- The maximum size of a single binary log file. When the maximum value is reached, it will automatically roll over. The default value is 1G
+-----------------+------------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 | -- Set whether to enable the binary log instant synchronization disk function, the default is 0, the operating system is responsible for synchronizing the log to the disk+---------------+-------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 | -- Number of days after which binary logs can be automatically deleted. The default value is 0, which means no automatic deletion. +------------------+-------+ 3.mysqlbinlog command- Client command tool for binary log
- --start-position=# Specify the starting position
- --stop-position=# Specify the end position
- --start-datetime=(YYYY-MM-DD hh:mm:ss) specifies the start time
- --stop-datetime=(YYYY-MM-DD hh:mm:ss) specifies the end time
- --base64-output=decode-row
- -v |-vv |-vvv |-vvvv show detailed information
[root@centos7 mysql]# mysqlbinlog --start-position=528864 --stop-position=529019 mariadb-bin.000004 --base64-output=decode-row -v
# at 528864
#180611 20:59:46 server id 1 end_log_pos 528992 Query thread_id=29 exec_time=0 error_code=0
use `school`/*!*/;
SET TIMESTAMP=1528721986/*!*/;
INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M') #SQL statement to change data /*!*/;
Date and time of the incident: 180611 20:59:46
The server ID where the event occurred: server id 1
End position of the event: end_log_pos 528992
Event Type: Query
The ID of the thread that executed this event on the server when the event occurred: thread_id=29
The difference between the timestamp of the statement and the time it was written to the binary file: exec_time=0
Error code: error_code=0
Event content:
GTID: Global Transaction ID, a unique attribute of MySQL 5.6 and MariaDB 10 or later: GTID Use mysqlbinlog mariadb-bin.000001 > file.sql command to redirect to the SQL file, and then use mysql -uroot -p < file.sql command to directly import the data, realizing the backup and restore function 4. Binary log management
MariaDB [(none)]> PURGE BINARY LOGS TO 'mariadb.000002'; -- Delete to 02, note: 02 is not deletedMariaDB [(none)]> PURGE BINARY LOGS BEFORE '2018-01-23'; -- Delete logs before 2018-01-23MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
MariaDB [(none)]> RESET MASTER; -- Delete all binary logs and reset index files MariaDB [(none)]> FLUSH LOGS; -- Manually trigger log rolling 7. Relay Log relay log : In a master-slave replication architecture, the slave server is used to save events read from the binary log of the master server. Summarize This article ends here. I hope it can be of some help to you. I also hope that you can pay more attention to more content on 123WORDPRESS.COM! You may also be interested in:- How to enable slow query logging in MySQL
- mysql enable slow query how to enable mysql slow query logging
- MySQL log settings and viewing methods
- MySQL log file details
| 
