Help /

Backup and Restore

Purpose

Each company Admin will be responsible for taking (sql) backup of their FA database at regular intervals. In case upgrades or some bulk transactions go awry, a rollback would be necessary for which the restore option will be a godsend. This however comes with a security vulnerability for now with a variety of workarounds.

Backup and Restore

Procedure

Setup => Backup and Restore

Notes

  • Only tables that match the company's prefix will be backedup.
  • On restoration, these tables will first be dropped before being recreated and populated.
  • Beware that the username and password for all users too will be overwritten on restoration as also the user preferences, language choices, etc.,.
  • These backups are stored in the company's backup folder - in the screenshot above, for company 1 it is in company/1/backup relative to the webroot.
  • Match the source backup file's table prefix to that of the target company before restoration into the latter. Check table prefix from default company.
  • The first executable line in admin/db/maintenance_db.inc define('EXPORT_MAX_INSERT', 50000); determines the maximum number of records in a single insert statement. Adjust this value depending on the RAM you have available to PHP.
  • Stripping AUTO_INCREMENT=xxx from MySQL dumps can be done as instructed here like: 
mysqldump -u root -d -R --single-transaction <db> | sed 's/ AUTO_INCREMENT=[0-9]*\b//' | gzip > out.sql.gz

or alternatively using sed directly on the sql file (eg., sql_dump_file.sql) like: 

sed 's/ AUTO_INCREMENT=[0-9]*\b//' -i sql_dump_file.sql

Tips and Tricks

  • The backup file naming format is determined in the function db_backup() in admin/db/maintenance_db.inc file around lines 451-459: 
function db_backup($conn, $ext='no', $comm='', $tbpref = TB_PREF)
{
	if ($conn['tbpref'] != "")
		$filename = $conn['dbname'] . "_" . $conn['tbpref'] . date("Ymd_Hi") . ".sql";
	else
		$filename = $conn['dbname'] . "_" . date("Ymd_Hi") . ".sql";

	return db_export($conn, clean_file_name($filename), $ext, $comm, $tbpref);
}

Make the backup name more human friendly

This can be done by altering the file name by

  • inserting a string denoting the company name (with spaces and non alpha-numeric characters either stripped or replaced with underscore)
  • extending the date format to include seconds ("Ymd_His") will alleviate the edge case of two or more backups being taken within the same minute.

Exclude some tables from backup

To exclude some tables from the backup, replace the following code (Lines 505 to 513) in admin/db/maintenance_db.inc: 

    // get auto_increment values and names of all tables
    $res = db_query("show table status");
    $all_tables = array();
    while($row = db_fetch($res))
    {
		if (($conn["tbpref"] == "" && !preg_match('/[0-9]+_/', $row['Name'])) ||
			($conn["tbpref"] != "" && strpos($row['Name'], $conn["tbpref"]) === 0))
    		$all_tables[] = $row;
    }

with the following code: 

    // get auto_increment values and names of all tables
    $res = db_query("show table status");
    $all_tables = array();
    $skip_tables_list = Array('users', 'security_roles', 'sql_trail');
	for ($i = 0; $i < count($skip_tables_list); $i++) {
		$skip_tables_list[$i] = $conn["tbpref"].$skip_tables_list[$i];
	}
    while($row = db_fetch($res))
    {
		if (($conn["tbpref"] == "" && !preg_match('/[0-9]+_/', $row['Name'])) ||
			($conn["tbpref"] != "" && strpos($row['Name'], $conn["tbpref"]) === 0))
			if (!(in_array($row['Name'], $skip_tables_list, true)))
				$all_tables[] = $row;
    }

The $skip_tables_list array the names of tables to be skipped (without table prefixes).

The tables missing tables in the backup remain untouched in the database when restored.

Restoring backup to specific prefix

When a new company is created, an entry is made in the $db_connections array in the config_db.php table for it. This entry contains the prefix to be used and the key for the entry need not be the same as the prefix used.

When companies are deleted out of order as is in practice, finding a missing prefix in the $db_connections array would clash with possible non numeric table prefixes and restoring from a deleted company into it's original prefix would be impossible.

The following method simulates situation including the creation of a new company with a specific prefix to enable the restoration of a backup taken with the same prefix earlier:

  1. Create your 3 companies anew so you have 0 (Default), 1,2,3 companies.
  2. Note down the elements of the $db_connections array in the config_db.php entries (backup the file).
  3. Delete company with prefix 1_.
  4. The entry for the company "1" is removed from the config_db.php file
  5. The value of the variable $tb_pref_counter in the config_db.php will signal the next prefix - alter it as needed even temporarily so as to create your company "1" again and then revert back.

Backup Code Analysis

  • The key file where the backup originates is admin/db/maintenance_db.inc
  • In it, the key function that starts the backup is function db_backup()
    • Parameter $tbpref makes it generic enabling usage of other prefixes possibly within extensions or in customisations
    • Creates the name of the backup sql file and goes to function db_export()
  • The actual backing up is done in the function db_export()
    • Parameter $tbpref makes it generic enabling usage of other prefixes possibly within extensions or in customisations
    • If no prefix is there for a company and other companies data resides in the same db, then all tables in the db including those of other companies in it too will get backed up!
    • Sets max chunk size before writing to sql backup file to 2MB or if present, from memory_limit value in php.ini
    • Writes standard parameters to sql backup as comments
    • Writes user comments to sql backup as comments
    • Acquire all table names in db and shortlist them based on prefix or all on no prefix!
    • SQL comment character hardcoded as "#" throughout

If your backup is taking too long, then tune the MySQL server with mysqltuner. In debian, install with apt-get install mysqltuner. Usage: 

mysqlcheck -o mydbname
mysqltuner

and monitor with: 

tail -fn0 /var/log/mysql.err
tail -n15 /var/log/mysql.err
vmstat 5 5