Linux上脚本备份数据库（升级版）

#!/bin/bash # 配置部分 mysql_user="root" mysql_host="localhost" mysql_port="3306" mysql_charset="utf8mb4" mysql_defaults_file="/home/mysql/mysql_back/.my.cnf" backup_base_dir="/mnt/sdd/home/mysqlbak" backup_dirs=("dflowsql" "dflow_clientsql" "dflow_hcwysql" "dflow_jnznsql") databases=("dflow" "dflow_client" "dflow_hcwy" "dflow_jnzn") expire_backup_delete="ON" expire_days=10 backup_time=$(date +%Y%m%d%H%M) welcome_msg="Welcome to use MySQL backup tools!" # 检查MySQL实例是否正常运行 mysql_ps=$(pgrep -f mysql | wc -l) mysql_listen=$(netstat -an | grep LISTEN | grep ":$mysql_port" | wc -l) if [ $mysql_ps -eq 0 ] || [ $mysql_listen -eq 0 ]; then echo "ERROR: MySQL is not running! Backup stopped!" exit 1 else echo $welcome_msg fi # 备份函数 backup_database() { local db_name=$1 local backup_dir=$2 mkdir -p $backup_dir /usr/local/mysql/bin/mysqldump --defaults-extra-file=$mysql_defaults_file \ --add-drop-table -h $mysql_host -P $mysql_port \ --default-character-set=$mysql_charset --hex-blob $db_name \ --single-transaction --routines --events | gzip > $backup_dir/$db_name-$backup_time.sql.gz if [ $? -eq 0 ]; then echo "Database backup success. Backup saved to $backup_dir/$db_name-$backup_time.sql.gz" else echo "Database backup failed for $db_name!" exit 1 fi } # 删除过期备份函数 delete_expired_backups() { local backup_dir=$1 local db_name=$2 if [ "$expire_backup_delete" == "ON" ] && [ -n "$backup_dir" ]; then expired_file_prefix="$db_name-$(date -d "-$expire_days days" +%Y%m%d)" find $backup_dir/ -type f -name "$expired_file_prefix*.sql.gz" -delete echo "Expired backup data deleted in $backup_dir for $db_name!" fi } # 备份视图创建语句函数 backup_views() { local db_name=$1 local backup_dir=$2 mkdir -p $backup_dir local view_backup_file="$backup_dir/${db_name}_views-$backup_time.sql" local compressed_view_backup_file="$view_backup_file.gz" mysql --defaults-extra-file=$mysql_defaults_file -h $mysql_host -P $mysql_port -D $db_name -B -N -e " SELECT CONCAT('CREATE OR REPLACE VIEW \`', '$db_name', '\`.\`', TABLE_NAME, '\` AS ', VIEW_DEFINITION, ';') FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '$db_name' ORDER BY TABLE_NAME; " > $view_backup_file if [ $? -eq 0 ]; then echo "View export successful. File saved to $view_backup_file" else echo "View export failed for $db_name!" exit 1 fi gzip -c $view_backup_file > $compressed_view_backup_file if [ $? -eq 0 ]; then echo "Backup file compressed. Compressed file saved to $compressed_view_backup_file" else echo "Compression failed for $view_backup_file!" exit 1 fi rm -f $view_backup_file } # 循环备份所有数据库 for i in "${!databases[@]}"; do backup_database "${databases[i]}" "$backup_base_dir/${backup_dirs[i]}" #backup_views "${databases[i]}" "$backup_base_dir/${backup_dirs[i]}" if [ "${databases[i]}" == "dflow_jnzn" ]; then backup_views "${databases[i]}" "$backup_base_dir/${backup_dirs[i]}" fi done # 删除过期备份 for i in "${!databases[@]}"; do delete_expired_backups "$backup_base_dir/${backup_dirs[i]}" "${databases[i]}" done