Data backup and restore in MySQL

First, there are three kinds of backup methods:

1. data Table File Backup

2. Single-table data backup

3. SQL backup

4. Incremental backup

Second, the storage engine in MySQL

There are two main types: MyISAM and InnoDB. --free of charge

Other charges include: BDB, Memory, Archive

InnoDB Storage: The table structure is stored separately, and all data is stored in the Ibdata1 file.

MyISAM Storage: tables, data, and indexes are all stored separately.

　　

Second, data table file backup and restore

Copy the corresponding table structure and data files directly from the folder under the database directory, then put it back directly when restoring.

However, this method differs from one storage engine to another:

InnoDB, because the data is in the Ibdata1 file except for the table structure, it can be restored to the original database, and the table will not be used when cross-database.

MyISAM, because the tables, data, and indexes are all stored separately, you only need to replicate the three files, and you can use them in any database.

Three, single-table data backup and restore

1. Data backup

Only one table can be backed up at a time, and only data can be backed up, and the table structure cannot be backed up. Typically used to export data to a file.

SELECT */Field list into outfile ' file path ' [Fields field processing lines row processing] from data source; --file does not exist to export

Fields Field Processing:

Enclosed by: The field uses what content is wrapped, the default is ';

Terminated by: The field uses what content ends, the default is ' \ t ';

Escaped by: The special symbol is handled in what way, the default ' \ \ ' backslash is escaped;

Lines Line Processing:

Starting by: What starts each line and defaults to ';

Terminated by: What is the end of each line and the default is a newline character;

Example:

1 Select *  intoOutFile'D:\BaiduNetdiskDownload\mysql\class.sql' 2Fields terminated by '---'Enclosed by '"' 3Lines starting by 'Be :'Terminated by 'ed\r\n'4  fromMy_class;

2. Data restore

You must ensure that the table structure exists for data restore.

Lode Data infile ' file path ' into Table table name fields field handles lines row processing; -How to back up, how to restore

Example:

1 LoadData infile'D:\BaiduNetdiskDownload\mysql\class.sql'  into TableMy_class2Fields terminated by '---'Enclosed by '"' 3Lines starting by 'Be :'Terminated by 'ed\r\n';

Third, SQL backup

1. Data backup

The backup is SQL statements and requires the use of the Mysqldump.exe software provided by MySQL.

Mysqldump/mysqldump.exe-hpup database name [table name 1[, table Name 2 ...]] > external file path--can be multiple table backup, if not write table name, indicates the whole library backup

-- single-table backup- p JDBC--Backup to the whole library   

2. Data restore

① using MySQL client restore:

Mysql.ext/mysql-hpup Database name < backup file path

　　　　

- - < D:\BaiduNetdiskDownload\mysql\student.sql

② using SQL command to restore:

source backup file path;

SOURCE D:\BaiduNetdiskDownload\mysql\student.sql;

Four, incremental backup

This method backs up the log files for the MySQL server and can be backed up for a specified period of time, so the backup data is not duplicated and the intermediate procedures are backed up.

Data backup and restore in MySQL