Steve Jones 2016-04-17
This is the script I run every morning to back up all of the databases in my MySQL database – it gets every database, including mysql, which has the users and access rights for users.

Obviously, you’ll need to modify a few of the variables in the script, but it shouldn’t be difficult. There are a few comments in the script. Feel free to ask questions if you have them, you can always e-mail me with steve at clug dot org.

#
# First step, create a user solely for executing backups with;
# MariaDB [(none)]> GRANT LOCK TABLES, SELECT ON *.* TO 'MySQLBackup'@'localhost' IDENTIFIED BY '2Rd4nyablIF%((qH8nN+9J';
# Query OK, 0 rows affected (0.00 sec)
#
# MariaDB [(none)]> flush privileges;
# Query OK, 0 rows affected (0.00 sec)
#
# MariaDB [(none)]> quit;
# Bye

User='MySQLBackup'
Pass='2Rd4nyablIF%((qH8nN+9J'
MySQL=/usr/bin/mysql
MySQLDump=/usr/bin/mysqldump
New=$(date +%Y-%m-%d)
Old=$(date -d "7 days ago" +%Y-%m-%d)

cd /Backup/MySQL

Databases=$(${MySQL} --user=${User} -p${Pass} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")

for db in ${Databases} ; do
  ${MySQLDump} --force --opt --user=${User} -p${Pass} --databases "${db}" > "${New}-${db}".sql
  ### If an oldfile exists, remove it. An added feature of this is that
  ### if you drop a DB, the last few days of its life will be here forever
  [ -f "${Old}-${db}.sql" ] && rm -f "${Old}-${db}.sql"

done