MySQL technology insider-InnoDB Storage engine-Reading Notes (2), mysql-innodb
MySQL technology insider-InnoDB Storage engine-Reading Notes (2)
Mysql is always indispensable for php development.
Series blog link http://itsong.net/articles/466.html
Chapter 3 files: mysql and innodb files
- Parameter file, configuration path, initialization parameter, memory size, etc.
- Log files, including error logs, binary logs, slow query logs, and query logs
- Socket file: The file required for connection using unix domain socket and unix domain socket. This is generally a local connection, which is faster than tcp.
- Pid file and process id file
- Table Structure file, which stores the table structure definition file
- Storage engine files. Each storage engine uses its own files to store data, including data and indexes.
*
Parameter file
musql --help | grep my.cnfWhich parameter files can be loaded?- Static Parameters cannot be changed throughout the instance life cycle, similar to read-only.
- Dynamic parameters, which can be changed using the set command
SET| [global | session] system_var_name = expr| [@@global. | @@session. | @@]system_var_name = expr
- Global and session indicate that the modification is based on the current session or the entire instance lifecycle.
Log Files
- Error Log,
show variables like 'log_error'Locate the error log. By default, the file name is the server host name. err
- Slow query log. The threshold value is modified through long_query_time. The default value is 10 seconds (meaning more than 10, excluding 10). By default, log_slow_queries is set to ON, mysqldumpslow can solve the problem of too many logs that are difficult to solve
- From 5.1, slow queries can be recorded in microseconds. In addition, the slow query will enter the TABLE, which is called mysql. slow_log. It is specified through log_output (FILE | TABLE ).
- Log_queries_not_using_indexes, which records slow query log files without indexes.
- Query logs. The default file name is host name. log. You can also enter the table, general_log
- A binary log that records all operations that change the database, excluding SELECT and SHOW operations. Including operation time and execution time. Mainly used for restoration and replication (master and slave Real-Time Synchronization)
- Binary log directory
show variables like 'datadir', The default file name is the host name, suffix is the serial number, bin_log.index is the binary index file, the storage log serial number
- The default binary log is not enabled, and the performance of binlog is reduced by 1%, but it is acceptable
- You need to use mysqlbinlog to view binlog. There are many operations in binlog, but I don't care much about it here.
Others
- Socket file, usually in/tmp, mysql. sock
- Pid file, host name. pid, variables is pid_file
- Table structure definition file, frm suffix file, record table structure definition and view definition, text file, can be directly cat
Innodb Storage engine File
- The stored data is stored in tablespaces. By default, a file named ibdata1 with an initial size of 10 MB is created. The default tablespace is used.
- After the innodb_data_file_path parameter is set, data in the table of the innodb Storage engine is recorded in this file.
- Set innodb_file_per_table. Each table based on the innodb Storage engine generates a tablespace. The table name is named. ibd, and the default tablespace is not entered.
- These independent tablespace files only store the table data, index, insert buffer, and other information. The remaining information is stored in the default tablespace.
Redo log files
- Ib_logfile0 and ib_logfile1 record the transaction logs of the innodb Storage engine.
- When each file is written to the end of the file, switch to another log file for writing. For higher reliability, you can set multiple Image Log groups.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
