How to efficiently dump a huge MySQL innodb database?
- by Jagbir
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?