Linux 备份Mysql数据库

首先创建专门用于备份数据的用户，并设置权限

sh >  mysql -uroot -p -e 
"GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,TRIGGER,PROCESS ON *.* TO 
'usre'@'localhost' identified BY 'password'"

第一种方案

#!/bin/bash
#program:全备份数据库
#author:gt
#date:2021/12/08
#version:1.0
#history:

#备份目录
backup_dir=/backup/mysql
test ! -d $backup_dir && mkdir -p $backup_dir
backup_name_prefix=full_backup
backup_name=${backup_name_prefix}$(date +'%Y%m%d')

#需要备份的数据库，自行修改需要备份的数据库，也可以做成接受参数的形式
database='sql_inform slow_query_log' 

#错误日志
log_dir=/var/log/mysql
test ! -d $log_dir && mkdir -p $log_dir
backup_err_log=$log_dir/mysql_backup_err.log

echo "===============================================$(date +'%Y-%m-%d %H:%M:%S')===============================================" > $backup_err_log

if [ -n "$database" ] ;then
    
    database_list=$(echo "$database" | awk 'BEGIN{RS=" "}{print $1}')
   
    #查询条件拼接,格式: 'a', 'b' 
    cond=''
    db_size=0
    for name in $database_list
    do
    if [ -z "$cond" ];then
        cond=$(echo "'$name'")
    else    
            cond=$(echo "$cond,'$name'")
    fi
    db_size=$(($db_size+1))
    done
 
    #生成临时表名文件
    tmp_table=/tmp/tables.txt
    sql="select table_schema, table_name from information_schema.tables where table_schema in ($cond)"
    #根据数据库名查询表名，并输出到临时文件
    /bin/mysql -ubackup -e "$sql" -s > $tmp_table 2>> $backup_err_log
    
    if [ ! -f $tmp_table ] || [ -z "$(cat $tmp_table)" ];then
        echo "查询备份数据库的表为空, sql: $sql" >> $backup_err_log
        exit 1;
    fi

    #生成数据库备份目录和表备份文件
    rm_db_dir=''
    success_db=''
    err_db=''
    err_num=0
    db_null=0
    for db in $database_list
    do
    #创建目录
    test ! -d $backup_dir/$db && mkdir -p $backup_dir/$db
    
    rm_db_dir=$(echo "$rm_db_dir $db")
        
    tables_list=$(cat $tmp_table | awk -v db="$db" '$1 == db{print $2}')
       
    if [ -n "$tables_list" ];then
        for table in $tables_list
        do
           #创建表sql文件
           /bin/mysqldump -ubackup -c --single-transaction $db $table > $backup_dir/$db/$table.sql 2>> $backup_err_log
        done
        success_db=$(echo "$success_db $db")
    else
        echo "数据库[ $db ]表为空" >> $backup_err_log
        err_db=$(echo "$err_db $db")
        err_num=$(($err_num+1))
        db_null=1
        fi
        # 导出函数
        test 0 -eq $db_null && /bin/mysqldump -ubackup -n -t -d -R $db > $backup_dir/$db/function.sql || db_null=0
    done
   
    if [ $db_size -eq $err_num ];then
    echo -e "导出数据库[ $err_db ]全部失败, 数据库表内容如下：\n$(cat $tmp_table)" >> $backup_err_log
        rm -fr $err_db
    rm -f $tmp_table
    exit 1
    elif [ $err_num -gt 0 ];then
    echo -e "导出数据库[ $err_db ]失败, 数据库表内容如下：\n$(cat $tmp_table)" >> $backup_err_log
    fi
  
    cd $backup_dir
  
    tar -zvc -f $backup_name.tar.gz $success_db > /dev/null 2>> $backup_err_log 
    
    #删除临时文件
    rm -rf $rm_db_dir
    rm -f $tmp_table
    
    find $backup_dir -mtime +6 -name "$backup_name_prefix*.tar.gz" -type f -exec rm -f {} \; 2>> $backup_err_log 
    echo "备份数据库[ $success_db ]完成" >> $backup_err_log
    exit 0
else
    echo "没有需要备份的数据库" >> $backup_err_log
    exit 1
fi

备份后的效果如下：分库分表备份，在数据量大的情况下， 方便查看和灵活恢复单表数据，缺点是执行速度相对第二种方案较慢，函数和存储过程备份文件为 function.sql

image.png

第二种方案

#!/bin/bash
#program:全备份数据库
#author:gt
#date:2021/12/08
#version:1.0
#history:

#备份目录
backup_dir=/backup/mysql
test ! -d $backup_dir && mkdir -p $backup_dir
backup_name_prefix=full_backup
backup_name=$backup_name_prefix$(date +'%Y%m%d')

#需要备份的数据库
database='sql_inform slow_query_log' 

#错误日志
log_dir=/var/log/mysql
test ! -d $log_dir && mkdir -p $log_dir
backup_err_log=$log_dir/mysql_backup_err.log

echo "===============================================$(date +'%Y-%m-%d %H:%M:%S')===============================================" > $backup_err_log

if [ -n "$database" ] ;then
    db_files=''
    database_list=$(echo "$database" | awk 'BEGIN{RS=" "}{print $1}')
    for db in $database_list
    do
        #备份数据，并生成新的二进制文件
        /bin/mysqldump -ubackup --no-create-db -c -R --single-transaction --databases $db -F > $backup_dir/$db.sql 2>> $backup_err_log
    #删除 use tale 自动切换数据库语法，避免误切换，导致覆盖已有的数据
    sed -i 's/USE.*\;//g' $backup_dir/$db.sql 2>> $backup_err_log
    db_files=$(echo "$db_files $db.sql")
    done
    
    cd $backup_dir
    #压缩备份文件
    tar -zvc -f $backup_name.tar.gz $db_files  > /dev/null 2>> $backup_err_log

    #删除源备份文件
    rm -f $db_files 2>> $backup_err_log

    #删除7天以前的备份数据
    find $backup_dir -mtime +6 -name "$backup_name_prefix*.tar.gz" -type f -exec rm -f {} \; 2>> $backup_err_log

    echo "备份数据库[ $database ] 成功" >> $backup_err_log
else
    echo "无备份数据库" >> $backup_err_log
fi

备份效果，每个数据库单独一个sql文件，里面包含表结构，表数据，视图，函数和存储过程，缺点就是当数据量大的时候，查阅比较麻烦

image.png