1

While searching for free/open-source MySQL incremental backups solutions I have found a number of ways to do incremental backups and all of them seem to either rely on "logical" way of backups/restore like mysqldump and binlog (or LSN) or "physical" way (like Percona's xtrabackup). However, in both cases the only scenario implied is backing up the database globally (all databases).

I am looking for the way to provide incremental backups per database: there are two db servers (master and slave) and each handles lots of databases. Each of these databases belong, let's say, to different customers and some of them tend to grow really big. While software upgrades database schema sometimes changes and before the upgrade we must make sure we are on the safe side so backup is a must but full backup is definitely not a way to go due to database sizes.

Please advise how this can be solved or at least suggest some ways to look for solution.

4 Answers 4

3

You can read binlogs with the mysqlbinlog tool, and produce a stream of logical changes, like a mysqldump backup. The mysqlbinlog has an option --database=dbname so you can extract only changes pertaining to the named database.

Read more about using this option here: http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_database

Two caveats:

  • This assumes that you have a continuous set of binary logs back either to the last full backup or to the last incremental dump.

  • It's not really a filter for changes to dbname. It's a filter for all binlog events applied to any database while dbname was selected as the default database. So if you have an event where someone updated dbname2.tablename while dbname was the default, then the dump will include that change to dbname2.

    This makes this method unsafe to use this as a per-database incremental backup method, unless you have 100% certainty that no such transactions occurred.

P.S.: Your comments about LSN are a bit incorrect. Mysqldump knows nothing about the LSN, which is a feature of the InnoDB redo log. Mysqldump does examine the binary log position, and can output that position if you use the option --master-data.

2
  • Thanks, this is helpful. I suspected that there must be some pitfalls with binlog events filtering like that (just like with filtering replication with ignore-db)
    – yurisum
    May 29, 2014 at 13:31
  • Yeah, like any tool, it will probably take you a while to experiment with it to figure out if it meets your needs. May 29, 2014 at 17:04
1

You can use https://sourceforge.net/projects/mysqlincrementalbackup/ script. Description from its website:

A complete incremental backup for MyISAM and InnodB in a mix environment for those applications use both of engines simultaneously using binary logs and a method that does not affect running database. There is no need to stop or lock the database, It does utilize only binary logs to extract update queries of databases. This tools uses automysqlbackup script as part of solution for its full backup.

1
  • 1
    Hi nima02, please take a look here
    – bummi
    Jun 1, 2015 at 11:47
0

You need to look into MySQL DUMP. You can write scripts that call MYSQL DUMP to pull the backups you need. You can then schedule these scripts to run as you need.

See here for further details

3
  • I already use mysqldump, the problem is that it supports increments based on LSN which is global for MySQL instance (as far as I understand) so if I dump only one database and then rotate binlog to make increments I'll have a lot of queries in binlog which has nothing to do with my particular database (since it contains all changes happened to all databases on that particular server), it's wasting of disk space and I'm not sure that applying such increments to full backup will work.
    – yurisum
    May 16, 2014 at 15:52
  • Are you using the innodb engine? May 16, 2014 at 16:15
  • Yes, almost all tables are InnoDB
    – yurisum
    May 29, 2014 at 13:31
0

MySQL incremental backups have their own nuances, but they can also be performed for a separate database.

It all depends on the way you perform incremental backups. At the database level, they can be performed in three ways [link].

binary log

Binlogs are written immediately for all the databases in one file. However, you can extract data from there for a specific database by specifying the --database parameter for the mysqlbinlog utility.

There is a peculiarity about which @Bill Karwin mentioned, but with the following workaround it can be resolved:

  1. Enable ROW format for binlogs - then the mysqlbinlog utility will pick up all changes that have been made in the tables
  2. After any changes in the data schema, perform a FULL backup (you can programmatically track changes in the data schema by comparing the mysqldump ---schema-only hash)

Here is more information about incremental binlog backups on Windows and on Linux

mysqlbackup

You can perform a backup of a separate database using the --exclude-tables parameter and --include-tables parameter

xtrabackup

You can perform a backup of a separate database using the following parameter --databases

See Also: Percona XtraBackup: Backup and Restore of a Single Table or Database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.