如何在CentOS上创建高效的MySQL数据库备份脚本？

CentOS MySQL备份脚本

背景介绍

在数据库的日常维护工作中，除了保证业务的正常运行以外，对数据库进行备份是至关重要的，以避免由于数据丢失带来的严重后果，通常备份可以按照备份时数据库状态分为热备和冷备，按照备份数据库文件的大小可以分为增量备份、差异备份和全量备份，本文将详细介绍一种全量备份的方法，利用shell脚本实现定时备份MySQL数据库，并支持过期删除功能。

环境准备

操作系统：CentOS 7 64位 Minimal版（VMware）

MySQL版本：5.7

备份工具：mysqldump程序

调度工具：crontab命令

脚本编写

1、新建shell脚本

    vi /opt/mysqlBackup.sh

2、

    #!/bin/bash
    # 以下配置信息请自己修改
    mysql_user="USER" # MySQL备份用户
    mysql_password="PASSWORD" # MySQL备份用户的密码
    mysql_host="localhost"
    mysql_port="3306"
    mysql_charset="utf8" # MySQL编码
    backup_db_arr=("db1" "db2") # 要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")
    backup_location=/opt/mysql  # 备份数据存放位置，末尾请不要带"/",此项可以保持默认，程序会自动创建文件夹
    expire_backup_delete="ON" # 是否开启过期备份删除 ON为开启 OFF为关闭
    expire_days=3 # 过期时间天数 默认为三天，此项只有在expire_backup_delete开启时有效
    # 本行开始以下不需要修改
    backup_time=$(date +%Y%m%d%H%M) # 定义备份详细时间
    backup_Ymd=$(date +%Y-%m-%d) # 定义备份目录中的年月日时间
    backup_3ago=$(date -d '3 days ago' +%Y-%m-%d) # 3天之前的日期
    backup_dir=$backup_location/$backup_Ymd # 备份文件夹全路径
    welcome_msg="Welcome to use MySQL backup tools!" # 欢迎语
    # 判断MYSQL是否启动,mysql没有启动则备份退出
    mysql_ps=$(ps -ef | grep mysql | wc -l)
    mysql_listen=$(netstat -an | grep LISTEN | grep $mysql_port | wc -l)
    if [ $mysql_ps == 0 ] || [ $mysql_listen == 0 ]; then
        echo "ERROR: MySQL is not running! backup stop!"
        exit
    else
        echo $welcome_msg
    fi
    # 连接到mysql数据库，无法连接则备份退出
    mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<EOF
    use mysql;
    select host, user from user where user='root' and host='localhost';
    exit
    EOF
    flag=$?
    if [ $flag != "0" ]; then
        echo "ERROR: Can't connect mysql server! backup stop!"
        exit
    else
        echo "MySQL connect ok! Please wait......"
    fi
    # 判断有没有定义备份的数据库，如果定义则开始备份，否则退出备份
    if [ "${backup_db_arr[@]}" != "" ]; then
        for dbname in ${backup_db_arr[@]} do
            echo "database $dbname backup start..."
            mkdir -p $backup_dir
            mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset | gzip > $backup_dir/$dbname-$backup_time.sql.gz
            flag=$?
            if [ $flag == "0" ]; then
                echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz"
            else
                echo "database $dbname backup fail!"
            fi
        done
    else
        echo "ERROR: No database to backup! backup stop"
        exit
    fi
    # 如果开启了删除过期备份，则进行删除操作
    if [ "$expire_backup_delete" == "ON" ] && [ "$backup_location" != "" ]; then
        find $backup_location/ -type d -mtime +$expire_days | xargs rm -rf
        echo "Expired backup data delete complete!"
    fi
    echo "All database backup success!"
    exit

3、保存并退出编辑器

4、赋予执行权限

    chmod +x /opt/mysqlBackup.sh

5、编辑crontab任务

    crontab -e

6、添加定时任务

    00 03 * * * /opt/mysqlBackup.sh

7、保存并退出编辑器

验证与测试

1、手动执行脚本

    /opt/mysqlBackup.sh

检查备份文件是否生成在指定目录中。

2、查看备份结果

    ls -lh /opt/mysql/$(date +%Y-%m-%d)/

3、恢复备份

    mysql -u username -p database < /path/to/backup.sql

4、检查恢复情况

    SELECT * FROM table;

常见问题与解答（FAQs）

Q1: 如何更改备份脚本中的MySQL用户和密码？

A1: 在脚本中找到以下行并替换相应的值：

mysql_user="USER" # MySQL备份用户
mysql_password="PASSWORD" # MySQL备份用户的密码

将USER替换为实际的MySQL用户名，将PASSWORD替换为实际的密码。

Q2: 如何更改备份脚本中的数据库列表？

A2: 在脚本中找到以下行并替换相应的值：

backup_db_arr=("db1" "db2") # 要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")

将db1和db2替换为实际需要备份的数据库名称，多个数据库名称之间用空格分隔。

小编有话说

通过上述步骤，我们实现了一个自动化的MySQL数据库备份脚本，该脚本不仅能够定期备份指定的数据库，还能够自动删除过期的备份文件，从而节省存储空间，希望本文能够帮助大家更好地管理和维护自己的数据库系统，如果在实际操作中遇到任何问题，欢迎随时留言讨论。