The astitute reader will recognize that the mysqlhotcopy script does, in fact, lock all tables involved in the backup, which may or may not have a serious impact on the users/applications using the database (although the output doesn't show it, the mysqlsnapshot script does a "FLUSH TABLES WITH READ LOCK", too):
Locked 57 tables in 0 seconds. [...] Unlocked tables.
You may be saying "yeah, but the backup took 1 second for all 57 tables." True, but we don't know how much data was in those 57 tables. How long does it take you to copy 100 megs of data? 100 gigs?
The highly astitute reader will also notice that mysqlhotcopy and mysqlsnapshot only work for ISAM and MyISAM tables, not Innodb tables. If you want transaction and foreign key support, you must use InnoDB tables - which means neither of these backup methods will work for you.
mysqldump will work with any storage engine since it makes a logical backup. It writes SQL statements, which can be pumped right back into MySQL.
Replication is, by far, the most flexible method of making online, consistent, reliable backups without affecting your database's performance. This is akin to Oracle Data Guard, where the data is replicated to another physical server. To make the backup, simply stop replication, flush the logs, then you can:
- shutdown MySQL, perform a cold backup of everything (data files, config files, logs, etc.) - use mysqldump to get a logical backup (you don't have to worry about users/apps since they don't touch that database anyway)
When the backup is complete, simply start replication back up.
One thing the article doesn't make clear is InnoDB Hot Backup can make online, hot-backups *without locking* tables using the InnoDB storage engine, only. If you have some tables using any of the other engines, MyISAM, BDB, CSV, etc., then you will incur locking issues and may or may not get a consistent snapshot of the data.
Learn about Linux
Download Linux
Get Linux help
There's a lot more to it than that
Posted by: Administrator on May 30, 2007 12:22 AMLocked 57 tables in 0 seconds.
[...]
Unlocked tables.
You may be saying "yeah, but the backup took 1 second for all 57 tables." True, but we don't know how much data was in those 57 tables. How long does it take you to copy 100 megs of data? 100 gigs?
The highly astitute reader will also notice that mysqlhotcopy and mysqlsnapshot only work for ISAM and MyISAM tables, not Innodb tables. If you want transaction and foreign key support, you must use InnoDB tables - which means neither of these backup methods will work for you.
mysqldump will work with any storage engine since it makes a logical backup. It writes SQL statements, which can be pumped right back into MySQL.
Replication is, by far, the most flexible method of making online, consistent, reliable backups without affecting your database's performance. This is akin to Oracle Data Guard, where the data is replicated to another physical server. To make the backup, simply stop replication, flush the logs, then you can:
- shutdown MySQL, perform a cold backup of everything (data files, config files, logs, etc.)
- use mysqldump to get a logical backup (you don't have to worry about users/apps since they don't touch that database anyway)
When the backup is complete, simply start replication back up.
One thing the article doesn't make clear is InnoDB Hot Backup can make online, hot-backups *without locking* tables using the InnoDB storage engine, only. If you have some tables using any of the other engines, MyISAM, BDB, CSV, etc., then you will incur locking issues and may or may not get a consistent snapshot of the data.
It's also licensed on a per-server basis.
#