Pithy formula: find my CNF, error finding Log. Find slow log
1, Database configuration file
1.my.cnf file
my.cnf is the configuration file loaded when mysql is started. It is usually placed in the mysql installation directory. Users can also load it in other directories, usually in / etc / my cnf.
MySQL configuration file has multiple files, as follows:
[root@k8s001 ~]# mysql --help |grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The order in which the MySQL instance reads the configuration file is: / etc / my cnf /etc/mysql/my. cnf /usr/local/mysql/etc/my. cnf ~/. my. cnf
Multiple configuration files follow the principle of parameter replacement: that is, the parameters in the following files will replace the same parameters in the previous files.
- my.cnf common file formats are as follows:
##mysql client settings [client] user=root password=Cmss@2021 ##Password free login settings port=3306 socket=/tmp/mysql.sock ##Indicates the settings after logging in to mysql [mysql] prompt=(\\u@\\h)[\\d]>\\_ ##Display login user name + ip + Library ##mysql server settings (all settings at the beginning of mysqld are server settings) [mysqld] server-id=1 port=3306 user=mysql datadir=/mdata/mysql_test_data log_error=error.log plugin-load=validate_password.so default_password_lifetime=0 #skip-grant-tables ##It means that it takes effect under a specific mysql version [mysqld-5.6] innodb_flush_neighbors=2 [mysqld-5.7] [mysqld-8.0] [mysqldump] quick [mysqladmin] ## Multi instance configuration [mysqld_multi] mysqld=/usr/local/mysql/bin/mysqld_safe #Used to start mysqld mysqladmin=/usr/local/mysql/bin/mysqladmin #Used to close mysqld, which requires a user name and password. If not configured, the user name and password of [client] will be used by default. If configured, please note that the password is configured as pass log=/usr/local/mysql/mysqld_multi.log #user=root #mysqladmin #pass=Cmss@2021 #Pay attention to password configuration ##Single machine multi instance 1 (version 5.7) [mysqld1] server-id=11 innodb_buffer_pool_size=32M port=3307 datadir=/mdata/data1 socket=/tmp/mysql.sock1 ##Single machine multi instance 2 (version 5.7) [mysqld2] server-id=12 innodb_buffer_pool_size=32M port=3308 datadir=/mdata/data2 socket=/tmp/mysql.sock2 ##Single machine multi instance 3 (version 5.6) [mysqld56] server-id=56 innodb_buffer_pool_size=32M port=3356 basedir=/usr/local/mysql56 #Declare mysql56 installation package directory datadir=/mdata/data56 socket=/tmp/mysql.sock3 ##Single machine multi instance 4 (version 8.0) [mysqld80] server-id=80 innodb_buffer_pool_size=32M port=3380 basedir=/usr/local/mysql80 datadir=/mdata/data80 socket=/tmp/mysql.sock4
2. Configure parameter variables
- From the scope, it can be divided into global and session
## The session variable is used by default show [global|session] variables like '%variable%'; set [global|session] variable='';
-
In terms of type, it can be divided into modifiable parameters and read-only parameters
-
Modifiable parameters can be modified online
(root@localhost)[(none)] show session variables like '%long%query%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) (root@localhost)[(none)] set long_query_time=20; Query OK, 0 rows affected (0.00 sec) (root@localhost)[(none)] show session variables like '%long%query%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 20.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
- Read only parameters can only be modified through the configuration file and restarted
(root@localhost)[(none)] show variables like '%datadir%'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | datadir | /mdata/mysql_test_data/ | +---------------+-------------------------+ 1 row in set (0.00 sec) (root@localhost)[(none)] set datadir='/data/'; ERROR 1238 (HY000): Variable 'datadir' is a read only variable
- The modification of all parameters does not support persistence (some versions of MySQL 8.0 seem to support it)
- Modifying parameters through the global domain will not affect the current session parameters. (unless reconnecting, reconnection is another session)
(root@localhost)[(none)] show session variables like '%long%query%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) (root@localhost)[(none)] set global long_query_time=30; Query OK, 0 rows affected (0.00 sec) (root@localhost)[(none)] show session variables like '%long%query%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) (root@localhost)[(none)] show global variables like '%long%query%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 30.000000 | +-----------------+-----------+ 1 row in set (0.01 sec)
- You can use the system table performance_schema.variables_by_thread to view the variable value of each session.
(root@localhost)[performance_schema] select * from variables_by_thread where variable_name='long_query_time'; +-----------+-----------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+-----------------+----------------+ | 35 | long_query_time | 30.000000 | | 36 | long_query_time | 10.000000 | | 37 | long_query_time | 20.000000 | +-----------+-----------------+----------------+ 3 rows in set (0.00 sec)
2, Table structure definition file
MySQL physical storage structure diagram
explain:
mysqld represents a database instance.
Each database instance has a datadir. There are multiple databases under one path, and each database corresponds to a folder.
There are multiple tables in each database, and each table corresponds to a group of table files. That is, there are multiple groups of table files under each folder.
- 1. The data file path is set by the parameter datadir
## This parameter is configured in / etc / my CNF, which is a read-only parameter (root@localhost)[(none)]> show variables like '%datadir%'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | datadir | /mdata/mysql_test_data/ | +---------------+-------------------------+ 1 row in set (0.00 sec) ## Initially, there are four databases root@localhost)[(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | ## Database 0 does not have a corresponding folder | mysql | ## Database 1 | performance_schema | ## Database 2 | sys | ## Database 3 +--------------------+ 4 rows in set (0.00 sec) ## Take a look at the tables in the mysql database (root@localhost)[performance_schema]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost)[mysql]> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | ## Corresponding to a group of files (3 files) | db | ## Corresponding to a group of files (4 files) | engine_cost | ## Corresponding to a group of files (2 files) ...
- 2. A database corresponds to a folder.
[root@k8s001 mysql_test_data]# pwd /mdata/mysql_test_data [root@k8s001 mysql_test_data]# ll Total consumption 123024 -rw-r-----. 1 mysql mysql 56 6 September 23:06 auto.cnf -rw-------. 1 root root 1679 6 September 23:06 ca-key.pem -rw-r--r--. 1 root root 1074 6 September 23:06 ca.pem -rw-r--r--. 1 root root 1078 6 September 23:06 client-cert.pem -rw-------. 1 root root 1679 6 September 23:06 client-key.pem -rw-r-----. 1 mysql mysql 67273 6 October 3:22 error.log -rw-r-----. 1 mysql mysql 299 6 October 3:21 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 6 October 3:22 ibdata1 -rw-r-----. 1 mysql mysql 50331648 6 October 3:22 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 6 September 23:06 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 6 October 3:22 ibtmp1 -rw-r-----. 1 mysql mysql 6 6 October 3:22 k8s001.pid drwxr-x---. 2 mysql mysql 4096 6 September 23:06 mysql ## Database 1 -rw-rw----. 1 root root 6 6 October 3:22 mysqld_safe.pid drwxr-x---. 2 mysql mysql 8192 6 September 23:06 performance_schema ## Database 2 -rw-------. 1 root root 1675 6 September 23:06 private_key.pem -rw-r--r--. 1 root root 451 6 September 23:06 public_key.pem -rw-r--r--. 1 root root 1078 6 September 23:06 server-cert.pem -rw-------. 1 root root 1679 6 September 23:06 server-key.pem drwxr-x---. 2 mysql mysql 8192 6 September 23:06 sys ## Database 3
Note: Why are there only three database folders?
Because information_ The tables in the schema are actually views, not basic tables, so there are no files related to them on the file system.
- 3. Each table corresponds to a group of documents
[root@k8s001 mysql_test_data]# cd mysql [root@k8s001 mysql]# ll Total consumption 11896 -rw-r-----. 1 mysql mysql 8820 6 September 23:06 columns_priv.frm##Table columns_priv corresponds to three files -rw-r-----. 1 mysql mysql 0 6 September 23:06 columns_priv.MYD -rw-r-----. 1 mysql mysql 4096 6 September 23:06 columns_priv.MYI -rw-r-----. 1 mysql mysql 9582 6 September 23:06 db.frm ##Table db corresponds to 4 files -rw-r-----. 1 mysql mysql 488 6 September 23:06 db.MYD -rw-r-----. 1 mysql mysql 5120 6 September 23:06 db.MYI -rw-r-----. 1 mysql mysql 65 6 September 23:06 db.opt -rw-r-----. 1 mysql mysql 8780 6 September 23:06 engine_cost.frm ##Table engine_cost corresponds to 2 files -rw-r-----. 1 mysql mysql 98304 6 September 23:06 engine_cost.ibd ...
Note: the MySQL official website provides an excellent MySQL utilities tool suite to assist in the use of MySQL.
For installation, see: https://blog.csdn.net/qq_41822345/article/details/117779815
## You can use the MySQL frm command (install the MySQL utilities tool suite first) to view * Structure of frm file
[root@k8s001 mysql]# mysqlfrm --diagnostic columns_priv.frm
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for time_zone.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:
CREATE TABLE `time_zone` (
`Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Use_leap_seconds` enum('Y','N') NOT NULL,
PRIMARY KEY `PRIMARY` (`Time_zone_id`)
) ENGINE=InnoDB COMMENT 'Time zones';
#...done.
3, Error log file
Error log: Records problems when starting, running, or stopping mysqld.
Error log, as the name suggests, is of course a log for recording error information. However, the error log file mentioned in this section is not only recording error information, but also recording the startup / shutdown information of MySQL service process. It does not mean that any errors will be recorded. Only critical errors occurred during the operation of service process will be recorded, In addition, if mysqld process finds that some tables need to be checked or repaired automatically, it will also throw relevant information to the log file.
It is recommended to uniformly modify the error log file to a fixed name, such as error log.
Initial installation mysql5 7 and mysql8 The initialization password for 0 is in this file.
Usage principle: once an error occurs in the mysqld instance, the file should be analyzed first.
Its location is as follows:
(root@localhost)[(none)]> show variables like 'log_error'; +---------------+----------------------------------+ | Variable_name | Value | +---------------+----------------------------------+ | log_error | /mdata/mysql_test_data/error.log | +---------------+----------------------------------+ 1 row in set (0.00 sec)
4, Slow query log file
Slow query log: records that all execution times exceed long_query_time seconds or queries that do not use indexes.
## View slow query log default file name: machine name - slow Log is off by default (root@localhost)[(none)]> show variables like '%slow%query%'; +---------------------+----------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /mdata/mysql_test_data/k8s001-slow.log | +---------------------+----------------------------------------+ 2 rows in set (0.01 sec) ## View long_query_time parameter (logs longer than 2s will be saved) (root@localhost)[(none)]> show variables like 'long_query%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ 1 row in set (0.00 sec) ## Enable slow query log. Slow query log is a global variable (root@localhost)[(none)]> set global slow_query_log=1; Query OK, 0 rows affected (0.00 sec) ## You can redefine the slow query log (root@localhost)[(none)]> set global slow_query_log_file='slow.log'; Query OK, 0 rows affected (0.01 sec) ## Clear slow query log) (remember to back up first) (root@localhost)[(none)]> flush slow logs; Query OK, 0 rows affected (0.00 sec)
Use examples:
## Sleep for 5s (execute in mysql) (root@localhost)[(none)]> select sleep(5); +----------+ | sleep(5) | +----------+ | 0 | +----------+ 1 row in set (5.00 sec) ## Sleep for 2s? (root@localhost)[(none)]> select sleep(2); +----------+ | sleep(2) | +----------+ | 0 | +----------+ 1 row in set (2.00 sec) ## View slow query log (executed in machine) [root@k8s001 ~]# cat /mdata/mysql_test_data/k8s001-slow.log /usr/local/mysql/bin/mysqld, Version: 5.7.16 (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 2021-06-15T02:10:53.481247Z # User@Host: root[root] @ localhost [] Id: 3 # Query_time: 5.000917 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1623723053; select sleep(5); # Time: 2021-06-15T02:14:47.427602Z # User@Host: root[root] @ localhost [] Id: 3 # Query_time: 2.000813 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1623723287; select sleep(2);
Slow query log parameter configuration
[mysqld] ## slow log slow_query_log=1 slow_query_log_file=/mdata/mysql_test_data/k8s001-slow.log long_query_time=2 min_examined_row_limit=100 #sql records with less than this value will not be scanned log_queries_not_using_indexes #Record sql without index log_throttle_queries_not_using_indexes=5 #Limit the number of sql records per minute log_slow_admin_statements=1 #Enable slow query log on the host log_slow_slave_statements=0 #Turn off slow query log from the machine log_timestamps=system #Time zone information [UTC|system] log_output=file #Specify the form of slow query log file [FILE|TABLE|NONE]
5, General log file
General log: records the established client connection and executed statements.
It can record all relevant operations of the database, which will lead to significant performance degradation. Therefore, it is generally not opened.
## View the general log. Default file name: machine name Log is off by default (root@localhost)[(none)]> show variables like '%general%'; +------------------+-----------------------------------+ | Variable_name | Value | +------------------+-----------------------------------+ | general_log | OFF | | general_log_file | /mdata/mysql_test_data/k8s001.log | +------------------+-----------------------------------+ 2 rows in set (0.01 sec) ## Open general log (root@localhost)[(none)]> set global general_log=1; Query OK, 0 rows affected (0.01 sec) ## Clear general log (root@localhost)[(none)]> flush general logs; Query OK, 0 rows affected (0.00 sec)
Usage scenario: bank audit; It is used to test the principle of understanding (for example, if you want to understand the principle of modifying the table schema online, you can open the general log to analyze the operations performed by the database).
6, Other important documents
- Binlog log
Binlog log, also known as binary log, records all statements that change data. Also used for replication. It records binary information.
Enable binlog in the configuration file
## bin log binary log log-bin=mysql-bin
Check whether Binlog log is enabled and log parameter sync_binlog
(root@localhost)[(none)]> show variables like '%log_bin%'; +---------------------------------+----------------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------------+ | log_bin | ON | | log_bin_basename | /mdata/mysql_test_data/mysql-bin | | log_bin_index | /mdata/mysql_test_data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------------------------+ 6 rows in set (0.00 sec) (root@localhost)[(none)]> show variables like "%sync_binlog%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 1 | +---------------+-------+ 1 row in set (0.00 sec) ## sync_binlog # 0, default. After the transaction is committed, the binary log is written to the disk from the buffer without flushing (fsync()), and only the operating system buffer is written. If the operating system goes down, some binary logs will be lost. # 1. After the transaction is committed, the binary file is written to the disk and the refresh operation is performed immediately, which is equivalent to writing to the disk synchronously without going through the cache of the operating system. # N. A refresh operation is performed every n writes to the operating system buffer.
- Redolog log
Redo log: when the data changes, InnoDB will record it in the redolog and update the memory. InnoDB will record it to the disk when appropriate.
Redo logs are transaction logs, which ensure persistence.
redo log is usually a physical log, which records the physical modification of the data page, rather than the modification of a row or rows. It is used to recover the submitted physical data page (recover the data page, and can only recover to the last submitted position).
View Redo log parameter innodb_flush_log_at_trx_commit
(root@localhost)[(none)]> show variables like "%flush%trx%"; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row in set (0.00 sec) ## innodb_flush_log_at_trx_commit # 0 means that when the transaction is committed, the data in the redo log buffer is not immediately flushed into the disk file, but depends on the main thread of InnoDB to refresh to the disk once a second. At this time, you may commit the transaction, and as a result, mysql goes down, and then all the data in the memory is lost. # 1. It means that when the transaction is committed, the redo log must be flushed from memory to the disk file. As long as the transaction is committed successfully, the redo log must be in the disk. Note that because of the "delayed write" feature of the operating system, the swipe in at this time is only written to the buffer of the operating system, so the synchronization operation can ensure that it is persistent to the hard disk. # 2. When committing a transaction, write the redo log to the os cache corresponding to the disk file instead of directly entering the disk file. The data in the os cache may not be written to the disk file until 1 second later.
The difference between binlog and redolog
1. Binary logs are generated at the upper level of the storage engine. No matter what storage engine it is, binary logs will be generated when the database is modified. The redo log is generated by the innodb layer and only records the modifications of the tables in the storage engine. And the binary log is recorded before the redo log.
2. The method of binary logging operation is a logical statement. redolog is a log in physical format. It records the changes of each page in the database. It supports concurrent changes.
3. The binary log is only written to the log "file" in the cache at one time when each transaction is committed (for non transaction table operations, it is written directly after each successful execution of the statement). The redo log is written into the redo log in the cache before the data is ready for modification, and then the data in the cache is modified; In addition, when issuing the transaction commit instruction, it is guaranteed to write the log to the redo log in the cache first, and then execute the commit action after the write is completed.
4. The transaction log records the situation of physical pages, which is idempotent, so the way of logging is extremely concise. Idempotency means that the state before and after multiple operations is the same. For example, if a row is newly inserted and then deleted, the state before and after the operation does not change. The binary log records all the operations that affect the data, and there are many records. For example, insert a row of records once and delete the row again.
- Undolog log
Undo log: it will be backed up to Undolog before operating data. If the rollback statement is used, it can be restored to the original state.
Undo log is a transaction log, which ensures atomicity.
Undo is used to roll back a row record to a version. undo log is generally a logical log, which is recorded according to each line of records.
For example, when deleting a record, Undolog will record the insert statement
When insert ing a record, Undolog will record the delete statement
When updating a record, Undolog will record an opposite statement
Check whether the Undolog log is enabled.
(root@localhost)[(none)]> show variables like "%undo%"; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | innodb_max_undo_log_size | 1073741824 | | innodb_undo_directory | ./ | | innodb_undo_log_truncate | OFF | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | +--------------------------+------------+ 5 rows in set (0.01 sec)
- sync_binlog and innodb_flush_log_at_trx_commit
sync_binlog
0, default. After the transaction is committed, the binary log is written to the disk from the buffer without flushing (fsync()), and only the operating system buffer is written. If the operating system goes down, some binary logs will be lost.
1. After the transaction is committed, the binary file is written to the disk and the refresh operation is performed immediately, which is equivalent to writing to the disk synchronously without going through the cache of the operating system.
N. A refresh operation is performed every n writes to the operating system buffer.
innodb_flush_log_at_trx_commit
0 means that when the transaction is committed, the data in the redo log buffer is not immediately flushed into the disk file, but depends on the main thread of InnoDB to refresh to the disk once a second. At this time, you may commit the transaction, and as a result, mysql goes down, and then all the data in the memory is lost.
1. It means that when the transaction is committed, the redo log must be flushed from memory to the disk file. As long as the transaction is committed successfully, the redo log must be in the disk. Note that because of the "delayed write" feature of the operating system, the swipe in at this time is only written to the buffer of the operating system, so the synchronization operation can ensure that it is persistent to the hard disk.
2. When committing a transaction, write the redo log to the os cache corresponding to the disk file instead of directly entering the disk file. The data in the os cache may not be written to the disk file until 1 second later.
For online environments, MySQL is generally based on the active and standby architecture. In order to ensure no data loss and maximum consistency between active and standby, these two parameters need to be set to 1. Even if the performance of some databases is sacrificed, security is the most important.