How To Backup And Restore A MySQL Database Through Command Line

Most of the time, you can easily manage your database with web-based tools such as phpMyAdmin. Unfortunately, there are times when you need to restore a large amount of data. This can’t be accomplished through phpMyAdmin because of PHP’s limits (either the maximum time a PHP script can use or the maximum file size upload).

Using the MySQL Command Line

Whether you are using MySQL on Linux or MS Windows, you can use the command line to perform most tasks. If you are using MS Windows, make sure that the path to the command line binaries is included in your system path. This will make things easier for you. Otherwise, you’ll have to type the full path to each command.

How To Backup A MySQL Database

MySQL provides a utility called “mysqldump.” Basically, this tool creates a flat file that contains the SQL instructions to restore your database. Here are a few usage examples of mysqldump:

Creating a simple database backup

mysqldump -u username -p database_name > file.sql

This creates a file that contains all of the SQL statements to create tables and restore data into an existing database. If the target database contains tables with the same names, they will be overwritten. If you want the existing tables to be dropped and recreated, use the add-drop-table option:

mysqldump --add-drop-table -u username -p database_name > file.sql

Alternately, you can choose to drop the whole database before recreating it and restoring data:

mysqldump --add-drop-databases -u username -p database_name > file.sql

Backing Up Multiple Databases

You can backup multiple databases to a single file using the databases option:

mysqldump -u username -p --databases database1 database2 database3 > file.sql

Creating a backup of all databases can be achieved using the all-databases option:

mysqldump -u username -p --all-databases > file.sql

Backing Up InnoDB Tables

If your database has InnoDB tables, you will need to deactivate referential integrity while restoring data. Unfortunately, this can’t be done using the mysqldump utility. To do so, backup your database as you normally would. When done, open the SQL file and add the following statement at the very beginning:

SET FOREIGN_KEY_CHECKS=0;

…and add the following at the end of the file:

SET FOREIGN_KEY_CHECKS=1;

Compressing MySQL Dump File

If you are using mysqldump in a Linux shell, you can pipe it through gzip to compress the dump file (assuming that you have gzip installed):

mysqldump -u username -p database_name | gzip -c file.sql.gz

Restoring A MySQL Backup

To restore a backup created with mysqldump, you will need to use the mysql command. If your SQL dump file does not contain any “create database” statement, you can use the following command:

mysql -u username -p database_name < file.sql

But, if it does, use the same command without specifying the database name:

mysql -u username -p < file.sql

More information on mysqldump can be found here.

About Stephane Brault

Stephane is a web developer and system administrator with over 18 years of experience. Specialized in PHP programming and Linux server administration, he also provided development and consulting services to SMBs for several years before becoming an online entrepreneur.

6 responses so far ↓

1. Response by : What’s In Your cPanel Backup? on Oct 9, 2008 at 12:03 am

[...] If the file is still too large, you have only two options left: ask your hosting provider to restore the file for you or if you have SSH access, restore the database from a command line. [...]

2. Response by : TJ on Aug 26, 2011 at 10:56 am

where do i access the sql file once output.

3. Response by : The Web Hosting Hero on Aug 26, 2011 at 11:38 am

@TJ: just specify the path

mysqldump -u username -p database_name | gzip -c /path/to/file.sql.gz

4. Response by : Tim on Nov 10, 2011 at 8:21 am

Keep in mind that the -p option with a space afterwards will force a prompt to input the password. Assuming a secure environment and a desire to have an automated/scheduled task, your options for entering the password are…

-pMyPassword
–password=MyPassword

5. Response by : John on Jan 17, 2012 at 5:39 pm

Thanks, helped me out.

6. Response by : Hamisi Jabe on Nov 17, 2012 at 6:53 am

Thanks for this tutorial, it was a very helpful tool to me. It saved me alot of time course i wanted to edit the file and save it as a .php then browse to setup the table and dump data

thanks men

Leave a Comment

Name

Email Address

Website

Notify me of followup comments via e-mail