MySQL备份脚本

        MySQL数据库备份可以将所有数据库看做一个整体进行备份，也可以将所有数据库单个备份后合并成一个文件，即备份分为整库备份和单库备份。

1.整库备份

适合于数据库比较少，数据量比较小的备份方式。

########################################################################
##                      mysql_daily_backup.sh                        ##
##                      created by author                            ##
##                           2024-10-16                              ##
##                      Backup method: mysqldump                     ##
########################################################################
#!/bin/bash

#backup keep days
keepdays=7

#path of backup
base_dir=/data/backup/mysql
backup_dir=$base_dir/mysqldump
log_dir=$base_dir/log

#date
dd=`date +%Y%m%d`
tm=$(date '+%Y-%m-%d %T')

#tool
tool=mysqldump

#user
username=xxxxxx

#password
password='xxxxxx'

#socket
socket=/var/lib/mysql/mysql.sock

#create backup directory
if [ ! -d $backup_dir ];
then
     mkdir -p $backup_dir
     chown mysql.mysql $backup_dir
fi

if [ ! -d $log_dir ];
then
     mkdir -p $log_dir
     chown mysql.mysql $log_dir
fi


#backup all databases
$tool -u $username -p$password -S $socket -B --all-databases | gzip > $backup_dir/all_databases_${dd}.sql.gz
echo "$tm create $backup_dir/all_databases_${dd}.sql.gz" >> $log_dir/mysqlbackup_${dd}.log

delfile=`ls -l -crt $backup_dir/all_databases*.sql.gz | awk '{print $9}' | head -1`
count=`ls -l -crt $backup_dir/all_databases*.sql.gz | awk '{print $9 }' | wc -l`
if [ $count -gt $keepdays ]
then
        rm -f $delfile
        echo "$tm delete $delfile" >> $log_dir/mysqlbackup_${dd}.log
fi

find $log_dir -type f -name 'mysqlbackup_*.log' -mtime +$keepdays -exec rm -rvf {} \;

注意修改其中的username，password，socket三个环境变量。

2.单库备份

如果想恢复时只恢复某一个数据库，则需要使用单库备份的方式。单库备份只需要备份所需要的数据库即可，所以需要排除系统数据库（information_schema|performance_schema|sys，这三个数据库在mysql软件安装时会自动创建）。

########################################################################
##                      mysql_daily_backup.sh                        ##
##                      created by author                            ##
##                           2024-10-16                              ##
##                      Backup method: mysqldump                     ##
########################################################################
#!/bin/bash

#backup keep days
keepdays=7

#path of backup
base_dir=/data/backup/mysql
backup_dir=$base_dir/mysqldump
log_dir=$base_dir/log
dblist=$backup_dir/dblist.txt

#date
dd=`date +%Y%m%d`
tm=$(date '+%Y-%m-%d %T')

#tool
tool=mysqldump

#user
username=xxxxxx

#password
password='xxxxxx'

#socket
socket=/var/lib/mysql/mysql.sock

#create backup directory
if [ ! -d $backup_dir ];
then
     mkdir -p $backup_dir
     chown mysql.mysql $backup_dir
fi

if [ ! -d $log_dir ];
then
     mkdir -p $log_dir
     chown mysql.mysql $log_dir
fi

#get database list
DATABASES=$(mysql -u$username -p$password -S $socket -e "show databases;" | egrep -v 'Database|information_schema|performance_schema|\bsys\b')
echo $DATABASES | awk '{for(i=1;i<=NF;i++) print $i}' > $dblist

#db count
dbcount=`cat $dblist | wc -l`

#backup single database
for((i=1;i<=$dbcount;i++))
do
    database_name=`sed -n "${i}p" $dblist`
    $tool -u$username -p$password -S $socket -B $database_name > $backup_dir/${database_name}_${dd}.sql
    if [ $? -eq 0 ]; then
        echo "Database $database_name backed up successfully."
    else
        echo "Error in backing up $database_name."
    fi
done

cd $backup_dir
tar -zcvf full_${dd}.tar.gz *${dd}.sql
echo "$tm create $backup_dir/full_${dd}.sql.gz" >> $log_dir/mysqlbackup_${dd}.log

#delete temp file
rm -f $backup_dir/*${dd}.sql
rm -f $dblist

delfile=`ls -l -crt $backup_dir/full*.tar.gz | awk '{print $9}' | head -1`
count=`ls -l -crt $backup_dir/full*.tar.gz | awk '{print $9 }' | wc -l`
if [ $count -gt $keepdays ]
then
        rm -f $delfile
        echo "$tm delete $delfile" >> $log_dir/mysqlbackup_${dd}.log
fi

find $log_dir -type f -name 'mysqlbackup_*.log' -mtime +$keepdays -exec rm -rvf {} \;

同样的，注意修改其中的username，password，socket三个环境变量。