Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Contribution: Auto Backup Database


spooks

Recommended Posts

Hi Sam,

 

Thanks so much for your auto backup contrib. It's really useful! I'm wondering if you can tell me how to modify it so that it only saves the last 28 days of backups and deletes anything older than 28 days?

 

Thanks so much,

Nicole

 

 

POST DONT PM!!!

 

I did post -twice. Thought you might be kind enough to respond.

Link to comment
Share on other sites

This forum provides free support, so you need to be patient & wait for those providing the free support to have time & inclination to reply, pestering for a responce is not a good way to get it.

 

So if I set auto backup interval to 30, it will create a new backup twice an hour?
If I set backup save interval to 24, it will save just one of those backups every day and delete the others?

 

Means backup twice an hour & archive one of those once a day, as I thought has been said here before.

 

Can anyone tell me know to modify this contrib (it's a great contrib!) so that it only saves the last 28 days of backups and deletes anything older than 28 days?

 

Its possible, but you would have to take care as you never know how far you have to go back.

My approach is to use ftp, transfer archives to pc, then delete whole block at once, quicker that way.

 

If I get the time & the inclination I might write some code to automate.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

Deleting old backups.

 

I have considered the option of automatically deleting any backups older than a set amount & feel that adding such would be detrimental to the package.

 

The backup routine is run on every page load in admin, so must be kept consice to avoid adding any unneccessary load or delays to admin.

 

If such an option is desired, it would be better to add it as a function to the likes of Database Backup Manager.

 

:)

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

  • 3 months later...
Deleting old backups.

 

I have considered the option of automatically deleting any backups older than a set amount & feel that adding such would be detrimental to the package.

 

The backup routine is run on every page load in admin, so must be kept consice to avoid adding any unneccessary load or delays to admin.

 

If such an option is desired, it would be better to add it as a function to the likes of Database Backup Manager.

 

:)

 

Shame! One of my customers who really doesn't want to be going "under the bonnet" of the shop (as they put it), keeps running out of hard drive space on their hosting, and it's always the auto backup thats caused it.

Once a shop is running well, you really only need to keep the database for product/customer information, can't really think of anything else that's likely to change much after the shops development phase...

It would be really useful to have a "Delete Backups after XX days", any chance you could be persuaded to have a re-think about this?

 

Cheers

 

Mat

Link to comment
Share on other sites

  • 2 months later...

you've made errors, do the install again more carefully, then double check everything u done.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

  • 2 months later...

Hi there.

 

Another great contribution by Sam, thanks a lot! :)

 

I think it would be nice to have the auto-delete option after X ammount of days defined in the admin.

 

Also it would be nice to include an email function to email the back up file to the admin. I'm thinking that if there's a problem with the server or the clients account, having the back ups stored in the server won't help much. And to tell people they have to go to their admin and download the back up file manually, well, we all know it just won't happen as often as needed. :rolleyes:

 

So is there any chance that these could be added to this great add on??

Edited by Patty

Patty

Link to comment
Share on other sites

Sorry, it does delete the old back ups and maintains only 2. That's good enough.

I was reading the forum before testing it all the way.

Great job! :)

 

But email function would be good to have. I hope it can be added.

Patty

Link to comment
Share on other sites

  • 1 month later...

Thanks for the excellent contribution Sam...it does what it says and the instuctions were easy to follow. It seems to be bug free and works like a champ.

 

Well done and thanks again...

 

Dan

Link to comment
Share on other sites

  • 1 month later...

since installing the latest version I am getting this with my site monitor:

 

Cannot redeclare microtime_float() (previously declared in /home/nrylgbf/public_html/rbreplicas.com/admanage/includes/auto_backup_db.php:45) in /home/nrylgbf/public_html/rbreplicas.com/admanage/includes/functions/sitemonitor_functions.php on line 332

 

Any ideas?

 

Thanks

Link to comment
Share on other sites

