When managing databases in production, one of the most important things you can do is secure access and automate backups.
Today, I took a few simple but powerful steps to make sure my database setup is safe and reliable.


🔐 Step 1: Restrict Database Access by IP

By default, MySQL listens on port 3306 which can be risky if it’s open to the world. To secure it, I configured my firewall (UFW) to only allow connections from a few trusted IPs:

sudo ufw allow from IPHERE to any port 3306

Then I verified the changes with:

sudo ufw status numbered

This ensures that only specific servers (like my app server) can reach the database, no one else.


💾 Step 2: Automate Backups with mysqldump

Next, I wrote a simple shell script that uses mysqldump to export all my databases into .sql files.

Script Can be found here: Mysql database backup

I keep one copy on my main server and another on a secondary server, so even if one goes down, I still have a recent backup available.


⏰ Step 3: Automate with Cron

To make sure backups happen automatically, I scheduled the script to run every 2 days at 5 AM:

0 5 */2 * * $HOME/database_backup

This way, I don’t have to think about it. Backups happen quietly in the background.


🧠 Final Thoughts

This small setup might seem simple, but it provides a lot of peace of mind. I now have:

  • A locked-down database server
  • Automated, redundant backups
  • A clean and predictable backup schedule

Security and reliability: Done right and kept simple!