db replication questions..

joey3002

im reading about how to do it and it seems quite easy.
but i have some questions.

will all the dbs be synced or do i tell it which ones to do it for? the reason i ask is that i do not need all the dbs synced
if the master goes down, will the site still function and just use the slave?

thanks

liquorvicar

there are various my.cnf commands you can use to specify which DBs and tables to replicate. investigate the replicate-do-db and replicate-ignore-db options.
you will probably need to build some sort of code for that. your PHP script will only use the DB host you specify.

Sxooter

Actually, these questions vary wildly from db to db. And from replication engine to replication engine. There are external engines, trigger based engines, log shipping engines.

And, MySQL Replication is easy to setup, but I hope you never experience a failure, as it is know for its unreliability. Seriously, look it up, it's not at all uncommon for MySQL replication to just silently stop working.

csn

Sxooter - aren't you using Slony in your new job? How do you like it (Slony)?

Sxooter

Slony's the bomb. It's a bit more complicated to setup than the log shippers are (slony 1.1 looks to support log shipping, by the way, and is in beta freeze). But there are perl scripts that pretty much do all the hard work for you. The hardest part is figuring out which perl scripts you need to run when setting up replication.

maransekar

Hi, I am having problems with replication as well. I have been using MySQL Replication for about an year now. It is working fine and keeping our datbases upto date in all our servers, but except one thing. Replication just stops automatically for some strange reason over the weekends. I thought it could be because there is no db activity over the weekend, then i tried running a script every 15 mins just to update a test record in the db to keep the db activity going, but even then it stops working. This results in me updating the replication every monday morning by taking a new dump of db and transfering it to the slave and restart the slave process.

any ideas? am i doing something wrong or any more configuration should be done to resolve this issue?

Many Thanks

Sxooter

Sounds like the way I've seen MySQL replication work in the past. I.e. unreliably.

I don't know what would be causing the issue you're having off the top of my head thought. Could well be a timeout issue. Have you searched the mysql discussion list archives? I know I've seen this kind of thing come up plenty before, and usually there's no real fix for it.

Have to admit, one of my favorite things about the postgresql / slony replication engines is the ability to add and remove replication to a live database with no interruption in service...