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 3 feasible ways.
How to Backup MySQL Database with Mysqldump
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.
- 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.
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 MySQL Database with MySQL 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:
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.
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 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. 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.
- Perfect Integration: Manage up to 30+ cloud drives centrally;
- Random Migration: Do one-time or incremental migration according to your own needs;
- Real-time Sync: Provide 10 custom sync modes to simplify collaboration;
- Smart Backup: One-key cloud-to-cloud transfer, sync, backup.
- Remote Upload: Directly download URLs, torrent files or magnet links to clouds.
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 Backup" option for backup.
This feature assists you in automatically backing up your data to the destination and also provides "Restore" options.
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, OneDrive, SharePoint Online, OneDrive for Business, MEGA, Flickr, Box, Box for Business, pCloud, Amazon S3, Wasabi, SugarSync, HubiC, 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.