🛡️ MySQL Multi-Server Auto Backup Utility (Windows)

A robust and flexible batch-script-based utility for automatically backing up multiple MySQL databases across multiple servers on Windows, with:

• .env-based configuration (no hardcoding)
• Timestamped .sql file creation
• Optional ZIP compression (via 7-Zip)
• Automatic cleanup of old backups
• Logging to both activity and error logs
• Support for Windows Task Scheduler for scheduled runs
• Optional silent background mode using run_hidden.vbs

---

📦 Features

• ✅ Multi-Server & Multi-Database Backup: Easily back up multiple databases from different servers using a single, centralized script.
• ✅ Dynamic .env Configuration: All sensitive information and configurations are stored in environment files, allowing for easy management and secure version control.
• ✅ Centralized & Detailed Logging: All activity is logged to a main activity log (.log), with specific mysqldump errors redirected to a dedicated error log for streamlined troubleshooting.
• ✅ Automated Cleanup: The script automatically deletes old backup files based on a configurable retention policy, preventing disk space issues.
• ✅ Robust Error Handling: The script checks for the existence of mysqldump.exe and 7z.exe before execution and provides clear, actionable error messages.
• ✅ Optional Compression: Save disk space by automatically compressing .sql files into .zip archives using 7-Zip.
• ✅ Silent Execution: Designed for seamless, non-interactive use with Windows Task Scheduler, with an optional VBS file to run the script silently in the background.
• ✅ Git-Friendly Structure: The project includes a .gitignore file to prevent sensitive .env files and large backup archives from being accidentally committed to version control.
• ✅ No Third-Party Installers: The script relies on standard Windows commands and the optional mysqldump and 7-Zip executables, requiring no complex installation process.
• ✅ Customizable Paths: All file and directory paths are configurable, allowing the script to be used in any environment without modification of the core logic.
• ✅ Automatic Security with .htaccess: Automatically places a .htaccess file in the backup directory to prevent public web access to your sensitive backup files.

---

✅ Folder Structure

mysql-auto-backup/
├── mysql_multiServerbackup.bat                \# Main backup script
├── servers/                  \# Contains per-server configuration files
│   ├── server1.env          \# e.g., db1.env
│   └── server2.env          \# e.g., db3.env
├── run\_hidden.vbs            \# Optional VBS launcher for silent execution
├── .env                      \# Main configuration file (ignored by git)
├── .env.example              \# Sample main configuration file
├── .gitignore                \# Prevents sensitive files from being pushed
└── README.md                 \# Project documentation

---

✅ Best Way for Full Silent Run (VBS + Task Scheduler)

Use this with wscript.exe via Task Scheduler:

Set WshShell = CreateObject("WScript.Shell")
WshShell.Run """F:\Backups\mysql_multiServerbackup.bat""", 0
Set WshShell = Nothing

Then schedule this:

Program/script: wscript.exe
Arguments:     "F:\Backups\run_hidden.vbs"

---

🔧 Setup Instructions

1. Clone This Repo

git clone [https://github.com/yourusername/mysql-auto-backup.git](https://github.com/yourusername/mysql-auto-backup.git)

2. Configure Environment

Copy .env.example to .env.

Edit .env and fill in your:

• SERVERS: a comma-separated list of server names (e.g., db1,db3).

Then, create a separate .env file for each server inside the servers/ directory (e.g., servers/db1.env and servers/db3.env) with the following content:

DB_HOST=172.16.1.120
DB_USER=root
DB_PASSWORD=.
DATABASES="db1 db2"

---

1. mysql_multiServerbackup.bat

This is the final, corrected script that handles multi-server backups, logs errors, and manages file retention.

---

2. run_hidden.vbs

This file remains the same and is used to run mysql_multiServerbackup.bat silently in the background.

Set WshShell = CreateObject("WScript.Shell")
WshShell.Run """F:\Aravind Erp Assi\GitHub\MySQL-Auto-Backup-Script--Windows-Batch-\mysql_multiServerbackup.bat""", 0
Set WshShell = Nothing

---

3. .env and servers/ Directory

The project now requires a main .env file and a servers/ directory containing a .env file for each server.

.env

SERVERS=db1,db3
BACKUP_ROOT=D:\DailyBackup
MYSQLDUMP_PATH=c:\path\to\mysqldump.exe
ZIP_EXE="C:\Program Files\7-Zip\7z.exe"
RETENTION_DAYS=7

servers/db1.env

DB_HOST=172.16.1.120
DB_USER=root
DB_PASSWORD=
DATABASES="db1 db2"

servers/db3.env

DB_HOST=172.16.1.100
DB_USER=db3_user
DB_PASSWORD=db3_userpassword
DATABASES=db3

---

3. Run Manually

Double-click mysql_multiServerbackup.bat to test.

4. Run Silently with Task Scheduler

Use run_hidden.vbs as your Task Scheduler action:

Program/script: wscript.exe
Arguments:     "F:\Path\To\mysql-auto-backup\run_hidden.vbs"

---

📄 MySQL Server Configuration

If you get an "access denied" or "host not allowed to connect" error, you need to configure your MySQL server to allow remote connections and grant the user proper permissions.

1. Allow Remote Connections

On the MySQL server, edit the my.ini (Windows) or my.cnf (Linux) file and change bind-address to 0.0.0.0.

bind-address = 0.0.0.0

2. Grant User Permissions

Log in as root on the MySQL server and use these modern commands (for MySQL 8.0+):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
ALTER USER 'root'@'%' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

3. Change Authentication (if needed)

If you're still getting "access denied," your client may not support the modern authentication plugin. Change it to the legacy one with this command:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

---

🧹 File Retention Policy

Backups older than RETENTION_DAYS in your .env will be deleted automatically after each run.

---

Output Structure

Backups\
├── db1\
│   ├── db1\
│   │   └── 2025-07-02\
│   │       └── db1_2025-07-02_1445.sql.zip
│   └── db2\
│       └── 2025-07-02\
│           └── db2_2025-07-02_1445.sql.zip
├── db3\
│   └── db3\
│       └── 2025-07-02\
│           └── db3_2025-07-02_1445.sql.zip

---

📄 License

MIT – Free to use, modify, distribute.

---

🙋 Questions?

Open an issue or contact the maintainer.

Google @ Aravind Govindhasamy 
Mail ID : [email protected]