mysql rsnapshot backup separate database table files

Backing up MySQL databases and tables as separate files with rsnapshot. Simply copying /var/lib/mysql won't always work, so the databases need to be exported.

First, create a separate MySQL user backup with these limited to read-only permissions: LOCK TABLES, SELECT, SHOW VIEW.

Install rsync - otherwise you'd get bash: rsync: command not found; rsync: connection unexpectedly closed.

Then set up this script:

#! /bin/bash

BACKUP_DIR="/var/backups/mysql"
MYSQL_USER="backup"
MYSQL_PASSWORD="hackme"
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do
  for tbl in `$MYSQL -u $MYSQL_USER -p$MYSQL_PASSWORD -N -B -e "SHOW TABLES FROM $db"`; do
    VAR_LOCATION="${BACKUP_DIR}/${db}/${tbl}.sql"
    mkdir -p "$(dirname ${VAR_LOCATION})"
    $MYSQLDUMP --skip-comments --compact -u $MYSQL_USER -p$MYSQL_PASSWORD $db $tbl > "${VAR_LOCATION}"
  done
done

The above is a combination of these scripts from mensfeld.pl and jamescoyle.net.

I previously tried using the following line from this answer:

mysqldump --user=dbuser --password --tab=~/output/dir dbname

But that doesn't work, really: it throws Access denied (using password: YES) when executing 'SELECT INTO OUTFILE' because it requires a special kind of GRANT you can't easily set via the MySQL Workbench UI. After all, you end up with a CSV backup which you have to treat differently.