Detailed explanation of MySQL log 01/17 Update SLTechnology News&Howtos

Detailed explanation of MySQL log

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Preface

The MySQL log records the daily operations and error messages of the MySQL database. MySQL has different types of log files (each stores different types of logs). From the logs, you can query the operation of the MySQL database, user operations, error information and so on.

MySQL logs fall into the following four categories:

Error log: record the problems that occur when the mysql service starts, runs or stops the mysql service; query log: records the connection of the established client and the statements executed; binary log: records the statement that records all change data, which can be used for data replication; slow query log: records all queries that have been executed for longer than long_query_time or does not use an index.

By default, all logs are created in the MySQL data directory, and by refreshing logs, you can force MySQL to close and reopen log files, Flush logs to refresh logs, or to perform mysqladmin flush-logs if you are using MySQL replication, more log files can be maintained on the replication server, which we call replacement logs. Starting logging degrades the performance of the MySQL database.

1) View system settings mysql > show global variables\ Gmysql > show global variables like'% log%';mysql > show session variables\ Gmysql > show session variables like'% log%'

To modify the parameters shown above, you can write them in the mysqld field in the main configuration file of MySQL, such as: binlog_cache_size = 1m. Or you can make a temporary change in the MySQL database: set global binlog_cache_size = 1048576, which will expire after MySQL restarts.

2) check the running status mysql > show global status\ Gmysql > show session status; [root@mysql ~] # mysql-Vmysql > status;mysql > select version (); 1. Error log

In mysql databases, error logging is enabled by default. By default, the error log is stored in the data directory of the mysql database. The name of the error log file is usually hostname.err. Where hostname represents the server hostname. The error log information can be configured by itself, and the information recorded in the error log can be defined through log-error and log-warnings, where log-error defines whether the error log is enabled and where the error log is stored, and log-warnings defines whether warning information is also defined in the error log. By default, the error log roughly records the following information: information during server startup and shutdown (not necessarily error messages, such as how mysql starts InnoDB's tablespace files, how to initialize its own storage engine, etc.), error messages during server operation, information generated when event scheduler runs an event, and information generated when server processes are started from the server MySQL has many system variables that can be set. Different settings of system variables will lead to different running states of the system. Therefore, mysql provides two sets of commands to view the system settings and running status.

In general, the definition of the log level has no session variables and only defines the state of the error log at the global level:

Where log_error is defined as the path to the error log file, and log_error_verbosity should be defined as follows:

VerbosityMessage1Error only2Error and warnings3Errors, warnings,and notes (default)

The path to the error log is specified in the main configuration file of my.cnf, as follows:

To facilitate maintenance, sometimes you want to back up the contents of the error log and start recording again, so you can use MySQL's FLUSH LOGS command to tell MySQL to back up the old log file and generate a new one. The backup file name ends with ".old".

Delete error log

Before mysql5.5.7: database administrators can delete error logs from a long time ago to ensure hard disk space on the mysql server. In the mysql database, you can use the mysqladmin command to open a new error log. The syntax of the mysqladmin command is as follows: mysqladmin-u root-p flush-logs can also log in to the mysql database and use the FLUSH LOGS statement to open a new error log. After mysql5.5.7: the server will turn off this feature. You can only rename the original error log file and manually flush the log to create a new one as follows:

[root@mysql ~] # cd / usr/local/mysql/data/ [root@mysql data] # mv mysqld.err {, .old} [root@mysql data] # mysqladmin-uroot-p flush-logsEnter password: 2, binary log

The binary log mainly records changes in the MySQL database. The binary log contains the information available in the update log in an efficient and transaction-safe manner. The binary log contains all updated data or potentially updated data. It also contains information about the execution time of each statement that updates the database, and it does not contain statements that have not modified any data. The main purpose of using binary logs is to recover the database as much as possible.

1) start the binary log (the binary log is off by default) [root@mysql data] # vim / etc/my.cnf # Edit the main configuration file [mysqld] basedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3306server_id=1socket=/usr/local/mysql/mysql.socklog-error=/usr/local/mysql/data/mysqld.errlog-bin=/usr/local/mysql/data/binary_log # specify the binary log's Path and name expire_logs_days=10 # number of days to clear logs max_binlog_size=100M # size limit for a single log file A new log file is created [root@mysql data] # systemctl restart mysqld # restart MySQL to make the configuration take effect [root@mysql data] # ll | grep binary # generates the following two files under the specified path-rw-r- 1 mysql mysql 154 20:59 binary_log.000001-rw-r- 1 mysql mysql 40 December 30 20:59 binary_log.index

