Databases Reference
In-Depth Information
tables
Creates a dump of the specified database tables.
where
Dumps only records meeting a specified
WHERE
clause.
You can use
mysqldump
in four main ways (assume you want to get the database dump
in the file
outputfile.sql)
:
• To make a backup of all the databases on a MySQL server, use the command:
$
mysqldump --user=root --password=
the_mysql_root_password
\
--result-file=
outputfile.sql
--all-databases
This dumps
CREATE DATABASE
,
USE
,
CREATE TABLE
, and
INSERT
statements for all data
in all databases that are accessible by the user
root
. If you specify a user other than
root
, the output is affected by the privileges of that user.
• To make a backup of specific databases, use the command:
$
mysqldump --user=root --password=
the_mysql_root_password
\
--result-file=
outputfile.sql
--databases
database_name
This dumps
CREATE DATABASE
,
CREATE TABLE
, and
INSERT
statements for only the
specified databases. Use this if you want a
CREATE DATABASE
statement, in preference
to the variant we showed you at the beginning of this section.
You can list several databases one after the other in the command. For example,
to dump the
music
and
wedding
databases, you would type:
$
mysqldump --user=root --password=
the_mysql_root_password
\
--result-file=
outputfile.sql
--databases music wedding
• To make a backup of specific tables from a database, use the command:
$
mysqldump --user=root --password=
the_mysql_root_password
\
--result-file=
outputfile.sql
database_name
table_name
You can list several tables, one after the other, in the command.
• To make a backup of specific data from a table in a database, use the command:
$
mysqldump --user=root --password=
the_mysql_root_password
\
--result-file=
outputfile.sql
database_name
table_name
where=
where_clause
For example, to use the
artist
table of the
music
database, and dump SQL state-
ments for all the artists having a name beginning with “N”, you would write:
$
mysqldump --user=root --password=
the_mysql_root_password
\
--result-file=
outputfile.sql
\
--where="artist_name like 'N%'" \
music artist
