Critical: Maintain A Reliable Backup Policy

Post date: Jul 02, 2011 7:35:32 PM

The SMCP system uses the very robust MySQL database engine to process and store all of the data in the program.  In case of a catastrophic loss of a server, for example, the program itself can be installed fairly easily and fairly quickly back to the replacement server.  If you don't have a copy of the program, we can and will supply it quickly.  But recovering your actual data is a different story.

AIRO Tech is NOT responsible for SMCP subscriber's data; that has to be maintained in house and should be managed by someone close by with whom you are comfortable.  But we do have some suggestions and we strongly recommend that you implement these in a way that's appropriate for your system.

It's just not enough to 'have a good backup' - that's OK for documents and spreadsheets, but the underlying structure of a database is not easily copied from place to place - you can't normally just 'copy it back' if it's lost.  Additionally, it consists of multiple files in particular paths, and involves particular read/write permissions and various limits to visibility.

For these reasons, we suggest that you make use of the 'data dump' feature in MySQL.  This function 'dumps' the entire database into a single file - that file includes embedded commands to automatically reconstruct the entire database structure and data.  That one file is all you need to completely rebuild all of your data.

A typical routine would be to automatically 'dump' each database (each company uses a single, separate database) into a file at night when people are not typically working.  You may want to keep several nights' worth of copies: it's not difficult to set up a script that removes the oldest copy and adds the most recent one.  These copies of 'dumped' files should be backed up as well.  Having several nights' copies allows you in an emergency to 'roll back' the state of your data for a number of days.

The 'dump' files can serve another purpose: they can be used to clean up and re-index your data periodically.  Doing this minimizes the chances for data corruption, and speeds up normal processing.  Over time, as records are updated and deleted, the databases become fragmented and indexing can become inefficient.  A 'dump' file can be 'loaded' back into the database to give you a fresh start and completely re-organized indexes.

The following are sample commands for dumping and loading a database - PLEASE NOTE: these are examples only and may not work on your version, so make sure you check the MySQL syntax and test these before using:

To 'dump' the database named 'servmgr' into a file at C:\mysqldump\servmgr.txt using MySQL username 'john' and password 'password123', use (something like) this command:

mysqldump -ujohn -p servmgr > c:\mysqldump\servmgr.txt

Then press enter - you'll be prompted for john's password, enter 'password123' (replace with the username and password for your system).

To 'load' this database back into the MySQL database server (or into another server, if you need to move your data), you will first need to remove the original database: run the MySQL program, and AT THE MySQL command prompt, delete the database using this command (of course, make SURE you have the database 'dumped' first!):

delete servmgr;

and press 'Enter'.  Now exit MySQL, and at the regular command prompt, use (something like) this command:

mysql -ujohn -p servmgr < c:\mysqldump\servmgr.txt

Again, you'll be prompted for the password, then the MySQL program will re-create the entire database that was 'dumped'.