Logging in to the database can also be viewed as follows:

2) View the binary log

The MySQL binary log stores all the change information, and the MySQL binary log is often used. When MySQL creates a binary log file, it first creates a file with the name 'filename' and suffix' .index', and then creates a file with the name 'filename' and the suffix' .000001'. When the MySQL service is restarted, the file with the suffix '.000001' is added, and the suffix name is added by 1.

Increment. If the log length exceeds the upper limit of max_binlog_size, a new log is also created. Show binary logs; can view the current number of binary log files and their file names. The binary log cannot be viewed directly, if you want to view the contents of the log

You can view it through the mysqlbinlog command:

You can also exit MySQL and use the mysqlbinlog command to view the command on the command line:

[root@mysql data] # mysqlbinlog binary_log.000001 3) Delete binary log

MySQL binaries can be configured for automatic deletion, and MySQL provides a way to delete binaries manually:

RESET MASTER: delete all binary log files

PURGE MASTER LOGS: delete only part of the binary log files

Reset master: delete all binary logs

Purge master logs to 'binary name': before a single binary log is deleted.

Mysql > purge master logs to "binary_log.000003"; mysql > purge master logs before '20180101; 4) restore MySQL data through binary log

For the specific process of restoring through binary logs, please refer to my previous blog post. As follows:

Detailed explanation of backup and recovery of MySQL

3. Transaction log

Transaction logs (InnoDB-specific logs because only Innodb supports transactions) can help improve the efficiency of transactions. Using the transaction log, the storage engine only needs to modify the memory copy of the table when it modifies the data, and then records the modification behavior in the transaction log on the hard disk, instead of persisting the modified data to the disk every time. The transaction log is appended, so the operation of writing the log is the sequential IPUP O in a small area of the disk, unlike the random IUnip O, which needs to move the head in multiple places on the disk, so using the transaction log method is relatively faster. After the transaction log is persistent, the modified data in memory can be slowly brushed back to disk in the background. At present, most storage engines are implemented in this way. If the modification of the data has been recorded in the transaction log and persisted, but the data itself has not been written back to disk, the system crashes and the storage engine can automatically recover the modified data when it is restarted. The recovery method it has depends on the storage engine.

1) View the definition of transaction log mysql > show global variables like 'innodb_lo%'

The output of the above instruction is explained as follows:

| | innodb_flush_log_at_trx_commit | 1 # whether innodb synchronizes the log from the buffer to the file when the transaction is committed |

When this value is 1 (the default), when each transaction commits, the log buffer is written to the log file, and the log file is refreshed by disk operations. Poor performance will result in a large number of disk I-white O, but this is the safest way; if set to 2, each commit transaction will write the log, but will not perform the brush operation. The log file is brushed at the same time every second. It is important to note that there is no guarantee that 100% will be flushed to disk every second, depending on the scheduling of the process. The data is written to the transaction log each time the transaction is committed, and the write here only invokes the write operation of the file system, which is cached, so this write does not guarantee that the data has been written to the physical disk. Set to 0, the log buffer is written to the log file once a second, and the log file is refreshed to disk, but nothing is done in a transaction commit.

Note: the concept of brushing

Flush is actually two operations, brush and write (write), it is important to distinguish between these two concepts. In most operating systems, the log buffer (memory) of Innodb is written to the log (calling the system call write), simply moving the data to the operating system cache, which also refers to memory. There is no actual persistence data.

So, usually when set to 0 and 2, the last second of data will be lost in the event of a crash or power outage, because at this time the data only exists in the operating system cache. The reason for saying "usually" is that more than 1 second of data may be lost, such as blocking when performing a flush operation.

Summary

A setting of 1 is of course the safest, but the performance page is the worst (compared to the other two parameters, but not unacceptable). If you do not have high requirements for data consistency and integrity, you can set it to 2, and if you only want the most performance, such as a log server with high concurrent writes, set to 0 to get higher performance.

Each transaction log is a file with a size of 50 megabytes (different versions of mysql vary): the name ib_logfile0,ib_logfile1 exists by default in mysql.

