Categories
Linux

A simple script to backup all mysql databases into separate files

Hi, today I’m going to present you a very simple script that I use to backup all my mysql databases into separate files compressed with gz compression. Doing this way, to restore a database you just need to extract the dump from the file and restore it with this command:

mysql -uUSER -pPASSWORD DB_NAME < db_dump.sql

The script is the following:

#|/bin/bash

#       croma25td simple mysql backup script v1.0
#       www.croma25td.com
#       croma25td at gmail dot com

# User defined variables
# Mysql user with read privileges on all databases
MYSQL_USER="username"
MYSQL_PASS="password"

# The current date
date=$(date +%Y-%m-%d_%H:%M:%S)

# The folder that will cointain all the backups
BACKUP_DIR=/backup/$date
# End user defined variables

# If the output folder doesn't exists create it
test -d $BACKUP_DIR || mkdir -p $BACKUP_DIR
# Get the database list, excluding some db names
for db in $(mysql -B -s -u $MYSQL_USER --password=$MYSQL_PASS -e 'show databases' | grep -vE '(information_schema|performance_schema|mysql)' )
do
  # dump each database in a separate file
  mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $db | gzip > $BACKUP_DIR/$db.sql.gz
done

First there are some user defined variables:

  • MYSQL_USER and MYSQL_PASS: the username and the password of a user with read privileges on all the database to include in the backup;
  • date: it’s simply the current date/time, used to easily identify different backups;
  • BACKUP_DIR: the folder where the backups will be saved, I used a /backup folder and every backup is within a folder named as the current date. This will create a structure like this:
    /backup
    /backup/2013-09-08_00:00:00
    /backup/2013-09-08_12:00:00
    /backup/2013-09-09_00:00:00
    /backup/2013-09-09_12:00:00

Then comes the script:

  • first there is a check on the output folder, to verify that exists and otherwise to create it;
  • then we obtain all the databases names, in two different ways:
    • if you want to exclude some database include their names within the grep -vE command using | as separator: mysql -B -s -u $MYSQL_USER –password=$MYSQL_PASS -e ‘show databases’ | grep -vE ‘(information_schema|performance_schema|mysql)
    •  otherwise to get all the names: mysql -B -s -u $MYSQL_USER –password=$MYSQL_PASS -e ‘show databases’
  • lastly mysqldump will create a backup for all the databases and gzip will compress it into a single file.

As I mentioned you need a user with read privileges on all the databases, and forget about using root user 🙂

So, to create a user just use these commands:

  • open a mysql console using the root account: mysql -uroot -p
  • create the user with name USER and with a PASSWORD:GRANT LOCK TABLES, SELECT ON *.* TO ‘USER’@’%’ IDENTIFIED BY ‘PASSWORD’;
  • then finally flush the privileges:flush privileges;
  • close the mysql console with \q , make the script executable with: chmod +x db_backup.sh  and test the script.

Now you also may want to add a crontab entry to execute it automatically.

As always if you have any questions of suggestions just use the comments below :)