CODEWITHSUNDEEP
databasemysql
user121196
user121196

Reputation: 31030

export a large db with terabytes of data

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.

Upvotes: 4

Views: 2083

Answers (2)

Kevin Bedell
Kevin Bedell

Reputation: 13404

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.

Upvotes: 4

paxdiablo
paxdiablo

Reputation: 881653

Since your intent seems to be having two copies of the DB, why not set up replication to do this?

That will ensure that both copies of the DB remain in an identical state (in terms of data anyway).

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.

Upvotes: 0

Related Questions