Introduction
It is often required and advisable to take backup of your databases at regular intervals or before making any updates that can alter the database structure. Most of the web applications nowadays use MySQL as their backend storage as well as a configuration source to keep their settings. Creating a physical backup of the MySQL backend directories for a future use cannot always work and the restoration is rather difficult with complicated procedures. We are going to discuss two of the methods that can be used to backup and restore your databases properly.
#1. mysqldump
MySQL and MariaDB includes a utility with it called the mysqldump which can be used to take backup of your hosted databases with ease. The SQL backup generated using this utility will be the whole set of SQL statements to imprint the source database, when restored will create the database objects in the same or another MySQL/MariaDB instance based on those SQL statements. The backup generated will be portable and the user can open the file with any text editor and modify the SQL statements if required.
Using mysqldump for all databases
The mysqldump command can be used to backup all databases deployed in the MySQL server by using the syntax below. You need to use the MySQL administrator user credentials for this. One executed it will ask you for the admin user password and the backup will be generated to the location that you specify for the output file.
mysqldump -u root -p --all-databases > all-DB-dump-$( date '+%Y-%m-%d_%H-%M-%S' ).sql
Using mysqldump for a single database.
You can use the admin user credentials or the db user of that specific database for the backup requirements. Please make sure the db user have GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER permissions over the database. You can use the below commands from a MySQL shell to create and grant required permissions for the user for backup operations.
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '<password>'; GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON db_name.* TO 'bkpuser'@'localhost';
Substitute bkpuser with the username that you want to use for the backup user, password with the actual password string for the user and db name with the database name. Once done, you can use the bkpuser and its credentials to take backup of the db_name database using the below syntax.
mysqldump -u [username] -p[password] -h [host] [databaseName] > [backup-name].sql
OR
mysqldump -u bkpuser -p<password> db_name > db_name.sql
Using mysqldump for a single table
You can use mysqldump in the below syntax to get backup of a single table from the database.
mysqldump -u [username] -p[password] -h [host] [databaseName] [tableName] > [backup-name].sql
OR
mysqldump -u bkpuser -p<password> db_name table_name > table_name.sql
Restoring the generated backup.
The mysql client utility can be used to restore or export the .sql file backup to a database. You can use the below command from MySQL shell to create an empty database for your backup restoration.
mysql> create database db_name; Query OK, 1 row affected (0.08 sec)
Use the mysql client in the below syntax to initiate the restoration from the .sql backup file.
mysql -u [username] -p [password] < backupFile.sql
The mysqldump utility can also be used to take backup of database from a remote server and the syntax for the same is as noted below.
mysqldump --host hostaddress -P portnumber -u username -ppassword dbname > dbname.sql
#2 Using phpMyAdmin
phpMyAdmin is a free software built with PHP and it is effectively in use for the MySQL administration globally. This is also shipped with common web control panels like cPanel, Plesk etc. phpMyAdmin support most of the operations available from the terminal including the database import/export feature.
Login to phpMyAdmin connected with your database, click on export tab, select the backup file format – chose SQL from the dropdown list and press the ‘Go’ Button. The backup will be generated and downloaded in the same browser session.
Select the ‘Import’ tab, click the ‘Choose File’ button, browse and find the backup file from the local machine and click ‘Go’ button to restore a backup from phpMyAdmin.
