Fix automysqlbackup warnings about PERFORMANCE INFORMATION and EVENTS

Whenever I'm asked to "review" or "go over" a small webserver the first things I usually recommend are fail2ban and automysqlbackup.

apt-get install automysqlbackup used to do the trick, but now that mysql 5.1 is in the rearview mirror of all modern distros the new virtual schemas throw scary email warnings about failing to back up PERFORMANCE or INFORMATION databases. If you aren't a DBA these emails are mortally terrifying! Who doesn't want PERFORMANCE and INFORMATION backed up?

Most google hits (and thus stackoverflow) recommend making the tangled mess of schema detection logic in the defaults file worse rather than better. If you didn't catch that --the default configuration file is dynamic and guessing what tables you want backed up. Unacceptable.

Today I noticed a promising configuration option called DBEXCLUDE="" and a note in the comments saying it only works when DBNAMES is set to all. I didn't know you could set DBNAMES to all. Isn't that what the script is supposed to do in the first place? Defaults can be strange things.

I had to dive into the code a bit to sort out the defaults but it seems you can set DBNAMES="all" (note the quotes) and then you can set DBEXCLUDE="db1 db2". Perfect!

# [...snip...]
DBNAMES="all"
DBEXCLUDE="INFORMATION_SCHEMA PERFORMANCE_SCHEMA"

Ah but mysql has a new surprise. There is an events table in the mysql schema that throws warnings once a month. ( Did you know that automysqlbackup backs up the mysql schema only once a month? Now you do. ) Wordpress and drupal will never notice its missing, so you want to exclude it but there isn't an option to exclude just tables in automysqlbackup.

You want to ignore the mysql.events table in the underlying mysqldump utility.

[mysqldump]
quick
quote-names
max_allowed_packet = 128M
ignore-table = mysql.events

While you are in there, bump up max_allowed_packet to 128M. The default is an ancient relic of the past.

That is all the pieces. Having just got back from SaltConf2014 and earning my shiney Salt Stack Certified Engineer moniker I whipped up a state file:

automysqlbackup:
  pkg.installed

backup_most_schemas:
  file.replace:
    - name: /etc/default/automysqlbackup
    - pattern: '^DBNAMES=`mysql.*'
    - repl: 'DBNAMES="all"'

exclude_some_schemas:
  file.replace:
    - name: /etc/default/automysqlbackup
    - pattern: '^DBEXCLUDE=""'
    - repl: 'DBEXCLUDE="performance_schema information_schema"'

dont_backup_event_table:
  file.replace:
    - name: /etc/mysql/my.cnf
    - pattern: '^max_allowed_packet\s*=\s*(16|32|64).\s*$'
    - repl: |
        max_allowed_packet = 128M
        events
        ignore-table = mysql.event