History of the simplest MySQL Data Backup and restoration tutorial (medium) (), mysql Data Backup

History of the simplest MySQL Data Backup and restoration tutorial (medium) (), mysql Data Backup

Data backup and Restoration:

Basic concepts:

BackupSave the existing data or records in another copy;

RestoreTo restore the data to the backup status.

Why do we need to back up and restore Data?

Prevents data loss and protects data records.

There are many data backup and restoration methods, including data table backup, single table data backup, SQL backup, and Incremental backup.

Single Table Data Backup

Single Table Data Backup: Only one table can be backed up at a time, and only data can be backed up. The table structure cannot be backed up.

The common scenario is to export data from a table to a file.

Backup method: select a part of data from the table and save it to an external file,

Select */Field List + into outfile + 'file storage path' + from data source;

Here, there is a premise for using single-table data backup:The exported external file does not exist, that is, the file in the file storage path does not exist..

Run the following SQL statement for testing:

-- Select * into outfile 'd for single-table data backup:/CoderLife/testMySQL/class.txt 'from class;

As shown in, the SQL statement has been successfully executed. Here, if we encounter:

ERROR 1290 (HY000): The MySQL server is running with the-secure-file-priv option so it cannot execute this statement.

This error can be solved by checking "details about secure-file-priv problems encountered when MySQL exports data.

To verify whether the data in the class table is exported to the specified location, we can go to this path to confirm:

As shown in, it is clear that we have exported data from the class table to the local machine! But here, we need to pay special attention to the following:For Files exported from the database, we 'd better useEditPlusAnd other editing tools to prevent garbled characters.

In addition, for the SQL syntax used to export data in a table, we can reverse the writing order, and there is no problem, for example:

Select */Field List + from data source + into outfile + 'file storage path ';

Run the following SQL statement for testing:

-- Single table data backup select * from class into outfile 'd:/CoderLife/testMySQL/class2.txt ';

Next, we will learn some advanced operations for single-table data backup, that isSpecify the processing method of fields and rows by yourself.

Basic syntax: Select */Field List + into outfile + 'file storage path' + fields + field processing + lines + row processing + from data source;

Field Processing:

Enclosed by: specifies the content of a field. The default value is an empty string;

Terminated by: Specifies the end of a field. The default value is \ t, which is the Tab key;

Escaped by: Specifies how special symbols are processed. The default value is \, which is used for escape by backslash.

Row processing:

Starting by: Specifies the start point of each line. The default value is an empty string;

Terminated by: Specifies the end of each line. The default value is \ r \ n, a line break.

Run the following SQL statement for testing:

-- Select * into outfile 'd:/CoderLife/testMySQL/class3.txt for data backup in a single table -- field processing fieldsenclosed by '"'terminated by' | 'linesstarting by 'start: 'From class;

As shown in, the exported file class3.txt is output in the specified format! Previously, we have tested various single-table data backup methods. Now we delete the data and try to restore the data, that isRestore external data to the data table.. But, because data in a single table can be backed up, if the table structure does not exist, it cannot be restored.

Basic syntax: Load data infile + 'file storage path' + into table + table name + [field list] + fields + field processing + lines + row processing;

Run the following SQL statement for testing:

-- Delete the data in the table class from class; -- view the data in the table class select * from class; -- restore the data in the table class load data infile 'd: /CoderLife/testMySQL/class3.txt 'into table class -- field processing fieldsenclosed by '"'terminated by' | 'linesstarting by 'start :'; -- view the data in the table class select * from class;

As shown in, the data is restored successfully after we delete the data in the table class.

Tip: The content enclosed by the symbol [] indicates the option; the symbol + indicates the meaning of the connection.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.