Best way to sync an existing MySQL DB to another server

Discussion in 'MySQL' started by JWRmedia, Oct 30, 2008.

0
  1. #1
    I've got daily backups of one of my sites that are automatically sent to another server. If my site goes down on one server, I just forward the domain to the other IP address and the site's as good as new.

    Only, I can't figure out a good way to sync the database. I want to grab the current database contents and sync a database on another server to "mirror" my existing database on my main site daily.

    I've found cronjobs and scripts that will automatically backup my database, but I don't have anything that would automatically import its contents into another database.

    Any ways of doing this??
     
    JWRmedia, Oct 30, 2008 IP
  2. penalty

    penalty Member

    Messages:
    36
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    43
    #2
    hi,
    try the mysql tool "mysqldump".

    on the mainserver do:

    mysqldump --opt db_name | mysql --host=remote_host -C db_name
     
    penalty, Oct 30, 2008 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    You should look into replication. Every time the master database gets an update the slave database is updated as well. This way your databases never have different data on them, and if the master goes down, you don't lose any information due to a gap in syncing.

    MySQL uses a binlog replication which is a bit of a pain to understand and configure, but once it's setup, it's a lot more reliable than dumping the existing database to the other server every day.
     
    jestep, Oct 30, 2008 IP
  4. JWRmedia

    JWRmedia Banned

    Messages:
    499
    Likes Received:
    35
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thats what I want to do, but the slave database will reside on a different server, in case the master server goes down.
     
    JWRmedia, Oct 30, 2008 IP
  5. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #5
    This is what you do in replication. You can easily setup MySQL master/slave replication if you follow the steps. The only time taking thing is taking dump from the master and importing it to the slave.
     
    mwasif, Oct 30, 2008 IP
  6. 3dcgmodel

    3dcgmodel Banned

    Messages:
    655
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #6
    That is what i find for a long time. Is it call mirror server like big site use it? when one server down, visitor can still visit your site,right?
     
    3dcgmodel, Nov 3, 2008 IP
  7. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #7
    Sort of. If you are simply replicating a database it wont provide any more redundancy than a single server. You have to either program into the site to use the second database if it can't connect to the master database or use a load balance / fail-over system that will swap the databases in the case that one goes down.

    It gets quite a bit more complicated to implement a fail-over system than just replication.
     
    jestep, Nov 4, 2008 IP