Patrick Galbraith (capttofu) wrote,
Patrick Galbraith

New innobackup feature: --slave-info

To have online backups of MySQL, We recently bought a license for InnoBase/Oracle's InnoDB Hot Backup Tool, ibbackup. This tool, used in conjunction with innobackup, has worked great in creating a nightly backup, with no downtime during the backup. Not even nagios messages!

I run innobackup/ibbackup on one of our slaves (well, it's also a dual master, but not used by apps). innobackup produces a backup in a directory that I specified, and when run results in a time-stamped directory, as show below:

ls -l 2008-09-17_03-00-03/
total 276272
-rw-r--r-- 1 root root 349 2008-09-17 03:00 backup-my.cnf
drwxr-x--- 2 root root 4096 2008-09-17 03:55 grazr
-rw-r--r-- 1 root root 27 2008-09-17 03:55 ibbackup_binlog_info
-rw-r----- 1 root root 186109952 2008-09-17 03:55 ibbackup_logfile
-rw-r----- 1 root root 10485760 2008-09-17 03:00 ibdata1
-rw-r----- 1 root root 85983232 2008-09-17 03:00 ibdata2
drwxr-xr-x 2 root root 4096 2008-09-17 03:55 mysql
-rw-r--r-- 1 root root 0 2008-09-17 03:55 mysql-stderr
-rw-r--r-- 1 root root 1326 2008-09-17 03:56 mysql-stdout
drwxr-xr-x 2 root root 4096 2008-09-17 03:55 test

These are all files needed in the datadir, and once you run innobackup with --apply-log, it applies the transaction log, and you can run off of this directory in the event of data loss. (ehem, some Denver newspaper...) Also, you can simply copy this directory to any new db you're building and have an instant slave.

innobackup also provides you with the binary log settings of the server the backup was created on (by running SHOW MASTER STATUS), which is great because you can apply them and have your server for an incremental backup to current time. The file in the listing about is:


It looks like this:

bin.002934 22485369 grazr

What is *doesn't* give you is the binary log file name and position of the slave's master, if you are running the backup on a slave. I like having this information because then I can take the backup, put it on a new MySQL slave, then start replication reading from the same master the server the backup was created on, along with binlog name and position of that master, and let replication update that server.

Since innobackup doesn't provide this, what I've done is to manually look at the master's binlog starting from the time of day that I perform the dump, get a binlog position, and use that as my starting point. This is a bit rough, and may not give you the exact starting point you want from that master.

So what I decided to do was to add functionality to get this information at the same time the server's own binlogs are read, to also perform a SHOW SLAVE STATUS to also get the server's master's binlog name and position.

This option is called --slave-info. It just tells innobackup to obtain this information when it obtains its own binlog position/name. It writes this info into a new file:


And I decided to make it even more convenient for use:


In effect, you now get a backup that you can simply run this master position change and and let replication take care of getting the server to catch up!

So, I make this patch available at:

I would make the whole script available, but not sure if that's ok with InnoBase folk (it is GPL, but I want to check with them)
Tags: backups, innodb, mysql
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic