MySQL 数据库备份脚本（shell）

一、单数据库备份

#!/bin/bash
time=` date +%Y%m%d%H%M%S `
pass=root
db_name=dbname
dir=/data/db-bak/${db_name}
mkdir -p $dir
mysqldump -uroot -p${pass} --databases ${db_name} | zip > ${dir}/db-${db_name}-${time}.sql.zip

二、完整备份

#!/bin/bash
time=` date +%Y%m%d%H%M%S `
dir=/root/db-bak/all
mkdir -p $dir
MYSQL_USER=root
MYSQL_PASS=root
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
# 需要排除的数据库
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} | zip > ${dir}/all-dbs-${time}.sql.zip

三、定时执行

# crontab -e
# 输入以下内容保存退出即可
0  6  1,11,21 * * /root/db-bak/mydb.sh