Data files and other files first recognized by mysql

Mysql first-recognized data files and other file data files are stored in mysql. the path specified by datadir in cnf may vary with the file formats and number of table files generated by the table engine. Mysql has multiple table engines and different table extensions. For example, innodb uses ". ibd" and archive uses ". arc extension, csvuses. CSV. Myisam Table engine myisam tables generate three files: MYI, MYD, and frm files. frm files mainly store table metadata (meta), including table structure definition information. No matter which table engine has an frm file, it is stored in the database data directory.. MYI file (index file): Mainly stores the index information of myisam which can be cached. in the MYI file, each myisam table corresponds to one. myi file, storage location and. same as frm and MYI. When using the innodb engine, the innodb engine must understand independent and shared tablespaces. Independent tablespace: each table is generated and stored in an independent file. Each table has a. frm description file and A. ibd file. This file contains the data and index content of a single table. By default, it is stored in the directory specified by mysql. Advantages and disadvantages of independent tablespace: Each table has its own independent tablespace. Data and indexes of each table are stored in each independent tablespace; A single table can be migrated in different data; tablespaces can be recycled (except for the drop table operation, empty tables cannot be recycled by themselves); drop table operation automatically recycles tablespaces, for statistical analysis or daily value tables, you can use alter table tablename engin = innodb to reduce unnecessary space after deleting a large amount of data; for innodb that uses inodb-plugin, truncate table will use space shrinkage .; No matter how you delete an independent tablespace, The tablespace fragments are not too serious. Disadvantage: A large increase in a single table, such as over 100 GB. If a single table grows too much, you can separate the files using the shared tablespace, but there is also a problem. If the access range is too large, multiple files will be accessed, it will be slow. There is also a solution for standalone tablespaces: to use a partition table, you can also move that large tablespace to another space and then make a connection. In fact, from the perspective of performance, when a table contains more than 100 GB, the response may be slow, and it is easy to detect problems in the independent tablespace. Shared tablespace: All table data of a database is stored in one file. By default, the file path of the shared tablespace is under the data directory, and the default file name is bata1, the initialization is 10 MB. Advantages and disadvantages of a shared tablespace: The tablespace can be divided into multiple files and stored on each disk (the tablespace file size is not limited by the table size, for example, one table can be distributed on different files ), put data and files together for convenient management. Disadvantage: all data and indexes are stored in one file, which will be a very large file in the future. Although a large file can be divided into multiple small files, however, multiple tables and indexes are stored in a table space in a hybrid manner. After a large number of deletion operations are performed on a table, there will be a lot of gaps in the table space, in particular, applications such as statistical analysis and daily value systems are not suitable for sharing tablespaces. How to enable independent tablespace? Check whether the standalone tablespace is Enabled:

mysql> show variables like '%per_table';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | OFF   |+-----------------------+-------+

 

Enable settings: Add innodb_file_per_table = 1 under the [mysqld] node in the my. cnf file. Note:

The innodb_file_per_table value can be modified, but it does not affect the previously used shared tablespace, unless you manually modify it or use innodb_file_per_table = 1 to use the exclusive tablespace innodb_file_per_table = 0 to change the data storage location of the exclusive empty tablespace using the shared tablespace innodb_data_home_dir = "C: \ mysql \ data \ "innodb_log_group_home_dir =" C: \ mysql \ data \ "innodb_data_file_path = ibdata1: 10 M: autoextendinnodb_file_per_table = 1

 

Parameter description: This configuration configures a single file with an expandable size of 10 MB, called ibdata1. No file location is provided, so it is in the MySQL data directory by default. [Data initialization settings]

Innodb_data_home_dir indicates that innodb_log_group_home_dir is the directory where the database files are stored. innodb_file_per_table indicates whether to use the shared and exclusive tablespace.

 

The preceding parameters must be added together. Note that InnoDB does not create a directory for parameters. Therefore, before starting the server, make sure that the "configured path directory" exists. This is true for any log file directory you configured. Use the mkdir command of Unix or DOS to create any required directories. By deploying the value of innodb_data_home_dir to the data file name in the original format, and adding a slash or backslash as needed, InnoDB forms a directory path for each data file. If the innodb_data_home_dir option is not mentioned in my. cnf, the default value is the "dot" directory./, which means the MySQL data directory. Replication-Related File 1.master.info the master.info file is stored in the data directory of the slave end, which stores the relevant information read by the master end of the slave, including the host location of the master, connect the user, password, port, current log location, read log location, and other information. 2. the relay log and relay log index mysql-relay-bin.xxxxxn files are used to store the BinaryLog Information read by the Slave I/O thread from the Master, then, the Slave SQL thread reads and parses the corresponding log information from the relaylog, converts it to the SQL statement executed by the Master, and then applies it on the Slave end. The functionality of a mysql-relay-bin.index file is similar to that of a mysql-bin.index, which is also the absolute path where the log is stored, except that it does not record BinaryLog, but RelayLog. 3. The relay-log.info file is similar to master.info, which stores information related to the relaylog written to the local through the Slave I/O thread. The SQL thread on the Slave side and some management operations can obtain the information about the current replication at any time. Other files 1. the system configuration file of systemconfigfile MySQL is generally "my. cnf, which is stored in the "/etc" directory by default in Unix/Linux. Windows is generally stored in the "c:/windows" directory. "My. the cnf file contains multiple parameter groups. Each parameter array is given a fixed group name through brackets, for example, the "[mysqld]" group contains the initialization parameters when the mysqld service is started. The "[client]" group contains parameters that can be read by the client tool, in addition, there are other specific parameter groups for each client software, such as "[mysql]" used by mysql programs, "[mysqlchk]" used by mysqlchk, and so on. If you write a client program by yourself, you can also set a parameter group name, configure the relevant parameters in it, and then call the parameters in the mysql client api program to read the relevant parameters from the api. 2. pidfilepid file is a process file of the mysqld application in Unix/Linux environment. Like many other Unix/Linux server programs, it stores its own process id. 3. The socketfile socket file is also available in Unix/Linux environments. in Unix/Linux environments, you can directly use UnixSocket to connect to MySQL without using a TCP/IP network.