Understanding MySQL physical files

mysql tutorial column introduces MySQL physical files.

1. The data storage file of the database

The MySQL database will create a database named under the data directory folder, used to store table file data in the database. Different database engines have different extensions for each table, for example: MyISAM uses “.MYD” as the extension, Innodb uses “.ibd”, Archive uses “.arc”, and CSV uses “.csv”.

1. “.FRM” file

Before 8.0, no matter what kind of storage engine, after creating a table, one will be generated to indicate the name ‘.frm’ files. The frm file mainly stores the data information related to the table, mainly including the definition information of the table structure. When the database crashes, the user can restore the data table structure through the frm file.

2. “.MYD” file

The “.MYD” file is dedicated to the MyISAM storage engine and stores the data of the MyISAM table. Each MyISAM table will have a “.MYD” file corresponding to it, which is also stored in the folder of the database to which it belongs, together with the “.frm” file.

3. “.MYI” file

The “.MYI” file is also dedicated to the MyISAM storage engine, mainly storing the index related to the MyISAM table information. For MyISAM storage, the content that can be cached mainly comes from the “.MYI” file. Each MyISAM table corresponds to a “.MYI” file, which is stored in the same location as “.frm” and “.MYD”.

4. “.ibd” file and “.ibdata” file

These two files are files that store Innodb data. There are two types of files to store Innodb data (including indexes), because Innodb’s data storage method can be configured to determine whether to use shared tablespaces to store stored data or exclusive tablespaces to store stored data. The exclusive tablespace storage method uses “.ibd” files to store data, and each table has one “.ibd” file, and the files are stored in the same location as the MyISAM data. If the shared storage tablespace is used to store data, it will be stored in an ibdata file, and all tables share one (or multiple, self-configurable) ibdata files.

The ibdata file can be configured by innodb_data_home_dir (data storage directory) and innodb_data_file_path (configure the name of each file) to form innodb_data_file_path Multiple ibdata files can be configured at one time #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend The configuration methods of shared tablespace and exclusive tablespace are both for data storage .

Shared table space: All table data and index files of a certain database are placed in one file.

Exclusive table space: Each table will be generated as an independent file for storage. Each table has a .frm table description file and a .ibd file. Among them, this file includes the data content and index content of a single table.

4.1 Comparison between the two

Shared table space:

Advantages: You can put tables The space is divided into multiple files and stored on each disk. Data and files are put together for easy management.

Disadvantages: All data and indexes are stored in one file, and multiple tables and indexes are mixed in the table space, so that after a large number of delete operations are performed on a table, there will be a large number of files in the table space Gaps, especially for statistical analysis, applications such as log systems are not suitable for shared table space.

Independent table space:

Advantages:

1. Each table has its own independent table space .

2. The data and indexes of each table will exist in its own table space.

3. You can move a single table in different databases.

4. The space can be reclaimed

   a) Drop table operation automatically reclaims the table space, if it is for statistical analysis or daily value table , after deleting a large amount of data, you can pass: alter table TableName engine=innodb ; Reclaim unused space.

   b) For a table that uses an independent table space, no matter how you delete it, the fragmentation of the table space will not seriously affect the performance, and there is still a chance to deal with it. Disadvantage: The increase of a single table is too large, such as exceeding 100 G. In comparison, the efficiency and performance of using an exclusive table space will be higher. Conversion between shared table space and independent table space.

show variables like "innodb_file_per_table"; ON represents independent table space management, OFF represents shared table space management;
 Modify the table space management method of the database Just modify the parameter value of innodb_file_per_table, but the modification cannot affect the previous
 Used shared tablespaces and separate tablespaces;
 innodb_file_per_table=1 is to use exclusive table space
 innodb_file_per_table=0 is to use shared table space

2. Log

Log file: query log, slow query log, error log, Transaction log, binlog log, error log, relay log

2.1 Query log

Query log is called in mysql It is general log (general log), don’t be misled by the name of “query log”, it is wrong.# Configure in the configuration file
[mysqld]
log-error=dir/{filename}

2.4 Binary Log

Binary Log can also be called Update Log ), is a very important log in MySQL. It is mainly used to record changes in the database, that is, DDL and DML statements of SQL statements, and does not include data record query operations.

# Check whether the binary log is enabled, the binary log is disabled by default
 show variables like "log_bin";
 # In MySQL, you can turn on the binary file log in the configuration file
 [mysqld]
 log-bin=dir/{filename} 

Among them, the dir parameter specifies the storage path of the binary file; the filename parameter specifies the file name of the binary file, and its format is filename.number, and the format of number is 000001, 000002, etc. Every time the MySQL service is restarted, a new binary log file will be generated. The filename part of the file name of these log files will not change, and the number will continue to increase.

There are three formats of binary logs: STATEMENT, ROW, MIXED.

① STATEMENT mode (SBR)

Every sql statement that modifies data will be recorded in binlog.  The advantage is that there is no need to record the data changes of each sql statement and each row.
 Reduce the amount of binlog logs, save IO, and improve performance.  The disadvantage is that in some cases it will lead to data inconsistency in the master-slave (such as
  sleep() function, last_insert_id() , and user-defined functions (udf) will have problems) 

