数据库备份

#!/bin/bash
#50 12,23 * * * sh /data/sh/mysql.sh >> log.txt 2>>log.txt
# mysql数据库信息
mysql_user="数据库用户名"
mysql_password="数据库用户密码"
mysql_host="数据库地址"
mysql_dbname="数据库名"
mysql_port="3306"
mysql_charset="utf8mb4"

# 备份文件地址
backup_location=.

# 是否删除过期数据
expire_backup_delete="ON"
expire_days=7
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="欢迎使用MySQL备份工具"
version=`grep -Po 'version[" :]+\K[^"]+' ../../src/version.json`
backup_file=$backup_dir/$backup_time-$version
# 判断mysql实例是否正常运行 如果本机安装可以开启
# mysql_ps=`ps -ef |grep mysql |wc -l`
# mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
# if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
#         echo "ERROR:MySQL is not running! backup stop!"
#         exit
# else
#         echo $welcome_msg
# fi

# 备份指定数据库中数据(此处假设数据库是mysql_backup_test)
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $mysql_dbname > $backup_file.sql
flag=`echo $?`
if [ $flag == "0" ];then
       tar zcf $backup_file.tar.gz $backup_file.sql
       rm -rf $backup_file.sql
       echo "$backup_file.tar.gz 备份成功"
else
       echo "$mysql_dbname数据库备份失败"
fi

# 删除过期数据
# if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
#         `find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`
#         echo "清理文件成功!"
# fi

MySQL 数据库备份单循环

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
PASS=123.com
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_NAME=$BACKUP_DIR/${DB}_${DATE}.sql
    if ! mysqldump -h$HOST -u$USER -p$PASS -B $DB > $BACKUP_NAME 2>/dev/null; then
        echo "$BACKUP_NAME 备份失败!"
    fi
done

MySQL 数据库备份多循环

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
PASS=123.com
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema|sys")

for DB in $DB_LIST; do
    BACKUP_DB_DIR=$BACKUP_DIR/${DB}_${DATE}
    [ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR &>/dev/null
    TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)
    for TABLE in $TABLE_LIST; do
        BACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sql
        if ! mysqldump -h$HOST -u$USER -p$PASS $DB $TABLE > $BACKUP_NAME 2>/dev/null; then
            echo "$BACKUP_NAME 备份失败!"
        fi
    done
done