Mirroring a DB

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Mirroring a DB

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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)
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post by AVATAr »

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
Post Reply