Whenever I'm asked to "review" or "go over" a small webserver the first things
I usually recommend are
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
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
DBNAMES="all" (note the quotes) and then you can set
# [...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