I was recently asked about if MySQL Enterprise Backup would be
able to restore single databases.
My initial answer was that this was complicated, but might be
doable with the Transportable Table Space (TTS) option.
But first let's go back to the basics. A common way of working
with mysqldump is to get a list of databases and then loop
through the databases and dump the data and schema to a SQL file.
But both backups and restores will take a lot of time if the size
of the database grows. And it's a luke-warm backup at best
instead of a hot backup. So that's why we have MySQL Enterprise
Backup.
MySQL Enterprise Backup allows you to make a hot backup of InnoDB
tables by copying the datafiles while watching the InnoDB redo
log files.
On disk the data from the InnoDB storage engine consists of a
system tablespace (one of more ibdataX files), the redo log files
(iblogfileX) and zero or more table-specific tablespace files
(*.ibd).
The data dictionary data is located in the system tablespace.
This is were the tablespace number is stored for each ibd file.
The redo logfiles is were the changes are written to before they
are written to the datafiles.
This all works fine if you use MEB to backup and restore a whole
instance.
Even if you stop MySQL you can't just copy a ibd file to another
server and expect it to work. This is because there might be
changes for that file still in the redo logfile and the table
space ID in the system tablespace might not match.
But you can copy a ibd file to another server if you follow the
right procedure. This (partly) works with 5.5, and is greatly
enhanced in 5.6.
This is how it works:
a FLUSH TABLES..FOR EXPORT command is issued for certain tables.
The tables are then read-only. The changes in the logfile are
written to the ibd file and the information from the system
tablespace is written to a .cfg file. Then the .ibd and .cfg
files can be copied to some other location. Then the table can be
unlocked.
You could create a per-database backup with MEB with the
--include-tables option. Then if will copy the specified tables'
.ibd files and the system tablespace and the redo logs.
This works, but the system tablespace might get big because of
many reasons. This is where TTS comes into play. This allows you
to make a backup of only the tables w/o copying the system
tablespace.
With the --use-tts and --include-table options I can backup all
tables for one database and then restore one or more of these
tables on another instance. This is without stopping the
destination instance.
Some per-database defaults like the default character set are
stored in the db.opt file, but this file is not copied. So be
aware!
But what if we want to do a point-in-time recovery for just one
database? That should work. First restore the database you're
concerned about and then use mysqlbinlog with the --database
option to restore the binlog entries which are for that specific
database.
I haven't use per-database (point-in-time) restores with MEB in
production, but it all seems to work fine in a test setup.
I've always used physical backups on a per-instance basis and
then mysqldump for single-table and single-database backups. But
this might be faster and the data only needs to be backupped
once. Also the mysqldumps I took were not prepared for
point-in-time restores as the locking required for that would
block the server for too long.
Please let me know your experiences in the comments.
Jul
02
2014