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.

MySQL .frm recovery

I had problems with my hard driver that caused my MySQL tables to be inaccessible.  I was getting “#1033 – Incorrect information in file” error.  I checked the files if there where obvious anomalies on the file caused by cross-linked file pointers but the files looked ok.

I had just reinstalled my MySQL due to the corruption and didn’t realize that this database was using InnoDB.  I thought it was enough to backup and restore the database directory as that would be enough for MyISAM databases.  Later I tried copying the InnoDB files from the data directory of the old installation.  It didn’t work at first until I re-started my computer.  The database was recovered after that (structure and data).