How to automate a MySQL backup, just in case something happens to destroy or alter your database tables.
If you’re using a hosted solution on Google Cloud, AWS, or Microsoft Azure, then you will probably be able to use their backup tools, or snapshot tools to keep a current backup. They’ll probably have a way for you to easily restore your database too!
But sometimes you’re going to be hosting your own database, on your own server, or on an EC2 instance, or virtual server. And when that’s the case. You can use the power of cron, and a small bash command to set up a nightly dump, and name it something like “date 2022-09-28.gz”
I found this treasure and hopefully it’ll help you too.
If you’re mysqldump bin is somewhere else, you’ll want to replace /usr/local/bin/mysqldump with a path to your own mysqldump binary. Same with name of the database and the password
// Set a backup every night at midnight for Linux
crontab -e
0 0 * * * /usr/local/bin/mysqldump -uLOGIN -PPORT -hHOST -pPASS abc123 | gzip -c > `date “+\%Y-\%m-\%d”`.gz