what's the best way to dump a large(terabytes) db? are there other faster/efficient way besides mysqldump? this is intended to be zipped, unzipped, and then reimported into another mysql db on another server.
转储一个大(tb) db的最好方法是什么?除了mysqldump之外，还有其他更快、更有效的方法吗?这是要压缩的、解压缩的，然后再导入到另一个服务器上的另一个mysql db中。
2 个解决方案
#1
3
If it's possible for you to stop the database server, the best way is probably for you to:
如果可以停止数据库服务器，最好的方法可能是:
- Stop the database
- 停止数据库
- Do a file copy of the files (including appropriate transaction logs, etc) to a new file system.
- 将文件(包括适当的事务日志等)复制到新的文件系统。
- Restart the database.
- 重新启动数据库。
Then move the copied files to the new server and bring up the database on top of the files. It's a bit complicated to do this, but it's by far the fastest way.
然后将复制的文件移动到新服务器，并在文件顶部显示数据库。这样做有点复杂，但这是目前为止最快的方法。
I used to be a DBA for a terabyte+ database in MySQL and this is one of the ways we'd do nightly backups of the database. mysqldump would've never worked for data that large. We'd stop the database each night and file copy the underlying files.
我曾经是MySQL的tb +数据库的DBA，这是我们每晚备份数据库的方法之一。mysqldump永远不会对这么大的数据起作用。我们每晚都要停止数据库，并将底层文件复制。
#2
0
Since your intent seems to be having two copies of the DB, why not set up replication to do this?
由于您的意图似乎有两个DB副本，为什么不设置复制来实现这个目的呢?
That will ensure that both copies of the DB remain in an identical state (in terms of data anyway).
这将确保DB的两个副本保持相同的状态(无论如何就数据而言)。
And, if you want a snapshot to be exported, you can:
如果您希望导出快照，您可以:
- wait for a quiet time.
- 等一个安静的时间。
- disable replication.
- 禁用复制。
- back up the slave copy.
- 备份从拷贝。
- re-enable replication.
- 重新启用复制。
#1
3
If it's possible for you to stop the database server, the best way is probably for you to:
如果可以停止数据库服务器，最好的方法可能是:
- Stop the database
- 停止数据库
- Do a file copy of the files (including appropriate transaction logs, etc) to a new file system.
- 将文件(包括适当的事务日志等)复制到新的文件系统。
- Restart the database.
- 重新启动数据库。
Then move the copied files to the new server and bring up the database on top of the files. It's a bit complicated to do this, but it's by far the fastest way.
然后将复制的文件移动到新服务器，并在文件顶部显示数据库。这样做有点复杂，但这是目前为止最快的方法。
I used to be a DBA for a terabyte+ database in MySQL and this is one of the ways we'd do nightly backups of the database. mysqldump would've never worked for data that large. We'd stop the database each night and file copy the underlying files.
我曾经是MySQL的tb +数据库的DBA，这是我们每晚备份数据库的方法之一。mysqldump永远不会对这么大的数据起作用。我们每晚都要停止数据库，并将底层文件复制。
#2
0
Since your intent seems to be having two copies of the DB, why not set up replication to do this?
由于您的意图似乎有两个DB副本，为什么不设置复制来实现这个目的呢?
That will ensure that both copies of the DB remain in an identical state (in terms of data anyway).
这将确保DB的两个副本保持相同的状态(无论如何就数据而言)。
And, if you want a snapshot to be exported, you can:
如果您希望导出快照，您可以:
- wait for a quiet time.
- 等一个安静的时间。
- disable replication.
- 禁用复制。
- back up the slave copy.
- 备份从拷贝。
- re-enable replication.
- 重新启用复制。