Mysql备份

1：Mysql备份全库（脚本）

#!/bin/sh
TIME=`date +%Y_%m_%d_%H_%M`       #时间
HOST=localhost        
DB_USER=xxxx     #用户
DB_PASS=xxxx   #用户密码
DUMP=/usr/local/mysql/bin/mysqldump   #安装mysql的路径
BACKUP_DIR=/home/mysql/backup
DB_NAME=FULL
SQL_BACKUP_NAME=${DB_NAME}_$TIME.sql
TAR_BACKUP_NAME=${DB_NAME}_$TIME.tar.gz
DAYS=7
#BACKUP_COPIES=7         #冗余数
#REMOTE_IP=10.190.193.143
#REMOTE_DIR=:date-backup/daba_backup
#REMOTE_USER=rsync_backup
#export RSYNC_PASSWORD=6

echo "------------------------------------------"
echo "$TIME:backup start"
$DUMP -h$HOST -u$DB_USER -p$DB_PASS -F -R --quick --single-transaction --all-databases > $BACKUP_DIR/$SQL_BACKUP_NAME
#if [[ $? -ne 0 ]]
#then
#    echo "$TIME backup failed"
#    exit 1
#fi
tar -zcvPf $BACKUP_DIR/$TAR_BACKUP_NAME $BACKUP_DIR/$SQL_BACKUP_NAME
rm -f $BACKUP_DIR/$SQL_BACKUP_NAME
echo $(date +%Y_%m_%d_%H_%M)":backup end"

#echo $(date +%Y_%m_%d_%H_%M)":SCP backup start"
#scp -p $BACKUP_DIR/$TAR_BACKUP_NAME $REMOTE_IP:$REMOTE_DIR
#echo $(date +%Y_%m_%d_%H_%M)":SCP backup end"

#echo $(date +%Y_%m_%d_%H_%M)":rsync backup start"
#rsync -avz $BACKUP_DIR/$TAR_BACKUP_NAME $REMOTE_USER@$REMOTE_IP:$REMOTE_DIR
#echo $(date +%Y_%m_%d_%H_%M)":rsync backup end"

echo $(date +%Y_%m_%d_%H_%M)":backup file delete start"
find $BACKUP_DIR -name "$DB_NAME_*.tar.gz" -type f -mtime +$DAYS -exec rm -f {} \;
#ls -t $BACKUP_DIR/$DB_NAME_*.tar.gz | awk 'NR>'$BACKUP_COPIES'' | xargs rm -f
echo $(date +%Y_%m_%d_%H_%M)":backup file delete end"
echo "------------------------------------------"

root@yuan-virtual-machine:/opt/mysql8/backup# tar -zxvf FULL_2022_11_08_16_34.tar.gz -C /
tar: 从成员名中删除开头的“/”
/opt/mysql8/backup/FULL_2022_11_08_16_34.sql
root@yuan-virtual-machine:/opt/mysql8/backup# ls
FULL_2022_11_08_16_34.sql  FULL_2022_11_08_16_34.tar.gz  opt
root@yuan-virtual-machine:/opt/mysql8/backup#

2：Mysql命令备份数据库

2.1全备备份全库

进入到mysql bin目录中，执行mysqldump

mysqldump -uroot -pxxx --force --all-databases > /xxx/xxx/xxx.sql

2.2备份单库

mysqldumo -uroot -pxxx --fore --databases axxx,bxxx > /xxx/xxx/xxx.sql