4. Slow query log (slow query log)

As the name implies, the slow query log records the query that takes a long time to execute, which is what we often call slow query. The slow log is in a simple text format and can be viewed through a variety of text editors. It records the time of the execution of the statement, the time consumed by the execution, the execution user, connecting to the host and other related information. The function of slow query log: slow query log is used to record query statements that have been executed for more than a specified time. Through the slow query log, you can find out which query statements are inefficient for optimization. It is generally recommended to turn it on, which has little impact on server performance, but can record queries that have been executed on the mysql server for a long time. That can help us locate performance problems. MySQL also provides a tool program mysqldumpslow that is specially used to analyze full query logs to help database administrators solve possible performance problems.

1) View the definition of slow query log

Note: the parameters for enabling slow log are different in different versions of mysql, but they can be seen through show variables like "% slow%" and show variables like "% long%".

Start slow query logging:

Method 1: open the slow query log through the configuration file my.cnf:

[root@mysql ~] # vim / etc/my.cnf # Edit the main configuration file and write the following lines [mysqld] slow_query_log=1 # 1 under the mysqld field to enable slow query slow_query_log_file=/usr/local/mysql/data/mysql-slow.log # specify slow query log location long_query_time=0.0001 # specify timeout If it takes longer than this value to set up the thread, the slow_launch_threads counter will increase [root@mysql ~] # systemctl restart mysqld # restart the service to make the configuration effective.

Log in to the database again to view the relevant information, and you will find that the changes have taken effect, as follows:

Method 2: define it directly by logging in to the MySQL server as follows:

Mysql > set global slow_query_log=1; mysql > set global long_query_time=0.0001

In the above definition, global means that it takes effect globally, and another option is session, which means that only the current session is in effect. The difference is that session is reset after exiting the current session, global is reset after the MySQL service is restarted, and the method written to the configuration file in method 1 is truly permanent.

Note: if the value of long_query_time is defined in the main configuration file, and the value of long_query_time is defined using the set directive on the MySQL command line, the value defined in the configuration file takes precedence.

The modified settings are as follows:

Use the mysqldumpslow command tool to view the slow query log at the terminal command exercise:

If you want to query the slow query log, you must guarantee two points. The first is to set the timeout of the slow query to be shorter. For example, I set it to 0.0001 above. As long as the query time exceeds this value, it is defined as a slow query. In order to verify the effect, it should be combined with the actual situation in the production environment. Second, after opening the slow query, you still need to execute several query statements in order to generate log information (query yourself casually).

Some of the options for the mysqldumpslow directive explain:

Use the options to view the slow query log:

[root@mysql data] # mysqldumpslow-t 2-a-s c mysql-slow.log# sorts by number of times, queries the first two items, and displays all the contents of the statement 5, data file

In MySQL, every database has a folder named after the database under the defined (or default) data directory, which is used to store various table data files in the database. Different MySQL storage engines have different data files. For example, MyISAM uses ".MYD" as the extension, Innodb uses ".ibd", Archive uses ".arc", CSV uses ".csv", and so on.

The metadata (meta) information related to the table in the ".frm" file is stored in the ".frm" file, including the definition of the table structure and so on. Regardless of the storage engine (the two commonly used storage engines in MySQL are MyISAM and InnoDB), each table has a ".frm" file named after the table. All ".frm" files are stored in the folder of the database to which they belong.

MyISAM database table file

.MYD file: table data file; .MYI file: index file ".MYD" file ".MYD" file is dedicated to the MyISAM storage engine to store MyISAM table data. Each MyISAM table will have a ".MYD" file corresponding to it, also stored in the folder of the database, and ".frm" file ".MYI" file ".MYI" file is also dedicated to the MyISAM storage engine, mainly storing MyISAM table index related information. For MyISAM storage, the content that can be cache is mainly from the ".myi" file. Every

A MyISAM table corresponds to a ".MYI" file, which is stored in the same location as ".frm" and ".MYD".

Innodb database table file

InnoDB uses table space (tablespace) to manage data and store table data and indexes.

