6.4.1. Dumping data in SQL format
Mysqldump
This section describes how to useMysqldumpTo create SQL-format dump files. For information about reloading such dump files, see Section 6.4.2, "reloading SQL-format backups ".
By default,MysqldumpWrites information as SQL statements to the standard output. You can save the output in a file:
shell> mysqldump [arguments
] > file_name
To dump all databases, invokeMysqldumpWith--all-databases
Option:
shell> mysqldump --all-databases > dump.sql
To dump only specific databases, name them on the command line and use--databases
Option:
shell> mysqldump --databases db1 db2 db3 > dump.sql
The--databases
Option causes all names on the command line to be treated as database names. Without this option,MysqldumpTreats the first name as a database name and those following as table names.
With--all-databases
Or--databases
,MysqldumpWritesCREATE DATABASE
AndUSE
Statements prior to the dump output for each database. this ensures that when the dump file is reloaded, it creates each database if it does not exist and makes it the default database so database contents are loaded into the same database from which they came. if you want to cause the dump file to force a drop of each database before recreating it, use--add-drop-database
Option as well. In this case,MysqldumpWritesDROP DATABASE
Statement preceding eachCREATE DATABASE
Statement.
To dump a single database, name it on the command line:
shell> mysqldump --databases test > dump.sql
In the single-database case, it is permissible to omit--databases
Option:
shell> mysqldump test > dump.sql
The difference between the two preceding commands is that--databases
, The dump output contains noCREATE DATABASE
OrUSE
Statements. This has several implications:
When you reload the dump file, you must specify a default database name so that the server knows which database to reload.
For reloading, you can specify a database name different from the original name, which enables you to reload the data into a different database.
If the database to be reloaded does not exist, you must create it first.
Because the output will contain noCREATE DATABASE
Statement,--add-drop-database
Option has no effect. If you use it, it produces noDROP DATABASE
Statement.
To dump only specific tables from a database, name them on the command line following the Database Name:
shell> mysqldump test t1 t3 t7 > dump.sql
http://dev.mysql.com/doc/refman/5.1/en/mysqldump-sql-format.html
6.4.2. Reloading SQL-format backups
To reload a dump file writtenMysqldumpThat consists of SQL statements, use it as input toMySQLClient. If the dump file was createdMysqldumpWith--all-databases
Or--databases
Option, it containsCREATE DATABASE
AndUSE
Statements and it is not necessary to specify a default database into which to load the data:
shell> mysql < dump.sql
Alternatively, fromMySQL, Usesource
Command:
mysql> source dump.sql
If the file is a single-database dump not containingCREATE DATABASE
AndUSE
Statements, create the database first (if necessary ):
shell> mysqladmin create db1
Then specify the database name when you load the dump file:
shell> mysql db1 < dump.sql
Alternatively, fromMySQL, Create the database, select it as the default database, and load the dump file:
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql
http://dev.mysql.com/doc/refman/5.1/en/reloading-sql-format-dumps.html