Home / Product/ Safely Backup and Restore MySQL Database

Safely Backup and Restore MySQL Database

Ailsa
  • posted by @Ailsa
  • August 30, 2018
Summary: Is there any other better way to do MySQL database backup and restore instead of mysqldump or phpMyAdmin? You will find the best free way to achieve that goal.

Foreword

MySQL is one of the most popular database engines, and provides the service of database for websites. As the most important component of the website, you have to backup MySQL database to avoid problems occur, like database corrupted, lost or impropriate modify, etc. How would you backup and restore MySQL database in Windows or Linux? Here you will get three ways to do that job for you.

MySQL database backup and restore solutions

Generally speaking, you could use MySQL built-in backup program named mysqldump to backup your MySQL database in case of data loss. Or you could use phpMyAdmin to manage your MySQL database. In addition, you can try the third party database backup software to do MySQL backup and restore for you.

Backup and restore MySQL database using command prompt (mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL database by using the mysqldump command (backup MySQL database command line). You have three ways to use mysqldump to backup MySQL database in Windows or Linux, the syntax is listed below:

1. Click Start, input cmd into the dialog box and press enter.

2. Type the command:

If you have a single MySQL database to backup, input this command:

shell> mysqldump db_name > backup-file.sql

You could dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, you could use the command:

shell> mysqldump --all-databases > all_databases.sql

Once you have created the target database, you can use the mysql command or MySQL Workbench to restore the data into the specific newly created database from the dump file.

Mysql –h [hostname] –u [username] –p [pass] [db-to-restore] < [backupfile.sql]

Backup and Restore MySQL using PHPMyAdmin

You could use PHPMyAdmin, which is one of the most popular applications for MySQL database management, to backup and restore your MySQL database by exporting and importing.

The steps of exporting MySQL database are listed below:

1. Open phpMyAdmin, select MySQL database at the liston the left of the screen.

2. Click the Export link. A new page appears to view the dump of database.

3. Click the Select All link to choose the tables in your MySQL database.

4. Choose the appropriate options in the SQL options area.

5. Click Save as file, and the corresponding compression option, and click Go to save the file locally.

Importing your MySQL database is similar with exporting:

1. Open phpMyAdmin. Click Add to add your MySQL database in the phpMyAdmin setup page. Input the connection details and login information when prompted.

2. Create an appropriately named database and select it on the left of the screen. Click the database name, select all the check boxes beside the table names to rewrite the existing database, and select Drop to delete the existing tables.

3. Click the SQL link to show the page where you can type in SQL commands, or upload your SQL file.

4. Use the browse button to find your MySQL database file.

5. Click the Go button to import the backup, execute the SQL commands, and re-create your database.

Backup and restore MySQL database easily and securely via eBackupper

Except for those two ways for MySQL database backup and restore, you could try the third party database backup software. eBackupper will satisfy your demands to backup and restore your MySQL database. eBackupper is a free web-based online data backup software, which allows you to backup websites and databases on any devices or platforms (Windows, Linux, AppleOS, etc.). First of all, you could go https://ebackupper.com/ to sign up an eBackupper account. Then log in to the account.

To backup and restore your MySQL database, you could do something first:

Add a cloud drive to your eBackupper account.

Add your MySQL database to your eBackupper account.

Add cloud drive

You could add your clouds to your eBackupper account to save MySQL database backup file. eBackupper supports cloud drives like Google Drive, OneDrive, Dropbox, etc. The instruction to describe how to add a cloud drive is listed below:

At the top of window, choose Management -> Clouds.

Add Clouds

Press +Add Cloud Drive to choose Google Drive/OneDrive/Dropbox, etc. And hit Next.

Add Cloud Drive

Add Google Drive

Click Google Drive/OneDrive/Dropbox/… Acount to add the cloud.

Add MySQL database

In order to add MySQL database, follow these steps to add:

1. Click Management and Sources at the top of page.

Add Sources

2. Choose DB Source, and press +Add DB Source button.

Add DB Source

3. Select MySQL to add and enter MySQL database authorization information and click OK.

Add MySQL

Tips:

  • Host: input the IP address of your MySQL database.
  • Alias: input a task name for your MySQL backup task.

MySQL database have been added successfully now, you can backup MySQL database at once.

Backup MySQL database easily and securely

After adding cloud drive and MySQL database, you can backup MySQL database in Windows or Linux now. Refer to this instruction:

Click Database Backup at the tool bar.

Database Backup

Click + Add the Database you want to backup.

Press Select Cloud Drive to choose the cloud drive you want to store the MySQL backup file.

Click Start Backup Now.

Start Backup Now

Tip: Enable the Schedule option to set the backup task running once/daily/weekly/monthly if required.

Restore MySQL database via eBackupper

It’s quite easy to restore MySQL database backup file. Here it is:

Click Task Manager, then it will show all your backup tasks.

Choose the MySQL database backup task, press Restore.

Restore MySQL

Select all files of MySQL database to restore, and hit Next.

Select Files for MySQL Database Restore

Choose a location for MySQL database: Restore to original location or Restore to a new location, and then press Restore to restore MySQL database.

Restore MySQL Database to Original Location or New Location

Conclusion

It’s easy to do MySQL database backup and restore to protect your data from losing or corrupting. eBackupper is a reliable and powerful solution to do website and database backup/restore. It allows you to restore the backup file to identify location or other locations to get a better performance. You can backup FTP/SFTP website to cloud drive, and this program allows you to download the backup file from the cloud drive to your local hard drive, backup multiple websites or databases in one task, selective backup or restore, etc.

Webmasters’ Favorite

  • One-click Backup to Cloud

    One simple click to backup websites as well as databases online data and save them to your private cloud drives. eBackupper currently supports Google Drive, Dropbox and OneDrive and more

  • Quickly and safely Recovery

    One-click restore target website or database from backups saved on cloud. One-click rescue failed website/database from data loss and reduce financial loss as much as possible for you.

  • Migrate Websites / Databases

    Specially restore websites or databases to other locations instead of original one; migrate websites/databases to a new platform to achieve better performance.

  • Learn More

Users’ Review

Joanna Berry

By using eBackupper cloud backup service, I get a full protection to my database with many valuable data. One suggestion is that hope eBackupper will support more kinds of databases and cloud drive destinations in the near future!

Markus Kasteiner

eBackupper saves me much time and money to back up my private website to my Google Drive without any charge. With this insurance, I will never worried about my website been attacked by any ransomware. It is just so amazing!

David Wilkinson

eBackupper is an online free service that allows users to back up their websites or databases to various cloud services as well as restore from cloud images to original or new target location. Only one click, protect our data from unexpected loss.