Backup MySQL Database

I've set up a cron job to schedule a daily backup (using backup_mysql.php?action=backupnow) and it's working but the backup is made in an uncompressed file format.

Does anybody know what parameter should be included in the cron job to invoke gzip compression (backup_mysql.php?action=backupnow 'parameter')?

I would like to have the backups gzipped for the obvious reason of reducing size. Strange thing is that a manual backup using the admin interface does produce a gzip file. Even stranger because at initially I only had the option 'No Compression (Pure SQL)' and still it produced a gzip file.

Looking for a parameter to invoke gzip compression using the cron job I ran across this post by Woodyman.

Woodymon:

The "No Compression (Pure SQL)" radio button in the BU mod dialog is defectively sticky, after the first time I selected I have never been able to unselect.

This seemed to be the same issue I was having and made me realize something was missing in my setup.

Found out that within admin/includes/init_includes/init_gzip.php there are four paths defined for gzip and zip configs. Within backup_mysql.php these paths are checked and, once found correct, options 'Use GZIP' and 'Use ZIP' are added to the admin interface.

In my case changing the paths to:

/bin/gzip
/bin/gunzip
/usr/bin/zip
/usr/bin/unzip

made the options appear. Check with your hosting provider if these settings don't work for you. Hope this helps.

hi...

i'm sure this is a dumb question:

i've just installed this mod for the first time... i'm quite thankful as i've often worried about not having a recent enough backup...

i haven't tried to create a backup yet, but the option is in the admin window and such, so i'm confident.

I would just like to know where it will save my backup

it says:
/home/content/(some removed)html/admin/backups/

but where is the 'home' folder...

like i said, i'm sure it's a dumb question...thanks in advance.

d'oh.

i figured it out...

thanks so much Dr. Byte!

Hi all,

I have just made a clean install of zencart 1.3.7 in a new MySQL (Client API version 5.0.18) database. After installing the "Backup MySQL" module (BACKUP_MYSQL Admin Tool v1.3) but I get the following error when trying to make a back-up using gzip:

Warning Result code: 2
Warning 0 => /usr/bin/mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
Warning 1 => /usr/bin/mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file
Warning 2 => /usr/bin/mysqldump: Got error: 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client when trying to connect
Error Failure: The database has NOT been saved.

Is there anything I can do to make it work?

Kind regards and many thanks for your help,

Goshawk.

two possible causes:

1. your database server isn't localhost, and thus it cannot connect to remote server
2. your webserver's command-line configuration for mysql client isn't fully complete, hence the charset warnings etc.

These are common symptoms of clustered hosting setups where the server administrator hasn't considered the need for command-line tools to be available to access the database from the webserver.

Hopefully your host can handle the error and make the required corrections. You'll have to report the issue to them.

Thank you DrByte for your quick response. I will take it up with my host. :)

HI, I am using my local test server - WinXP with WAMP and MYSQL 5

When I try and run the backup I get from the Admin:

Result code: 1
0 => 'C:\wamp\mysql\bin/mysqldump.exe" "--host' is not recognized as an internal or external command,
1 => operable program or batch file.
Failure: The database has NOT been saved.

I edited the backup_mysql.php

define('LOCAL_EXE_MYSQL', 'c:\wamp\mysql\bin\mysql.exe');
define('LOCAL_EXE_MYSQLDUMP', 'c:\wamp\mysql\bin\mysqldump.exe');

If the problem is that WinXP is not letting the guest user run CMD.exe. Can you give me the (mysqldump.exe ...) command string so I can run it as a local scheduled task?

Then when I get ZenCart up and running on a Linux server I can set it back to default.

THANKS
JOhn ><>

@LibertyFlag:

I'm unable to connect to your web site. Meaning, I cannot see the screenshots you have posted. Perhaps posting them as an attachment of your next message should facilitate others to visualize what might of had happened (and if this error has not been covered yet). :wink2:

Anyone ever have the issue of the backup that goes to 101% and finishes immediately without actually backing up anything?

deadeye:

Anyone ever have the issue of the backup that goes to 101% and finishes immediately without actually backing up anything?

I guess it appears to be an issue when GZip is turned on. I didn't have the problem prior, but intermittingly I was getting it the last few months. Not sure why it'd work sometimes and not others if host related. ZC 1.3.7; backup mySQL plugin 1.3

Sorry,
a question about Database backup.:blink:
Which is the advantage of Installing this addon compared to do the backup from phpAdmin interface?
If I can use phpAdmin by now, is there a best way or something to pay attention to backup zen database in order to eventually recover it ?
thanks

This contribution allows you to do backups of much larger databases.
Using phpMyAdmin may encounter a timeout on larger databases which take a lengthy time to prepare backups with. That's the primary benefit.

Please take at look at this list of errors arising when I try to use the Backup MySQL database module

