PHP & MySQL | Tutorials

David Carr

How to backup and restore a MySQL Database

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

Domains are often purchased from multiple providers, keeping track of where a domain is and its DNS settings can be tricky. Domain Mapper solves this by listing all your domains in one place. View your DNS settings and receive reminders to renew your domains. Try it today.

Support my work by donating with PayPal.

Subscribe to my newsletter

Subscribe and get my books and product announcements.

© 2009 - 2021 DC Blog. All code MIT license. All rights reserved.