在本教程中，我将展示如何编写shell脚本以备份所有MySQL数据库。
这是一个及时拍摄数据库备份的重要意义。

shell脚本将从mysql server中创建转储所有数据库，然后将文件转换为压缩格式(tar.gz)。
脚本正在使用MySQLDump和Tar命令为此目的。

shell脚本备份所有MySQL数据库和压缩文件

#!/bin/bash
#Check if user input hostname, if not – exit from script
if [ "$#" = "0" ]; then
echo "Usage: `basename 
#./mysql_backup.sh localhost
Processing localhost...
Dumping mysql... Done
Dumping test... Done
Compressing files... Done
Dump of host localhost finished
` "
exit 2
fi
#We use some variables:
#we save hostname that user input to MYSQL variable
MYSQL=
#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 ( != "Database" &&  != "information_schema") {print }}'`
#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"

脚本输出

我们将脚本命名为"mysqlabackup.sh"并在localhost上运行。
下面显示脚本的输出。

#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

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

解压缩tar.gz文件

我们可以使用tar命令解压缩压缩文件，如下所示：