Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "logical and physical backup of MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Logical backup

Method 1: generate plain text in a specific format

SELECT * INTO OUTFILE'F:\ data.txt' FIELDS TERMINATED BY', 'FROM g_user

LOAD DATA INFILE'F:\ data.txt' INTO TABLE g_user FIELDS TERMINATED BY','

IGNORE number LINES

If you do not specify a FIELDS clause, the default is the same as if you write:

FIELDS TERMINATED BY't 'ENCLOSED BY' 'ESCAPED BY'\'

If you do not specify a LINES clause, the default is the same as if you write:

LINES TERMINATED BY'\ n'

Send 2: generate Insert statement backup

Using the tool dump

Physical backup

MyISAM storage engine

All data from the MyISAM storage engine is stored in the "datadir" directory set in the MySQL configuration. In fact, whether we are using the MyISAM storage engine or any other storage engine, every database will have a folder in the "datadir" directory (the same is true of the database mysql, which includes system information). There are three files in each MyISAM storage engine table in each database, namely, the ".frm" file to record the table structure metadata, the ".MYD" file to store the table data, and the ".MYI" file to store the index data. Because MyISAM is a non-transactional storage engine, he does not have his own log file. So for the physical backup of the MyISAM storage engine, in addition to backing up the common physical files of the MySQL system, you only need to back up the above three files.

Innodb storage engine

The Innodb storage engine is a transactional storage engine, and the location where the data is stored may be different from that of the MyISAM storage engine, depending on our configuration of Innodb. The configuration that determines where the Innodb stores the data is specified by the three directory locations of "innodb_data_home_dir", "innodb_data_file_path" and "innodb_log_group_home_dir", and another parameter "innodb_file_per_table" that determines how the Innodb's tablespace is stored. The first three parameters specify where the data and log files are stored, and the last parameter determines whether Innodb stores data in a shared or exclusive tablespace. The relevant usage instructions of these parameters have been explained in the first article "introduction to MySQL Storage engine" and in the official manual of MySQL, so we will not repeat them here.

If we use shared tablespace storage, Innodb needs to back up all the data files set by the "innodb_data_home_dir" and "innodb_data_file_path" parameters, and the ".frm" files of all Innodb storage engines in the corresponding database directory in "datadir".

If we use exclusive tablespaces, in addition to backing up all the files that need to be backed up in the shared tablespace mode above, we also need to back up all the ".idb" files in the corresponding database directory in "datadir", which stores the data of the Innodb storage engine tables in the exclusive tablespace mode. Www.2cto.com

In addition, in addition to the data files mentioned above, Innodb has its own log files that store redo information and related transaction information in the location set by the "innodb_log_group_home_dir" parameter. So for Innodb physical backup to work effectively, we need to back up all the log files at the location set by the "innodb_log_group_home_dir" parameter.

FLUSH TABLES WITH READ LOCK

Physical backup, you know.

UNLOCK TABLES

Backup strategy

Physical backup of important content

Then other logical backups

This is the end of the logical and physical backup of MySQL database. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.