The simplest MySQL data backup and restore tutorial in history (Part 2) (Part 36)

Data backup and restoration part 2, as follows

Basic concepts:

Backup , save another copy of the current data or records;

Restore : restore the data to the state at the time of backup.

Why do we need to back up and restore data?

Prevent data loss; protect data records.

There are many ways to back up and restore data, which can be divided into: data table backup, single table data backup, SQL backup and incremental backup.

Single table data backup

Single-table data backup can only back up one table at a time, and can only back up data, not the table structure.

The common usage scenario is to export the data in the table to a file.

Backup method: Select a portion of the 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 prerequisite for using single-table data backup, that is: the exported external file does not exist, that is, the file under the file storage path does not exist .

Execute the following SQL statement to test:

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

As shown in the figure above, the SQL statement has been executed successfully. 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 resolved by reading " Details of secure-file-priv issues encountered when exporting MySQL data ".

In order to verify whether the data in the class table is really exported to the specified location, we can confirm it under this path:

As shown in the figure above, we have obviously exported the data in the class table to the local computer! However, there is one thing we need to pay special attention to here, that is: for files exported from the database, we'd better open them with editing tools such as EditPlus to prevent garbled characters .

In addition, for the above SQL syntax used to export data in the table, we can actually reverse the writing order without any problem, for example:

select */field list + from data source + into outfile + 'file storage path';

Execute the following SQL statement to test:

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

Next, we will learn some advanced operations for backing up single-table data, that is, how to specify the processing methods of fields and rows .

Basic syntax : select */field list + into outfile + 'file storage path' + fields + field processing + lines + line processing + from data source;

Field processing:

enclosed by: specifies what content to wrap the field with. The default is an empty string.

terminated by: specifies what the field ends with, the default is \t, Tab key;

escaped by: specifies how special symbols are handled. The default is \\, which is backslash escape.

Row processing:

Starting by: specifies what each line starts with, the default is an empty string;

terminated by: specifies what each line ends with, the default is \r\n, the newline character.

Execute the following SQL statement to test:

--Specify the single table data backup processing method select * into outfile 'D:/CoderLife/testMySQL/class3.txt'
-- Field processing fields
enclosed by '"'
terminated by '|'
lines
starting by 'START:'
from class ;

As shown in the figure above, it is obvious that the exported file class3.txt is output according to the format we specified! Previously, we have tested various methods of backing up single-table data. Now we delete the data and try to restore the data, that is, restore the external data back to the data table . However, since single-table data backup can only back up data, 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 + line processing;

Execute the following SQL statement to test:

-- Delete the data in the class table delete from class;

-- View the data in the class table select * from class;

-- Restore the data in the class table load data infile 'D:/CoderLife/testMySQL/class3.txt'
into table class
-- Field processing fields
enclosed by '"'
terminated by '|'
lines
starting by 'START:';

-- View the data in the class table select * from class;

As shown in the figure above, it is obvious that after we deleted the data in the table class, the data was restored successfully.

Tips: The content enclosed by the symbol [] indicates optional items; the symbol + means connection.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.