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.

We want to sync 2 mysql servers with eachother (products & qa system). We would like to sync all databases, data and users of the production machine to a secondary mysql machine.

What would be the easiest and best way to do this automatically? Synchronization can but doesn't have to be done instantly. We are ok with sync every 12 hours or so, although faster is better. Ease of setup and maintenance is more important than speed for us.

We run mysql 5 on a Centos 5 machine

share|improve this question

3 Answers 3

up vote 3 down vote accepted

I think what you want is replication: see here.

share|improve this answer
4  
That page is for MySQL 4. Make sure you use the MySQL 5 Replication docs. –  lladnar Jun 27 '10 at 20:15

for initial sync, and in general - if you do not want to use mysql replication - take a look at: mk-table-sync from maatkit project.

share|improve this answer
    
is this a possibility to use on a production system where we have one very active master server that needs to be replicated to a slave mysql server? The slave won't be very active. –  solsol Jun 27 '10 at 22:12
1  
@solsol - i used it on production but with very little write load. i used it successfully to fix broken replication and then restart it again. –  pQd Jun 27 '10 at 23:01
    
thanks pQd, is it possible to just sync the whole mysql server with it? I mean users, new databases, ... –  solsol Jun 28 '10 at 10:08
    
"It does not synchronize table structures, indexes, or any other schema changes. It synchronizes only data." –  solsol Jun 28 '10 at 10:11
1  
@solsol - i dont know what's your database size, but maybe mysqldump on both machines and then rsync + restore would be a better option? –  pQd Jun 28 '10 at 21:22

An alternative to MySQL replication would be MySQL set up on top of DRBD, and have DRBD handles the syncing process. DRBD will do incremental back-up by the second to a secondary server. Together with Heartbeat, you can have a low-cost high-availability MySQL database set-up.

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.