Steps for backing up MySQL SQL syntax

This article mainly introduces how to back up MySQL SQL syntax. We all know that MySQL SQL syntax is often used in practical applications, therefore, backing up MySQL SQL syntax is also of high concern. The following describes the main content of the article.

Backup

The backup table syntax works almost the same way as mysqlhotcopy. It locks tables and copies data files. It can achieve online backup, but the effect is not ideal, so it is not recommended. It only copies table structure files and data files, and does not copy index files at the same time, So recovery is slow.
Example:

 
 

  
  
BACK TABLE tbl_name TO '/tmp/db_name/'; 
 
 

Note: You must have the FILE Permission to execute this SQL statement, and the directory/tmp/db_name/must be writable by the mysqld user. The exported FILE cannot overwrite the existing FILE, to avoid security issues.

Select into outfile is to export data to a common text file. You can customize the field interval to process the data conveniently.
Example:

 
 

  
  
SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name; 
 
 

Note: You must have the FILE Permission to execute the MySQL SQL syntax, and the FILE/tmp/db_name/tbl_name.txt must be writable by the mysqld user. The exported FILE cannot overwrite the existing FILE, to avoid security issues.

3.2 recovery
To RESTORE a data TABLE, run the restore table statement.
Example:

 
 

  
  
RESTORE TABLE FROM '/tmp/db_name/'; 
 
 

The permission requirements are similar to those described above.

If you use the select into outfile method to back up a file, you can run the load data infile statement to restore the DATA table.
Example:

 
 

  
  
LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name; 
 
 

The permission requirements are similar to those described above. Before you import data, the data table already exists. If you are worried that data will repeat, you can add the REPLACE keyword to REPLACE existing records or use the IGNORE keyword to IGNORE them.

Original article title: MySQL SQL syntax backup

Connection: http://www.cnblogs.com/kfarvid/archive/2009/11/12/1601587.html