Automatically backing up a MySQL database prevents data loss caused by errors, hacks, or failed updates. The simplest approach is to use a cronjob with mysqldump or a control panel like Plesk that schedules and stores backups. In this article, you’ll learn which methods exist, how to set them up, and what to watch out for so that recovery actually works.
Why automatic backups are important
Databases change constantly: new orders, user accounts, form submissions. It’s easy to forget manual backups, and precisely at the moment you need one, the latest backup is often too old or missing entirely. By automating the process, you ensure there’s always a recent copy ready, without having to think about it yourself.
A good backup strategy doesn’t just involve making copies, but also storing them in a safe location, separate from the server itself, and regularly testing whether a restore actually works.
Backing up with mysqldump and cron
On a server with shell access, you can combine mysqldump with a cronjob to create a dump at fixed times. A simple command looks like this:
mysqldump -u user -p'password' databasename > /path/to/backup_$(date +%F).sql
By putting this command in a cronjob, for example every night at 03:00, you automatically get a daily file with the date in the name. Make sure old backups are cleaned up automatically over time, otherwise disk space will fill up.
If you want to store the backup extra securely, compress the file with gzip and move it to an external storage location, so that a problem with the server itself doesn’t affect the backup.
Automating backups via Plesk
If you’d rather avoid the command line, a control panel like Plesk is a pleasant alternative. Through Plesk, you set up a backup schedule where you choose the frequency, time, and retention period yourself. Databases are stored together with the rest of the website, so you don’t have to puzzle out which files belong together during a restore.
At Tandata, you’ll find this setting under the Backup Guide section within Plesk, where you can also choose to run backups automatically on a daily basis in addition to the standard backups already made for you.
Choose your method
Determine whether you’ll use mysqldump with cron or the backup function in Plesk.
Set up a schedule
Plan the backup during a quiet moment, for example at night, so the load on the server is minimal.
Determine the retention period
Keep multiple versions so you can also go back to a point from a few days ago.
Store outside the server
Move or copy backups to external storage for extra security.
Test the restore
Regularly check whether a backup can actually be restored.
Testing and restoring backups
A backup is only valuable if you know it works. Therefore, periodically restore a test backup to a temporary database to check whether all tables and data are intact. With mysqldump, you use the command mysql -u user -p databasename < backup.sql for this.
If you use Plesk, you can easily select and restore a previous version via the backup interface, without having to manually move files around. If you run into problems, the support team is available day and night via WhatsApp, ticket, or email to help.
If you’re considering switching to a provider where backups and security are properly arranged by default, check out the options for switching to Tandata or read more about web hosting with daily backups included.
Frequently Asked Questions
How often should I back up MySQL?
That depends on how often your data changes. For most websites, daily backups are sufficient, but with a lot of transactions, more frequent backups may be needed.
Can I combine automatic backups with manual backups?
Yes, it’s even recommended to always make an additional manual backup before major changes, such as updates.
Where is the best place to store mysqldump files?
Outside the server itself, for example on external storage, so that a problem with the server doesn’t affect the backup.
Does this also work for large databases?
Yes, although mysqldump can be slower with very large databases. In that case, consult your hosting provider about the best approach.
Are backups already arranged by default at Tandata?
Yes, Tandata makes daily backups of hosting packages; you can also set up additional schedules yourself via Plesk.
Conclusion
Automating MySQL backups can be done easily with mysqldump and a cronjob, or through the backup function in Plesk. It’s important to set up a fixed schedule, store backups outside the server, and regularly test whether a restore actually works.