MySQL Tutorial/MySQL Utilities/mysqldump

Automatically drop any tables that exist before restoring the stored tables with the " --add-drop-table "

c:\mysql\bin\mysqldump -u root -p --add-drop-table myDatabase > myDatabase.sql

Create the database if it doesn"t already exist with "--databases" option to specify the database we wish to back up

c:\mysql\bin\mysqldump -u root -p --databases myDatabase > myDatabase.sql

mysqldump

mysqldump outputs the table structure and data in series of SQL commands stored in a text file.

The simplified syntax is

c:\mysql\bin\mysqldump -u <username> -p <database> [<table>] > file.sql

Optimising a dump

"--opt" is used override the mysql server"s normal method of reading the whole result set into memory giving a faster dump.

c:\mysql\bin\mysqldump -u root -p --opt myDatabase > myDatabase.sql

Restoring a Dump

Restoring a dump depends on what you have actually dumped.

c:\mysql\bin\mysql -u root -p myDatabase < myDatabase.sql

To make a copy of live data

To do this you would need to duplicate a local database.

First create the duplicate database:

mysql> CREATE DATABASE myDatabase2;

Using mysqldump to copy databases

It is possible to combine a dump and a restore on one line by using a pipe "|" to pass the output of the dump directly to mysql basically bypassing the file.

We can use this method to copy a database to another server or even create a duplicate copy.

For example to copy the "myDatabase" database to a mysql server called "remote.server.ru":

c:\mysql\bin\mysqldump -u root -p --databases myDatabase | \
> mysql -u backup -p MyPassword -h remote.server.ru

The "\" at the end of the first line means you wish to contine the command on another line before executing it.