The easiest way to backup MySQL database

The simplest way to export table data to a text file is to use theSELECT... INTO OUTFILEstatement to directly export the export query results to a file on the server host.

Use the SELECT... INTO OUTFILE statement to export data

The syntax of the statement combines the conventionalSELECT INTOandOUTFILE filenameat the end. The default output format is the same asLOAD DATA, so the following statement exports the tutorials_tbl table to C:\tutorials.txt and uses tab-delimited, newline-terminated files:

mysql> SELECT * FROM tutorials_tbl -> INTO OUTFILE 'C:\tutorials.txt';

Options can be used to specify how to quote and delimit columns, changing the record output format. UseCRLFto export thetutorial_tblto theCSVformat table, using the following statement:

mysql> SELECT * FROM passwd INTO OUTFILE 'C:\tutorials.txt' -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\r\n';

SELECT... INTO OUTFILEhas the following attributes:

• The output file is created directly by theMySQLserver, so the file name should indicate the desired file name, which will be written to the server host. There are also statements similar to the local version ofLOAD DATAthat does not have theLOCALversion.

• Must have MySQL'sFILE permissionto execute theSELECT ... INTOstatement.
  
• The output file must not exist yet. This is important to preventMySQLfrom getting the wrong file.
  
• There should be a server host or some way to retrieve files for login accounts on that host. Otherwise,SELECT ... INTO OUTFILEmay not have any value given.
  
• UnderUNIX, files created are readable by everyone and owned byMySQL Server. This means that, while the file is able to be read, it may not be deleted
  

Export table as raw data

mysqldumpProgram is used to copy or backup tables and databases. It can write the table output as a raw data file, or as a set of records that recreates the table'sINSERTstatements.

To dump a table as a data file, you must specify a --tab optionto specify the directory forMySQL Serverto write to the file.

For example, to dump from thetutorials_tbl tablein thedatabase testto a file in theC:\tmp directory, you can use this command:

$ mysqldump -u root -p --no-create-info \ --tab=c:\tmp TEST tutorials_tbl password ******

Recommended:mysql tutorial

The above is the detailed content of The easiest way to backup MySQL database. For more information, please follow other related articles on the PHP Chinese website!