I have a website that runs off a fairly large database (around 2 million rows, about 300GB).
Every day I import data from an xml feed to update the database. When I run the import it first of all flushes the table then imports all the new data.
This is a problem as the import takes about an hour, therefore the site is unusable for this time.
So to overcome this I want to set up database replication by setting up a master which I will do the imports on at midnight and then schedule the slave database, which I will run the site on, to replicate at 6am.
Is this possible? I have been searching for information on scheduling replication but I cannot find anything.
If you have two db servers, you could set up a config for you website with the connection string to your database. when your main db needs to get the updated data, switch the config for you site to use the back up db. Once the main db has all the data, switch the config back to the main db and update the backup for the next night. You can use cron and bash to do all of this.
The reason I would consider this approach for the size of your database as opposed to gregbrown54's, is that approach would likely lead to load issues for your users while it's happening.
If your updating, why not rewrite to update the data rather than destroy and re import ?
It's sounds to me like he's doing something like getting the mls real estate dumps. To take the approach you suggested, he'd still need to load the data in a database to figure out what is needed and what isn't or do string compares on the current and previous xml scripts. I don't know for sure if this is what is going on, but that's what it sounds like to me.