How to perform an incremental or differential backup with basic MySQL commands?

Incremental backups can be made using the tools provided by MySQL, such as mysqldump, later using tools such as diffto store only the differences.

The problem is that it mysqldumphas several problems for a business use:

• The backup process overloads the CPU and memory due to the way the data is obtained. Different SQL queries are launched to the server and the server must process the results to send them to mysqldump. At the same time , it mysqldumpmust collect that information and save it in an SQL format in order to be able to reproduce it later.

• The size of the backups is much larger than the data on disk. In the case of tables with binary data (blobs) they are dumped in hexadecimal.

• A subsequent or simultaneous processing (using pipes) of compression would increase the backup time or processor load and the restore time because the decompression process would have to be performed.

• Backup duration is extremely high compared to the equivalent of reading data directly from disk.

• Restore is extremely slow compared to the equivalent of writing directly to disk.

• Although the tool provides options such as --single-transactionmaking consistent backups at the database level, it is not robust (*) and can block access to all tables and databases on the server if the transaction log fills up during large dumps. number of records until the backup transaction is completed.

• Options such as --lock-tablesblock the use of the tables during the dump, causing queries against those tables to block.

• The restore process overloads the CPU, disk and memory of the server due to the interpretation and validation of each SQL query made and also caused by the verification of the keys in the insertion of each record, increasing the more records have been inserted ( although the impact can be reduced by using options like --disable-keys).

(*) From the documentation :

While a --single-transactiondump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECTthat is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail .

Using ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLEduring a backup might get incorrect results or cause it to fail.

Proposed solution

If you can make use of another tool (completely free, unlike MySQL) I recommend using the backup tool developed by Percona called XtraBackup :

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL, MySQL ® and MariaDB®. With over 2,100,000 downloads, Percona XtraBackup performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.

In Spanish:

Percona XtraBackup is a free, open source, complete backup solution for all versions of Percona Server, MySQL® and MariaDB®. With over 2,100,000 downloads, Percona XtraBackup performs highly secure, heavily compressed, non-blocking online backups to transactional systems so applications remain fully available during scheduled maintenance windows.

Or, what is the same, the performance of the database is slightly degraded during the backup but no table locking is performed or interferes with the normal work of the server.

In the documentation on incremental backups you can see an example of use:

To make a complete backup, just run:

xtrabackup --backup --target-dir=/data/backups/base

To perform an incremental backup from the previous one:

xtrabackup --backup --target-dir=/data/backups/incremental1 \
    --incremental-basedir=/data/backups/base

Functioning

The process is detailed on the product's website .

It makes a binary copy of the disk files, minimizing the impact to the server, analyzing the transaction log to mark the exact starting point (the LSNlog sequence number) and, therefore, being able to provide a complete backup consistent, while updating the log data in the background.

Differential backups work by copying only the differences stored in the transaction log, reducing disk access and backup time.

The only drawback is that the MyISAM tables cannot be saved incrementally because they are not transactional, so the complete copy of them is made by performing a table lock.

Nowadays there is no compelling reason to maintain tables in MyISAM, so this should not be a problem.

Restoring the backup forces a recovery to update the information in the tables as far as the transaction log ( crash recovery ), so the recovery will also result in a consistent restore at the full server base level (and not at the database level as the "in a transaction" mode would do).

Also, since the restore is done in a different directory (which then needs to be moved to the production directory) a MySQL server can be run on a different port to access the restored data.

It can be useful to test them or access them to make a specific access to some deleted data or to make a selective export or recovery.

Disadvantages

If the tables are not optimized (they have "residue to purge", modified or deleted records that leave the old values ​​taking up unused space) during the backup the residue will be copied to the full backup (but not to the incremental one), so it is It is advisable to execute a OPTIMIZE TABLEto those tables of greater size or that tend to accumulate a greater residue before a complete backup.

Backup via LVM

Another way to perform a consistent backup is through LVM snapshots .

All MySQL content should be stored on the same logical volume for this solution to be applicable (transaction logs, replication logs if any, tablespaces, etc).

During the recovery process a crash recovery should be forced to be done rollbackfor all those transactions that were not completed at the time of the backup.

The process would be carried out as follows:

# Creamos un LV para el snapshot
lvcreate -L1G -n mysqlbackup -s mysqldatos

Where:

• -L1Gallows the original partition to be modified 1 GB before it is full. If database activity is high, a larger snapshot may be required.

• -n mysqlbackupwill name mysqlbackupthis logical volume.

• -s mysqldatosindicates that it will be a snapshot of the logical volume mysqldatos.

To mount the snapshot and access it for storage or calculate incremental backup:

