Questions and Answers from the Backup Webinar

Q from Sven - Are mysqlhotcopy and ibbackup only part of 6.0?
mysqlhotcopy is available in all releases. ibbackup is a separate offering that has been available for a long time

Q from Adam: Is the SQL based output portable to another database server engine e.g. MSSQL?
There are options of mysqldump that make the output as much generic as possible, and that is portable, but we can't guarantee it works with every RDBMS. The DDL is the less portable section

Q from Andrea: Is a hot mysqldump generally a good idea?
Unfortunately the answer is "it depends". With MyISAM, for example, this is not generally a good idea, since you have to lock the tables to make the dump consistent - i.e. it is not a hot dump anymore, you may call it "warm". With InnoDB the dump is consistent, provided we execute it with the Repeatable read isolation level.

Q from Simon: What is your suggestion to backup databases with tables with mixed engines?
A LVM snapshot will work. In MySQL 5.2, this will be available with the built-in backup facility. mysqldump can still do a good job, but it will lock the MyISAM tables.

Q from Francisco: Can I execute a mysqldump from a remote machine, using ftp or sftp for example?
It is possible to execute mysqldump from a remote machine, but you may experience poor performance due to the network. mysqldump will not use ftp or sftp, it will use the usual tcp connection to connect to the db server and retrieve the data.

Q from Roalt: If I apply a the lock-all-tables in the mysqldump, would that affect replication too?
If you are dumping the slave, the sql thread of replication (the thread responsible to update the slave) will stop, but in the meantime the master and the I/O thread can still work.

Q from Sven: How long will mysqlhotcopy take to backup a 50 GB database?
The time depends on several factors and it's hard to say. One thing to consider though is that mysqlhotcopy will be in any case faster than mysqldump

Q from Simon: Can I use replication and then a binary copy of the data from a slave?
You can, but you have to consider several aspects. First of all, it will always be a binary copy of that server (slave) and it may not work on the master. MyISAM tables can be recovered on the master as well, but InnoDB data files will be different.

Q from Michael: Are indexes re-generated when we apply a restore from a logical backup?
Yes, the whole table, with data and indexes, will be recovered from scratch.

Q from Martjin: Can you restore a db to a new copy, still keeping the old one (with mysqlhotcopy)?
Yes, this is definitely possible. Simply use different names for the db schema

Q from Kristiaan: Is there any way to automate the backups to run on a regular basis?
Yes, You can use any scheduling system, crontab etc, depending of your operating system.

Q from Sven-Ake: What tool do you recommend to backup a MyISAM database that is about 50 GB? We want to do a hot backup, or at least a warm backup.
A LVM snapshot would be my first choice. I would also consider replication or the use DRBD, if you need HA as well. If you use InnoDB, hotbackup may be a solution.

Q from Roalt: Is DRBD stable enough for production of large databases?
I would say that DRBD is stable and definitely good for production. The size of the DB is not important, it is more important to consider the throughput required by the write operations.

Q from Ludovico: Will the new backup API allow integration with other backup solutions? (Legato, Omniback, Data Protector, Veritas etc.)
Yes, but you should not expect this integration available when we will release the API

Q from Kelly: Can binary logs be created for a specific database or will binaries always be for all databases within your instance?
The logs are created per server, therefore there is no way to distinguish logs for different schemas

Q from Kristiaan: If we want to transfer a copy of a MySQL database each night from the production system to the database, is there a way to get it to merged?
I would not call it a typical backup procedure. Anyway, it depends on what you mean with "merge", how you want to handle conflicts and so on.

Q from Phil: Does mysql have anything that combines the core DB backup with ftp mechanism?
Not today, but mysqldump can be run remotely.

Q from Jon: In a mixed engine schema, if LVM snapshots are not an option, what would you recommend? mysqldump is quite slow for us in practice.
Backup on a slave server with replication, for example. If you use InnoDB , Innobackup is also an option.

Q from Roalt: Are there any scripts to provide incremental backups from a replicating slave database server?
Incremental backups rely on the use of binary logs or, in case of use within the server, using relay logs. The procedure is fairly simple and it can be found in the Ref Manual.

Q from Nadav: Is there a flag when using mysqldump not to insert the schema name into the dump file?
Yes, you may use the --no-create-info and --no-create-db params

Q from Krzysztof: What about backup/restore function available in MySQL Administrator?
MySQL Administrator executes a mysqldump, but it handles it through a GUI and keeps track of the backups

Q from Adam: We have a 200GB customer file, what is the best option to store?
for a db of this size, I would definitely recommend a physical/raw backup. Logical backups may be too slow.

Q from Phil: How much additional performance load does running replication create?
Very little, since it's asynchronous and transactions are acknowledge immediately on the master server. The main impact on performance is related to the use of the binlog, which is actually another write on disk.

Q from Michael: Are you aware of any problems using mysqldump with non-English characters. Do I need to have a supported locale installed on my linux to have these statement "write" correctly?
There should not be any problem, if you have encountered them, I would suggest to check for bugs

Q from Roalt: Are LVM snapshots using LVM2 on Linux any good with MyISAM?
Yes, there should not be any problem, assuming you do a FLUSH TABLES first

Q from Alessandro: How are managed calls to functions like "NOW()" in Replication?
They are converted to the current time in the binary log

Q from Ewerton: Is there any tool to verify physical or logical corruptions in tables and indexes?
Unfortunately no, not at the moment.

Q from Jonathan: NAS backup: what's the recommended method for ensuring that NAS snapshots are restorable with minimal work after the restore?
There are different methods and they all depend on your environment. The main difference is related to the use of InnoDB or MyISAM. With MyISAM, things are pretty simple whilst with InnoDB it's a little bit trickier. If you can afford to make the database read only for few seconds, that is the best and safest way to apply the snapshot.

Q from Nadav: is ZMANDA for Unix only, and if yes, is it GUI only or command line also?
ZManda Recovery Manager (ZRM) runs on Linux and it can execute backups on Linux, Solari, OS X and Windows. ZRM can be executed from command line.

Q from Cristian: If I backup a slave server, can I restore the backup on the master?
If you have not applied any change to the data model, you can restore your logical backup on the master as well on the slave. If you have a physical backup, you master and server must be configured in the same way (DB version, location, parameters etc.).

Q from Arthur: Could you explain the --tab param in mysqldump?
With --tab you define that the output will be a tab delimited set of files with the same name of the tables and sql files with the same name to create the tables. The parameter is used to pass the path where these files must be created.

Q from Jonathan: How does the NAS-based backup ensure DB consistency?
It doesn't, that is up to the database. You may ensure consistency with MyISAM flushing the tables and switching to read-only mode. For InnoDB, this approach will leave the database in a recoverable state that must be applied when the backup will be restored.

Q from Arthur: Can you point my to some more text on using the NetApp with mysql
You may have a look at this document: http://www.netapp.com/library/tr/3601.pdf

MySQL Backup and Recovery - Today and Tomorrow

Thanks to all of you who have attended! (and apologise for having posted this info after such a long time)
We have had lots of questions and very interesting feedback and suggestions.

You can find the slides and the Webex presentation here.

I would like to post more on this subject, specifically on snapshot backup, comparing the different options with NetApp, DRBD and LVM.