0 => /usr/bin/mysqldump: unrecognized option --result-file=/xxx/xxx/xxx/xx/www.xxxx/htdocs/xxx/xxx/admin/backups/db_DB285429-20070614200830.sql' 1 => /usr/bin/mysqldump Ver 6.6 Distrib 3.22.29, for sun-solaris2.7 (sparc) 2 => By Igor Romanenko, Monty, Jani & Sinisa. This software is in public Domain 3 => This software comes with ABSOLUTELY NO WARRANTY 4 => 5 => Dumping definition and data mysql database or table 6 => Usage: /usr/bin/mysqldump [OPTIONS] database [tables] 7 => 8 => -a, --all Include all MySQL specific create options 9 => -#, --debug=... Output debug log. Often this is 'd:t:o,filename
10 => -?, --help Display this help message and exit.
11 => -c, --complete-insert Use complete insert statements.
12 => -C, --compress Use compression in server/client protocol
13 => -e, --extended-insert Allows utilization of the new, much faster
14 => INSERT syntax
15 => --add-drop-table Add a 'drop table' before each create
16 => --add-locks Add locks around insert statements
17 => --allow-keywords Allow creation of column names that are keywords
18 => --delayed-insert Insert rows with INSERT DELAYED
19 => -F --flush-logs Flush logs file in server before starting dump
20 => -f, --force Continue even if we get an sql-error.
21 => -h, --host=... Connect to host.
22 => -l, --lock-tables Lock all tables for read.
23 => -t, --no-create-info Don't write table creation info.
24 => -d, --no-data No row information.
25 => -O, --set-variable var=option
26 => give a variable a value. --help lists variables
27 => --opt Same as --add-drop-table --add-locks --all
28 => --extended-insert --quick --lock-tables
29 => -p, --password[=...] Password to use when connecting to server.
30 => If password is not given it's solicited on the tty.
31 => -P, --port=... Port number to use for connection.
32 => -q, --quick Don't buffer query, dump directly to stdout.
33 => -Q, --quote-names Quote table and column names with `
34 => -S, --socket=... Socket file to use for connection.
35 => -T, --tab=... Creates tab separated textfile for each table to
36 => given path. (creates .sql and .txt files).
37 => NOTE: This only works if mysqldump is run on
38 => the same machine as the mysqld daemon.
39 => -u, --user=# User for login if not current user.
40 => -v, --verbose Print info about the various stages.
41 => -V, --version Output version information and exit.
42 => -w, --where= dump only selected records; QUOTES mandatory!
43 => EXAMPLES: "--where=user='jimf'" "-wuserid>1" "-wuserid<1"
44 => Use -T (--tab=...) with --fields-...
45 => --fields-terminated-by=...
46 => Fields in the textfile are terminated by ...
47 => --fields-enclosed-by=...
48 => Fields in the importfile are enclosed by ...
49 => --fields-optionally-enclosed-by=...
50 => Fields in the i.file are opt. enclosed by ...
51 => --fields-escaped-by=...
52 => Fields in the i.file are escaped by ...
53 => --lines-terminated-by=...
54 => Lines in the i.file are terminated by ...
55 =>
56 => Possible variables for option --set-variable (-O) are:
57 => max_allowed_packet current value: 25165824
58 => net_buffer_length current value: 1047551
Failure: The database has NOT been saved.I don't suppose anyone out there can shed some light on what might be causing this problem - it's troubling me greatly, so any guidance would be much appreciated?

BTW: It would seem that backups via PHPMyAdmin work OK. The Unix server is running MySQL 5.0.21

Your server's mysqldump binary doesn't support some of the required parameters.
You'll have to use a different method to do your database backups, perhaps via phpMyAdmin instead.

DrByte:

Your server's mysqldump binary doesn't support some of the required parameters.
You'll have to use a different method to do your database backups, perhaps via phpMyAdmin instead.
DrByte

Thanks for that info.

The ability to backup the databse direct from the Admin back end is great, and I would prefer to make backups from there.

Is there anything specific that I should ask my hosting provider to change in order to get it to work?

mysqldump Ver 6.6 Distrib 3.22.29, for sun-solaris2.7 (sparc)You might ask them to upgrade your MySQL version.
v3.22 is very old.
I think the backup contribution was written based on v3.23 minimum, and is compatible thru to v5.x

DrByte:

You might ask them to upgrade your MySQL version.
v3.22 is very old.
I think the backup contribution was written based on v3.23 minimum, and is compatible thru to v5.x
Hi

I am puzzled by this: the database I created is defined by the host as being version 5.0.21 - I was given an option as to whether I wanted version 4 or version 5 of MySQL database, and chose version 5.

Is the reference> /usr/bin/mysqldump Ver 6.6 Distrib 3.22.29, for sun-solaris2.7 (sparc) to the version of MySQL dump, rather than for the whole database?

to the version of MySQL dump, rather than for the whole database?

v3.22 for mySQL is said as is. Indeed, this is very old and would be recommended to send a support ticket to your web hosting service requesting an upgrade to the latest version released from mySQL manufacturer.

TheOracle:

v3.22 for mySQL is said as is. Indeed, this is very old and would be recommended to send a support ticket to your web hosting service requesting an upgrade to the latest version released from mySQL manufacturer.
I am sorry to appear to be really dense, but the host is adamant that my zen cart databse files are in a MySQL database and the version is 5.0.21.

Without wishing to be a pain - since they are adamant that this is the case - is the Distrib value of 3.22.29 referring to the mysqldump 'software'?

I have asked fo an 'upgrade' but I am unsure as to what I am sking to be upgraded!

bTW: It is worth noting that the Zen Cart server information enquiry output states that the mysQL version is 5.0.21!!

Perhaps this is about some kind of partial upgrade here since I don't see how a complete upgrade would contain two different version from one command file to another. All versions should be equally constant under each mySQL releases normally.