automysql backup

English Applications
1

Hey Guys,

i’ve installed automysqlbackup but i’m getting nothing but errors.

when i run the schript from commandline i get an email saying it has made the backup to the harddrive and rotated the backups. but it exceeds the maximum attachement size. (in automysqlbackup i’ve set it to 16000 (i quess something like 20Mb) and in postfix it is set to unlimited.).

when i let cron.daily run the script i get an daily - FAILLURE.
in my email.
SCRIPT: automysqlbackup.2.5.sh exited with RETURNCODE = 1.

both cases the backup is indeed made to the harddrive (total size after about 1 month is 2.2Mb) rotation is working fine… it’s just that no attachements are mailed. i’ve tested different email adresses.

kind regard
Arnold

2

Hi

When it returns status=1 that means there were errors. As I can’t find my crystal ball right away it would be helpful if you could let us know what those errors are. Comment the rm commands at the end of the script where it removes the log files and have a look at what they say. They will be in your backup directory.

What is your setting of MAILCONTENT ? ‘quiet’ would be a good choice. Setting this to ‘files’ will mail all the backups and that can easily exceed the maximum attachment size.

3

Unless you have very specific requirements and assuming you have logging set up in my.cnf, mysqldump -u root -p -F - -master-data=2 - -delete-master-logs -A > backup.sql
will back everything up to a single file which contains the script which will restore everything including passwords.

Even if you have specific requirements, you may find mysqldump provides all you need.

4

A copy of the databases will also be sent to the ‘backups’ folder you created. Since the backups are both named and dated, none of them will be overwritten each night.

Get your payday loan online here! Fast canadian payday loans and payday loan.

5

Thanks for the reply.

if i had a log i would share it, but for some reason there are no log files created.
the backup directory has all appropriate rights so as i said the only error i’ve got is the exceeds maximum attachment.

and yes i’ve put the mail option to ‘files’ because i want to store the backup files on a different pc/location.

i have set it to store every database in a separate directory.

sorry i don’t have more info.
iĺl try to submit the configuration shortly.

kind regards
Arnold

6

List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3”

DBNAMES=“all”

Backup directory location e.g /backups

BACKUPDIR="/home/arnoldnijboer/backups"

Mail setup

What would you like to be mailed to you?

- log : send only log file

- files : send log file and sql files as attachments (see docs)

- stdout : will simply output the log to the screen if run manually.

- quiet : Only send logs if an error occurs to the MAILADDR.

MAILCONTENT=“files”

Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])

MAXATTSIZE=“16000”

Email Address to send mail to? (user@domain.com)

MAILADDR=“arnold.nijboer@wanadoo.nl”

============================================================

=== ADVANCED OPTIONS ( Read the doc’s below for details )===

#=============================================================

List of DBBNAMES for Monthly Backups.

MDBNAMES=“mysql $DBNAMES”

List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)

DBEXCLUDE=""

Include CREATE DATABASE in backup?

CREATE_DATABASE=yes

Separate backup directory and file for each DB? (yes or no)

SEPDIR=yes

Which day do you want weekly backups? (1 to 7 where 1 is Monday)

DOWEEKLY=1

Choose Compression type. (gzip or bzip2)

COMP=bzip2

Compress communications between backup server and MySQL server?

COMMCOMP=no

Additionally keep a copy of the most recent backup in a seperate directory.

LATEST=no

The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)

MAX_ALLOWED_PACKET=

For connections to localhost. Sometimes the Unix socket file must be specified.

SOCKET=

Command to run before backups (uncomment to use)

#PREBACKUP="/etc/mysql-backup-pre"

Command run after backups (uncomment to use)

#POSTBACKUP="/etc/mysql-backup-post"

7

It sounds as if you need to study /etc/my.cnf in more detail before you try anything with mysql.

Log files are created by removing the # from the start of
#log-bin=mysql-bin

You then have a continuous record of transactions between backups.

Even if you back up mysql daily without logs enabled, you could lose the entire day’s transactions.

There is a detailed discussion of mysqldump under mysql programs in info mysql which would also repay study.

8

i’m sorry to sound a little junior…
but the log’s for automysqlbackup aren’t created by mysql right.
so why should i change the my.cnf

and the file backup are created just right.
it’s the email that isn’t being send. ore even more specific
the mails are send. but no attachment

bellow is the email i receive (without the .sql.bz2 attachments)

======================================================================
Backup of Database Server - SkynetServer

Backup Start Time zo aug 30 11:23:15 CEST 2009

Daily Backup of Database ( information_schema )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/information_schema/information_schema_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/information_schema/information_schema_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/information_schema/information_schema_2009-08-30_11h23m.zondag.sql: 2.498:1, 3.203 bits/byte, 59.97% saved, 1224 in, 490 out.

Daily Backup of Database ( Arnoldnijboer )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/Arnoldnijboer/Arnoldnijboer_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/Arnoldnijboer/Arnoldnijboer_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/Arnoldnijboer/Arnoldnijboer_2009-08-30_11h23m.zondag.sql: 2.566:1, 3.118 bits/byte, 61.02% saved, 8885 in, 3463 out.

