MySQL export backup (data export) method detailed

The easiest way to export table data to a text file is to use the Select ... The query results into the OUTFILE statement are exported directly to a file on the server host.

Select for export Data ... into outfile declaration:

The syntax of this sentence combines the general select into OutFile file name end. The default output format is the same load DATA, so the following statement exports the TUTORIALS_TBL Table tab-delimited, newline-terminated file to/tmp/tutorials.txt:

The code is as follows	Copy Code
Mysql> SELECT * from TUTORIALS_TBL -> into outfile '/tmp/tutorials.txt ';

You can also change the output format, using options to indicate how columns and records are referenced and delimited. CRLF terminating line to export a table in TUTORIAL_TBL CSV format, use such a statement:

The code is as follows	Copy Code
Mysql> SELECT * from passwd into outfile '/tmp/tutorials.txt ' -> FIELDS terminated by ', ' enclosed by ' ' -> LINES terminated by ' RN '; SELECT ... into outfile

Has the following properties:

The MySQL server creates an output file directly, such that the file name should specify the file you want to be written on the server host. A similar statement without a local version of the load DATA version.

You must have MySQL file permission to execute the Select ... INTO statement.

The output file must not exist. This prevents MySQL from being able to destroy files that may be important.

It should be on the server host or some method to retrieve the file from that host's login account. Otherwise select ... into outfile may be worthless.

Under UNIX, the world's readable files and MySQL servers are created. This means that while the file can be read, it cannot be deleted.

Export table as raw data:
Use the Mysqldump program to copy or back up tables and databases. It can write the original data file of the table output or recreate the records in the table for a group of INSERT statements.
To dump a table, you must specify a data file-the option on the tab that represents the directory where you want the MySQL server to write to the file.

Use the Mysqldump program to copy or back up tables and databases. It can write the original data file of the table output, or recreate the records in the table for a set of INSERT statements.
To dump a table, you must specify a data file-the option on the tab that represents the directory where you want the MySQL server to write to the file.

The code is as follows	Copy Code
$ mysqldump-u root-p--no-create-info --tab=/tmp Tutorials Tutorials_tbl Password Hu Jintao

To export the contents or definition of a table in SQL format:
The SQL format in a table is exported to a file, using a command such as:

The code is as follows	Copy Code
$ mysqldump-u root-p Tutorials tutorials_tbl > Dump.txt Password Hu Jintao

This will create the contents of the file as follows:

The code is as follows

Copy Code

-------------------------------------------------------------------------------- --MySQL Dump 8.23 -- --Host:localhost Database:tutorials --------------------------------------------------------- --Server version 3.23.58 -- --Table structure for table ' TUTORIALS_TBL ' -- CREATE TABLE Tutorials_tbl ( tutorial_id Int (one) not NULL auto_increment, Tutorial_title varchar not NULL default ', Tutorial_author varchar () not NULL default ', Submission_date Date Default NULL, PRIMARY KEY (tutorial_id), UNIQUE KEY Author_index (tutorial_author) ) Type=innodb; -- --Dumping data for table ' TUTORIALS_TBL ' -- INSERT into Tutorials_tbl VALUES (1, ' Learn PHP ', ' John Poul ', ' 2007-05-24 '); INSERT into Tutorials_tbl VALUES (2, ' Learn MySQL ', ' Abdul S ', ' 2007-05-24 '); INSERT into Tutorials_tbl VALUES (3, ' JAVA Tutorial ', ' Sanjay ', ' 2007-05-06 ');

To dump multiple tables, they name all the database name parameters. To dump the entire database, do not specify a database after any tables, as follows:

The code is as follows	Copy Code
$ mysqldump-u root-p Tutorials > Database_dump.txt Password Hu Jintao

The following commands are used on all available database backup hosts:

The code is as follows	Copy Code
$ mysqldump-u root-p--all-databases > Database_dump.txt Password Hu Jintao

To export the contents or definition of a table in SQL format:

These methods can be used to implement a backup strategy for a database.

To copy a table or database to another host:
If you want to copy a table or database from a MySQL server, then use the Mysqldump database name and table name.

Run the following command on the source host. This will dump the complete database to the Dump.txt file:

The code is as follows	Copy Code
$ mysqldump-u ROOT-P database_name table_name > Dump.txt Password * * * *

Copy the complete database without using a specific table name, as explained above.

Now, the FTP dump.txt file is on another host, using the following command. Before you run this command, make sure that you have created the database name on the target server.

The code is as follows	Copy Code
$ mysql-u root-p database_name < Dump.txt Password * * * *

Another method, without using an intermediate file, mysqldump output is sent directly over the network to the remote MySQL server. If you can connect to the host of the cookbook database for both servers, use the following command:

The code is as follows	Copy Code
$ mysqldump-u root-p database_name States | MYSQL-H other-host.com database_name

The command mysqldump is connected to the local server and writes to the dump output pipeline. Half of MySQL commands connect to remote MySQL server otherhost.com. It reads each statement sent by the input tube and other host.com servers.

The above are all manual backup or export data, the following we write an automatic scheduled backup

Windows with the "Task Scheduler" function, set a time, let the system run the script, do not realize the automatic backup database function? First, post the script code:

code is as follows

copy code

@echo on        & nbsp;  REM------------------------Backup bugdb which is InnoDB-----------------------------       del f:/backup/website/bugdb_*.sql      CD f:/usr/wamp/mysql/bin      set year=%date:~0,4%      set month=%date:~5,2%       Set day=%date:~8,2%      set filename=bugdb_%year%%month%%day%.sql   & nbsp;  mysqldump.exe bugdb-uroot-p123456 > f:/backup/website/%filename%       br>     @echo off

Explain the meaning of each code in turn:

Line four: Deletes the SQL file that contains the word "bugdb_" in the file name under the specified directory. Because this code was written by me earlier, run every night on the company's server. So before each backup, first delete the files that were successfully backed up the previous night.
Line five: Enter the MySQL bin directory, because this directory has a Mysqldump.exe file, the file when the MySQL database with the backup and restore the MySQL database Tools, this script file is used to this tool.
Line Six: The year that takes the current system date, expressed as four digits, such as 2010.
Line seventh: The month of the current system date, expressed as a two-digit number, such as 03.
Line eighth: the date of the current system date, expressed as a two-digit number, such as 12.
Line Nineth: Define the backup file name, the final file name in bugdb_20100312.sql form, that is, the file name plus date form.
Line Tenth: Perform a backup.

Again, explain the syntax format of mysqldump. The format is:

Mysqldump.exe "database name to back up"-U (receive username)-p > "path and filename of backup file"

The role of ">" is output redirection, that is, the Mysqldump.exe backup data output to a file and save.

Copy the above script into a text file and save it as a *.bat, such as a backup.bat batch file, and then use the file, which I have d:/scripts/backup_bugdb.bat.

Open Task Scheduler in Control Panel to create a new scheduled task:

　　

In the "Run" inside the browse button to find the Backup.bat batch file just saved, on the Schedule tab and the Settings tab according to their actual needs to set, after setting up the point "OK" to save the task. The system then runs the script periodically for a specified period of time to automatically back up the database.

　　

Also attach the command to restore the database

The code is as follows	Copy Code
d:htmlwampmysqlbinmysql.exe-uroot-p123456 --default-character-set=utf8 Bugdb < F:bugdb_20100312.sql