Automatic database backup
Automatic database backup
First, I'd like to say congratulations to the CMSMS developers for creating this great CMS - it's now one of three that I use in different circumstances depending on the customer, the other two being Wordpress and Joomla.
I've had a look around for database backup modules and I can't find anything to that quite fits with what I'm used to on other CMS, i.e. a control panel where I configure a DB backup to be taken at some interval and then emailed to a configurable address.
For both my Wordpress and Joomla sites DB backups just appear in my email account at the configured intervals but for CMSMS there doesn't seem to be a way of doing this and I have to resort to manaul backups or try and configure cron jobs.
Does anyone have any suggestions? It seems to me that this would be very useful basic functionality for this CMS. I'm prepared to put some work into module development if someone would like to give me some guidance.
Apologies in advance if I've missed the obvious.
I've had a look around for database backup modules and I can't find anything to that quite fits with what I'm used to on other CMS, i.e. a control panel where I configure a DB backup to be taken at some interval and then emailed to a configurable address.
For both my Wordpress and Joomla sites DB backups just appear in my email account at the configured intervals but for CMSMS there doesn't seem to be a way of doing this and I have to resort to manaul backups or try and configure cron jobs.
Does anyone have any suggestions? It seems to me that this would be very useful basic functionality for this CMS. I'm prepared to put some work into module development if someone would like to give me some guidance.
Apologies in advance if I've missed the obvious.
-
- Forum Members
- Posts: 116
- Joined: Mon Jul 28, 2008 4:48 pm
Re: Automatic database backup
I solved this problem using phpMySQLAutoBackup - http://www.dwalker.co.uk/phpmysqlautobackup/
I combined all the relevant PHP files together into a single file, hacked the code to use the CMSMS config file for the database connection settings and copied the code into a UDT. I then changed the LogoutPost event to call the UDT.
Now, whenever anyone logs out of the admin panel I am emailed a MySQL backup.
phpMySQLAutoBackup is released under GPL but I'm not pasting it here without permission.
Nick
I combined all the relevant PHP files together into a single file, hacked the code to use the CMSMS config file for the database connection settings and copied the code into a UDT. I then changed the LogoutPost event to call the UDT.
Now, whenever anyone logs out of the admin panel I am emailed a MySQL backup.
phpMySQLAutoBackup is released under GPL but I'm not pasting it here without permission.
Nick
Re: Automatic database backup
Nick, wouldn't this nice GPL+GPL integration make a nice CMSms module ?
Pierre
Pierre
Re: Automatic database backup
Thanks Nick, Pierre.
I think the basic requirements would be:
1) backup of the core database files
2) ability to configure additional tables to backup
3) ability to configure tables to exclude (e.g. logs)
4) immediate backup
5) periodic backup
Using Nick's idea there is the phpMyAdmin code to look at, there are also implementations on the Wordpress site (e.g. http://wordpress.org/extend/plugins/wp-db-backup/).
Is there a timer routine running in cmsms on would we have to rely on a trigger (like someone accessing cmsms) to kick off the backup?
I think the basic requirements would be:
1) backup of the core database files
2) ability to configure additional tables to backup
3) ability to configure tables to exclude (e.g. logs)
4) immediate backup
5) periodic backup
Using Nick's idea there is the phpMyAdmin code to look at, there are also implementations on the Wordpress site (e.g. http://wordpress.org/extend/plugins/wp-db-backup/).
Is there a timer routine running in cmsms on would we have to rely on a trigger (like someone accessing cmsms) to kick off the backup?
Re: Automatic database backup
Why not use a cron job instead.
Here's a simple daily rotating backup script that I use:
Replace , and with appropriate values (minus the brackets).
Nullig
Here's a simple daily rotating backup script that I use:
Code: Select all
#/bin/sh
rm -rf /root/sqlbackups/07
mv /root/sqlbackups/06 /root/sqlbackups/07
mv /root/sqlbackups/05 /root/sqlbackups/06
mv /root/sqlbackups/04 /root/sqlbackups/05
mv /root/sqlbackups/03 /root/sqlbackups/04
mv /root/sqlbackups/02 /root/sqlbackups/03
mv /root/sqlbackups/01 /root/sqlbackups/02
mkdir /root/sqlbackups/01
mysqldump -u<username> -p<password> -hlocalhost <dbname> | gzip > /root/sqlbackups/01/<dbname>`date +\%y\%m\%d`.sql.gz
Nullig
-
- Forum Members
- Posts: 116
- Joined: Mon Jul 28, 2008 4:48 pm
Re: Automatic database backup
I've spoken to the author of phpMySQLAutobackup and he is happy for me to post my version of his code. I'll do that this evening.
Of course, there is nothing to stop anyone downloading and installing phpMySQLAutoBackup as it is. It is a set of PHP programs that you install on your server and call in someway, e.g. by adding some code to the bottom of your template to trigger the backup. There some code to prevent the backup from running too frequently, e.g. not more than once every 24 hours, etc.
All I've done is make it simpler to install and use by joining all the relevant bits of PHP together so it can be pasted into a UDT. The UDT can then be called from an Event (I use LogoutPost) or a page or template like any other UDT.
By default it backs up all tables, or it will only backup tables you specify. It doesn't do exclusions.
Nick
Of course, there is nothing to stop anyone downloading and installing phpMySQLAutoBackup as it is. It is a set of PHP programs that you install on your server and call in someway, e.g. by adding some code to the bottom of your template to trigger the backup. There some code to prevent the backup from running too frequently, e.g. not more than once every 24 hours, etc.
All I've done is make it simpler to install and use by joining all the relevant bits of PHP together so it can be pasted into a UDT. The UDT can then be called from an Event (I use LogoutPost) or a page or template like any other UDT.
By default it backs up all tables, or it will only backup tables you specify. It doesn't do exclusions.
Nick
Last edited by Anonymous on Mon Nov 24, 2008 10:33 pm, edited 1 time in total.
-
- Forum Members
- Posts: 116
- Joined: Mon Jul 28, 2008 4:48 pm
Re: Automatic database backup
Here is the code. To use it create a User Defined Tag and paste in the code.
I have changed the program to use the CMSMS config for the database connection details. You do need to set $from_emailaddress and $to_emailaddress to your own email addresses. If you are going to call the tag from a public page you should also ensure $time_internal is set to an appropriate value.
It will backup all the tables in the database. If you only want to backup a subset of tables you will need to define those tables in the variables $table_select[x]
As I've joined all the separate php files together, which would normally reside in their own directory structure, some of the comments in the code that refer to various files and directories no longer apply or make sense.
If you want more information or help please start with the phpMySQLAutoBackup website and forum - http://www.dwalker.co.uk/phpmysqlautobackup/
Nick
I have changed the program to use the CMSMS config for the database connection details. You do need to set $from_emailaddress and $to_emailaddress to your own email addresses. If you are going to call the tag from a public page you should also ensure $time_internal is set to an appropriate value.
It will backup all the tables in the database. If you only want to backup a subset of tables you will need to define those tables in the variables $table_select[x]
As I've joined all the separate php files together, which would normally reside in their own directory structure, some of the comments in the code that refer to various files and directories no longer apply or make sense.
If you want more information or help please start with the phpMySQLAutoBackup website and forum - http://www.dwalker.co.uk/phpmysqlautobackup/
Nick
Code: Select all
/**********************************************************
* phpMySQLAutoBackup *
* Author: http://www.DWalker.co.uk *
* Now released under GPL License *
* *
**********************************************************
* Version Date Comment *
* 0.2.0 7th July 2005 GPL release *
* 0.3.0 19th June 2006 Upgrade *
* - added ability to backup separate tables *
* 0.4.0 Dec 2006 removed bugs/improved code *
* 1.4.0 Dec 2007 improved faster version *
**********************************************************/
$phpMySQLAutoBackup_version="1.4.3";
// ---------------------------------------------------------
// For support and help please try the forum at: http://www.dwalker.co.uk/forum/
// you need to add your details below:
require( "../config.php" );
$db_server = $config['db_hostname']; // your MySQL server - localhost will normally suffice
$db = $config['db_name']; // your MySQL database name
$mysql_username = $config['db_username']; // your MySQL username
$mysql_password = $config['db_password']; // your MySQL password
$from_emailaddress = ""; // your email address to show who the email is from (should be different $to_emailaddress)
$to_emailaddress = ""; // your email address to send backup files to
//best to specify an email address on a different server than the MySQL db ;-)
$save_backup_zip_file_to_server = 0; // if set to 1 then the backup files will be saved in the folder: /phpMySQLAutoBackup/backups/
//(you must also chmod this folder for write access to allow for file creation)
// Below you can uncomment the variables to specify separate tables to backup,
// leave commented out and ALL tables will be included in the backup.
//$table_select[0]="MyFirstTableName";
//$table_select[1]="mySecondTableName";
//$table_select[2]="myThirdTableName";
//note: when you uncomment $table_select only the named tables will be backed up.
$limit_to=10000000; //total rows to export - IF YOU ARE NOT SURE LEAVE AS IS
$limit_from=0; //record number to start from - IF YOU ARE NOT SURE LEAVE AS IS
//the above variables are used in this formnat:
// SELECT * FROM tablename LIMIT $limit_from , $limit_to
//interval between backups - stops malicious attempts at bringing down your server by making multiple requests to run the backup
$time_internal=3600;// 3600 = one hour - only allow the backup to run once each hour
// Turn off all error reporting
error_reporting(0);
// For debugging uncomment line below:
//error_reporting(E_ALL);
// No more changes required below here
// ---------------------------------------------------------
//define('LOCATION', dirname(__FILE__) ."/files/");
//include(LOCATION."phpmysqlautobackup.php");
/**********************************************************
* phpMySQLAutoBackup *
* Author: http://www.DWalker.co.uk *
* Now released under GPL License *
* *
**********************************************************
* Version Date Comment *
* 0.2.0 7th July 2005 GPL release *
* 0.3.0 19th June 2006 Upgrade *
* - added ability to backup separate tables *
* 0.4.0 Dec 2006 removed bugs/improved code *
* 1.4.0 Dec 2007 improved faster version *
* 1.4.1 Jan 2008 - table_select check moved up *
**********************************************************/
$phpMySQLAutoBackup_version="1.4.3";
// ---------------------------------------------------------
// For support and help please try the forum at: http://www.dwalker.co.uk/forum/
if(($db=="")OR($mysql_username=="")OR($mysql_password==""))
{
echo "Configure your installation BEFORE running, add your details to the file /phpmysqlautobackup/run.php";
exit;
}
if (isset($table_select))
{
$backup_type="\nBACKUP Type: partial, includes tables:\n";
foreach ($table_select as $key => $value) $backup_type.= "$value;\n";
}
else $backup_type="\nBACKUP Type: Full database backup (all tables included)\n\n";
//include(LOCATION."phpmysqlautobackup_extras.php");
/**********************************************************
* phpMySQLAutoBackup *
* Author: http://www.DWalker.co.uk *
* Now released under GPL License *
* *
**********************************************************
* Version Date Comment *
* 0.2.0 7th July 2005 GPL release *
* 0.3.0 19th June 2006 Upgrade *
* - added ability to backup separate tables *
* 0.4.0 Dec 2006 removed bugs/improved code *
* 1.4.0 Dec 2007 improved faster version *
* 1.4.2 July 2008 modified to overcome issue with *
* "double newline in headers" *
**********************************************************/
$phpMySQLAutoBackup_version="1.4.3";
// ---------------------------------------------------------
// For support and help please try the forum at: http://www.dwalker.co.uk/forum/
function has_data($value)
{
if (is_array($value)) return (sizeof($value) > 0)? true : false;
else return (($value != '') && (strtolower($value) != 'null') && (strlen(trim($value)) > 0)) ? true : false;
}
function xmail ($to_emailaddress,$from_emailaddress, $subject, $content, $file_name, $backup_type)
{
$mail_attached = "";
$boundary = "----=_NextPart_000_01FB_010".md5($to_emailaddress);
$mail_attached.="--".$boundary."\r\n"
."Content-Type: application/octet-stream;\r\n name=\"$file_name\"\r\n"
."Content-Transfer-Encoding: base64\r\n"
."Content-Disposition: attachment; \r\n filename=\"$file_name\"\r\n\r\n"
.chunk_split(base64_encode($content))."\r\n";
$mail_attached .= "--".$boundary."--\r\n";
$add_header ="MIME-Version: 1.0\r\nContent-Type: multipart/mixed;\n boundary=\"$boundary\" \n";
$mail_content="--".$boundary."\r\n"."Content-Type: text/plain; \r\n charset=\"iso-8859-1\"\r\n"."Content-Transfer-Encoding: 7bit\r\n\r\nBACKUP Successful...\r\n\r\nPlease see attached for your zipped Backup file; $backup_type \r\nIf this is the first backup then you should test it restores correctly to a test server.\r\n\r\n phpMySQLAutoBackup is developed by http://www.dwalker.co.uk/ \r\n\r\n Have a good day now you have a backup of your MySQL db :-) \r\n\r\nPlease consider making a donation at: \r\n http://www.dwalker.co.uk/make_a_donation.php \r\n (any amount is gratefully received)\r\n".$mail_attached;
return mail($to_emailaddress, $subject, $mail_content, "From: $from_emailaddress"."\r\n"."Reply-To:$from_emailaddress"."\r\n".$add_header);
}
function write_backup($gzdata, $backup_file_name)
{
$fp = fopen(LOCATION."../backups/".$backup_file_name, "w");
fwrite($fp, $gzdata);
fclose($fp);
//check folder is protected - stop HTTP access
if (!file_exists(".htaccess"))
{
$fp = fopen(LOCATION."../backups/.htaccess", "w");
fwrite($fp, "deny from all");
fclose($fp);
}
}
//include(LOCATION."phpmysqlautobackup_extras.php");
//include(LOCATION."schema_for_export.php");
/**********************************************************
* phpMySQLAutoBackup *
* Author: http://www.DWalker.co.uk *
* Now released under GPL License *
* *
**********************************************************
* Version Date Comment *
* 0.2.0 7th July 2005 GPL release *
* 0.3.0 19th June 2006 Upgrade *
* - added ability to backup separate tables *
* 0.4.0 Dec 2006 removed bugs/improved code *
* 1.4.0 Dec 2007 improved faster version *
* 1.4.3 Sept 2008 added support for CURRENT_TIMESTAMP*
**********************************************************/
$phpMySQLAutoBackup_version="1.4.3";
// ---------------------------------------------------------
// For support and help please try the forum at: http://www.dwalker.co.uk/forum/
$link = mysql_connect($db_server,$mysql_username,$mysql_password);
if ($link) mysql_select_db($db);
if (mysql_error()) exit(mysql_error($link));
//add new phpmysqlautobackup table if not there...
if(mysql_num_rows(mysql_query("SHOW TABLES LIKE 'phpmysqlautobackup' "))==0)
{
$query = "
CREATE TABLE phpmysqlautobackup (
id int(11) NOT NULL,
version varchar(6) default NULL,
time_last_run int(11) NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;";
$result=mysql_query($query);
$query="INSERT INTO phpmysqlautobackup (id, version, time_last_run)
VALUES ('1', '$phpMySQLAutoBackup_version', '0');";
$result=mysql_query($query);
}
//check time last run - to prevent malicious over-load attempts
$query="SELECT * from phpmysqlautobackup WHERE id=1 LIMIT 1 ;";
$result=mysql_query($query);
$time_last_run=mysql_result($result,0,'time_last_run');
if (time() > ($time_last_run+$time_internal)) { // exit if already run within last time_interval
////////////////////////////////////////////////////////////////////////////////////
$query="UPDATE phpmysqlautobackup SET time_last_run = '".time()."' WHERE id=1 LIMIT 1 ;";
$result=mysql_query($query);
if (!isset($table_select))
{
$t_query = mysql_query('show tables');
$i=0;
$table="";
while ($tables = mysql_fetch_array($t_query, MYSQL_ASSOC) )
{
list(,$table) = each($tables);
$table_select[$i]=$table;
$i++;
}
}
$thedomain = $_SERVER['HTTP_HOST'];
if (substr($thedomain,0,4)=="www.") $thedomain=substr($thedomain,4,strlen($thedomain));
$buffer = '# MySQL backup created by phpMySQLAutoBackup' . "\r\n" .
'#' . "\r\n" .
'# http://www.dwalker.co.uk/phpmysqlautobackup/' . "\r\n" .
'#' . "\r\n" .
'# Database: '. $db . "\r\n" .
'# Domain name: ' . $thedomain . "\r\n" .
'# (c)' . date('Y') . ' ' . $thedomain . "\r\n" .
'#' . "\r\n" .
'# Backup Date: ' . strftime("%d %b %Y",time()) . "\r\n\r\n";
$i=0;
foreach ($table_select as $table)
{
$i++;
$export = "\r\n" .'drop table if exists ' . $table . ';' . "\r\n\r\n" .
'create table ' . $table . ' (' . "\r\n";
$table_list = array();
$fields_query = mysql_query("show fields from " . $table);
while ($fields = mysql_fetch_array($fields_query)) {
$table_list[] = $fields['Field'];
$export .= ' ' . $fields['Field'] . ' ' . $fields['Type'];
if (strlen($fields['Default']) > 0) $export.=($fields['Default']=='CURRENT_TIMESTAMP')? ' default '.$fields['Default'] : ' default \''.$fields['Default'].'\'';
if ($fields['Null'] != 'YES') $export .= ' not null';
if (isset($fields['Extra'])) $export .= ' ' . $fields['Extra'];
$export .= ',' . "\r\n";
}
$export = ereg_replace(",\r\n$", '', $export);
// add the keys
$index = array();
$keys_query = mysql_query("show keys from " . $table);
while ($keys = mysql_fetch_array($keys_query)) {
$kname = $keys['Key_name'];
if (!isset($index[$kname])) {
$index[$kname] = array('unique' => !$keys['Non_unique'],
'columns' => array());
}
$index[$kname]['columns'][] = $keys['Column_name'];
}
while (list($kname, $info) = each($index)) {
$export .= ',' . "\r\n";
$columns = implode($info['columns'], ', ');
if ($kname == 'PRIMARY') {
$export .= ' PRIMARY KEY (' . $columns . ')';
} elseif ($info['unique']) {
$export .= ' UNIQUE ' . $kname . ' (' . $columns . ')';
} else {
$export .= ' KEY ' . $kname . ' (' . $columns . ')';
}
}
$export .= "\r\n" . ');' . "\r\n\r\n";
$buffer.=$export;
// dump the data
$query="select * from " . $table ." LIMIT ". $limit_from .", ". $limit_to." ";
$rows_query = mysql_query($query);
while ($rows = mysql_fetch_array($rows_query)) {
$export = 'insert into ' . $table . ' (' . implode(', ', $table_list) . ') values (';
reset($table_list);
while (list(,$i) = each($table_list)) {
if (!isset($rows[$i])) {
$export .= 'NULL, ';
} elseif (has_data($rows[$i])) {
$row = addslashes($rows[$i]);
$row = ereg_replace("\r\n#", "\r\n".'\#', $row);
$export .= '\'' . $row . '\', ';
} else {
$export .= '\'\', ';
}
}
$export = ereg_replace(', $', '', $export) . ');' . "\r\n";
$buffer.= $export;
}
}
mysql_close();
//include(LOCATION."schema_for_export.php");
// zip the backup and email it
$backup_file_name = 'mysql_'.$db.strftime("_%Y-%m-%d_%H-%M-%S.sql",time()).'.gz';
$dump_buffer = gzencode($buffer);
if ($from_emailaddress>"") xmail($to_emailaddress,$from_emailaddress, "phpMySQLAutoBackup: $backup_file_name", $dump_buffer, $backup_file_name, $backup_type);
if ($save_backup_zip_file_to_server) write_backup($dump_buffer, $backup_file_name);
//include(LOCATION."phpmysqlautobackup.php");
}
Last edited by Anonymous on Mon Dec 01, 2008 12:52 pm, edited 1 time in total.
Re: Automatic database backup
Looks nice Nick
I'll try it
I'll try it
Important Code of Conduct
Why don't use CMSMS Docs or search?
Do you read Help?
Admin for Swedish Translations Team
Moderator Swedish Forum
After your problem is solved, push the green button
Why don't use CMSMS Docs or search?
Do you read Help?
Admin for Swedish Translations Team
Moderator Swedish Forum
After your problem is solved, push the green button
Re: Automatic database backup
Thanks Nick/Nullig.
-
- Forum Members
- Posts: 116
- Joined: Mon Jul 28, 2008 4:48 pm
Re: Automatic database backup
If you get an error like this when you try to save the UDT:
Make sure you have a blank line at the bottom of the code.
Nick
Code: Select all
* Invalid code entered.
* Parse error: syntax error, unexpected $end in /home/account/public_html/abcadmin/adduserplugin.php(94) : eval()'d code on line 288
Nick
Re: Automatic database backup
Nick
One small mod required: the line where the time interval is checked calls exit() if the time interval has not been exceeded. This terminates (I think) the logout.php script and you are not logged out of the system. I've changed the line to:
and this appears to work fine.
I haven't checked a restore yet but apart from that this script looks like it will work for me. I like the fact that there is an interval timer so that the number of dumps produced can be controlled.
One small mod required: the line where the time interval is checked calls exit() if the time interval has not been exceeded. This terminates (I think) the logout.php script and you are not logged out of the system. I've changed the line to:
Code: Select all
if (time() > ($time_last_run+$time_internal)) { // exit if already run within last time_interval
... rest of script
} // last line of script
I haven't checked a restore yet but apart from that this script looks like it will work for me. I like the fact that there is an interval timer so that the number of dumps produced can be controlled.
-
- Forum Members
- Posts: 116
- Joined: Mon Jul 28, 2008 4:48 pm
Re: Automatic database backup
Hi digiw12,
You're quite right. I actually have my time interval set to 1, as I only call the UDT from the LogoutPost event. I put it back to 3600 so nobody got caught out if they called the UDT from a public page, e.g. a busy homepage!
I've updated the code using your mod.
Of course, if the program is installed and used as orignally intended by the author, calling 'exit' works fine.
Nick
You're quite right. I actually have my time interval set to 1, as I only call the UDT from the LogoutPost event. I put it back to 3600 so nobody got caught out if they called the UDT from a public page, e.g. a busy homepage!
I've updated the code using your mod.
Of course, if the program is installed and used as orignally intended by the author, calling 'exit' works fine.
Nick
Last edited by Anonymous on Mon Dec 01, 2008 1:00 pm, edited 1 time in total.
Re: Automatic database backup
How about a cron job to reimport/restore the DB every hour?...
Re: Automatic database backup
You could run:
every hour.
Nullig
Code: Select all
mysql -u<username> -p<password> <dbname> < /path/to/your/backups/backup.sql
Nullig
Re: Automatic database backup
Thanks Nullig, but I'm not sure which user and password I'm supposed to use, the ones to get to my phpMYadmin, I use cPanel, or the actual DB user/pass.?...