mysqldump is a command line program used to export data from MySQL. The data is usually exported to a file and is in the form of MySQL queries. mysqldump can be used to backup a database and its data, or to
move/recreate a database on another server.
Because mysqldump is a command line program, we first start by opening a console window, just like we do
for the mysql program. Three important
switches
used by the mysql program
are also used for mysqldump:
-u
- Followed by the username you wish to login with. In Unix, this
defaults to the user you are logged in as.
-p
- Tells mysqldump that we will be entering a password.
-h
- Follow by the host (IP or domain name) you wish to login to. This
default to localhost.
Next, the database name is entered, followed by a list of that database's tables which you wish to have
exported, all delimited with spaces.
mysqldump -u root -p -h db.example.com MyDatabase MyTable1 MyTable2 >
MyDatabase.sql
Once run, the program will prompt for a password since the switch -p was used. Then, the program will
create queries which create the tables MyTable1 and MyTable2 as well all the data contained in both
tables.
You'll notice I used > to redirect the output of mysqldump to the file MyDatabase.sql. Without doing
this, the queries generated by the program would be dumped to the console.
Optionally, the table names can be dropped to export the full database.
mysqldump -u root -p MyDatabase > MyDatabase.sql
-t
- Leave out all
create table
statements, a good way to backup only data.
-d
- Leave out all
insert
statements.
-A
- Dump all databases. No database or table names should be
specified.
-B
- Dump multiple databases. No table names should be specified, only
a list of database names.
Dump a database's table structure and data:
mysqldump -u root -p MyDatabase > MyDatabase1.sql
Perform a data backup on all databases:
mysqldump -u root -p -A -t > AllDatbasesData.sql
Dump the create table statement of table MyDatabase.MyTable1:
mysqldump -u root -p -d MyDatabase MyTable1 > MyTable1CreateTable.sql
Dump the entire structure of MyDatabase:
mysqldump -u root -p -d MyDatabase > MyDatabaseStructure.sql
Dump multiple database's table structures and data:
mysqldump -u root -p -d -B MyDatabase1 MyDatabase2 > MyDatabasesStructure.sql
http://dev.mysql.com/doc/refman/5.0/en/mysql
dump.html
http://en.wikipedia.org/wiki/Database_dump
