Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the MySQL physics files are, which are very detailed and have certain reference value. Friends who are interested must finish reading them.

1. Data storage file of the database

The MySQL database creates a folder under the data directory with the name of the database to store the table file data in the database. Each table has a different extension for different database engines. For example, MyISAM uses ".MYD" as its extension, Innodb uses ".ibd", Archive uses ".arc", and CSV uses ".csv".

1. ".FRM" file

Regardless of the storage engine before 8.0, a '.frm' file named to indicate that a table is created must be generated. Frm file mainly stores the data information related to the table, 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 data from MyISAM tables. Each MyISAM table has a ".MYD" file corresponding to it, which is also stored in the folder of the database to which it belongs, along with the ".frm" file.

3. ".MYI" file

The ".MYI" file is also dedicated to the MyISAM storage engine and mainly stores the index-related information of the MyISAM table. For MyISAM storage, the content that can be cache is mainly 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 kinds of files are files for storing Innodb data. The reason why there are two kinds of files to store Innodb data (including indexes) is that the data storage method of Innodb can be configured to decide whether to use shared table space to store data or exclusive table space to store data. Exclusive tablespace storage uses ".ibd" files to store data, and each table has an ".ibd" file, which is stored in the same location as MyISAM data. If you choose a shared storage tablespace to hold the data, the ibdata file is used for storage, and all tables share a single (or multiple, self-configurable) ibdata file.

Ibdata files can be configured through two parameters: innodb_data_home_dir (data storage directory) and innodb_data_file_path (configure the name of each file). Multiple ibdata files can be configured at one time in innodb_data_file_path # innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend configuration, shared tablespaces and exclusive tablespaces are all based on the way data is stored.

Shared tablespaces: all the table data and index files in a database are placed in one file.

Exclusive tablespace: each table will be generated and stored as a separate file, with each table having a .frm table description file and an .ibd file. This file includes the data contents of a single table and the contents of the index.

4.1 comparison between the two

Shared tablespaces:

Advantages: you can divide the tablespace into multiple files and store them 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 tablespace, so there will be a lot of gaps in the tablespace after a large number of deletions to a table, especially for statistical analysis. Applications such as logging systems are the least suitable for shared tablespaces.

Independent tablespaces:

Advantages:

Each table has its own independent table space.

The data and indexes of each table are stored in its own tablespace.

It is possible to move a single table in different databases.

Space can be recycled.

A) the Drop table operation automatically reclaims tablespaces. For statistical analysis or daily value tables, you can delete a large amount of data by: alter table TableName engine=innodb; reclaim unused space.

B) for tables that use independent tablespaces, no matter how they are deleted, the fragmentation of the tablespaces will not seriously affect performance, and there is still a chance to deal with them. Disadvantages: the increase of single table is too large, such as more than 100G. In comparison, the efficiency and performance of using exclusive tablespaces leads to a higher conversion between shared and independent tablespaces.

Show variables like "innodb_file_per_table"; ON stands for independent tablespace management, OFF represents shared tablespace management; modify the database tablespace management method to modify the innodb_file_per_table parameter values, but the modification can not affect previously used shared tablespaces and independent tablespaces; innodb_file_per_table=1 for the use of exclusive tablespaces innodb_file_per_table=0 for the use of shared tablespaces copy code 2. Journal

Log files: query log, slow query log, error log, transaction log, binlog log, error log, relay log

2.1 query log

Query log is called general log (general log) in mysql. Don't be misled by the name of query log, mistakenly thinking that query log will only record select statements. In fact, query log records the commands executed by the database, regardless of whether these statements are correct or not, they will be recorded. I think this is the reason why general log is "universal". Since there may be a lot of database operation commands and are executed frequently, when the query log is turned on, the database may need to keep writing the query log, which will increase the IO pressure on the server and increase a lot of system overhead. So by default, the query log of mysql is not open, but opening the query log also helps us to analyze which statements are executed intensively. Whether the data corresponding to the execution of intensive select statements can be cached, and the query log can also help us to analyze the problem, so we can decide whether to open the query log according to the actual situation, and manually if necessary. If the query log is enabled, we can store the query log in the following three ways.

Method 1: store the query log in the specified log file.

Method 2: store the query log in the mysql.general_log table.

Method 3: store the query log in both the specified log file and the general_ log table of the mysql library.

Check whether the query log enables show VARIABLES LIKE 'general_log'; replication code

Show variables where variable_name like "general_log%" or variable_name= "log_output"; copy code

General_log: indicates whether the query log is enabled. ON: enabled. OFF: not enabled. Default is OFF.

Log_output: indicates how to store the query log after it is enabled. Log_output can be set to four values: "FILE", "TABLE", "FILE,TABLE" and "NONE".

# set the output mode of query log set global log_output= [none | file | table | file,table]; # set the log file path of general log set global general_log_file='/tmp/general.log';# enable general logset global general_log=on;# turn off general logset global general_log=off; copy code 2.2 slow log

The so-called slow query is set up to record SQL statements for more than a certain period of time!

Enable the slow log function of MySQL # check whether to enable SQL log query of unused index show variables like 'log_queries_not_using_indexes';# enable SQL log query of unused index set global log_queries_not_using_indexs=on/off;# check how long the query is recorded in the slow log show variables like' long_query_time' # set recording duration. 0: all records. After setting, restart set global long_query_time=10# to check whether mysql slow log function show variables like 'slow_qurey_log'# is enabled or disabled. Slow log set global slow_qurey_log=on/off;# view log location show variables like' slow_query_log_file';# log storage mode show variables like "log_output"; copy code flie

