What do I have to consider when backing up a database with millions of entries? Are there any tools (maybe bundled with the MySQL server) that I could use?
备份具有数百万条目的数据库时,我需要考虑什么?我可以使用任何工具(可能与MySQL服务器捆绑在一起)吗?
5 个解决方案
#1
7
Depending on your requirements, there's several options that I have been using myself:
根据您的要求,我自己使用了几个选项:
- if you don't need hot backups, take down the db server and back up on the file system level, i. e. using tar, rsync or similar.
- if you do need the database server to keep running, you can start out with the mysqlhotcopy tool (a perl script), which locks the tables that are being backed up and allows you to select single tables and databases.
- if you want the backup to be portable, you might want to use mysqldump, which creates SQL scripts to recreate the data, but which is slower than mysqlhotcopy
- if you have a copy of the db at a certain point in time, you could also just keep the binlogs (starting at that point in time) somewhere safe. This can be very easy to do and doesn't interfere with the server's operation, but might not be the fastest to restore, and you have to make sure you don't miss part of the logs.
如果您不需要热备份,请关闭数据库服务器并备份到文件系统级别,即。即使用tar,rsync或类似的。
如果确实需要数据库服务器继续运行,可以从mysqlhotcopy工具(perl脚本)开始,该工具锁定正在备份的表,并允许您选择单个表和数据库。
如果你希望备份是可移植的,你可能想要使用mysqldump,它创建SQL脚本来重新创建数据,但是比mysqlhotcopy慢
如果你在某个时间点有一个db的副本,你也可以将binlogs(从那个时间点开始)保存在安全的地方。这可以非常容易,并且不会干扰服务器的操作,但可能不是最快的恢复,并且您必须确保不要错过部分日志。
Methods I haven't tried, but that make sense to me:
方法我没试过,但这对我有意义:
- if you have a filesystem like ZFS or are running on LVM, it might be a good idea to do a snapshot of the database by doing a filesystem snapshot, because they are very, very quick. Just remember to ensure a consistent state of your db during the whole operation, e. g. by doing
FLUSH TABLES WITH READ LOCK(and of course, don't forgetUNLOCK TABLESafterwards)
如果您有像ZFS这样的文件系统或者在LVM上运行,那么通过执行文件系统快照来创建数据库快照可能是个好主意,因为它们非常非常快。只需记住在整个操作过程中确保数据库的一致状态,例如: G。用READ LOCK做FLUSH TABLES(当然,之后不要忘记UNLOCK TABLES)
Additionally:
- you can use a master-slave setup to replicate your production server to either a different machine or a second instance on the same machine and do any of the above to the replicated copy, leaving your production machine alone. Instead of running continously, you can also fire up the slave on regular intervals, let it read the binlog, and switch it off again.
- I think, MySQL cluster and the enterprise licensed version have more tools, but I have never tried them.
您可以使用主从设置将生产服务器复制到同一台计算机上的其他计算机或第二个实例,并对复制的副本执行上述任何操作,从而使您的生产计算机独立。您也可以定期启动从站,让它读取binlog,然后再将其关闭,而不是连续运行。
我认为,MySQL集群和企业许可版本有更多的工具,但我从未尝试过它们。
#2
5
Mysqlhotcopy is badly described - it only works if you use MyISAM, and it's not hot.
Mysqlhotcopy描述得很糟糕 - 只有在使用MyISAM时它才有效,而且它并不热。
The problem with mysqldump is the time it takes to restore the backup (but it can be made hot if you have all InnoDB tables, see --single-transaction).
mysqldump的问题是恢复备份所需的时间(但如果您拥有所有InnoDB表,则可以使其变热,请参阅--single-transaction)。
I recommend using a hot backup tool, like what is available in XtraBackup: http://www.percona.com/docs/wiki/percona-xtrabackup:start
我建议使用热备份工具,例如XtraBackup中提供的工具:http://www.percona.com/docs/wiki/percona-xtrabackup:start
#3
3
Watch out if using mysqldump on large tables using the MyISAM storage engine; it blocks selects while the dump is running on each table and this can take down busy sites for 5-10 minutes in some cases.
注意是否使用MyISAM存储引擎在大型表上使用mysqldump;它会在每个表上运行转储时阻止选择,这可以在某些情况下将繁忙的站点删除5-10分钟。
Using InnoDB, by comparison, you get non-blocking backups because of its row-level locking, so this is not such an issue.
相比之下,使用InnoDB,由于其行级锁定,您可以获得非阻塞备份,因此这不是一个问题。
If you need to use MyISAM, a common strategy is to replicate to a second MySQL instance and do the mysqldump against the replicated copy instead.
如果需要使用MyISAM,常见的策略是复制到第二个MySQL实例,并对复制的副本执行mysqldump。
#4
2
Use the export tab in phpMyAdmin. phpMyAdmin is the free easy to use web interface for doing MySQL administration.
使用phpMyAdmin中的导出选项卡。 phpMyAdmin是免费的易于使用的Web界面,用于执行MySQL管理。
#1
7
Depending on your requirements, there's several options that I have been using myself:
根据您的要求,我自己使用了几个选项:
- if you don't need hot backups, take down the db server and back up on the file system level, i. e. using tar, rsync or similar.
- if you do need the database server to keep running, you can start out with the mysqlhotcopy tool (a perl script), which locks the tables that are being backed up and allows you to select single tables and databases.
- if you want the backup to be portable, you might want to use mysqldump, which creates SQL scripts to recreate the data, but which is slower than mysqlhotcopy
- if you have a copy of the db at a certain point in time, you could also just keep the binlogs (starting at that point in time) somewhere safe. This can be very easy to do and doesn't interfere with the server's operation, but might not be the fastest to restore, and you have to make sure you don't miss part of the logs.
如果您不需要热备份,请关闭数据库服务器并备份到文件系统级别,即。即使用tar,rsync或类似的。
如果确实需要数据库服务器继续运行,可以从mysqlhotcopy工具(perl脚本)开始,该工具锁定正在备份的表,并允许您选择单个表和数据库。
如果你希望备份是可移植的,你可能想要使用mysqldump,它创建SQL脚本来重新创建数据,但是比mysqlhotcopy慢
如果你在某个时间点有一个db的副本,你也可以将binlogs(从那个时间点开始)保存在安全的地方。这可以非常容易,并且不会干扰服务器的操作,但可能不是最快的恢复,并且您必须确保不要错过部分日志。
Methods I haven't tried, but that make sense to me:
方法我没试过,但这对我有意义:
- if you have a filesystem like ZFS or are running on LVM, it might be a good idea to do a snapshot of the database by doing a filesystem snapshot, because they are very, very quick. Just remember to ensure a consistent state of your db during the whole operation, e. g. by doing
FLUSH TABLES WITH READ LOCK(and of course, don't forgetUNLOCK TABLESafterwards)
如果您有像ZFS这样的文件系统或者在LVM上运行,那么通过执行文件系统快照来创建数据库快照可能是个好主意,因为它们非常非常快。只需记住在整个操作过程中确保数据库的一致状态,例如: G。用READ LOCK做FLUSH TABLES(当然,之后不要忘记UNLOCK TABLES)
Additionally:
- you can use a master-slave setup to replicate your production server to either a different machine or a second instance on the same machine and do any of the above to the replicated copy, leaving your production machine alone. Instead of running continously, you can also fire up the slave on regular intervals, let it read the binlog, and switch it off again.
- I think, MySQL cluster and the enterprise licensed version have more tools, but I have never tried them.
您可以使用主从设置将生产服务器复制到同一台计算机上的其他计算机或第二个实例,并对复制的副本执行上述任何操作,从而使您的生产计算机独立。您也可以定期启动从站,让它读取binlog,然后再将其关闭,而不是连续运行。
我认为,MySQL集群和企业许可版本有更多的工具,但我从未尝试过它们。
#2
5
Mysqlhotcopy is badly described - it only works if you use MyISAM, and it's not hot.
Mysqlhotcopy描述得很糟糕 - 只有在使用MyISAM时它才有效,而且它并不热。
The problem with mysqldump is the time it takes to restore the backup (but it can be made hot if you have all InnoDB tables, see --single-transaction).
mysqldump的问题是恢复备份所需的时间(但如果您拥有所有InnoDB表,则可以使其变热,请参阅--single-transaction)。
I recommend using a hot backup tool, like what is available in XtraBackup: http://www.percona.com/docs/wiki/percona-xtrabackup:start
我建议使用热备份工具,例如XtraBackup中提供的工具:http://www.percona.com/docs/wiki/percona-xtrabackup:start
#3
3
Watch out if using mysqldump on large tables using the MyISAM storage engine; it blocks selects while the dump is running on each table and this can take down busy sites for 5-10 minutes in some cases.
注意是否使用MyISAM存储引擎在大型表上使用mysqldump;它会在每个表上运行转储时阻止选择,这可以在某些情况下将繁忙的站点删除5-10分钟。
Using InnoDB, by comparison, you get non-blocking backups because of its row-level locking, so this is not such an issue.
相比之下,使用InnoDB,由于其行级锁定,您可以获得非阻塞备份,因此这不是一个问题。
If you need to use MyISAM, a common strategy is to replicate to a second MySQL instance and do the mysqldump against the replicated copy instead.
如果需要使用MyISAM,常见的策略是复制到第二个MySQL实例,并对复制的副本执行mysqldump。
#4
2
Use the export tab in phpMyAdmin. phpMyAdmin is the free easy to use web interface for doing MySQL administration.
使用phpMyAdmin中的导出选项卡。 phpMyAdmin是免费的易于使用的Web界面,用于执行MySQL管理。