0

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

solsol
  • 1,121
  • 8
  • 21
  • 31

3 Answers3

3

I think what you want is replication: see here.

pQd
  • 29,561
  • 5
  • 64
  • 106
Fahad Sadah
  • 1,496
  • 11
  • 21
1

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.

pQd
  • 29,561
  • 5
  • 64
  • 106
  • 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
0

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.

blacklotus
  • 85
  • 6