5 Solutions to Create MySQL Automatic Backup

How to backup MySQL Database automatically in Windows Server?

For people who need to back up their database frequently, it's not a good idea to do the setup manually every time. So, is there a way to create MySQL automatic backup in Windows servers or client computers at certain times? Keep reading to learn 5 effective methods.

Way 1. Create MySQL Automatic Backup with AutoMySQLBackup

To create MySQL automatic backup, AutoMySQLBackup could helps you create daily, weekly, monthly backup for your MySQL databases. It provides you email notification, compression and encryption, backup rotation, and incremental database backup.

1. Please download AutoMySQLBackup.

2. Install Automysqlbackup and create auto MySQL Backup with following operations:

• Run the install.sh script.

• Edit the /etc/automysqlbackup/myserver.conf to customize your fit your setup (like MySQL user, password, backup location).

• Create a script called runmysqlbackup using the lines below:

#!/bin/sh
/usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf
chown root.root /var/backup/db* -R
find /var/backup/db* -type f -exec chmod 400 {} \;
find /var/backup/db* -type d -exec chmod 700 {} \;

• Copy the script to /etc/cron.daily folder, and make it executable.

3. The MySQL automatic backup will be run from the command line via this command:

automysqlbackup /etc/automysqlbackup/myserver.conf

Way 2. MySQL Auto Backup Script

1. Create a MySQL auto backup script like th following:

#!/bin/bash
#
BKP_USER="myuser"     # Enter the username for backup
BKP_PASS="mypassword"        # Enter the password of the backup user
#
BKP_DEST="/backup" # Enter the Backup directory,change this if you have someother location
#
BKP_DAYS="2" # Enter how many days backup you want,
#
MYSQL_HOST="localhost"  
#
#
BKP_DATE="$(date +"%d-%m-%Y-%H:%M:%S-%a")";
#
IGNORE_DB="information_schema mysql performance_schema"
#
[ ! -d $BKP_DEST ] && mkdir -p $BKP_DEST || :
#
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
#
#
DB_LIST="$($MYSQL -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS -Bse 'show databases')"
#
for db in $DB_LIST
do
    skipdb=-1
    if [ "$IGNORE_DB" != "" ];
    then
for i in $IGNORE_DB
do
   [ "$db" == "$i" ] && skipdb=1 || :
done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
BKP_FILENAME="$BKP_DEST/$db.$BKP_DATE.gz"
#
#
        $MYSQLDUMP -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS $db | $GZIP -9 > $BKP_FILENAME
    fi
done
#
find $BKP_DEST -type f -mtime +$BKP_DAYS -delete
#

Tip: change the value of BKP_USER, BKP_PASS, BKP_DEST, BKP_DAYS according to your needs.

2. Name the script as mysqlbackup-script.ps1.

3. Then click Start, search for powershell, and run as adminstrator.

4. Run the command: Set-ExecutionPolicy RemoteSigned, input A and hit Enter.

5. And then run the command:

& "C:\PATH\TO\SCRIPT\first_script.ps1" (for me, this is: & “D:\Ailsa\mysqlbackup-script.ps1”)

Way 3. Create Auto MySQL Backup via Task Scheduler

1. Copy the following content into a text file and save it:

cd “C:\Program Files\MySQL\MySQL Server 5.6\bin”
mysqldump -h 127.0.0.1 -u root -p --all-databases > "D:\Ailsa\allmysql.sql"

2. Rename the file named mysqlbackup.bat(make it executable).

3. Input task scheduler in search box, and choose Task Scheduler.

4. Click Task Scheduler Library > Create Task....

5. Create a name for the MySQL automatic backup in General tab.

6. Click New... in Triggers tab, select On a schedule in Begin the task section, and then choose Daily, and hit OK.

7. Tap New... in Actions tab, choose Start a program in Action, click Browse... to select the mysqlbackup.bat file and hit OK > OK.

And it will run at the specific time you set up automatically to create MySQL automatic backup.

Way 4. MySQL Automatic Backup with cPanel

cPanel is a web hosting control panel to manage the website, including MySQL databases. You could create MySQL automatic backup with cPanel Cron Jobs. Cron Jobs allows you to automate certain commands or scripts on your site running at daily, weekly, etc.

here are the steps to create MySQL auto backup:

1. Please login cPanel, and select Cron Jobs in Advanced section.

2. You could customize the Cron Email and Add New Cron Job, here we just set the last.

3. Click Common Settings to set up the intervals to run the MySQL database backup. (or you could set the time to run at the following box)

4. Input the command in Command box: mysqldump -u -p --all-databases > database_backup.sql (change it to fit your settings accordingly)

5. And then press Add New Cron Job.

Way 5. Create MySQL Database Automatic Backup in Windows

Here, we will show you how to create MySQL automatic backup in Windows Server 2019 with the MySQL backup software - AOMEI Backupper Technician Plus. It suppots unlimited computers running Windows Server 2003, 2008, 2012, 2016, 2019 (including R2), 2022, SBS 2003, 2008, 2011 or Windows 11, 10, 8.1, 8, 7, Vista, XP.

With it, you can easily set up a schedule for your MySQL database, and the options include daily, weekly, monthly, etc. If you are worried that frequent backups will take too much disk space, you can also perform incremental or differential backup to copy only changed files, or choose a higher compression level.

1. Launch AOMEI Backupper Technician Plus, and select Backup and File Backup.

2. Click + Add Folder or + Add File button to choose the MySQL database files. Rename the Task Name if necessary.

3. Select a destination to save MySQL auto backup file.

4. Tap Schedule option to run MySQL database backup task via Daily, Weekly, Monthly, Event triggers, or USB plug in, choose Incremental Backup in Advanced tab.

5. Press Start Backup >> button to create auto MySQL Backup in Windows Server 2019.

Tips:

• Options: It enables you to comment, compress, split, encrypt the MySQL automatic backup file, and enable email or SMS notification, etc.

• Automatic Backup Cleanup: If there are many backups on the destination and show low disk space, you could enable automatic backup cleanup in Backup Schedule > Backup Scheme to auto delete old backup images.

The End

Automatic backup can save you the trouble of setting up tasks manually each time, and is undoubtedly a great way to continuously protect MySQL database. So how to create MySQL automatic backup? This article provides 5 ways for you. If you take the last way, you can also use AOMEI Backupper to perform MySQL incremental backup easily.