Quick Search:

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.

Back up MySQL Database
Back up MySQL Database

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.

Pre-requisites:

  • A Linux operating system
  • MySQL installed
  • Existing database
  • Mysqldump utility (should be included with your MySQL software)

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;

Run Command
run-command

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;
Stop MySQL Server
Stop MySQL Server

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.

However, physical backups have two important disadvantages:

  • Backups can only be ported to other machines with the same or similar hardware characteristics.
  • The solution for creating a hot backup can be too expensive for small businesses, as the only Windows tool that allows you to create a physical backup without stopping the server is MySQL Enterprise Backup.

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. 

Select Databases from phpMyAdmin
Select Databases from phpMyAdmin

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

Export
Export

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

Choose Export Methods
Choose Export Methods

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).

Choose Tables to Backup
Choose Tables to Backup

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

Choose Compression Formats
Choose Compression Formats

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.

Execute the Export Task
Execute the Export Task

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:

  • Scheduled backup: You can back up data to other cloud accounts daily, weekly or monthly.
  • Fast backup: When the advanced version is enabled, MultCloud can provide you with a high-speed backup mode, which can save a lot of time.
  • Restore at any time: After the task is enabled, you can find the backup data to restore.

 

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.

Sign Up
Sign Up

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

Add MySQL
Add MySQL

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.

Files in MySQL
Files in MySQL

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.

Copy MySQL Files to Google Drive
Copy MySQL Files to Google Drive

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

Cloud Transfer
Cloud Transfer

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.

Invite Friends
Invite Friends

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.

MultCloud Logo

Manage 30+ popular clouds in one platform.

Sign up Free

100% secure

New Release

Save Email as PDF

After adding Gmail or Outlook to MultCloud, it can convert emails to PDF and save them on your device or cloud storage.

Get Started for Free