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.
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.
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
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]
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.
Except for those two ways for MySQL database backup and restore, you could try the third party database backup software. AOMEI Database Backupper will satisfy your demands to backup and restore your MySQL database. AOMEI Database Backupper 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 AOMEI Database Backupper 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 AOMEI Database Backupper account.
Add your MySQL database to your AOMEI Database Backupper account.
You could add your clouds to your AOMEI Database Backupper account to save MySQL database backup file. AOMEI Database Backupper 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.
Press +Add Cloud Drive to choose Google Drive/OneDrive/Dropbox, etc. And hit Next.
Click Google Drive/OneDrive/Dropbox/… Acount to add the cloud.
In order to add MySQL database, follow these steps to add:
1. Click Management and Sources at the top of page.
2. Choose DB Source, and press +Add DB Source button.
3. Select MySQL to add and enter MySQL database authorization information and click OK.
Tips:
MySQL database have been added successfully now, you can backup MySQL database at once.
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.
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.
Tip: Enable the Schedule option to set the backup task running once/daily/weekly/monthly if required.
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.
Select all files of MySQL database to restore, and hit Next.
Choose a location for MySQL database: Restore to original location or Restore to a new location, and then press Restore to restore MySQL database.
It’s easy to do MySQL database backup and restore to protect your data from losing or corrupting. AOMEI Database Backupper 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.
One simple click to backup websites as well as databases online data and save them to your private cloud drives. AOMEI Database Backupper currently supports Google Drive, Dropbox and OneDrive and more
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.
Specially restore websites or databases to other locations instead of original one; migrate websites/databases to a new platform to achieve better performance.
Joanna Berry
Markus Kasteiner
David Wilkinson
