Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Is there a way to replicate master/slave without disturbing master for dump like mysqldump or cold copy of data files? I'm ok to bring down the instance only once for master to enable log-bin. But there is no feasibility for dump or even cold copy. As I have 400G of data and 1min down time only.

share|improve this question
1  
Do you run all InnoDB or all MyISAM or a mix of both? – Derek Downey Oct 15 '12 at 13:25
All are in innodb – Mannoj Oct 15 '12 at 13:45
What version of MySQL ??? – RolandoMySQLDBA Oct 15 '12 at 14:44
It is 5.0.77-log – Mannoj Oct 16 '12 at 5:17

1 Answer

up vote 1 down vote

I would recommend Percona's Xtrabackup for this, especially since you mentioned all of your tables are InnoDB. From the page:

Percona XtraBackup is designed to make backups a seamless procedure without disrupting the performance of the server in a production environment.

The tool will allow you to take a backup without locking innodb tables. It will do a lock to copy any MyISAM tables (such as the tables mysql in the mysql database) but those shouldn't take long.

I cannot tell you how long it will take on 400GB database, but it should do it without downtime.

share|improve this answer
yeah.. thats the last option I have, if this is the case on any mode of replication. – Mannoj Oct 15 '12 at 14:13
eh? You're going to have to get a full backup of your 'master' to set up any replication. – Derek Downey Oct 15 '12 at 14:15
yep I get that. But my peers having a strong objection on this concept in HA. The underlying concept is. For any HA either via cluster or replication if primary is down and secondary takes over the threads as a failover and inorder to rebuilt the primary do we need to get the dump from failover node? only in terms of backup? "I say yes,.. thats the only option by taking dump from failover node and construct the failed primary by taking down time in non-peek hrs.. – Mannoj Oct 15 '12 at 14:25
xtrabackup won't require you to take down any nodes, especially if you don't have myisam tables. – Derek Downey Oct 15 '12 at 14:30
Yes, I have done that earlier, for myisam 90GB data it goes for a toss too... I don't have to worry abt that one though. But I was checking is there an option to automatically pick data from failover node to primary without any binary logs or any backup methods. Just wondering what kind of replication would do this.. :( – Mannoj Oct 15 '12 at 14:34
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.