② ROW mode (RBR)

Do not record the context information of each sql statement, only need to record which data has been modified and how it has been modified.  and not in certain cases
 The problem that the stored procedure, or function, or trigger call and trigger cannot be copied correctly.  The disadvantage is that it will generate a lot of logs, especially
 When alter table, the log will skyrocket.  

③ MIXED mode (MBR)

Mixed use of the above two modes, general replication uses STATEMENT mode to save binlog, for STATEMENT  Mode cannot be replicated using the
  ROW mode saves binlog, and MySQL will choose the log saving method according to the executed SQL statement.  

binlog replication configuration

In the mysql configuration file my.cnf or, you can configure through the following options binary log

binlog_format = MIXED //binlog log format, mysql uses statement by default, it is recommended to use mixed
 log-bin = mysql-bin //binlog log file
 expire_logs_days = 7 //binlog expiration cleanup time
 max_binlog_size = 100m //binlog each log file size
 binlog_cache_size = 4m //binlog cache size
 max_binlog_cache_size = 512m //Maximum binlog cache size
 server-id = 1 

2.5 Basic operation of binary files

1. You can use the following command to check which binary logs are in MySQL File: show binary logs

2. show master status command is used to view the current binary log;

3. The binary log is stored in binary format and cannot be opened and viewed directly. If you need to view the binary log, use the show binlog events in 'mysql-bin.000001'; command.

4. Delete binary files

   1. All binary logs that can be deleted using the RESET MASTER statement
   2. Each binary log file is followed by a 6-digit number, such as 000001. Use the PURGE MASTER LOGS TO 'filename.number' statement to delete the log before the number of the specified binary log
   3. Use PURGE MASTER LOGS TO & #39;yyyy-mm-dd hh:MM:ss' statement, you can delete the binary log created before the specified time

5. use Binary file recovery data

   1. Create database

      CREATE TABLE `33hao_activity` (
         `activity_id` mediumint(9) NOT NULL AUTO_INCREMENT COMMENT 'id',
         `activity_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'title',
         `activity_type` enum('1','2') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Activity type 1: Commodity 2: Group purchase',
         `activity_banner` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Activity banner big picture',
         `activity_style` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'activity page template style identifier code',
         `activity_desc` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'description',
         `activity_start_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'start time',
         `activity_end_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'end time',
         `activity_sort` tinyint(1) UNSIGNED NOT NULL DEFAULT 255 COMMENT 'sort',
         `activity_state` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT ' Activity state 0 is off, 1 is on',
         PRIMARY KEY (`activity_id`) USING BTREE
       ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'active table' ROW_FORMAT = Compact; 

   2. Add 2 pieces of data

      INSERT INTO `33hao_activity` VALUES (1, '2017 New Year's Eve gift event', '1&#39  ;, '05364373801675235.jpg', 'default_style', '', 1483113600, 1483286400, 0, 0);
       INSERT INTO `33hao_activity` VALUES (2, ' Turntable lottery pop-up window', '1', '06480453986921327.jpg', ''  , & # 39; Turntable lottery pop-up window & # 39;, 1594656000, 1594915200, 0, 0); 

   3. Delete data

      drop table `33hao_activity`; 

   4. Restore data

      • According to the needs of the node, we can check the log file to provide Create statement nodes for our database creation, table creation, data addition, etc., so as to restore data.

        mysqlbinlog --start-position=154 --stop-position=2062 D:/phpstudy_pro/Extensions/MySQL5.7.26/data/mysql-bin.  000001 | mysql -uroot -p 

      • Restore data according to time

        mysqlbinlog --start-datetime=  '2020-09-27 22:22:22' --stop-datetime='2020-09-27 22:30:00' /www/server/data/mysql-bin  .000036 | mysql -uroot -p 

      • Execute binlog log directly

        mysqlbinlog /www/server/  data/mysql-bin.000036 | mysql -uroot -p 

More related free learning recommendations: mysql tutorial(video)

The above is the detailed content of understanding MySQL physical files , more please pay attention to other related articles on 1024programmer.com!

`activity_sort` tinyint(1) UNSIGNED NOT NULL DEFAULT 255 COMMENT ‘sort’,
`activity_state` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT ' Activity state 0 is off, 1 is on',
PRIMARY KEY (`activity_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'active table' ROW_FORMAT = Compact;

INSERT INTO `33hao_activity` VALUES (1, '2017 New Year's Eve gift event', '1&#39  ;, '05364373801675235.jpg', 'default_style', '', 1483113600, 1483286400, 0, 0);
 INSERT INTO `33hao_activity` VALUES (2, ' Turntable lottery pop-up window', '1', '06480453986921327.jpg', ''  , & # 39; Turntable lottery pop-up window & # 39;, 1594656000, 1594915200, 0, 0); 

drop table `33hao_activity`; 

More related free learning recommendations: mysql tutorial(video)

The above is the detailed content of understanding MySQL physical files , more please pay attention to other related articles on 1024programmer.com!