Christos Kalantzis <com> wrote:
No need to replay the logs. Mysqldump creates a consistent snapshot
(think: full backup). A bunch of binlogs, starting at the time of a
snapshot can be used to create another consistent snapshot (think:
This is different from other databases where you get a dirty snapshot
of the tablespaces at first and then use the logs to create a clean
(consistent) state of the tablespace.
The key is --single-transaction
Aka restoring a full backup.
Aka applying an incremental backup (roll forward).
You're completely right. Logical backup like described above has
1. it is single threaded - does not use the hardware well
2. indexes have to be rebuilt; restore may take *very* long
Depending on your hardware there is a database size limit for logical
backup (and even more: restore). Of course this depends on your data.
Very rough figure: expect problems at 10GB and more. If you have only
few indexes and big columns (BLOBs?) even 100GB my be OK.
The answer is physical backup. You could use some form of snapshot
technology to get a snapshot of the tablespace and transaction logs.
InnoDB will be able to recover from a dirty snapshot; if you manage
to flush & lock all tables for the time to create the snapshot, even
better. A very common backup strategy for MySQL uses tion to
create a mirror of the database. This mirror can then be used to take
i.e. cold backups.
Especially for InnoDB tables there is a commercial hotbackup tool
available from http://www.innodb.com.
Axel Schwenke, Senior Software Developer, MySQL AB
Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/