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.

before posting I was digging whole site but didn't find help for my problem, so I hope someone will help...

Facts:

  • 30 Gb mysql database on remote server (about 20.000.000 rows)

  • data are once weekly updated in local network (mysql)

  • I need to transfer/replace local updated database with remote

connection is about 2mb (real mb, not mbps) up/down

Point is that I can't have 'down time' of remote mysql server. Until now I

Tried:

  • navicat data sync -> Ok, but take about 3 days to finish

  • dbForge -> ok but need 5 days to finish

  • mysql dump transfer to remote server and execution -> about day, but a lot of downtime

  • rsync folder with database /mysql/lib/MY_DATABASE -> 4 hours, but after that I need to execute always 'repir on remote server' which takes about 2 hours, and a lot of down time

  • mysql dump piped from cl to directly goto server -> still now satisfied many problems

  • I could give you more things that I tried...

  • mysql replication -> slow

Anyase, what is best,best way to:

  • refresh remote mysql on weekly level

  • and in same time to have 0 sec down time nor huge server load

If you have any idea please share

share|improve this question
1  
What's the rate of change in the live data, i.e. how much load would there be if you ran a slave server locally and replicated all changes live, and then once a week simply copied the local database around into another mysql instance. –  EightBitTony Jun 26 '12 at 11:33
    
Data rate change on weekly level is about 7-8% –  Jeff Jun 26 '12 at 11:48
    
Tried combination that you mention (replication) , that was one of first ideas, works nice but take long time to finish (3 days aprox) –  Jeff Jun 26 '12 at 11:49
    
Not sure what you mean took 3 days, I'm suggesting running a slave server 24/7 so it's always up-to-date with remote, then stopping the local slave, taking a dump or copy, and restarting the local slave. –  EightBitTony Jun 26 '12 at 11:59
    
Due to huge amount of data, when update is finished on local server, I see for 48-72 hours changes made on remote (slave). Local server process about 2.000 records/sec, every record has 3-4 operations, what is about 6-8K database calls/sec, remote server power and connection speed makes that remote slave receives requests, put in que and execute, that whole thing take 2-3 days, I mean on that. –  Jeff Jun 26 '12 at 12:34

1 Answer 1

Run two mysql servers locally.

Run one of those local servers as a slave replica of the remote server so that all changes on the remote server are up-to-date locally.

Then, when you need to refresh the other local server, stop the slave local server, take a copy of the DB (using the fastest local method possible), and then restart the local slave so it immediately catches up with the remote again.

Your other local server now has a refreshed copy of the DB.

I don't use mysql much at this level - but you can probably also achieve this with a single mysql server, which just slave replicates a single DB instance from the remote server, and then you copy that DB locally to another DB on the same mysql server.

share|improve this answer
    
huh, complicated, but thanks, I will try! Any other suggestions? –  Jeff Jun 26 '12 at 14:15
    
Complicated? I think it's very simple in reality, replicate data constantly to avoid huge updates over slow networks, and use local fast storage to do the duplication. It's what I tried suggesting twice in the comments before writing it as an answer. You've already ruled out every option which requires you to stop the remote server or copy lots of data over the network in one go. –  EightBitTony Jun 26 '12 at 14:24
    
+1 for replication. It is the way to go if you want 0 sec downtime. And since you can pull the updates as they happen the server load will be minimally affected. –  drcelus Jun 26 '12 at 14:26

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.