The easiest way to backup MySQL database

The simplest way to export table data to a text file is to use the SELECT... INTO OUTFILE statement 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 conventional SELECT INTO and OUTFILE filename at the end. The default output format is the same as LOAD 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. Use CRLF to export the tutorial_tbl to the CSV format 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 OUTFILE has the following attributes:

• The output file is created directly by the MySQL server, 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 of LOAD DATA that does not have the LOCAL version.

• Must have MySQL's FILE permission to execute the SELECT ... INTO statement.
  
• The output file must not exist yet. This is important to prevent MySQL from 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 OUTFILE may not have any value given.
  
• Under UNIX, files created are readable by everyone and owned by MySQL 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's INSERT statements.

To dump a table as a data file, you must specify a --tab option to specify the directory for MySQL Server to write to the file.

For example, to dump from the tutorials_tbl table in the database test to a file in the C:\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!