Proposal to deprecate mysqlhotcopy

In the MySQL team, we are considering deprecating the mysqlhotcopy utility. To provide some background, here is an excerpt from the MySQL manual:

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup. It is a fast way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix.

And now let me explain the motivations leading to our proposal:

  • The name implies that this utility will work for ‘MySQL’ and is hot, but actually neither are true:
    1. Only MyISAM and ARCHIVE engines are supported.
    2. Tables are read-locked before the copy phase.

    By contrast, backing up InnoDB tables with mysqldump --single-transaction or MySQL Enterprise Backup uses the more traditional definition of ‘hot’, and allows both READ and WRITE operations to continue during the backup.

  • There is no warning when backing up InnoDB tables that this operation is not supported. The .ibd files are also copied as part of creating the backup, which may mislead a user to believe they have successfully taken a backup.
  • The manual page for mysqlhotcopy notes that the utility ‘runs on UNIX’, since it relies on some filesystem locking semantics which can not be guaranteed to work cross-platform. We would like to ensure that Windows users always have a first-class experience.
  • mysqlhotcopy does not guarantee a consistent backup in the case that there are VIEWs present. I have a test-case available here to demonstrate.

Our current plan is to deprecate mysqlhotcopy in MySQL 5.6, with removal as early as MySQL 5.7. Do you use mysqlhotcopy? Will you be affected by the removal of this program?

We are seeking feedback from users who will be affected by this plan. Please leave a comment or get in touch!

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Sveta Smirnova

    Morgan, while MEB can copy MyISAM and Archive table files it requires you to use InnoDB storage engine. mysqlhotcopy is still needed for those installation which run with option –skip-innodb. I don’t know how many such installations run nowadays though. Also mysqlhotcopy from 5.6 distribution should work for them anyway.

  • Simon Brook

    Bump! Sorry, but only just started paying attention to the deprecation warnings. We have a few large databases (approx 100 million rows) that don’t make sense to backup using mysqldump and also still work on MyISAM tables for the speed. We use mysqlhotcopy to back those up because it is the only realistic way to get them offsite in a decent timeframe (compressed backup directory comes to about 2.5GB)

    What is proposed to replace it ?

    • The safest way to backup a majority MyISAM install is a filesystem snapshot. There is a third party utility called mylvmbackup which can do the synchronization between lvm and mysql.