Mysql – Incremetal backup using thesqldump in MySQL

backupMySQL

I wanted to take an incremental backup using mysqldump for a MySQL database. I have created a script and had taken backup using crontab. It took backup but not incremental; it only takes a full database backup. I didn't find any way to take incremental backup.

My code is given below:

backup.sh Script

#!/bin/bash
YEAR=$(date +%Y)
MONTH=$(date +%m)
DAY=$(date +%d)
HOUR=$(date +%H)
mkdir -p $YEAR/$MONTH/$DAY/$HOUR
mysqldump -uroot -pRedhat@123 mydb > $YEAR/$MONTH/$DAY/$HOUR/backup.sql

Crontab

crontab -e
# Minute   Hour   Day of Month       Month          Day of Week        Command
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)
30        *         *            *                1-6       exec  '/bin/bash /home/backup.sh'

Backup Files Listing

bash backup.sh

[root@master 17]# ls -lh backup.sql 
-rw-r--r--. 1 root root 49M Jun 15 17:05 backup.sql

[root@master 17]# ls -lh backup.sql 
-rw-r--r--. 1 root root 362M Jun 18 17:23 backup.sql

I want that it will take only changed blocks backup not full backup like above example.

Additional Information

I am using the free version of MySQL. No enterprise features available.

Best Answer

As pointed out in my previous answer, mysqldump does not provide incremental backup/dump capabilities:

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Reference: 4.5.4 mysqldump — A Database Backup Program (MySQL Documentation)

mysqldump doesn't contain any option(s) that would allow you to perform an incremental dump.

Reference: Option Syntax - Alphabetical Summary (MySQL Documentation)

Solution

To have an incremental backup without being able to use the mysqlbackup program you would have to perform the following task.

Physical (Raw) Backup

You can perform a backup of the MySQL \data directory to have a current backup of the databases in your MySQL instance. It is recommended to close/shutdown the instance to have a consistent state:

Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup.

(emphasis mine)
Reference: 7.1 Backup and Recovery Types (MySQL Documentation)

Incremental Binary Log Backup

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see Section 5.4.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS or mysqldump --flush-logs. See Section 4.5.4, “mysqldump — A Database Backup Program”.

Reference: 7.2 Database Backup Methods (MySQL Documentation)

FLUSH LOGS

Closes and reopens any log file to which the server is writing. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. If relay logging is enabled, the sequence number of the relay log file is incremented by one relative to the previous file.

Reference: FLUSH LOGS

An Example:

  1. Once a week at 8pm:
    a. Create a copy of the MySQL /data directory while the database is down.
    b. After "FULL" Backup: Restart the database.

  2. Every other day at 8pm:
    a. execute a FLUSH LOGS command
    b. create a backup copy of the binary logs directory

You can further optimise the incremental backup by storing the name of the last n-1 binary log file as an additional flag-file and starting off from there. That depends on your requirements.

DISCLAIMER
Please test your backup and restore procedure before you implement this in a productive environment. No guarantee given that this procedure will work for you.

Related Question