Simple yet effective way to replicate/mirror a database (any database at that)

Here’s what I did:

• First, I centralized all my data calls into a single object or function which receives an sql statement as the parameter.

• I check if the SQL statement is performing an INSERT, UPDATE or DELETE. To do this I just check if the first word is “SELECT” ( you may have to improve that if you’re using stored Procedures ).

• If the statement modifies data on the database then I put the complete sql statement in an “sql_transfer.sql” file which is a simple text that is accessible through the web.  This server now acts as the publisher.

• I also have an option to perform real-time replication if the other server is within the same network. If this option is on, then I immediately execute the same sql statement on the mirror server.

• Normally, I’d have a cron running every so number of minutes that pulls the sql_transfer.sql from my publisher server.  If the download was successful then the file is deleted from the server using a script on the publisher server. The downloaded sql_transfer.sql is then executed on statement a time deleting each one that was executed. A flag is raised telling the process not to download while the sql_transfer.sql is not yet empty.

Advantages:

• Works even across domains and ISPs.

• Doesn’t require complex replication tools.

• Easy to recover in case of network problems. can be manually executed.

• Works on practically any database platform.