# Averiguamos el nombre del dispositivo donde se ha creado el volumen lógico
lvdisplay | egrep "LV Path*mysqlbackup$"
# Creamos el punto de montaje (si no existe)
[ -d /mnt/mysqlbackup ] || mkdir /mnt/mysqlbackup
# Montamos el snapshot
mount /dev/mapper/vg-mysqlbackup /mnt/ops/mysqlbackup
# [hacemos el backup del tipo que sea]
# ...
# Desmontamos y borramos el snapshot
umount /mnt/ops/mysqlbackup
lvremove /dev/mapper/vg-mysqlbackup

The tool to manage full and incremental backups from the snapshot could be duplicity, which supports incremental backup of large files, storing only the modified parts from the previous backup.

Some time ago I was looking for the school and for a project that I had that is that.

Incremental backup of your database with Git

A really interesting way to make backups of your databases (because it is trivial and very powerful) is using Git. The process is simple and is based on performing database dumps so that each row of the tables is an isolated insert, that way in each commit we will only be saving the differences with respect to the last state (both deletes and inserts, such as updates).

In the specific case of MySQL, we would initially do something like this:

$ mkdir mydatabase && cd mydatabase
$ mysqldump --skip-extended-insert > data.sql
$ git init
$ git add data.sql
$ git commit -m "Initial dump of mydatabase"

From then on we can automate the process with a script as simple as this:

$ cat /usr/bin/git_backup_database!/bin/sh
BACKUP_DIRECTORY=$1
DATABASE=$2

cd $BACKUP_DIRECTORY &&
mysqldump --skip-extended-insert $DATABASE > data.sql &&
git commit -am "Updating dump `date +%Y_%m_%d__%H_%M`"`

Depending on the volume of queries that your database has, it will be interesting for you to put it in cron with a certain frequency or another. Additionally it would be advisable to run $ git gc to optimize the repository. For example, twice a day and once a week maintenance:

0 0 * * * /usr/bin/git_backup_database /path/to/mydatabase mydatabase
0 12 * * * /usr/bin/git_backup_database  /path/to/mydatabase mydatabase
0 1 * * 1 cd /path/to/mydatabase && git gc

Also, from another computer, no one prevents you from doing one $ git clone ssh://equipo:path/to/mydatabaseand having all the history of the database in a flash (well, that's relative, it will occupy its own...) or even schedule a $ git pull to have several backups on different machines. In short, endless options open up.

As we have mentioned in the comments, I think you have an option that has not been considered until now, which is to make the platform work instead of looking for a more "manual" solution.

Your problem is that it grows a lot BBDDand I understand that what you don't want to do is backupconstantly blocking the BBDD.

I can give you 3 solutions, depending on what you need or can afford at the platform level, I recommend one or the other, in this case I put them in order of complexity/cost for the application.

MySQL Master/Slave

In this case what we have is a one master de MySQLand one slaveof that machine. In this way, while you can continue writing in him master, he slavehas a complete copy BBDDof the one that you can only read, but that you can block as many times as you want and which you can shamelessly crush, since he will get updatesthem as he goes. that I can

Mounting it is relatively simple, I leave you a tutorial on how to install amaster/slave de MySQL

Even if you control a little English, following the commands is enough. I have it in a very large application in production and no problem.

The good thing about it is that it can be assembled with two machines, therefore the cost of the platform is quite small and you can expand it as much as you want. The number of reads is very high and the number of writes as high as the master. In the same way, if the crashes slavenothing happens and if the crashes masteryou can change the slaveto masterand save the application.

mysql-cluster

It is a new feature of the version 5.7that I have only been able to test with a test project so I cannot give you an extremely good feedback, I can only tell you that it seems that it has been tested a lot and the opinions that I have seen are quite favorable.

Here you have the tutorial to install acluster de MySQL , it is not as simple as a master/slavebut it gives you many more things than it. For example, it has automatic shardingto allow you to greatly increase the number of writes. If you don't know what it is sharding, they comment on it on StackOverflow in English , surely reading will help you.

Galley Cluster

It is one clusterof master/master/masterthis, you can read and write from the three servers at the same time, I think it is too much for what you need, but if you think that the application can increase a lot in size, it is a very good option.

The problem is that there must be at least 3 servers for it to clusterbe sufficiently consistent, since what is called must exist quorum. The number of reads does not increase as much as in a cluster de MySQLbecause it does not have the automatic shardingbut unlike master/slavea server can go down without any problem because the quorumcan continue to exist.

Here you have how to install galera in CentOS7 and install galera in Ubuntu 16.04 , since I don't know what system you use, I'll leave you both. I always prefer CentOSor Debianfor everything related to servers.

differences between GaleraandMySQL Cluster

The differences between Galeraand the clustes of Mysqlare quite a few, I recommend that you read this slideshare , since if we put it here the answer would be almost infinite and then the opinions of each one would enter as to which is better or worse.

I think that with this I can give you an idea of ​​the options you have at the platform level, without having to pull something more "strange" things like putting a gitto a file ofMySQL

If you need more information, comment and I'll help you as much as I can.