An internet project I am coding for utilizes a complex CSV to MySQL ripper tools to produce their database. This means that to update the db quite happy with the most recent changes in the CSVs, a ripper tools operates which truncates the appropriate tables (but leaves others which are filled with the website) and fills them again using the data in the CSVs.

Yes, this isn't an excellent process but you will find reasons why this method was selected on the standard "focusing on the particular db" approach.

What I am battling with is exercising the easiest method to run this update process without harming the consumer experience an excessive amount of. A couple of amounts to bear in mind:

1) This method needs to be run regularly, in the plethora of every couple of days / monthly
2) The db ripper tools presently takes around an hour and can most likely require 15 hrs later on, a minimum of when the forecasts concerning the database growth are right (yes, ouch!)
3) The sql dump from the full database is presently under 20MB (which enables easy import through phpmyadmin) and can break that barrier in no time. I suppose this should not be considered a problem when i can only use SSH upload rather.

Here are the options I figured of, all utilizing a separate database with global configurations too (these configurations are checked for each read/write on the website). Alternative 2 appears to become the worst because it prevents read access for the entire duration of the conversion, which may be quite lengthy when i stated. These block write access for comparable very long time that is fine though, it doesn't prevent customers from registering or anything critical like this. I am quite interested in the feasibility from the third alternative because it theoratically enables to shorted down time from the read functionality when i do not have to upload a large dump.

Has anybody done something similar to this? I'd appreciate superior options if they are available or any feedback regarding how to improve these and whether or not to chose 1 or 3. Thanks ahead of time :)

Alternative 1
1) Set globalsettings_booleans_writeable to 0
2) Download current DB (SQL dump)
3) Import downloaded DB locally
4) Run converter (in update mode) on local database
5) Export local DB
6) Set globalsettings_booleans_readable to 0
7) Import exported DB online
8) Set globalsettings_booleans_readable to 1
9) Set globalsettings_booleans_writeable to 1

Alternative 2
1) Set globalsettings_booleans_writeable to 0
2) Set globalsettings_booleans_readable to 0
3) Run converter (in update mode) on live database
4) Set globalsettings_booleans_readable to 1
5) Set globalsettings_booleans_writeable to 1

Alternative 3
1) Set globalsettings_booleans_writeable to 0
2) Create a remote copy of the database
3) Run converter (in update mode) on remote copy
4) Set globalsettings_booleans_readable to 0
5) Replace remote original with remote copy (easy?)
6) Set globalsettings_booleans_readable to 1
7) Set globalsettings_booleans_writeable to 1

It appears in my experience that many exclusivity might be prevented by analyzing the CSV to determine what records really would create a switch to the database. It appears such as the CSV generator may be the actual supply of the information, and also the database is only a mirror from it, right?

If that's the case, the CSV records which lead to no change might be overlooked, the d/b tables not cut down, and also the remaining CSV records might be operated with Alternative 2, which most probably would take merely a couple of minutes.

The main weakness of the approach is that if records are erased in the source and there is no indication the d/b will need them erased in your area.