Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to backup a MYSQL database so that the backup database is as updated as possible, but I don't want to set my cron job so that it copies the orignal db every 5 minutes because it would hinder performance. is there a solution? I heard about db replication, but would it effect the performance of the original db?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

MySQL master->slave replication is really what you're looking for.

The slave will connect to the master as if it was another client just reading the binary logs (once each transaction has been commited) and then it replies the transaction on the slave server, so the overhead on the master is very very low.

Check the MySQL replication page (http://dev.mysql.com/doc/refman/5.0/en/replication.html) in order to set it up, it requires a couple changes to your my.cnf file

share|improve this answer
    
overhead on master, depending on number transactions and number of slaves. For a busy server, overhead can be significant. –  alvosu Jan 29 '11 at 14:57
    
Not true alvosu, I don't think you're grasping properly how binlog replication works in MySQL –  lynxman Jan 29 '11 at 15:02
    
I'm right. Without binlog: time mysql -u testmaster < 100000insert.sql real 0m2.234s With binlog and replitaion: time mysql -u testmaster < 100000insert.sql real 0m2.412s –  alvosu Jan 29 '11 at 15:19
    
So we're stating that a difference of 0.178s of CPU time in a million insert transactions is noticeable overhead, interesting :) –  lynxman Jan 29 '11 at 15:22
    
We'll have to agree to disagree on this one alvosu, I think the gain of this quite insignificant overhead you're defending is completely worth it, having your backups from a slave DB with this method will outweight this, from my own experience it's quite a gain –  lynxman Jan 29 '11 at 15:34

As this link explain the Master-Slave replication process normally is not a very big hit on the master server. You can use your slave as backup or use it to get the cron backups from time to time (you can stop the slave to backup stuff and it will sync the changes with the master when it get back). Give it a read, I think M-S replication is your solution.

share|improve this answer

In the case you describe, MySQL replication can be helpful, but will not be the "silver bullet" at all.

If a user/application error happens on the master, let's say a wrong "DROP TABLE" statement, it will be replicated to the slave too, so you would be in a serious trouble when trying to rollback to a previous state.

That's why I recommend installing a database slave and keep doing full backups (via mysqldump or innobackup) + binary logs backups, so you can do a point in time recovery (PITR) in case you need it.

However, if the master crashes (HW or network failure) the slave will always allow you to do a quick switchover with minimal amount of data loss. This and running reporting/read-only queries on them, are what slaves are most useful for.

My 2 cents.

Fran

share|improve this answer

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.