Cannot redeclare microtime_float() (previously declared in /home/nrylgbf/public_html/rbreplicas.com/admanage/includes/auto_backup_db.php:45) in /home/nrylgbf/public_html/rbreplicas.com/admanage/includes/functions/sitemonitor_functions.php on line 332

 

 

It simply means that the sitemonitor contrib creates a function called microtime_float() which this also does, you need to change all instances of that name to something else, in one or the other.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

Thank you, your help is very much appreciated, as always:)

 

Auto Backup

 

This contribution automatically backs up your database at regular intervals whilst you are logged into admin.

It will create an archive backup at a longer intervals, you set the intervals in admin.

It can also zips the backups, if required, also set in admin.

 

The zip function uses the pear library to avoid problems with disabled system etc functions.

 

Contrib is at http://addons.oscommerce.com/info/2314

Link to comment
Share on other sites

After using this successfully for a number of months I am suddenly getting this error:

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/*********/admin/includes/functions/database.php on line 45

2008 - MySQL client ran out of memory

 

select customer_id,click_id,full_name,session_id,ip_address,time_entry,time_last_click,

last_page_url,page_desc from user_tracking

 

I have run a check on Mysql database via cpanel and all looks OK. (Have also purged all records past last 72 hours in user_tracking)

 

Any ideas?

Link to comment
Share on other sites

 

What makes you think this is the cause of your error?, when exactly does it occur.

 

It looks to me this has smthg to do with user tracking u have added!

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

well this is smthg that has never come up b4, & I have tested with a very large dbase, what is your memory setting, have you tried to increase?

 

install Database backup manager http://addons.oscommerce.com/info/5769 see if u get a similar issue with that.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

That addon was also installed before the autobackup (or possibly around the same time).

 

It seems to run up until it has 7.9MB of sql and then it conks out with the error described. (It used to end up with about 60MB gzipped). It may easily be something altered on the server. Any suggestion where and what I can change server-wise? Through control panel for instance. I don't really think it is caused by your contribution, but yours is the one it shows up on.

 

Any help appreciated

:)

Link to comment
Share on other sites

 

that implies that your memory limit is 8M, which is low, to increase

 

add:

 

memory_limit = 32M to your php.ini file

 

or add:

 

ini_set('memory_limit', '32M');

 

to application_top.php

 

or add:

 

php_value memory_limit 32M

 

to your .htaccess file in the root

 

but you may have to speak to your host b4 any are effective

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

It seems years of collecting data with user tracking bloated the database considerably. On closer investigation it looks like the purge did not work, so records were emptied from the database via phpmyadmin.

 

The autobackup now works on the much smaller database.

Many thanks for your help and suggestions.

Link to comment
Share on other sites

Uploaded Auto Backup Install V3.0

 

Features in this version:

 

Automatically backs up your database at regular intervals whilst you are logged into admin.

It will create an archive backup at a longer intervals, you set the intervals in admin.

It can also zip or gzip the backups, if required, also set in admin.

Backups are compatible with Database Backup Manager so selective restores can be made.

A cron job can also be set up to create timed daily backups, regardless of whether anyone logs in or not.

Cron job backups occur in exactly the same way as admin backups, using the same admin settings.

 

Enjoy and keep your data safe!! ;)

Edited by spooks

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

When I make a backup with this tool, the size of the file is getting over 35mb. I installed some time ago the following Contrib: Database backup manager, it saves a lot of space, with this the backup is only 12mb!

 

- Instead of trying to imitate the "create table" query that mysql does, the create table query itself is used instead.

 

- Instead of using:

 

insert into 'table_name' (column1, column2, column3) values (value1, value2, value3);

 

for each row in a table an extended insert is used where (maximum) 20 rows are added to the "insert into" statement:

 

insert into 'table_name' (column1, column2, column3) values (value_a1, value_a2, value_a3),

(value_b1, value_b2, value_b3),

(value_c1, value_c2, value_c3),

.... etcetera

(value_t1, value_t2, value_t3);

 

