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.
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
Example:
mysqldump -h localhost -p -u root mydatabase > mybackup.sql
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");
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.
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
The command is passed through exec:
$file = 'myfile.sql';
$dbuser = '';
$dbpass = '';
$dbname = '';
exec("mysql --user=$dbuser --password='$dbpass' $dbname < $file");
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
Subscribe to my newsletter for the latest updates on my books and digital products.
Find posts, tutorials, and resources quickly.
Subscribe to my newsletter for the latest updates on my books and digital products.
