Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My job has a testing, staging and production enviroment. They want most database changes to be done on the staging environment and for the production environment to replicate the staging environment. From the research I've done, it appears that Mysql Replication process would be the best way to do this. So the Staging Database would be the master DB and the live production site would run on the slave DB. So my question is this.

From what I found, I was able to figure out how to setup a master slave database environment but the resources I've found do not go in detail on how frequently the slave talks to the master database. I would like for the slave to talk to the master either on demand or during a particular time during the day. I maybe misunderstanding the resources I am reading but thats why I am asking this question. I would not like for the database to constantly listen for new changes in the master log. How does replication work by default and how can I get it to listen at a particular time or when I tell it to.

Thanks

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Since this is ad-hoc synching, don't set up any master-slave or master-master, as you're making your life more complicated than necessary. Merely set up a cron job or at job to synch via dump, with your choice of: simple file copy, sockets or tcp/ip mysql connection for the transfer.

share|improve this answer
    
So your saying if I simply just do a sqldump and send the sql file over to the production server. mysql will know how to sync up data without completely overwriting everything ? –  Anthony Reed Jan 28 '13 at 21:12
    
If possible, I would like to migrate only the differences in data or schema for performances purposes –  Anthony Reed Jan 28 '13 at 21:24
    
if you do it via a file, u end up manually doing the import or u can set up a cron to say watch a directory and do the import. Either that or socks/tcpip and choose the right kind of dump to include or exclude the wipe of existing tables. –  Chris Jan 28 '13 at 21:32
    
Just read your comment about performance need to diff synch. How many rows in whole db? Unless it's millions of records performance is fine. You could also just take the relevant part of the logs from staging and re-play on production at will. –  Chris Jan 28 '13 at 21:35
    
I see. I did some research and it appears as long as I don't specify to drop the existing tables in my dump, mysql will just append whatever new data to the existing table. which is ultimately what I want. I might just go ahead and utilize the dump instead. And probably use diff to compare. –  Anthony Reed Jan 28 '13 at 22:00
add comment

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.