mysql files, logs

1. Mysql files
Mysql database contains the following file types:
a. Data file (datafile): stores the specific data files in the table.
b. Data dictionary: records information about all Innodb tables in the database.
c. Redo log (redolog): a file that records database changes. It is used for recovery operations after a system abnormal crash (power outage). Multiple configurations can be configured, such as: ib_logfile0, ib_logfile1.
d. Rollback Log (undolog): There is also the ibdata file of mysql, and the user records the rollback operation of the transaction.
e. Archive log (binlog): After the transaction is submitted, it is recorded in the archive log.
f. Other logs: slowlog, errorlog, querylog.

2. Mysql log:
a.binary log: records all statements that change data. Also used for replication
Function: recovery, replication, auditing.
Open method:
[mysqld]
log-bin =mysql-bin
Binary log format: --binlog-format={row |STATEMENT| MIXED}
statement: statement-based recording
row: Row-based records will clearly record the changes in each row of the table. Use this logging method
MIXED: Mixed logging. By default, statement-based logging can be automatically switched to row-based logging under special circumstances.
Note: Prior to MySQL 5.7.7, statement-based logging The record format is the default. In MySQL 5.7.7 or later, row-based logging format is the default.
Parameters that affect binary logging behavior:
max-binlog-size:
binlog-cache-size
sysnc-binlog
binlog-do-db
binlog-ignore-db
log-slave-update
binlog-format
mysql-bin.index: The file is a binary log. The function of index is to record the absolute paths of all binary logs to ensure that various MySQL threads can successfully find them based on it. All required binary log files.
b.Error log Error log
1. Record the error information generated during server operation
2. Record the information generated when the service is started and stopped
3. When the replication process is started on the slave server, the information of the replication process will also be recorded
Enable error log: By default, the system will automatically start it
In the main configuration file:
log-error= Specify the location of the error log. The myaql user must have write permission in this location.

Error level:
ERROR_LEVEL-->Error level
WARNING_LEVEL-->Warning level
NOTE_LEVEL-->Information level

c.General-log
General query log: records the established client connection and executed statements

d.slow log Slow log: The default is 10s long_query_time = 10s. It is recommended to change it to 1s
Record all queries whose execution time exceeds long_query_time seconds or queries that do not use indexes
2. Detailed explanation of commonly used parameters:
Note: Modify the following parameters and need to re- It will take effect only after starting the database service.
slow_query_log=off|on --Whether to turn on slow query log
slow_query_log_file=filename --Specify the save path and file name, the default is the data file directory, hostname-slow.loglong_query_time=2 --Specify how many seconds to return the query The result is slow query
long-queries-not-using-indexes　--Record all query statements that do not use indexes
min_examined_row_limit=1000　--Record those slow queries caused by searching more than 1000 times
long-slow-admin-statements　　　　　 --Record those slow optimize table, analyze table and alter table statements
log-slow-Slave-statements　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ductdie], `` analyze table, and 　　　alter table statements that are slow in the optimize table, alter table statements

log-slow-Slave-statements　　　　​​​​​​​​​​​​​​​​-Record those slow queries generated by Slave

The above is the content of the mysql file and log. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!

###