Page 1 of 1

Mirroring a DB

Posted: Thu Jul 15, 2004 2:57 am
by JayBird
Morning peeps,

Here thes the situation. Our company has an Intranet running on our local network.

Abviously it is bulit using PHP and MySQL.

One part of the intranet has information that needs to be mirrored in another MySQL DB offsite with one of our web hosts.

I'm not sure the best way to do this. When performing my queries (inserting and updating data), should i just perform them on each DB.

This method seems a long winded way of doing it.

Or once i have done the queries on the local DB, i could syncronize the DBs, but this would involve upload a lot of unchanged data and would probably take too long to be unoticeable.

Anyone else got ideas of how i should go about such a task?

Thanks

Mark

Posted: Thu Jul 15, 2004 5:47 am
by kettle_drum
Maybe just store a list of queries that you have run on the local server and then once an hour/day run all those queries on the remote server.

Posted: Thu Jul 15, 2004 5:54 am
by JayBird
kettle_drum wrote:Maybe just store a list of queries that you have run on the local server and then once an hour/day run all those queries on the remote server.
Yeah, i thought about that too, but both DBs need to be the same at all times :?

Mark

Posted: Thu Jul 15, 2004 6:14 am
by JAM
cronjob a mysqldump one intranet-db, and the same (but import) on the extranet one? Size of the db's might be of interest as you yourself mention "would probably take to lon to be unoticeable", but have you tried it?
Narrowing down' queries to unique data, to limit the stress in the traffic, that is <> from the two db's shouldn't be to hard. This is of course depending on amount of data, and what data you are storing.

Personally, I use;
a) phpMyAdmin's export/import features, tho neither is automated...
b) 3rd part software for the win32 enviroment, that I belive can be automated with a combination of tools...

http://dev.mysql.com/doc/mysql/en/Repli ... HOWTO.html just 'might' be of some interest also. (See comment marked: Posted by snix on July 27 2003 6:21pm)

Posted: Thu Jul 15, 2004 8:28 pm
by AVATAr

Posted: Fri Jul 16, 2004 9:11 am
by pickle
Replication is just what you're looking for. I use it currently at my company to make sure two servers are the same. Essentially what happens is every time a query is run on one server (only those queries that change data), they are logged. Then once every 30 seconds or so, the slave of that server checks it's master to see if any changes have occurred. If it has, the slave does those queries as well.

Replication in MySQL

Posted: Fri Jul 16, 2004 9:15 am
by JayBird
Thanks everyone, i may go for the saving the Query aproach, and running them on a regular basis.

What method do you use to store the queries?

Mark