[4 Easy Ways] How to Backup MySQL Database

Quick Search:

• Preface
• How to Backup MySQL Database using Command Line
• How to Backup a MySQL Database using Workbench
• How to Backup MySQL Database using phpMyAdmin
• How to Backup MySQL Database to Another Server Automatically
• Summary
• FAQs

Preface

Almost everyone these days knows that backups are important and that digital data stored on a computer can be lost or corrupted, not to mention databases stored on a server. If you are the administrator of the server, you should pay special attention to your data. 

Many individuals and businesses choose to store their entire site's data in a MySQL database, which can be catastrophic if lost or corrupted. So, how to take backup of MySQL database? This article will provide the following 4 feasible ways.

How to Backup MySQL Database using Command Line

When it comes to backing up MySQL, we have to mention "Mysqldump", which is a command-line tool provided by MySQL itself. If you have a shell or telnet access to the web server, you can use Mysqldump. The Mysqldump client utility can dump the database, including the SQL statements needed to rebuild the database.

By default, the dump file contains SQL commands to restore tables and data. To back up a MySQL database, the general syntax is:

sudo mysqldump -u [user] -p [database_name] > [filename].sql

• Replace [user] with your username and password (if required).
• The [database_name] is the path and filename of the database.
• The > command specifies the output.
• The [filename] is the path and filename you want to save the dump file as.

Other examples:

Back up the entire database management system: mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

To restore data, use the following commands: mysql -u [user] -p [database_name] < [filename].sql

To include multiple databases in a backup dump file: sudo mysqldump -u [user] -p [database_1] [database_2] [database_etc] > [filename].sql

But the disadvantage of this method is obvious - the command line is very complicated, and the user has to remember a lot of commands. Well, there are other backup methods, such as copying compression, using third-party tools, etc.

How to Backup a MySQL Database using Workbench

The most straightforward way to create a MySQL backup is to simply copy the contents of the MySQL data directory. To manually create a file-based backup of MySQL server, follow these steps:

Step 1. Find the MySQL Server data directory, the easiest way is through MySQL Workbench. To do so, run this command:

select @@datadir;

Step 2. Find and stop the MySQL server service

• Press win+R;
• Enter services.msc;
• Find the MySQL Server service in the service list;
• Right-click on it and select Stop;

Step 3. Copy or package the contents of the MySQL server data directory to another location.

Step 4. Start the MySQL server by clicking "Start" in the context menu of the service.

How to Backup MySQL Database using phpMyAdmin

You can easily create a dump file (export/backup) of the databases used by your account. At this point, you can do it by visiting the phpMyAdmin tool available in cPanel.

Step 1. Open phpMyAdmin from your web hosting control panel such as cPanel.

Step 2. Select databases from phpMyAdmin's sidebar navigation panel. 

Step 3. Click the "Export" option in the top navigation bar. 

Step 4. On the export page, select the export options: Quick and Custom. For your convenience, you can choose the "Custom" option.

Step 5. Select the table to be backed up. When you select the "Custom" option, the page will allow us to choose which tables to back up. If you're not sure what to choose, leave it as is (all tables are selected by default).

Step 6. Select the "Compression" option as gzipped and leave the rest unchanged. 

Step 7. Click the "Go" button, then your backup file will be downloaded automatically. The file name will be NameOfYourDB.sql.gz file. Depending on the size of your database, this process may take some time.

How to Backup MySQL Database to Another Server

In addition to the above backup methods, you can also use a third-party cloud-to-cloud backup tool - MultCloud to backup MySQL to other services. MultCloud is a web-based cloud file manager that allows you to back up, sync and transfer files across cloud services with ease and freedom.

Compared to other tools, MultCloud can give you more benefits:

 

MultCloud has added MySQL backup since version 3.5, so you can access MySQL data in MultCloud. Because MultCloud also supports other servers, and it also allows adding unlimited MySQL accounts to it, you can use MultCloud to backup MySQL to another server, such as Google Drive.

Steps to backup MySQL using MultCloud:

Step 1. Visit MultCloud to create an account. Enter your email address and password to register. Alternatively, you can authorize login directly using your Google and Facebook accounts.

Step 2. Select MySQL and add it to MultCloud. You will need to enter information including display name, MySQL server name, username and password.

Step 3. Select the server you want to backup MySQL data to, for example: Google Drive. Then add it to MultCloud as above. You can see all the data in MySQL and Google Drive in MultCloud's interface.

Step 4. Backup MySQL to Google Drive. If you want to back up the file, you can right-click and select "Copy", then switch to the Google Drive interface to paste. You can also select the "Copy to" option and then select Google Drive in the popup.

If you need to move the entire database of MySQL to another server, for convenience, you can choose the "Cloud Transfer" option for backup.

Summary

Regarding "How to Backup MySQL Database", the above methods can meet all your needs. In addition, it is worth mentioning that MultCloud, as a multiple cloud storage manager, supports 30+ cloud storage services, which can help you better manage multiple cloud storage accounts.

Dropbox, Dropbox Business, Google Drive, Google Workspace, Google Photos, iCloud Photos, iCloud Drive, OneDrive, SharePoint Online, OneDrive for Business, MEGA, Flickr, Box, Box for Business, pCloud, Amazon S3, Wasabi, SugarSync, Evernote, CloudMe, Cubby, MyDrive, WEB.DE, Yandex, HiDrive, MySQL, Egnyte, Putio, Backblaze, MediaFire, ownCloud, ADrive, Baidu, even NAS, WebDAV, FTP/SFTP, Shared with me and Shared Drive.

If you want to sync files, "Cloud Sync" can help sync files between multiple cloud storage services in real time. By the way, by sharing MultCloud to your social accounts and submitting the sharing link, you will have a chance to get LIFETIME UNLIMITED data traffic in MultCloud.

FAQs

1. What are the different backup formats available for MySQL databases?

MySQL databases can be backed up in various formats, such as SQL, CSV, XML, or compressed formats like Gzip or Zip. The SQL format is the most common as it allows for easy restoration and is supported by most MySQL tools.

2. Should I lock tables while performing backups?

InnoDB tables support online backups, which means you can perform backups without locking the tables. However, for MyISAM tables, it is recommended to lock the tables during the backup process to ensure data consistency.

3. Where should I store my MySQL backups?

Store your MySQL backups in secure, off-site locations, such as external hard drives, network-attached storage (NAS) devices, or cloud storage platforms. This helps protect your data from physical damage or local disasters.

4. How often should I back up my MySQL database?

The backup frequency depends on your data's criticality and how frequently it changes. For critical data, it's best to perform daily or incremental backups, while less critical data may require weekly or monthly backups.