Skip to content

Instantly share code, notes, and snippets.

@andsens andsens/dump.sh
Last active May 7, 2020

Embed
What would you like to do?
Backup all MySQL databases into separate files
#!/bin/sh
## backup each mysql db into a different file, rather than one big file
## as with --all-databases. This will make restores easier.
## To backup a single database simply add the db name as a parameter (or multiple dbs)
## Putting the script in /var/backups/mysql seems sensible... on a debian machine that is
## Create the user and directories
# mkdir -p /var/backups/mysql/databases
# useradd --home-dir /var/backups/mysql --gid backup --no-create-home mysql-backup
## Remember to make the script executable, and unreadable by others
# chown -R mysql-backup:backup /var/backups/mysql
# chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh
## crontab entry - backup every night at 02:00
# sudo -u mysql-backup crontab -e
# 0 2 * * * /var/backups/mysql/dump.sh
## Create 'backup' mysql user
# CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t';
# GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'localhost' ;
USER="backup"
PASSWORD="s3cr3t"
OUTPUTDIR=$(dirname $0)"/databases"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
if [ -z "$1" ]; then
databases=`$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|information_schema'`
for database in $databases; do
$MYSQLDUMP \
--user=$USER --password=$PASSWORD \
--force \
--quote-names --dump-date \
--opt --single-transaction \
--events --routines --triggers \
--databases $database \
--result-file="$OUTPUTDIR/$database.sql"
done
else
for database in ${@}; do
$MYSQLDUMP \
--user=$USER --password=$PASSWORD \
--force \
--quote-names --dump-date \
--opt --single-transaction \
--events --routines --triggers \
--databases $database \
--result-file="$OUTPUTDIR/$database.sql"
done
fi
@Azendale

This comment has been minimized.

Copy link

Azendale commented May 23, 2017

You should replace the line:

databases=$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|information_schema'
with something like:
databases=$($MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v '^mysql$\|^information_schema$')

so that you don't leave out databases with 'mysql' in the name (but still leave out the database 'mysql').

@arnisjuraga

This comment has been minimized.

Copy link

arnisjuraga commented Mar 23, 2018

Also, add -
mkdir -p $OUTPUTDIR
otherwise it will generate an error and will not backup anything, of course, if $OUTPUTDIR does not exists. Just to be sure.

@slideup-benni

This comment has been minimized.

Copy link

slideup-benni commented Jun 3, 2019

You skip to backup the user permissions, so maybe you should add
$MYSQL --user=$USER --password=$PASSWORD --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | $MYSQL --user=$USER --password=$PASSWORD --skip-column-names -A | sed 's/$/;/g' > mysql_user_grants.sql

From: https://dba.stackexchange.com/a/31023

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.