复制 /var/lib/mysql 是 mysqldump 的一个很好的替代品吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2482491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is copying /var/lib/mysql a good alterntive to mysqldump?
提问by Matteo Riva
Since I'm making a full backup of my entire debian system, I was thinking if having a copy of /var/lib/mysqldirectory is a viable alternative to dumping tables with mysqldump.
由于我正在对整个 debian 系统进行完整备份，因此我在考虑是否拥有/var/lib/mysql目录副本是使用 mysqldump 转储表的可行替代方案。
- are all informations needed contained in that directory?
- can single tables be imported in another mysql?
- can there be problems while restoring those files on a (probably slightly) different mysql server version?
- 该目录中是否包含所有需要的信息？
- 可以在另一个mysql中导入单个表吗？
- 在（可能略有不同）不同的 mysql 服务器版本上恢复这些文件时会出现问题吗？
采纳答案by Etienne Dechamps
- Yes
- Yes if the table is using the MyISAM (default) engine. Not if it's using InnoDB.
- Probably not, and if there is, you just need to execute mysql_upgrade to fix them
- 是的
- 如果表使用的是 MyISAM（默认）引擎，则是。如果它使用 InnoDB，则不会。
- 可能不是，如果有，你只需要执行 mysql_upgrade 来修复它们
To avoid getting databases in a inconsistent state, you can either shutdown MySQL or use LOCK TABLES and then FLUSH TABLES before the backup. The second solution is a little better because the MySQL server will remain available during the backup (albeit read only).
为避免使数据库处于不一致状态，您可以关闭 MySQL 或在备份之前使用 LOCK TABLES 然后 FLUSH TABLES。第二种解决方案要好一些，因为 MySQL 服务器在备份期间将保持可用（尽管是只读的）。
回答by AlBlue
This approach is only going to work safely if you shut the database down first. Otherwise you could well end up in an inconsistent state afterwards. Use the /etc/init.d/mysql stop command first. You can then restart it after the backup is taken.
只有先关闭数据库，这种方法才能安全地工作。否则，您之后很可能会处于不一致的状态。首先使用 /etc/init.d/mysql stop 命令。您可以在备份完成后重新启动它。
回答by Joonas Pulakka
It's perfectly OK as long as you shut down the MySQL sever first and use exactly the same version to retrieve the "backup". Otherwise it isn't.
只要您先关闭 MySQL 服务器并使用完全相同的版本来检索“备份”就完全可以了。否则不是。
回答by TomDogg
For a complete discussion of the 2 strategies, you need to read this: https://dev.mysql.com/doc/refman/5.5/en/backup-types.html
有关这两种策略的完整讨论，您需要阅读：https: //dev.mysql.com/doc/refman/5.5/en/backup-types.html
The currently best free and open-source solution seems to be Percona's: http://www.percona.com/software/percona-xtrabackup
目前最好的免费开源解决方案似乎是 Percona 的：http://www.percona.com/software/percona-xtrabackup
回答by seven
I'll go with a strong NO.
我会坚决反对。
From my experience, backing up/restoring raw mysql data files can be used only on the same os/server version. It does not work cross platform (eg. ubuntu/macos) with same server versions nor if mysql server versions are different on same platform.
根据我的经验，备份/恢复原始 mysql 数据文件只能在相同的操作系统/服务器版本上使用。它不适用于具有相同服务器版本的跨平台（例如 ubuntu/macos），也不适用于同一平台上的 mysql 服务器版本不同。
Percona XtraBackup (innobackupex)from Percona MySQL distro will let you do live & differential mysql backup and serve you the backup files that can be restored by copying to /var/lib/mysql/. You need to be running Percona Server for MySQL to use all of this.
PerconaMySQL 发行版的Percona XtraBackup (innobackupex)将让您进行实时和差异 mysql 备份，并为您提供可以通过复制到/var/lib/mysql/. 您需要运行 Percona Server for MySQL 才能使用所有这些。
