How to automate MySQL backups with artificial intelligence? | Antonio Pedoto
Practical guide to automating MySQL backups with AI: Python scripts, local LLM programming, and production testing. Learn more!
Want to automate MySQL backups with artificial intelligence? Here is how I did it on a Linux server running MySQL 8.0.
A Python script creates the dump, compresses it and checks it. A local LLM reads the results of those checks and tells you if something is wrong. No external APIs: your data stays on your server.
Want to see how it works? Keep reading.
How to automate MySQL backups with artificial intelligence?
Backups should not be a manual job. I always ran them by hand. Then I got tired of it.
Basic principles of backup automation
Automation means three things: schedule, run, verify. No forgotten runs. No rushed checks the next morning.
The starting point is a Python script. Then I add a local LLM to read the results.
Why? Because manual checks do not scale. And what if the backup fails at night? Nobody knows until morning.
I have seen worse. An empty backup. Zero notifications. Lost data.
Role of AI in script optimization
AI does not replace the script. It works alongside it. Python does the real checks: size, format, checksum. The LLM reads those results and explains them in plain language.
One important point: the model cannot open the file on its own. It only receives the report the script passes to it.
I use a local model, for example Llama 3 with Ollama. If something looks wrong, an email arrives. Right away.
Setting up the Python environment for the MySQL connection
First things first: install the right tools. Then protect your credentials. Always.
Installing the necessary libraries
I install the MySQL client, which includes mysqldump:
Then the Python libraries the script uses:
Finally Ollama and the local model:
No dead weight. Only what you need.
Setting up credentials and environment variables
Never write passwords in your code. Use a .env file readable only by your user.
Here is my .env. Replace the UPPERCASE values with your own:
Protect the file and create the backup folder:
In the script I load the variables like this:
Done. No passwords in the source code.
Developing the Python script for automated backups
Now the core: dump, compression, checks. Each function does one thing only.
Generating the MySQL dump and compressing it
I use subprocess to run mysqldump. The output goes straight into a gzip-compressed file.
The password travels in the MYSQL_PWD environment variable. That way it does not show up in the process list, as it would with -p on the command line.
The --single-transaction option creates a consistent dump of InnoDB tables without locking them.
Saving to local or cloud storage
I keep a local copy. Then I send one to S3.
boto3 reads the AWS credentials from ~/.aws/credentials, not from the script.
Prefer Google Drive? I use rclone, after setting it up with rclone config:
Integrating a local LLM for intelligent scheduling
This is where artificial intelligence comes in. Not to run the backups. To read the checks and flag problems.
Prompt engineering for backup scheduling
First the objective checks, done in Python. Does the file open? Is it big enough? Does it start and end like a MySQL dump?
The last line of a successful dump always contains Dump completed. If it is missing, the dump stopped halfway.
The minsizemb threshold applies to the compressed file: set it based on the usual size of your backups.
Then I pass the report to the local LLM and ask for a verdict:
No external APIs. Everything stays local. The model only receives the report, never the data.
Automatic integrity verification of the dump
I add a SHA-256 checksum. It tells me if a copy, local or on S3, has become corrupted.
Then I tie everything together in a single entry point:
Note: the final decision is made by the Python checks. The LLM adds a readable explanation; it does not replace the checks.
Deploying in production with cron and monitoring
A script is useless if it does not run on its own. Here is how I put it into production.
Configuring the cron job and logging
I open the crontab with crontab -e and add this line:
Every night at 2 a.m. The cd is there so that load_dotenv() finds the .env file in the script folder.
For logging, at the top of the script:
The log file must be writable by the user running cron. To keep it small, add a rule in /etc/logrotate.d/.
Automatic email reporting
If something goes wrong, I need to know right away.
You need a local mail server, for example Postfix. Alternatively, use your provider's SMTP server with a login.
Every failed backup triggers an email. Every successful one, just a line in the log.
FAQ
How much does it cost to automate MySQL backups with AI? Almost nothing. Ollama and open source models run locally, and the Python scripts are free. You only pay for the server and, if you use it, cloud storage.
Is it safe to use artificial intelligence to manage database backups? Yes, if the model runs locally. The LLM only receives the file name, size, check results and checksum. Never the data, never the credentials.
What are the risks of relying on automated backup scripts? The main risk is silent failure. That is why the script checks every dump and sends an email when something looks wrong. And every now and then, test a real restore on a test database.
In summary Use Python with mysqldump and gzip to create compressed, consistent backups. Protect credentials with a .env file and pass the password through MYSQL_PWD. Run the objective checks in Python and use the local LLM to explain the results. Put everything into production with cron, logs and logrotate. Turn on email alerts for errors and test your restores.
Automation is not a luxury. It is a necessity. With a bit of local AI, it also becomes easier to read.
Want to try it? It took me one afternoon.