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