Top 5 ways to backup and restore MySQL database

If you are using MySQL database for storing your important information then you might be well aware of the importance of regular backup. Backup is required to restore data in case of disaster. Fortunately MySQL provides many advanced tool for backing and restoring database. So today we will look at various methods of backing and restoring MySQL database.

Backup & Restore using mysqldump command: This is an inbuilt utility of MySQL. Using this command you can backup both single and multiple databases to an external source like file, server etc.

To back up a single database use below command:
mysqldump -u [username] -p [pass] [database_name] > backupfile.sql
Where username is the name of database username, pass is the password of database, database-name is the name of database, backupfile is the name of backup. The above command will backup all the data in database_ name to backupfile which will contain the sql statements to recreate the database.

To backup multiple database use below command:
mysqldump -u [username] -p [pass] - - databases [databases database1 database2 database3]  > backupfile.sql

You can restore the backup that you have created using mysqldump using below command:
mysql -u [username] -p [database_name] &gt backupfile.sql

Simply copy database files: Since MySQL uses the same table format on all platforms so you can easily copy your database file from one platform to another. You just need to copy the data/directory to backup file

Automatically backup using Amozon S3: You can also use an automated script that first take the backup of MySQL database and then move it to Amozon S3.

Backup & restore using PHPMyAdmin: If phpMyAdmin is installed on your system then you can easily backup and restore your MySQL database. To backup the database follow below steps:

1. Open your phpMyAdmin account.
2. Select your database.
3. Now select Export Link and make sure to click on Select All Link to choose all the tables of your database.
4. Save your file and click on Go button to save the file locally on your computer.

To restore the database follow below steps:

1. Open your phpMyAdmin account.
2. Select the database in which you want to import your data.
3. Now click the Import tab on the top of the screen.
4. Click Browse to select the backfile stored on your computer and click Go button.

Backup using AutoMySQLBackup: It is a script that you can download from here. It provides following benefits:

1. You can backup multiple databases using a single script.
2. It automatically compresses the backup file.
3. You can email your backup file.
4. You can decide on which day you want to run backup.