Daily Backup of Database ( Forum )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/Forum/Forum_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/Forum/Forum_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/Forum/Forum_2009-08-30_11h23m.zondag.sql: 5.681:1, 1.408 bits/byte, 82.40% saved, 1924844 in, 338827 out.

Daily Backup of Database ( Hippie )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/Hippie/Hippie_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/Hippie/Hippie_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/Hippie/Hippie_2009-08-30_11h23m.zondag.sql: 5.031:1, 1.590 bits/byte, 80.12% saved, 7280139 in, 1447093 out.

Daily Backup of Database ( Manege )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/Manege/Manege_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/Manege/Manege_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/Manege/Manege_2009-08-30_11h23m.zondag.sql: 3.284:1, 2.436 bits/byte, 69.55% saved, 12459 in, 3794 out.

Daily Backup of Database ( Ponykamp )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/Ponykamp/Ponykamp_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/Ponykamp/Ponykamp_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/Ponykamp/Ponykamp_2009-08-30_11h23m.zondag.sql: 4.606:1, 1.737 bits/byte, 78.29% saved, 55996 in, 12158 out.

Daily Backup of Database ( autorodeo )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/autorodeo/autorodeo_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/autorodeo/autorodeo_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/autorodeo/autorodeo_2009-08-30_11h23m.zondag.sql: 3.033:1, 2.637 bits/byte, 67.03% saved, 58428 in, 19261 out.

Daily Backup of Database ( id0355_id0355 )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/id0355_id0355/id0355_id0355_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/id0355_id0355/id0355_id0355_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/id0355_id0355/id0355_id0355_2009-08-30_11h23m.zondag.sql: 1.396:1, 5.730 bits/byte, 28.38% saved, 72662 in, 52044 out.

Daily Backup of Database ( mysql )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/mysql/mysql_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/mysql/mysql_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/mysql/mysql_2009-08-30_11h23m.zondag.sql: 5.147:1, 1.554 bits/byte, 80.57% saved, 396573 in, 77042 out.

Daily Backup of Database ( nlzoektwerk )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/nlzoektwerk/nlzoektwerk_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/nlzoektwerk/nlzoektwerk_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/nlzoektwerk/nlzoektwerk_2009-08-30_11h23m.zondag.sql: 3.482:1, 2.298 bits/byte, 71.28% saved, 8579 in, 2464 out.

Daily Backup of Database ( webmail )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/webmail/webmail_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/webmail/webmail_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/webmail/webmail_2009-08-30_11h23m.zondag.sql: 2.409:1, 3.321 bits/byte, 58.49% saved, 1361 in, 565 out.

Daily Backup of Database ( wielenshoeve )
Rotating last weeks Backup…
‘/home/arnoldnijboer/backups/daily/wielenshoeve/wielenshoeve_2009-08-30_11h13m.zondag.sql.bz2’ verwijderd

Compression information for /home/arnoldnijboer/backups/daily/wielenshoeve/wielenshoeve_2009-08-30_11h23m.zondag.sql.bz2
/home/arnoldnijboer/backups/daily/wielenshoeve/wielenshoeve_2009-08-30_11h23m.zondag.sql: 19.966:1, 0.401 bits/byte, 94.99% saved, 271132 in, 13580 out.

Backup End zo aug 30 11:24:05 CEST 2009

Total disk space used for backup storage…
Size - Location
2,2M /home/arnoldnijboer/backups

9

I see your problem. Sourceforge reports that there is no support for this software.

However, as far as I can see from the limited information available, it is only a set of scripts for mysqldump.

So you probably need to examine the scripts it is trying to run to determine the problem as the output it is giving is not going to help you.

What puzzles me is why you are backing up individual databases rather than letting mysqldump put them in a single backup file.

Given the size of your databases, unless you have a huge volume of traffic, that would appear to be overkill.

Moreover, if you haven’t already enabled logging in mysql, automysqlbackup isn’t going to be as helpful as you might think.

It might be more helpful if you told us a bit more about why you need a daily backup regime rather than relying on bin-logs and a less frequent backup regime.

As no-one else has responded to your query, I suspect automysqlbackup isn’t widely used.

10

hi John,

thanks for the reply,

i’m doing the individual backup because my server is like a test server.
websites come and go. so if i do a individual backup i can copy the backup file with the website and pass it to “the client”.
so he/she can install it on there permanent server!
or in the worst case if i do need to reinstall the webserver. i just can install the databases i need.

i used to do a backup with phpmybackup but that needs a php code inside a webpage to get activated. and as i mentioned i do not have websites that stay very long on this server. so i was looking for a cron job activated backup script.
and automysqlbackup was one of the first results @ google.

if you have a beter script/method please tell me, i don’t want to spent to much time on some script that doesn’t get suport anymore :S

kind regards.
Arnold

11

You seem to need two separate processes: one backs up the databases routinely and the other backs up individual databases.

The question is how many changes there are on the database during testing or if those changes are relevant. If they are not, then my comment about log files is less relevant.

