How to copy a MySQL DB to another DB

[soned]

Hi here is the scenario:

 

Lets say I have 2 shared hosting accounts with TCH, one is my primary host the other is backup.

 

Initially, Both have identical php files and database data.

 

The primary one is then launched to the world and the database quickly grows.

 

How do I make it so that whatever changes are done to the primary database is also done to the backup database?

 

thanks!!!!!

[TCH-JimE]

Hmmm, your looking at something like a cronjob to dump the database and then another one to FTP it into the other site and then a further one to upload any databases it finds in that folder. You may even be able to create just one cron and make it run a php file which will do the entire lot for you.

 

I hope that gives you somewhere to start. If the database becomes huge, it may become a problem constantly moving it.

 

JimE

[Deverill]

You can also do it by hand which is a more simple task. Just backup at one site, transfer it and then restore it. It depends on how often you want to "synch" the two. One time after development is fine by hand. Every night or once a week may be worth investigating the cronjob method.

[soned]

thanks for your replies. I actually need the 2 servers to be in sync in real time. this is because i have a job site, and i want to applicants to access data from server 1 and recruiters on server 2. the reason being, the recruiters are the ones paying, and being in the same box as the applicants, their queries are very slow!

 

what I have in mind is to have all php codes in box1, and when data are inserted/updated/deleted, it saves to both box1 and box2.

 

The recruiters will still use box1 to log in, but their data will come from box2.

 

This means that everytime I do insert/update/delete statements, I will do so once for box1 and another for box2.

 

Do you guys see any problems here? Box 1 is generating a lot of mysql cpu overhead, but memory is stable at 30%.

 

The only overhead this configuration will make is the 2 sql commands instead of 1, but since the commands are issued to separate boxes, it shouldn't have THAT much effect. right?

 

cheers!

 

FYI - box 1 is dedicated server, and I just made an order for box 2

[soned]

i got a hint from sitepoint forums, which is exactly what i need.

 

 

 

1 word:

 

"replication"

[TCH-JimE]

Hello,

 

As this could cause a huge amount of network problems, I don't believe its nesscary. What your essientally asking for is RAID in some setup which we do not offer here.

 

However, if you wish to check, I suggest you option a support desk ticket and explain what you want and what will happen and see what the server gurus think too

 

JimE