Mysqldump, into outfile and load data for database import and export backup differences

encountered a large number of import and export data is really troublesome, moving a few g of data, the operation is relatively slow, and if a little careless, but also to rewrite the process, the simplest method is also the least efficient way is to use PHP write import and Export data program, with Shell to run PHP, But people have been ignoring mysqldumpand MySQL's Into outfilewith the Load Data, if these commands are used flexibly, it is convenient for database import and export as well as backup. Export backup data using mysqldump and source import

If you want to export an entire database or a table of one database, and keep the names of the tables in the database intact, you can use the mysqldump and source methods when importing to another database again.

Mysqldump the specific use of parameters can be used mysqldump --help to view, here I briefly say I commonly used several parameters.

1, mysqldump export the entire table of data, including the building of table information, which is the most basic usage

mysqldump-uusername-ppassword databasename tablename >/home/db/db_bak2012

where-u-p and MySQL parameters are the same, representing the user name and password, followed by the database name and indicate,> after the path to be exported.

The above export data to import the database when you can enter MySQL, and then use the following command to implement

source/home/db/db_bak2012

PS: This method is to export the entire table data, and with the build table information, if the imported database has the same name of the table, will be replaced

2. Use mysqldump to export a fixed-condition database mysqldump has a parameter where you can set the database to export the invariant, where there is a shorthand is W, the former using the –where the latter is-W for example, I want to export a record named DATA0 database Table0 with status 1, you can use the following command

Mysqldump-uusername-ppassword data0 Table0-wstatus=1 >/home/db/db_bak2012

3, the first two methods to export data have the table data, if the exported data is only appended, then use mysqldump two parameters –no-create-info and –no-create-db, that is, the following example:

Mysqldump-uusername-ppassword databasename tablename--no-create-db--no-create-info >/home/db/db_bak2012 using into outfile and load Data infile import and export backup data

If you want to export some of the fields in a table or some of the qualifying records, you need to use MySQL into outfile and load data infile . For example, the following MySQL command exports the data from the MyTable table of Select to the/home/db_bak2012 file.

SELECT * FROM MyTable where status!=0 and name!= "into outfile '/home/db_bak2012 ' fields terminated by ' | ' enclosed b Y ' "' lines terminated by '/r/n ';

If you want to import the data you just backed up, you can use the load file method, such as the following MySQL command, to import the exported data into the Mytable_bak table:

load Data infile '/home/db_bak2012 ' into table Mytable_bak fields terminated by ' | ' enclosed by ' "' Lines terminated b Y '/r/n ';

The advantage of this method is that the exported data can be self-defined format, and the export is pure data, there is no table information, you can directly import another database with the different tables, compared to mysqldump more flexible.

Mysqldump, into outfile and load data for database import and export backup differences