MySQL Master Slave Replication


#1

I’m not sure if this is the right forum to ask this, but I want to set up MySQL master slave replication. Is it possible to do this on my shared hosting account? I don’t see the option in PHPMyAdmin.

I develop Wordpress sites, and I have a local MAMP server I use when developing locally and then I sync my changes to the remote server. To sync the database I have to export from my local PHPMyAdmin and then import to the remote PHPMyAdmin. If I could set up master/slave I can just set the remote database as the master, and my local database would be the slave. All my writes would go to the remote server, and my reads would come from my local server, speeding up my development and keeping both DBs in sync! I already gave myself remote access to my Dreamhost DB, and Wordpress has a plugin called HyperDB which enables using Wordpress with a master/slave setup. So I’m halfway there, I just need to figure out how to set up replication on the Dreamhost end.


#2

Not possible on shared hosting.

If you’re on a MySQL VPS or dedicated server, we may be able to set up replication for you. That being said, it’s going to be way more trouble than it’s worth for this setup — you’re going to be much better off just pointing your local WP install to the DreamHost database directly instead of messing around with replication. It’ll be a tiny bit slower, but setting up replication will take far more time than you’ll ever save this way.


#3

Ok, thanks. You’re probably right, maybe not worth it. I develop with Wordpress day in and day out, and every little page refresh is wasted time. I had done what you suggested, pointing my local install to my Dreamhost DB, which is faster but there’s still a lag. Developing 100% locally is my ideal. I would welcome any ideas you might have about how I can sync my local DB to my remote DB more easily, like having some kind of automated script or something.


#4

Can you elaborate on the “may be able to set up replication for you”? Why is it “may”? And under what circumstances will you (or not) set up replication for VPS or dedicated customers?

I’m new to dreamhost and trying plan ahead.