However, if the clients need the database with all the changes made during testing, using a cron generated file will lose the most recent changes.

What about writing a separate mysqldump script for each database with the location of the backup in the same folder as the other files at the time you set things up? Then, when the client requests it, run the backup and send them the whole folder? This script would not need logfiles running as it would simply be a snapshot of the database at the time.

If alongside that you turn on logging and create a separate script to back everything up, you will have protection for the databases as a whole including any changes that clients have made during testing. But you would only ever need to restore this backup if things went wrong.

12

Hi John,

that should be something to consider.
but what about emailing.
and furthermore how can i write such a script?
i have no experience writing binary scripts.

that’s why i was looking for a out-of-the-box application such as automysqlbackup and phpmysqlbackup.

kind regards,
Arnold

13

Hi arnoldnijboer

Even if it says somewhere that automysqlbackup.sh is not supported that’s no problem. Shell scripts are easy ho debug and hack.

Looking closely at the code it seems to me that line 641 of the script is buggy. It looks like this:

ATTSIZE=`du -c $BACKUPFILES | grep ":digit:]:space:]]total$" |sed s/\s*total//`

This will fail if your locale is not set to english. You can check this if you issue the following command from the commandline:

du -c somefile1 somefile2

Fix the respective line with your favorite editor to read:

ATTSIZE=$(du -c $BACKUPFILES | tail -n 1 | sed -e "s/\	.*//")

There remains the problem that your backups return errors at some point. Comment line 686 of the script to read:

#eval rm -f "$LOGERR"

and then after running the script have a look at the file in your backup directory named ERROR_<something>.log and post it’s contents (or part of it in case it’s too long).

I’m absolutely sure that we get this running.

14

I’ve written a cron script for MySQL backups which I’ve been using for years now without a problem. It has a config file in /etc/dumpmysql where one can configure stuff. It’s in my repo and is called… emmm… “dumpmysql” :smiley:

15

Hey Vodoo,

thanks for you’re help… a 1000 thanks.
it’s working now.

for other people. the problem was:
mutt wasn’t installed :frowning:

and after commenting out the removal off the error file,
i could read the errors. weird do! that standard the error file is removed.

Kind regards.
Arnold

16

Taken from the automysqlbackup.sh file:-

The MAILCONTENT and MAILADDR options and pretty self explanitory, use

these to have the backup log mailed to you at any email address or multiple

email addresses in a space seperated list.

(If you set mail content to “log” you will require access to the “mail” program

# on your server. If you set this to “files” you will have to have mutt installed

on your server. If you set it to “stdout” it will log to the screen if run from

the console or to the cron job owner if run through cron. If you set it to “quiet”

logs will only be mailed if there are errors reported. )

I found this thread while posting about another (unrelated SQL question) and I wanted to automate my MySQL backups, this script seems to work fine and emailing the SQL files and logs to me fine (once Mutt was installed).

17

Last note from my side: The script uses a rather strange way to generate a unique filename in lines 346 and 347. If you keep the logs and list them with ls -l it will not show them in the correct (chronological) order and there is a remote chance to overwrite one of the files.

I would change this to:

LOGERR=$BACKUPDIR/ERRORS_$DBHOST-`date +%Y%m%d-%H%M%S`.log

No one is likely to run the backups twice within one second (unless you have a few thousand users on your system doing backups, grin). The reason why the author choose to remove the error log probably is because this file is created each time even if it’s empty. The script would gradually fill the /backup directory with empty logfiles. It would probably be much better to conditionally remove the logfile only if it is empty.

Something like this:

if ! test -s "$LOGERR" ; then
     eval rm -f "$LOGERR"
fi
18

those are some great suggestions Vodoo!

and i really don’t wanna push you’re great help.
but do you think it is possible to get 1 error file (apache style)
like.
date … error …
date2 … error2 …
…
…

kind regards,
Arnold

19

Well, that would require some changes in the design of the script. It starts with a fresh error logfile because it wants to test later on if this file is still of length zero. Keeping a single logfile would break this.

But you can try to add the contents of the session logfile to a general logfile before deleting the session logfile.

Re-enable the deletion of the logfile and just before this happens add this (last line already present):

date >> $BACKUPDIR/GENERAL_ERRLOG_$DBHOST.log
cat "$LOGERR" >> $BACKUPDIR/GENERAL_ERRLOG_$DBHOST.log
eval rm -f "$LOGERR"

It can’t break a lot, try it and have fun. If it doen’t work feel free to complain and we will fix it.

20

Okey Vodoo,

i’ve put some logic thinking into you’re script and modified it.
else i’m getting 1 big log file with only dates.
i hope it wil now only log errors.
and if no errors then does nothing to the logfile.

can you confirm my d.i.y. coding?

if ! test -s “$LOGERR” ;
then
eval rm -f “$LOGERR”
else
date >> $BACKUPDIR/GENERAL_ERRLOG_$DBHOST.log
cat “$LOGERR” >> $BACKUPDIR/GENERAL_ERRLOG_$DBHOST.log
eval rm -f “$LOGERR”
fi