This saves on disk writes when making the backup, disk space (file size about 1/3 less) and saves a lot on mysql insert queries when restoring a backup (20 rows inserted instead of 1 per query).

Example, instead of:

drop table if exists address_book;
create table address_book (
 address_book_id int(11) not null auto_increment,
 customers_id int(11) default '0' not null ,
 entry_usertype char(1) not null ,
 entry_gender char(1) not null ,
 entry_company varchar(50) ,
 entry_firstname varchar(32) not null ,
 entry_lastname varchar(32) not null ,
 entry_street_address varchar(64) not null ,
 entry_suburb varchar(32) ,
 entry_postcode varchar(10) not null ,
 entry_city varchar(32) not null ,
 entry_state varchar(32) ,
 entry_country_id int(11) default '0' not null ,
 entry_zone_id int(11) default '0' not null ,
 PRIMARY KEY (address_book_id),
 KEY idx_address_book_customers_id (customers_id)
);

insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('1', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('2', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('3', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('4', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('5', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('6', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('7', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('8', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('9', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
insert into address_book (address_book_id, customers_id, entry_usertype, entry_gender, entry_company, entry_firstname, entry_lastname, entry_street_address, entry_suburb, entry_postcode, entry_city, entry_state, entry_country_id, entry_zone_id) values ('10', 'xxx', '', 'm', '', 'xxx', 'xxx', 'xxx', NULL, 'xxx', 'xxx', NULL, 'xxx', '0');
etc.

The same code is now:

drop table if exists `address_book`;
CREATE TABLE `address_book` (
 `address_book_id` int(11) NOT NULL auto_increment,
 `customers_id` int(11) NOT NULL default '0',
 `entry_usertype` char(1) NOT NULL default '',
 `entry_gender` char(1) NOT NULL default '',
 `entry_company` varchar(50) default NULL,
 `entry_firstname` varchar(32) NOT NULL default '',
 `entry_lastname` varchar(32) NOT NULL default '',
 `entry_street_address` varchar(64) NOT NULL default '',
 `entry_suburb` varchar(32) default NULL,
 `entry_postcode` varchar(10) NOT NULL default '',
 `entry_city` varchar(32) NOT NULL default '',
 `entry_state` varchar(32) default NULL,
 `entry_country_id` int(11) NOT NULL default '0',
 `entry_zone_id` int(11) NOT NULL default '0',
 PRIMARY KEY  (`address_book_id`),
 KEY `idx_address_book_customers_id` (`customers_id`)
);

insert into `address_book` (`address_book_id`, `customers_id`, `entry_usertype`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) values (1,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(2,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(3,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(4,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(5,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(6,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(7,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(8,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(9,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
(10,xxx,'','m','','xxx','xxx','xxx',NULL,'xxx','xxx',NULL,xxx,0),
etc.

 

Maybe this also can be done with this script, I tried to implement, but I receive only error messages... ;)

Link to comment
Share on other sites

 

Are u sure your comparing like with like? ie u are backing up all tables in both cases & using same compression ?

 

My tests show only a 8% difference in size.

 

I was aware that different methods are used with that & I will look at this when time allows, but care must be taken to ensure a reliable backup, this has to work in the background, Database backup manager does not.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

  • 4 weeks later...

I'm running:

osCommerce 2.2-MS2

Server OS: Linux 2.6.18-128.7.1.el5

MySQL 5.0.81-community

PHP Version 5.2.9

 

Post #63 mentioned :

It simply means that the sitemonitor contrib creates a function called microtime_float() which this also does, you need to change all instances of that name to something else, in one or the other.

So before installing I changed microtime_float to microtime_float_autobackup as I have Site Monitor installed.

 

I have 4 backups stored, it didn't create a new backup.

I have logged out and back in and nothing, I set the timer to 2 minutes instead of 20 and still no backups.

Getting no errors.

 

Ideas as to what I did wrong?

 

Thanks

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...