MySQL Technology insider-INNODB Storage Engine-Reading notes (ii)
As PHP development, the use of MySQL is always indispensable
Series Article blog Link http://itsong.net/articles/466.html
Chapter III file MySQL with InnoDB several types of files
- parameter file, configuration path, initialization parameters, memory size, etc.
- Log files, including error log, binary log, slow query log, query log
- Socket file, a UNIX domain socket that is required to connect to a UNIX domains socket, which is generally a native connection, faster than the usual TCP
- PID file, Process ID file
- Table structure file, the file that holds the table structure definition
- Storage engine files, each storage engine does its own file to save data, including data and indexes.
*
Parameter file
musql --help | grep my.cnfYou can find which parameter files are loaded- Static parameters, the entire instance life cycle must not change, similar to read-only.
- Dynamic parameters, which can be changed by the SET command
SET| [global | session] system_var_name = expr| [@@global. | @@session. | @@]system_var_name = expr
- Global and session indicates whether the modification of this parameter is based on the current session or the life cycle of the entire instance.
Log file
- Error log,
show variables like ‘log_error‘ locating the error log, by default, the file name is the server host name. err
- Slow query log, the threshold value through long_query_time modification, the default value of 10 seconds (meaning is greater than 10, not including 10), the default does not start the slow query log, to set Log_slow_queries to ON, Mysqldumpslow can solve the problem that the log is too difficult to solve
- Starting with 5.1, slow queries can be recorded in microseconds. And the slow query will enter the table, called Mysql.slow_log, by Log_output (FILE | TABLE) specifies
- Log_queries_not_using_indexes, logging slow query log files that do not use indexes
- Query log, the default file name is host name. Log, you can also enter the table, General_log
- A binary log that records all operations that make changes to the database, excluding select and show operations. This includes information such as Operation time and execution time. Mainly used for recovery, replication (master,slave real-time synchronization)
- The directory where the binary log is located
show variables like ‘datadir‘ , the default file name is the hostname, the suffix is the serial number, the Bin_log.index is the binary index file, and the log sequence number is stored
- Default binary log is not turned on, binlog performance is reduced by 1%, but acceptable
- See Binlog There's a lot to do with Mysqlbinlog,binlog, but I don't care much about it here.
Other
- Socket file, General again/TMP, Mysql.sock
- PID file, host name. Pid,variables to Pid_file
- Table structure definition file, frm suffix name file, record table structure definition and view definition, text file, can be directly cat
InnoDB Storage Engine Files
- The stored data is stored as a tablespace, and by default there is a file with an initial size of 10MB, named Ibdata1, and a default tablespace.
- After you set the Innodb_data_file_path parameter, the data for the table that InnoDB the storage engine is recorded in the file.
- With innodb_file_per_table, each table based on the InnoDB storage engine produces a single table space, with the file name table name. IBD, it will not enter the default tablespace.
- These separate tablespace files store only the data for the table, index and insert buffers, and the rest of the information is stored in the default table space.
Redo log Files
- Ib_logfile0 and Ib_logfile1, which records the transaction log for the InnoDB storage engine
- Each file is written to the end of the file, switch to another log file to write, in order to achieve higher reliability, you can set up multiple mirror log groups
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Technology insider-INNODB Storage Engine-Reading notes (ii)
