MySQL Database Backup & Restore (MySQLDump)

MySQLDump is used to export tables to text files.

Syntax:

mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]

[username] - this is your database username
[password] - this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the filename for your database backup

In this article, our database is muffin.
username: root
password: password

Part I: BACKUP

1. Taking the full backup of all the tables including the data

c:\> mysqldump -u root -p passwd muffin> muffin.sql

2. Taking the backup of table structures only

c:\> mysqldump -u root -p passwd --no-data muffin> muffindatano.sql

3. Taking the backup data only

c:\> mysqldump -u root -p passwd --no-create-info muffin > muffindata.sql

4. Exporting few tables to text files

Syntax:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]

--add-drop-table : This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

Example:

c:\> mysqldump --add-drop-table -u root -p passwd muffin table1 table2 > muffinTable.sql

5. back up a database from one MySQL server to another

mysqldump --host=host1 --opt mydatabase mysql --host=host2 -C newdatabase

Note that the target database must already exist on the remote server. To create newdatabase on host2(target box) first if newdatabase not exisit.

c:\> mysql -u root -p passwd

mysql > create database newdatabase;

6. You might zip backup dump on unix platform

c:\> mysqldump ---user root --password=passwd mydatabase gzip > /usr/local/mydatabase.gz

Part II: RESTORE

syntax:

mysql - u user_name -p your_password database_name < backup_dump.sql

e.g.

c:\> mysql muffin < muffin.sql -u root -p passwd

If restoring to a new db, you must create a new db first:

mysql > create database muffin;

If your database already exists and you are just restoring it, try this line instead:

mysqlimport -u user_name -p your_password database_name backup_dump.sql