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
Example:
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