Wednesday, May 1, 2013

Solve AutoMySQLBackup warning mysql.event

Fix for: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

edit /usr/sbin/automysqlbackup (wherever your /etc/cron.daily/automysqlbackup points)
find the OPT="--quote-names" line (about line 354)

Change it to
OPT="--quote-names --events --ignore-table=mysql.event"

OR, (Based upon additional reading of responses):
one of:
OPT="--quote-names --events"

OPT="--quote-names --ignore-table=mysql.event"

The original line suppresses the warning and ignores the table (redundant).
If you wish to have the table in your backup, don't ignore the table, just use --events
If you don't care about the table, ignoring the table will also suppress the event message.

What's optimal? Depends on if you use events and require them to be restored.
http://dev.mysql.com/doc/refman/5.1/en/events-table.html



Reference: http://bugs.mysql.com/bug.php?id=68376

8 comments:

picolin said...

What if I want to include the events table? I have looked and try everything on the automysqlbackup.conf file and nothing works, I really dont want to exclude it.

tech guy said...

> If you wish to have the table in your backup, don't ignore the table, just use --events

picolin said...

I'm using the automysqlbackup script to automatically backup the database and I can't find a place where to specify that. I've tried things like OPT='('--events') or OPT="--quote-names --events" on the automysqlbackup conf file.

Any suggestion? everyone seems to skip the table

tech guy said...

As I don't know what's in your automysqlbackup configuration file, or what version you're using, I can't tell you what to do for your setup...

http://sourceforge.net/projects/automysqlbackup/files/latest/download

It appears 3.0 r6, the opt is in automysqlbackup (the app) instead of automysqlbackup.conf

change
opt=( '--quote-names' '--opt' )
to
opt=( '--quote-names' '--opt' '--events')

tech guy said...

or
mysql_opt=( '--events' )

might be more appropriate (?)

picolin said...

I'm using the latest version 3.0_rc2
and none of the suggested options you gave seem to fit in. I've read in different places that is not a bug, but I think it is. Or at least there is no place to specify the --events option "explicitly" as the log says

tech guy said...

I'm at a loss to assist you at this point.

Look at the file named automysqlbackup

You should see:
(around line 438?)

parse_configuration () {
# OPT string for use with mysqldump ( see man mysqldump )
opt=( '--quote-names' '--opt' )

# OPT string for use with mysql (see man mysql )
mysql_opt=()

It seems reasonable to change the mysqldump opt= file to include '--events' as per my previous statements.

BEFORE YOU REPLY "It still doesn't work" note that *I* didn't write automysqlbackup. I'm just reading the code and attempting to explain it. If you still don't understand it, please contact the creator of automysqlbackup, and/or provide an exact method for someone to recreate your experience.

Anonymous said...

Another solution could be to add to /etc/my.cnf in section [mysqldump] the following:

events
ignore-table=mysql.events

Blog Archive