MySQL primary data files and other files

In MySQL, the data file is stored in the path specified in My.cnf DataDir, the table engine used by the different file formats, the number of table files will vary. MySQL has a variety of table engines, the table extension is not the same, such as InnoDB with " .IBD", archive with ".arc ", CSV with ". csv" and so on. The  myisam table engine MyISAM table produces three files: MYI, MYD, frm files  frm files: metadata (meta) of the primary storage table, including table structure definition information, and so on. Whichever table engine has a frm file, it is stored in the database's data directory.  . Myi file (index file): The main storage MyISAM index information can be the content of the cache mainly from. Myi file, each MyISAM table corresponds to a. myi file, stored in the same location as the. frm, myi.  innodb engine   When using the InnoDB engine, you need to understand the stand-alone table space and the shared table space.   Stand-alone tablespaces: Each table is generated as a separate file store, one for each table. frm's description file, and a. ibd file. This file includes the data and index contents of a single table, which is stored by default in the directory specified by MySQL.   Stand-alone table space advantages and disadvantages:  Advantages: Each table has its own independent table space, each table data and indexes are stored in separate table space, you can implement a single table in different data migration; Table space can be recycled (except the drop table operation, the table empty cannot be reclaimed by itself) The drop table operation automatically reclaims the tablespace, if the statistical analysis or the daily value of the table, delete a large amount of data can be passed: ALTER TABLE TableName ENGIN=INNODB the space to be retracted , using TRUNCATE table for INNODB using inodb-plugin uses space shrinkage. , for the use of stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace is not too serious.   Disadvantage:  single table increased too large, such as more than 100G. For single-table growth problems, if you use shared tablespace can separate files, but there is also a problem, if the scope of access is too large to access multiple files, it will be slower. There is also a workaround for stand-alone table spaces: Using partitioned tables, you can also move that large table space to a different space and then make a connection. In fact, from a performance point of view, when a table more than 100 g may respond is also slower, for the independent table space is also easy to find problems early processing.   Shared tablespaces: one databaseAll the table data, index files are all in a file, the default file path for this shared tablespace in the data directory, the default file name is Bata1, initialized to 10M.   Sharing table spaces advantages and Disadvantages   Advantages: You can divide the tablespace into multiple files on each disk (the Tablespace file size is not limited by the size of the table, such as a table can be distributed across different files), and the data and files are conveniently managed together.   Disadvantage: All the data and index into a file, in the future will be a large file, although a large file can be divided into several small files, but multiple tables and indexes in the table space mixed storage, so that a large number of delete operations table space will have a large number of gaps, especially for statistical analysis, Such applications as day-value systems are most unsuitable for sharing table spaces.    How do I open a stand-alone table space?   See if a single-production table space is turned on: mysql> show variables like '%per_table '; +-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | OFF   |+-----------------------+-------+  settings on: Add innodb_file_per_table=1  Note under the [MYSQLD] node in the my.cnf file:  innodb_file_per_table values to be modified, but not for shared table spaces that were previously used, unless manually modified or innodb_file_per_table=1 to use exclusive tablespace innodb_ File_per_table=0 to modify the data storage location for exclusive empty table spaces using shared tablespace Innodb_data_home_dir = "C:\mysql\data\" innodb_log_group_home_dir = "c \ Mysql\data\ "innodb_data_file_path=ibdata1:10m:autoextendinnodb_file_per_table=1 parameter description: This setting configures a single file with an expandable size of 10MB , named Ibdata1. Not given the location of the file, so the default is the number in MySQLWithin the directory. "Settings for data initialization" Innodb_data_home_dir               represents the directory where the database files are stored Innodb_log_group_ Home_dir       Log directory innodb_file_per_table               whether shared and exclusive tablespaces are used to Several parameters of   above must be joined together. For some notes on the parameters InnoDB does not create a directory, so make sure that the "configured path directory" does exist before starting the server. This is true for any log file directory you have configured. Use the UNIX or DOS mkdir command to create any required directories.   forms a directory path for each data file by simply deploying the Innodb_data_home_dir value to the data file name and adding slashes or backslashes where needed. If the Innodb_data_home_dir option is not mentioned in my.cnf at all, the default value is "dot" directory./, this means MySQL data directory.    Replication Related Documents1.master.info file master.info file stored in the slave end of the data directory, which is stored in the slave corresponding to the master side of the relevant information read, including Master's host ground, connect users, passwords, ports, the current log location, Information such as the log location that has been read. The 2.relay log and relay log index MYSQL-RELAY-BIN.XXXXXN files are used to store the Binarylog information that the I/O thread on the slave side reads from the Master side, The slave-side SQL thread then reads and parses the corresponding log information from the Relaylog, translates it into the SQL statement executed by master and then applies it on the slave side. The function of the Mysql-relay-bin.index file is similar to Mysql-bin.index, which is also the absolute path where the log is stored, except that the Binarylog is not recorded, but Relaylog. 3. The Relay-log.info file is similar to Master.info, which holds information about the Relaylog that is written to the local through the slave I/O thread. SQL threads for the slave end and some administrative operations to get information about the current replication at any time Other Files1, systemconfigfile mysql system configuration file is generally "my.cnf", Unix/linux under the default in "/etc" directory, the Windows environment is generally stored under the "c:/windows" directory. The "my.cnf" file contains a variety of parameter option groups (group), each of which is given a fixed group name through brackets, such as the "[Mysqld]" group includes the initialization parameters of the Mysqld service startup, "[Client]" The group contains parameters that the client utility can read, and there are other specific parameter groups for each client software, such as "[MySQL]" used by the MySQL program, "[Mysqlchk]" used by mysqlchk, and so on. If the reader friend wrote a client program, you can also set a parameter group name, configure the relevant parameters inside, and then call the MySQL Client API program read the parameter reading API read the relevant parameters. 2, Pidfilepid file is a mysqld application in the Unix/linux environment of a process files, and many other Unix/linux server-side programs, like the storage of their own process ID. 3, Socketfile socket file is also in the Unix/linux environment, the user in the Unix/linux environment, the client connection can not use the TCP/IP network directly using Unixsocket to connect to MySQL.

MySQL primary data files and other files