How to efficiently dump a huge MySQL innodb database?

Posted by Jagbir on Server Fault See other posts from Server Fault or by Jagbir
Published on 2012-11-05T11:03:17Z Indexed on 2012/11/05 11:05 UTC
Read the original article Hit count: 242

Filed under:
|
|

I got an Ubuntu 10.04 production MySQL database server where total size of database is 260 GB while size of root partition is itself 300 GB where DB is stored, essentially means around 96% of / is full and there's no space left for storing dump/backup etc. No other disk is attached to server as of now.

My task is to migrate this database to other server sitting in different datacenter. Question is how to do that efficiently with minimum downtime?

I'm thinking in line of:

  • Request to attach an extra drive to server and take a dump in that drive.
  • Transfer dump to new server, restore it and make new server slave of existing one to keep data in sync
  • When migration is needed, break replication, update slave config to accept read/write requests and make old server read-only so it won't entertain any write requests and tell app developers to update there config with new IP address for db.

What's your suggestions to improve this or any alternate better approach for this task?

© Server Fault or respective owner

Related posts about mysql

Related posts about innodb