Select sleep (10) complete execution to view log

# Time: 2020-10-26T05:12:09.564006Z# User@Host: root [root] @ localhost [] Id: 1 percent Query_time: 10.000272 Rows_sent: 1 Rows_examined: 1SET timestamp=1603689119;select sleep (10); copy the code

Table mode

Log analysis tool mysqldumpslow

View help information for mysqldumpslow:

-s ORDER ORDER sort by (al,at,ar,c,l,r,t) "at" is the default value al: average lock time ar: average number of rows sent at: average query time c: count l: lock time r: sent lines t: query time-r Reverse sort order (largest last instead of first)-t NUM displays only the first n queries-a do not abstract all numbers to N Abstract a string into an abstract number with at least n numbers in the "S"-n NUM name-g PATTERN grep: only records containing this string are considered-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is'*' I.e. Match all-i NAME name of server instance (if using mysql.server startup script)-l don't subtract lock time from total time copy code 2.3error log

Error log (Error Log) is the most commonly used log in MySQL, which mainly records the message in the process of starting and stopping the MySQL server, the failure and abnormal situation of the server in the process of running, and so on.

# check the error log location show variables like "log_error"; # in MySQL, you can use the mysqladmin command to open a new error log to ensure the hard disk space on the MySQL server. The syntax of the # mysqladmin command is as follows: after mysqladmin-uroot-p flush-logs# executes the command, the MySQL server first automatically creates a new error log and then renames the old error log to filename.err-old. It can be deleted manually. # configure [mysqld] log-error=dir/ {filename} copy Code 2.4 binary log in the configuration file

Binary log (Binary Log), also known as change log (Update Log), is a very important log in MySQL. It is mainly used to record the changes of the database, that is, the DDL and DML statements of SQL statements, without data record query operations.

# check whether binary log log is enabled, and binary log log turns off show variables like "log_bin" by default; # you can turn on binary file log [mysqld] log-bin=dir/ {filename} copy code in MySQL.

The dir parameter specifies the storage path of the binary file, and the filename parameter specifies the file name of the binary file in the form of filename.number,number 000001, 000002, and so on. Each 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 for binary logs: STATEMENT,ROW,MIXED.

① STATEMENT Mode (SBR)

Each sql statement that modifies the data is recorded in binlog. The advantage is that there is no need to record the data changes of every sql statement and every row, which reduces the amount of binlog log, saves IO, and improves performance. The disadvantage is that in some cases, it can lead to inconsistent data in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf), etc.) copy code

② ROW Mode (RBR)

Instead of recording the context of each sql statement, you only need to record which piece of data has been modified and how it has been modified. And there is no problem that the calls and triggers of stored procedures, or function, or trigger, in certain cases can not be copied correctly. The disadvantage is that a large number of logs will be generated, especially in the case of alter table. Copy the code

③ MIXED Mode (MBR)

With the mixed use of the above two modes, the general replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog. MySQL will choose the log preservation method according to the SQL statement executed. Copy the code

Binlog replication configuration

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

Binlog_format = MIXED / / binlog log format, mysql defaults to statement It is recommended that you 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 copy code 2.5 binary basic operation

You can use the following command to see which binary log files are in MySQL: show binary logs

The show master status command is used to view the current binary log

Binary logs are 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.

Delete binaries

All binary logs that can be deleted using the RESET MASTER statement each binary log file is followed by a six-digit number, such as 000001. Using the PURGE MASTER LOGS TO 'filename.number' statement, you can delete logs before the number of specified binary logs. Using the PURGE MASTER LOGS TO' yyyy-mm-dd hh:MM:ss' statement, you can delete binary logs created before a specified time

Use binaries to recover data

Create a database

CREATE TABLE `33hao _ activity` (`activity_ id` mediumint (9) NOT NULL AUTO_INCREMENT COMMENT 'id', `activity_ title` varchar (255i) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT' title', `activity_ type`enum ('1' activity_') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'activity type 1: commodity 2: group purchase, `activity_ banner` varchar' activity banner 'big picture' `Style` varchar (1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'active page template style identification code', `activity_ date`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' `state` tinyint (1) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'activity state 0 is off and 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 Copy the code

Add 2 new pieces of data

INSERT INTO `33haoactivity` VALUES (1, 'turntable draw pop-up window', 1594656000,1594915200,0,0); INSERT INTO `33hao_ activity` VALUES (2, 'turntable draw pop-up window', '06480453986921327.jpgpop-up window', 'turntable draw pop-up window', 1594656000,1594915200,0,0)

Delete data

Drop table `33hao_ activity`

Recover data

According to the needs of the node, we look at the statement nodes created when the log files are provided to us for database creation, table creation, data addition, etc., so as to recover the data.

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

Recover data based on time

Mysqlbinlog-- start-datetime='2020-09-27 22 stop-datetime='2020-09-27 22 22 stop-datetime='2020-09-27 22 22 15 30 stop-datetime='2020 00' / www/server/data/mysql-bin.000036 | mysql- uroot-p copy code

Execute binlog log directly

Mysqlbinlog / www/server/data/mysql-bin.000036 | mysql- uroot-p and above are all the contents of MySQL physical files. Thank you for your reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.