Currently seeking new career opportunities in web development, particularly with Laravel, Hire Me

How to backup and restore a MySQL Database

David Carr

Tutorials PHP & MySQL

I was recently asked how do backup and restore a database. As with most things in programming, there are multiple ways to export and restore a database I’ll outlook a couple of them in this post.

Method 1 Command Line

Login to your Shell/Command prompt then run the following command, this will generate a dump of the database into the file specified at the end of the command, the password is omitted from the command. You will be prompted for the password, passing the password in a command is considered insecure as it can leave traces in the history logs.

mysqldump -h [host] -p -u [user] [database name] > filename.sql

The keys used are:
-h = the host most likely localhost
-p = password - leave blank to be prompted
-u = username


mysqldump -h localhost -p -u root mydatabase > mybackup.sql

Method 2 Exec

This method relies on exec been able to run on the server. 

Set the login credentials and also the filename, in this case, the filename will be the date and time such as 2015-02-20-16-30-00.sql

$toDay = date('d-m-Y-H-i-s');
$dbhost = ‘localhost';
$dbuser = '';
$dbpass = '';
$dbname = '';

exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost $dbname > ".$toDay.".sql");

Method 3 phpMyAdmin

If the above methods are not available to you, a web-based MySQL client can be used such as phpMyAdmin:

1) Login to phpMyAdmin
2) Select the database from the left menu
3) click export from the main menu
4) select the desired options and format, if in doubt select the quick and SQL format.
5) download the generated sql file.

Importing sql file into a database

Method 1 Command Line

Like the export and import can be run by calling mysql then the credentials followed by the database to import into and the path to the sql file.

mysql -u username -p database_name < file.sql

Method 2 Exec

The command is passed through exec:

$file = 'myfile.sql';
$dbuser = '';
$dbpass = '';
$dbname = '';

exec("mysql --user=$dbuser --password='$dbpass' $dbname < $file");

Method 3 phpMyAdmin

To import using phpMyAdmin, the steps are revered from exporting:

1) Login to phpMyAdmin
2) Select the desired database
3) Click on import
4) Select the sql file and press go to upload and import the data

Laravel Modules Your Logo Your Logo Your Logo

Become a sponsor

Help support the blog so that I can continue creating new content!


My Latest Book

Modular Laravel Book - Laravel: The Modular way

Learn how to build modular applications with Laravel Find out more

Subscribe to my newsletter

Subscribe and get my books and product announcements.

Learn Laravel with Laracasts

Faster Laravel Hosting

© 2006 - 2024 DC Blog. All code MIT license. All rights reserved.