.ibd file: a single table space file, each table uses a table space file (file per table), to hold the user database table data and indexes. InnoDB shared tablespaces (that is, InnoDB filesets, ibfile set): ibdata1, ibdata2, etc., store InnoDB system information and user database table data and indexes, shared by all tables. " Both the .ibd file and the ibdata file are files for storing Innodb data. The reason why there are two kinds of files to store Innodb data (including indexes) is that the Innodb data storage mode can be configured to decide whether to use shared table space to store data or exclusive table space to store data. Exclusive tablespace storage uses ".ibd" files to store data, and each table has an ".ibd" file, which is stored in the same location as MyISAM data. If you choose a shared storage tablespace to store the data, the ibdata file will be used to store the data, and all tables will share a

(or multiple, can be configured by yourself) ibdata file. Ibdata files can be configured by the two parameters innodb_data_home_dir and innodb_data_file_path, innodb_data_home_dir configuration data storage directory, and innodb_data_file_path configuration of the name of each file. You can configure multiple ibdata files at a time in innodb_data_file_path. Files can be of a specified size or auto-extended, but Innodb limits that only the last ibdata file can be configured as an auto-extension type. When we need to add a new ibdata file, it can only be added at the end of the innodb_data_file_path configuration, and we must restart MySQL to complete the ibdata addition. However, if we use exclusive tablespace storage, there will be no such problem.

Summary

Both shared and exclusive tablespaces are based on the way data is stored. Shared tablespaces: all the table data and index files in a database are placed in one file. Exclusive tablespace: each table will be generated and stored as a separate file, with each table having a .frm table description file and an .ibd file. This file includes the data contents of a single table and the contents of the index.

The advantages and disadvantages between the two

Shared tablespaces:

Advantages: you can divide the tablespace into multiple files and store them on each disk. Data and files are put together for easy management.

Disadvantages: all data and indexes are stored in one file, and multiple tables and indexes are mixed in the tablespace, so there will be a lot of gaps in the tablespace after a large number of deletions for a table, especially for statistical analysis. applications such as daily value systems are the least suitable for shared tablespaces.

Independent tablespaces:

Advantages:

Each table has its own independent table space. The data and indexes of each table are stored in its own tablespace. It is possible to move a single table in different databases. Space can be reclaimed a) the Drop table operation automatically reclaims tablespaces. For statistical analysis or daily value tables, you can delete a large amount of data through: alter table TableName engine=innodb; to retract unused space. B) for tables that use independent tablespaces, no matter how they are deleted, the fragmentation of the tablespaces will not seriously affect performance, and there is still a chance to deal with them.

Disadvantages: a single table increases too much, such as more than 100 gigabytes. In comparison, the efficiency and performance of using exclusive tablespaces is a little higher. 1) View the tablespace management type mysql > show variables like'% innodb_file_per%' of the current database +-+-+ | Variable_name | Value | +-+-+ | innodb_file_per_table | ON | +-+-+

ON stands for independent tablespace management and OFF for shared tablespace management; (to view the tablespace management of a single table, you need to see whether each table has a separate data file).

2) modify the Innodb shared tablespace [root@mysql ~] # vim / etc/my.cnf # to edit the main configuration file, and write the following configuration innodb_file_per_table=0 # under the mysqld field. 0 to use the shared tablespace, 1 to set an automatically expandable size for the exclusive tablespace innodb_data_file_path=ibdata1:100M:autoextend # The 100m data file innodb_data_home_dir=/usr/local/mysql/data # defines the default storage path of the shared tablespace [root@mysql ~] # systemctl restart mysqld # startup reported an error Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl-xe" for details.

View the log as follows:

Note: errors are slightly different in different versions of mysql. Pay attention to the contents of the error log. The general meaning is to set the pages page to 6400, exceeding its maximum value of 4864, then calculate that the initial size of 100m Magi pages is 6400, which means 1M=64pages, and its maximum value is 4864, that is to say, we can set the maximum size of 4864Universe 64marker 76m.

Modify the configuration file again as follows:

[root@mysql ~] # vim / etc/my.cnf # modify the main configuration file again innodb_data_file_path=ibdata1:76M:autoextend# modify the initial size to 76m [root@mysql ~] # systemctl restart mysqld # restart the MySQL service again # similarly, if the startup still fails, check the error log again, whether the pages page size is set or unreasonable 3) View the tablespace management type of the modified database

If the status is OFF, the shared table storage space is used, and all tables created later will use the defined shared tablespace to store data.

-this is the end of this article. Thank you for reading-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.