I'm new to MySQL but I have experience with other major RDBMS like Oracle and MSSQL. I'm trying to find out what are some of the widely used methods (or best practices) when it comes to refreshing one or more MySQL databases between two servers on a nightly basis.
For example: refresh the "QA" server from "PROD". I would like to develop an automatated process to handle this refresh/reload and put it on a scheduled job and have it run nightly. The current setup is running under Linux.

I would like to get some feedbacks or suggestions from those who have done this before or provide me with a few pointers.

Thank you


I would imagine that a nightly "refresh" between two databases like you say would involve dumping one database and importing the dump into the other database. That would lead to major performance drags every time you do that process.

Personally, I don't think that is a great idea when there are tools that allow you to replicate data between database servers. I am afraid I don't entirely understand what your use case entails, but I would recommend reading up on MySQL Replication

| improve this answer | |
  • Harrison, the goal is to keep QA as closely to PROD as possible so testers can perform their test cases when there are new changes. Yes, I did come across few posts about MySQL replication. I have configured replication in MSSQL many times before and wasn't sure how complicated that would be in the Linux world since I'm also new to Linux. I was looking for something simple but if replication is most efficient then I will read up on this. Thanks – sydney Apr 25 '17 at 17:02
  • Just read a few postings on mysql replication. surprisingly it doesn't look that terrible or complicate to setup. In fact, it appears to be much easier to setup compared with mssql replication. Are there any "gotchas" i should be aware of? Better yet, can someone give "pros" and "cons" on mysql replication? What about the log file it uses? Will it keep growing & growing until you do something to it? – sydney Apr 25 '17 at 18:02
  • You can specify the size of the log file. I am quite new with MySQL Replication as well. The biggest gotcha I have dealt with is setting up the replication using a server that already has lots of data on it and cannot be down for an extended period of time. – Hman Apr 25 '17 at 20:48
  • Some links that helped me: set up replication, rsync files to slave node, and mysql data directory location – Hman Apr 25 '17 at 20:50

Set up 2 slaves. Have one offline from replication so that QA can play with it. The next day, put it back online and let it catchup. Meanwhile let QA have the other Slave.

Maybe you could ping-pong hourly, thereby giving QA data that is not older than one hour.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.