In this tutorial, I will show how to write a shell script to backup all your mysql databases. Its very essential to take database backup in timely manner.
The shell script will create dump of all databases from mysql server and then convert file to a compressed format (tar.gz). The script is using mysqldump and tar command for this purpose.
Shell Script to Backup all mysql database and compress file
#!/bin/bash #Check if user input hostname, if not – exit from script if [ "$#" = "0" ]; then echo "Usage: `basename $0` " exit 2 fi #We use some variables: #we save hostname that user input to MYSQL variable MYSQL=$1 #Folder to keep backups BACKUP_DIR="/data" #we getting current timestamp, to use in filenames NOW=`date +%Y%m%d-%H%M%S` #name of file will be HOSTNAME-CURRENT_TIMESTAMP NAME=${MYSQL}-${NOW} #we use mysql access without password PASSWORD="" #This will create folder, where we will keep all dumps mkdir ${BACKUP_DIR}/${NAME} #Telling user that we start to process hostnname echo "Processing ${MYSQL}..." #This will get list all of databases, we use mysql command to login to server, and with awk get only #database name without description and database with name information_schema DBS=`echo "show databases;" | mysql --host=${MYSQL} --user=root --password=${PASSWD} | \ awk '{if ($1 != "Database" && $1 != "information_schema") {print $1}}'` #Now we will process every database, we will create dump for each for DB in ${DBS}; do #telling user that we starting to dump each database echo -n " Dumping ${DB}... " #if database is not mysql we need to lock tables, to avoid problems when user during same time save #something to db if [ ${DB} != "mysql" ]; then LOCKS="--lock-tables" else LOCKS="" fi #command that will create dump mysqldump --host=${MYSQL} --user=root --password=${PASSWD} --force \ --allow-keywords --complete-insert --add-drop-table --add-locks --flush-logs \ ${LOCKS} --result-file=${BACKUP_DIR}/${NAME}/${DB}-`date +%Y%m%d-%H%M%S`.sql ${DB} < /dev/null #telling user that process done for this database echo "Done" done echo #Now we will compress all dumps (stored in directory) to single file echo -n " Compressing files... " #with tar command we compress directory tar -czf ${BACKUP_DIR}/mysql-${NAME}.tar.gz ${BACKUP_DIR}/${NAME} >/dev/null 2>&1 echo Done #removing uneeded directory rm -rf ${BACKUP_DIR}/${NAME} echo "Dump of host ${MYSQL} finished"
Script Output
We named script as 'mysql_backup.sh' and running on localhost. Below shows the output of the script.
#./mysql_backup.sh localhost Processing localhost... Dumping mysql... Done Dumping test... Done Compressing files... Done Dump of host localhost finished
#ls -la /data/ # to get list of files inside directory total 152 drwxrwxrwx 2 root root 4096 Mar 4 14:44 . drwxr-xr-x 23 root root 4096 Mar 4 14:25 .. -rw-r--r-- 1 yevhen yevhen 139361 Mar 4 14:44 mysql-localhost-20130304-144450.tar.gz
Unpack tar.gz file
You can use tar command to unpack the compressed file as shown below:
server# tar -zxvf mysql-localhost-20130304-144450.tar.gz data/localhost-20130304-144450/ data/localhost-20130304-144450/test-20130304-144450.sql data/localhost-20130304-144450/mysql-20130304-144450.sql
Thanks Bobbin.
I'll update my article with some of your good points ;-)
If you want to include how to get this backup sent to Amazon S3, I've explained it in my post as well.
https://simplebackups.io/blog/the-ultimate-mysql-database-backup-script/
Would love to have your feedback on it!
Laurent