Jump to content



Photo
* * * * * 1 votes

Contribution: Auto Backup Database


  • Please log in to reply
177 replies to this topic

#61   Dan Cole

Dan Cole
  • Community Sponsor
  • 381 posts
  • Real Name:Dan Cole
  • Gender:Male
  • Location:Ontario, Canada

Posted 14 August 2009 - 02:17

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

#62   thinkingforward

thinkingforward
  • Members
  • 101 posts
  • Real Name:Chris

Posted 20 September 2009 - 17:01

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

#63   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 20 September 2009 - 23:13

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.

#64   thinkingforward

thinkingforward
  • Members
  • 101 posts
  • Real Name:Chris

Posted 21 September 2009 - 19:09

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



#65   flatcat

flatcat
  • Members
  • 28 posts
  • Real Name:Tony

Posted 22 September 2009 - 07:49

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?

#66   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 22 September 2009 - 10:12


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.

#67   flatcat

flatcat
  • Members
  • 28 posts
  • Real Name:Tony

Posted 22 September 2009 - 14:19

User tracking has been on there for years, way before I added Autobackup.
It happens when the autobackup starts up

#68   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 22 September 2009 - 22:17

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.

#69   flatcat

flatcat
  • Members
  • 28 posts
  • Real Name:Tony

Posted 23 September 2009 - 08:48

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
/smile.gif' class='bbc_emoticon' alt=':)' />

#70   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 23 September 2009 - 10:39


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.

#71   flatcat

flatcat
  • Members
  • 28 posts
  • Real Name:Tony

Posted 24 September 2009 - 07:27

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.

#72   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 24 September 2009 - 18:22

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!! /wink.gif' class='bbc_emoticon' alt=';)' />

Edited by spooks, 24 September 2009 - 18:34.

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.

#73   LED-Store

LED-Store
  • Members
  • 33 posts
  • Real Name:Cosmo Kramer
  • Location:Switzerland

Posted 25 September 2009 - 11:00

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... /wink.gif' class='bbc_emoticon' alt=';)' />

#74   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 25 September 2009 - 17:24


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.

#75   newtest

newtest
  • Members
  • 224 posts
  • Real Name:no its not

Posted 21 October 2009 - 19:03

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

#76   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 21 October 2009 - 19:27

No backup on lgin



how r u logging in to admin, with htaccess by any chance?
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.

#77   newtest

newtest
  • Members
  • 224 posts
  • Real Name:no its not

Posted 22 October 2009 - 00:57

yes /blush.gif' class='bbc_emoticon' alt=':blush:' />

Got it sorted, thanks for the contribution and the time to answer my silly post /smile.gif' class='bbc_emoticon' alt=':)' />

#78   newtest

newtest
  • Members
  • 224 posts
  • Real Name:no its not

Posted 25 October 2009 - 18:45

Hi Sam,

I'm getting a backup on each login and every 20 minutes while logged in.
I'm not getting a cron auto archive backup each night/every 24 hours as set.
I do have cron set in cpanel. My admin settings below:

Auto Backup Interval 20
Backup Save Interval 24
Backup Zip gzip

My backup.log:

Status: 404 Not Found
X-Powered-By: PHP/5.2.9
Content-type: text/html
No input file specified.

So do I need to ask host if cron command is differant? My cron below:
php $HOME/adminfoldername/includes/auto_backup_db.php > $HOME/public_html/adminfoldername/backups/backup.log

backups presently saved:
Download backup.log 10/23/2009 04:00:01 100 bytes
Download db_myusername_domainname-20091019220042.sql 10/19/2009 22:00:43 4,355,749 bytes Info
Download db_myusername_domainname_200910220908.sql.gz 10/22/2009 09:08:17 483,823 bytes Info
Download db_myusername_domainname_200910220931.sql.gz 10/22/2009 09:31:19 483,527 bytes Info
Download db_myusername_domainname_200910230836.sql.gz 10/23/2009 08:36:05 484,664 bytes Info

Also I can click on any backup in admin and download it, but if click on backup.log to D/L I get this error:

Error: Download link not acceptable.

Thanks

#79   spooks

spooks
  • Members
  • 7,017 posts
  • Real Name:Sam
  • Gender:Male
  • Location:UK

Posted 25 October 2009 - 19:05

not getting a cron auto archive backup



Unfortunatly the format for cron job commands does vary by server, you need to look through the docs for yours, or ask your host.

Some servers provide a test function to see if your new command works, but only some....
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.

#80   newtest

newtest
  • Members
  • 224 posts
  • Real Name:no its not

Posted 27 October 2009 - 04:21

Hi Sam,

I got the crom command from my host:
php /home/myusername/public_html/myadminfoldername/includes/auto_backup_db.php > /home/myusername/public_html/myadminfoldername/backups/backup.log

When it runs I get this in backup log
X-Powered-By: PHP/5.2.9
Content-type: text/html

<br />
<b>Warning</b>: include(includes/functions/refund_functions.php) [<a href='function.include'>function.include</a>]: failed to open stream: No such file or directory in <b>/home/myusername/public_html/myadminfoldername/includes/functions/general.php</b> on line <b>1478</b><br />
<br />
<b>Warning</b>: include(includes/functions/refund_functions.php) [<a href='function.include'>function.include</a>]: failed to open stream: No such file or directory in <b>/home/myusername/public_html/myadminfoldername/includes/functions/general.php</b> on line <b>1478</b><br />
<br />
<b>Warning</b>: include() [<a href='function.include'>function.include</a>]: Failed opening 'includes/functions/refund_functions.php' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in <b>/home/myusername/public_html/myadminfoldername/includes/functions/general.php</b> on line <b>1478</b><br />
<br />
<b>Warning</b>: require(includes/languages/add_ccgvdc_english.php) [<a href='function.require'>function.require</a>]: failed to open stream: No such file or directory in <b>/home/myusername/public_html/myadminfoldername/includes/languages/english.php</b> on line <b>335</b><br />
<br />
<b>Warning</b>: require(includes/languages/add_ccgvdc_english.php) [<a href='function.require'>function.require</a>]: failed to open stream: No such file or directory in <b>/home/myusername/public_html/myadminfoldername/includes/languages/english.php</b> on line <b>335</b><br />
<br />
<b>Fatal error</b>: require() [<a href='function.require'>function.require</a>]: Failed opening required 'includes/languages/add_ccgvdc_english.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in <b>/home/myusername/public_html/myadminfoldername/includes/languages/english.php</b> on line <b>335</b><br />

Also I can click on any backup in admin and download it, but if click on backup.log to D/L I get this error:

Error: Download link not acceptable.

I have tested the CCGV and it appears to be working fine>

Ideas please?