How to Make a MySQL Database Backup (With Examples)
Categories:
9 minutes reading
Having your website data compromised is one of the most damaging things that can happen when working with databases. It can disrupt your business, damage your reputation, and even lead to legal consequences. And the worst part? It can happen at any stage. A faulty plugin update, a wrong click in phpMyAdmin, or a corrupt server file is all it takes to wipe out years of work. That’s why learning how to create a MySQL backup is one of the most important lessons when you start managing your website’s inner workings. Most beginners don’t struggle with SQL syntax. The real risk lies in not backing up what they’ve already built.
If you’re just starting to explore the backend of your website, mastering backups is even more critical than creating staging environments or spinning up new databases.
Thus, along with our complete MySQL database tutorial, we decided to write an in-depth guide specifically on creating and maintaining MySQL backup, filled with examples from our practice. Now, open your command line, and let’s get started.
Importance of Regular MySQL Backups
You don’t have to be a developer to accidentally delete your database. In our experience as hosting providers, we’ve witnessed all sorts of mishaps. Messing with the wrong plugin. Clicking on pop-ups that you don’t read because you’re in a hurry, or simply forgetting the name of the right database. We’ve seen it all.
Thankfully, we ensure all our users with managed hosting plans are regularly backed up so we can restore the database effortlessly and quickly.
Having a backup and a team of professionals to act lightning-fast certainly minimizes the damage. However, if you’re not using managed hosting or relying on less-than-trustworthy hosting providers, a deleted database can severely impact your business, especially if there’s no backup.
Backups act as your safety net. Whether you run a blog, a business website, or an eCommerce store, your database holds your site’s most valuable content. It’s where your website draws information about your users, orders, posts, forms, and everything related to your website. Without a recent copy, recovery becomes a guesswork process or an expensive endeavor, if it’s even possible.
We’ve also seen cases where hosting servers fail, updates break compatibility, or someone accidentally drops a database table using phpMyAdmin. In one case, a beginner created a staging site without knowing it would overwrite their live content. Thankfully, we had a backup and restored their website in a matter of minutes, saving days of progress that otherwise would have been lost.
Regular MySQL backups make sure none of these events turn into disasters.
And “regular” doesn’t mean once a month. For active sites, a daily backup is often the minimum. For high-traffic stores or dynamic platforms, multiple daily backups are ideal.
MySQL backups are essential when undergoing major WordPress or plugin upgrades, plugin installations, or even if you want to downgrade WordPress if a plugin or a theme is acting weird on the new version.
Now, you might feel scared to even try touching MySQL. However, that’s not a solution. If you want to, you can always try playing around with the security of your own server. All you need to know is how to install WordPress on localhost, and you can experiment safely there.
So, now that we know why, let’s move on to the how.
Using mysqldump for CLI MySQL Backups
While HostArmada offers its users the opportunity to manage MySQL database in cPanel, it’s wise to learn how to do that through the command line. It’s the most effective way to backup your MySQL with full control over what you export. It’s perfect for manual backups or advanced automation down the road.
All you need to learn is one easy command — mysqldump
What Is mysqldump?
Mysqldump is a utility that creates a .sql file containing the structure and data of your database. This file can later be used to restore your site or migrate to a new server.
Think of it as creating a full snapshot of your database — tables, records, relationships, and all.
mysqldump in Action
Here’s basically what you need to enter
mysqldump -u root -p my_website_db > my_website_backup.sqlThis command tells MySQL to:
- Use the root user
- Prompt for your password (-p)
- Dump everything from my_website_db
- Save it into a file called my_website_backup.sql in your current directory
In our experience, it’s best if you double-check that you’re in a folder with write access before running the command. Also, ensure there are no typos in the database name.
If you’ve forgotten your database name, just use the show databases command in your MySQL shell.
Backing Up Multiple Databases
To back up more than one database at a time:
mysqldump -u root -p --databases db1 db2 db3 > multi_backup.sqlNeed to back up everything? Use the –all-databases flag:
mysqldump -u root -p --all-databases > full_backup.sqlAvoiding Beginner Mistakes
Don’t assume backups work just because the file exists. After creating a .sql backup, open it in a code editor or test it in a staging environment. This ensures you didn’t accidentally back up an empty or incorrect database.
Automating MySQL Backups with Scripts
Doing manual backups every time you update your site quickly becomes tedious and, honestly, a productivity killer. At first, it might feel satisfying to type in the command and generate your .sql file. But repeat that five or more times a day, and it becomes a chore you’re likely to skip when it matters most.
While knowing how to create database in mysql command line is an important foundational skill, it’s not something you need to do every day. Backing up your database, on the other hand, is a task you may need to perform multiple times per day, especially if you’re actively developing, managing transactions, or deploying frequent updates.
That’s where automation steps in. With just a few lines of code, you can schedule your backups to run silently in the background, saving time, eliminating human error, and giving you peace of mind.
Here’s what you need to do:
Sample Backup Script (Linux)
Here’s a basic shell script to back up a single MySQL database:
#!/bin/bash
# Set credentials and file name
USER="root"
PASSWORD="your_password"
DATABASE="my_website_db"
OUTPUT="/backups/db_backup_$(date +%F_%H-%M).sql"
# Create the backup
mysqldump -u $USER -p$PASSWORD $DATABASE > $OUTPUTSave it as mysql_backup.sh, and make it executable:
chmod +x mysql_backup.shSchedule it with cron:
crontab -eAdd this to run daily at 2:00 AM:
0 2 * * * /path/to/mysql_backup.shThis generates timestamped backups like db_backup_2025-06-26_02-00.sql, making it easy to organize or rollback.
Sample Backup Script (Windows)
For Windows, you can use a simple .bat file:
mysqldump -u root -pYourPassword my_website_db > C:db_backupsbackup.sqlThen automate it:
- Open Task Scheduler
- Create a new basic task
- Set your schedule
- Point to the .bat file
Automating multiple backups
Need to back up several databases? Use a loop in your script:
DATABASES="db1 db2 db3"
for DB in $DATABASES
do
mysqldump -u $USER -p$PASSWORD $DB > /backups/${DB}_$(date +%F).sql
done
Each backup is saved as a separate, date-labeled file.
Remember
- Store backups outside your main hosting folder when possible
- Use restricted permissions to secure backup directories
- Test your script after editing. Don’t assume it’s working silently
GUI and cPanel Backup Options
Not everyone wants to mess with command-line tools, and that’s perfectly fine. If you’re more comfortable with a visual interface, cPanel and graphical tools like MySQL Workbench offer a simpler way to create backups. Coding is hard, and that’s why most good hosting providers give you access to cPanel.
Typically, the pathway is easy:
- Log into your cPanel account
- Scroll to the “Files” section and click “Backup”
- Under “Download a MySQL Database Backup,” select your database name
- Click to download the .sql.gz file — that’s your backup!
You can’t preview what’s inside a .gz file unless you extract it. Always double-check the file size and date before considering the backup valid.
There might be some divisions depending on your hosting provider. If you’re a HostArmada user with managed hosting plan, you don’t need to worry about backups. Still, if you want to know where the magic happens, our ‘How to Use the Backup Configuration Feature in WHM‘ guide will lead you through the process.
MySQL Workbench (GUI)
MySQL Workbench is another great option if you’re managing your site from a local computer. It’s a free graphical interface for interacting with MySQL databases.
To back up using Workbench:
- Open MySQL Workbench and connect to your database
- Go to server> Data Export
- Choose your database and export options
- Click Start Export
This tool is especially handy for remote database management or when you don’t want to log into hosting panels regularly.
If you’re doing multiple backups per day or running production sites, CLI + automation will still be your best long-term strategy. But GUI options are a great entry point and excellent for low-risk tasks or manual snapshots before updates.
Where and How to Store Backups
Considering how important the backup is, it’s only logical you’d want to keep it somewhere safe. While your server is definitely among the most secure places you can choose, if it’s the same server where the original database is, that’s not ideal. If the server gets corrupted, crashes, or entirely burns out, your MySQL backup will be just as gone as the original database.
So, here’s what to do
Use Off-Site or Cloud Storage
This is the bare minimum. Your MySQL backup should be on a separate server or in the cloud, away from your live website. You can keep it in Google Drive, Dropbox, or a remote FTP Server. In our experience, the first two are ideal for beginners, as they are familiar and easy to access. However, they are not the most secure environments.
The FTP Server is much better secured, but it’s also more expensive. Having a dedicated storage server doesn’t come cheap.
Finally, you can choose Amazon S3 or BackBlazeB2, which are ideal for long-term storage with built-in redundancy.
Keep a Local Copy
Saving backups to your personal computer is convenient, but it shouldn’t be your only strategy. Hard drives fail, laptops get stolen, and files can be overwritten. Still, having a local copy is smart, especially for quick testing or rollback during development.
Structure and Label Backups Clearly
You need to know which backup is which. If you want to revert to a previous version of your website, one that’s not yet corrupted, yet you have maximum information, it’s not enough to roll back to the last MySQL backup. The breach may have occurred several days ago, so you need to know precisely when each backup was taken. The easiest way to do that is to timestamp your MySQL backups in the filename, as well as the backed-up environment. For example, “production_website_database-06-26.sql”
This makes it easier to find the right version quickly, especially during high-stress moments like a restore or migration.
Encrypt and Protect Your Backup Files
Corrupt backup files are not as rare as one may think. That’s less than ideal, as MySQL backups usually contain sensitive user data like emails, passwords, personal info, and other information. If they fall into the wrong hands, your site could face legal and security risks.
Use tools like gpg or ZIP encryption to secure your backup files. Always apply proper file permissions (e.g., chmod 600) when storing backups on a server.
In our experience, while regular backups are essential, what makes a recovery plan reliable is how smart and secure you will store your MySQL backup.
MySQL Backups are Your Safety Net
Learning how to create and store a MySQL backup isn’t just a technical skill. It’s your website’s safety net. It doesn’t take a malicious act to lose your database. All it takes is a moment of absentmindedness to see years’ worth of work gone forever. That’s why understanding backups is more important than knowing how to stage, migrate, or even create a new database.
But here’s the good news. You don’t have to handle it all on your own.
At HostArmada, we completely eliminate the stress of backups. Every hosting plan includes automated daily backups — with the number of backups scaling up depending on the plan you choose. Whether you’re launching a personal blog or managing an eCommerce store, we’ve got your data protected.
And if something ever does go wrong, our 24/7/365 support team is just one chat, call, email, or ticket away. It’s up to you how you want to communicate with them.
e can restore your site from a recent backup in less than an hour — so you can stay focused on growing your business, not fixing tech disasters.
Plus, all HostArmada plans come with lightning-fast speeds, top-of-the-line security, and a 99.9% uptime guarantee, giving you peace of mind from day one.
Check out our hosting plans and choose the one that fits your needs best. Your data deserves it.
