MySQL architecture consists of physical files. 1. Log Files. 1. error logs: ErrorLog content: all serious warnings and error messages during MyQLServer running, and detailed information about each start and shutdown of MySQLServer. Path: name of the default storage location in the Data Directory: the default file name is named hostname. err. Modify:
MySQL architecture consists of physical files. 1. Log File 1. Error Log: Error Log content: all serious warnings and Error messages during MyQL Server running, detailed information about each MySQL Server startup and shutdown. Path: name of the default storage location in the Data Directory: the default file name is named hostname. err. Modify:
MySQL architecture composition: physical file Composition
I. Log Files
1. Error Log: Error Log
Content: All severe warnings and error messages during MyQL Server running, as well as detailed information about each MySQL Server startup and shutdown.
Path: the default storage location is in the data directory.
Name: the default file name is hostname. err.
Modify: "-- log-error [= file_name]" to modify the Directory and file name.
Extension: the flush logs Command tells MySQL to back up old log files and generate new log files. The backup file name ends with ". old.
2. Binary Log: Binary Log & Binary Log Index
Content: MySQL records all queries (query statements, execution time, resources consumed, and related transaction information) that modify Database Data to log files in binary format.
Path: the default storage location is in the data directory.
Name: mysql-bin. ****** (* Indicates 0 ~ A number between 9 to indicate the log serial number)
Modify: "-- log-bin [= file_name]" to modify the Directory and file name.
Extended: "-- max_binlog_size" sets the maximum storage limit of binlog.
"-- Binlog-do-db = db_name" records binlog for a (db_name) Database
"-- Binlog-ignore-db = db_name" ignores the binlog records of a (db_name) database.
Note: db_name is not the database where the data updated by the query statement is located, but the database where the current connection is located.
The mysql-bin.index file (binary log index) function is to record the absolute paths of all Binary logs, ensuring that various MySQL threads can find all the Binary Log files needed based on it smoothly.
3. update log: update log
MySQL no longer supports log updates since MySQL 5.0.
4. query log: query log
Content: record all the queries in MySQL (including all the select statements, which are large in size and affect performance after being enabled. Use them with Caution !)
Path: the default storage location is in the data directory.
Name: the default file name is hostname. log.
Modify: "-- log [= fina_name]" to modify the Directory and file name.
Extension: this function is generally only used to track certain special SQL Performance problems.
5. slow query log: slow query log
Content: Slow query logs record a query with a long execution time
Path: the default storage location is in the data directory.
Name: the default file name is hostname-slow.log
Modify: "-- log-slow-queries [= file_name]" to modify the Directory and file name.
Extension: the slow query log uses a simple text format. You can view the content in the log in a variety of text editors. It records the statement execution time, execution time, execution user, connection host, and other related information.
MySQL also provides the tool program mysqlslowdump, which is used to analyze full query logs, to help database administrators solve possible performance problems.
6. Innodb online redo log: innodb redo log
Content: The redo log records all the physical changes and transaction information made by Innodb. With the redo log and undo information, Innodb ensures transaction security under any circumstances.
Path: the default storage location is in the data directory.
Name: ib_logfile ****** (* Indicates 0 ~ A number between 9 to indicate the log serial number)
Modify: Use innodb_log_group_home_dir to change the log storage location and set the log quantity through innodb_log_files_in_group.
Extension: unavailable for the moment
Ii. Data Files
1. ". frm" File
Table-related metadata (meta) information is stored in the ". frm" file, including the definition information of the table structure (one-to-one with the table ).
2. ". MYD" File
The ". MYD" file is dedicated to the MyISAM storage engine and stores data in the MyISAM table (one-to-one with the table ).
3. ". MYI" File
The ". MYI" file is also dedicated to the MyISAM storage engine, which stores the index-related information of the MyISAM table (one-to-one with the table ).
4. ". ibd" and ibdata files
Both types of files are files that store Innodb data. They are used to store Innodb data (including indexes ), the reason is that the Innodb data storage method can be configured to decide whether to use the shared table (ibdata file) space to store the stored data or exclusive table (. ibd file, one-to-one with table) storage space for stored data.
Iii. Replication Related Files
1. master.info File
The master.info file is stored in the data directory of the Slave, which stores information about the Master of the Slave, including the Master Address, connection user, connection password, connection port, and current log location, the location of the log that has been read.
2. relay log and relay log index
The mysql-relay-bin.xxxxxn file is used to store the Binary Log information read by the Slave I/O thread from the Master, and then the Slave SQL thread reads and parses the corresponding log information from the relay Log, converts the SQL statement executed by the Master node and then applies it on the Slave side.
The functionality of a mysql-relay-bin.index file is similar to that of a mysql-bin.index, and it is also the absolute path where the Log is stored, but it is not a Binary Log, but a Relay Log.
3. relay-log.info files
Similar to master.info, it stores information related to the relay log written to the local through the Slave I/O thread. The SQL thread on the Slave side and some management operations can obtain information about the current replication at any time.
4. other files
1. system config file
MySQL system configuration files are generally "my. cnf, which is stored in the "/etc" directory by default in Unix/Linux. Windows is generally stored in the "c:/windows" directory.
2. pid file
Pid file is a process file of mysqld applications in Unix/Linux environments. Like many other Unix/Linux server programs, it stores its own process id.
3. socket file
Socket files are also available in Unix/Linux environments. in Unix/Linux environments, you can directly use Unix Socket to connect to MySQL without using a TCP/IP network.
