MySQL数据库数据备份还原

#!/bin/bash

#Author Mr.Wu

#mysql数据库数据备份和恢复

#2023-2-10 13:58:53

#######################################################

read -p "请输入账户名：" users

read -p "请输入账户密码：" psw

backup_dir1="/opt/mysql_backup/"

backup_dir2="/opt/mysql_backup_add/"

backup_log="/root/mysql_backup.log"

data_dir="/data/mysql/log"

dev_dir="/dev/null"

all_sql="/opt/mysql_backup/all.sql"

colour=(31 34)

result=("失败！！！" "成功！！！")

messages=("全库备份" "指定库完全备份" "指定库增量备份" "指定库完全备份恢复" "指定库增量备份恢复")

#######################################################

delimiter(){#定义分界符

echo -e "\033[32;1m＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\033[0m"

}

echoinfo(){#定义输入信息

echo -e "\033[35;1m#$1\033[0m"

}

echoresult(){#定义输出信息

echo -e "\033[$1;1m$2$3\033[0m"

}

if_test(){#定义if判断语句

if [ $? -eq 0 ];then

echoresult ${colour[1]} $1 ${result[1]}

else

echoresult ${colour[0]} $1 ${result[0]}

exit

fi

}

checkall_sql(){#检查是否存在全库备份文件

if [ ! -f $all_sql ];then

echo "$all_sql不存在，无需操作" &>> $backup_log

else

mv -f $all_sql /opt/all.sql.bak`date "+%Y%m%d%H%M%S"` &> $dev_dir

fi

sleep 2

}

check_dir1(){#判断完全备份目录是否存在

if [ ! -d $backup_dir1 ];then

mkdir $backup_dir1

elif [ -d $backup_dir1 ];then

echo "$backup_dir1目录已存在，请判断是否为空！！！" &>> $backup_log

if [ ! -z "$backup_dir1" ];then

tar -zcvf /opt/mysqlfull_`date "+%Y%m%d%H%M%S"`.tar.gz $backup_dir1 --remove-files &> $dev_dir

elif [ -z "$backup_dir1" ];then

echo "$backup_dir1目录为空,可以使用" &>> $backup_log

fi

fi

sleep 2

}

fullback(){#全库备份

delimiter | tee -a $backup_log

echoinfo ${messages[0]} | tee -a $backup_log

checkall_sql | tee -a $backup_log

mysqldump -u $users -p$psw -A > $all_sql &> $dev_dir#先做一次全库数据备份

sleep 2

if_test ${messages[0]} | tee -a $backup_log

}

dumpfulback(){#指定库完全备份

delimiter | tee -a $backup_log

echoinfo ${messages[1]} | tee -a $backup_log

mysqldump -u $users -p$psw --single-transaction --flush-logs -B test > $backup_dir1test_db_`date "+%Y%m%d%H%M%S"`.sql &> $dev_dir

num="$[$(cat $data_dir/mysql_bin.index | wc -l) -1]"

if [ ! -f $backup_dir1num.txt ];then

touch $backup_dir1num.txt &> $dev_dir

fi

echo "$num" > $backup_dir1num.txt

ls -l $data_dir/ &>> $backup_log

ls -l $backup_dir1 &>> $backup_log

sleep 2

if_test ${messages[1]} | tee -a $backup_log

}

check_dir2(){#判断增量备份目录是否存在

if [ ! -d $backup_dir2 ];then

mkdir $backup_dir2

elif [ -d $backup_dir2 ];then

if [ ! -z "$backup_dir2" ];then

tar -zcvf /opt/mysqladd_`date "+%Y%m%d%H%M%S"`.tar.gz $backup_dir2 --remove-files &> $dev_dir

elif [ -z "$backup_dir2" ];then

echo "$backup_dir2目录为空,可以使用" &>> $backup_log

fi

fi

sleep 2

}

backup_recover(){#备份及还原

#指定库增量备份

delimiter | tee -a $backup_log

echoinfo ${messages[2]} | tee -a $backup_log

check_dir2 | tee -a $backup_log

num="$(cat $backup_dir1num.txt)"

mysql -u $users -p$psw -e "insert into test.test1(id,name) values(100,"Tom"),(110,"Jerry"),(111,"Marry");" &> $dev_dir

mysql -u $users -p$psw -e "update test.test1 set id=888 where name="Marry";" &> $dev_dir

mysql -u $users -p$psw -e "drop database test;" &> $dev_dir

mysql -u $users -p$psw -e "flush logs;" &> $dev_dir

num1="$(cat $data_dir/mysql_bin.index | wc -l)"

num2=1

while [ $num2 -lt $num1 ]

do

if [ $num2 -gt $num1 ];then

binlogname="$(sed -n "${num2}p" $data_dir/mysql_bin.index)"

#binlogname="$data_dir/$(ls -lt $data_dir | grep -v "mysqld.log" | grep -v "mysql_bin.index" | grep -v "总用量" | awk '{print $9}' | sed -n "${num2}p")"

\cp -rf $binlogname $backup_dir2

if [ $? -eq 0 ];then

echo -e "\033[40;32m$binlogname增量备份成功\033[0m" &>> $backup_log

fi

fi

num2=$((num2+1))

done

num3=$[$(cat $data_dir/mysql_bin.index | wc -l) -1]

if [ ! -f $backup_dir1num.txt ];then

touch $backup_dir1num.txt

fi

echo "$num3" > $backup_dir1num.txt

sleep 2

if_test ${messages[2]} | tee -a $backup_log

#完全备份恢复

delimiter | tee -a $backup_log

echoinfo ${messages[3]} | tee -a $backup_log

test_sql="$(ls $backup_dir1 -lrt | sed -n "2p" | awk '{print $9}')"

mysql -u $users -p$psw -e "set sql_log_bin=0;" &> $dev_dir

mysql -u $users -p$psw < $backup_dir1$test_sql &> $dev_dir

mysql -u $users -p$psw -e "select * from test.test1;" &>> $backup_log

sleep 2

if_test ${messages[3]} | tee -a $backup_log

#增量备份恢复，需手动进行节点恢复

#delimiter | tee -a $backup_log

#echoinfo ${messages[4]} | tee -a $backup_log

#atnum="$(mysqlbinlog -v $binlogname | grep -in -A 13 -B 13 "drop" | awk /at/'{print $3}' | sed -n "1p")"

#mysqlbinlog --stop-position=$atnum $binlogname | mysql -u $users -p$psw &> $dev_dir

#mysql -u $users -p$psw -e "select * from test.test1;" &>> $backup_log

#sleep 2

#if_test ${messages[4]} | tee -a $backup_log

}

#######################################################

check_dir1

wait

checkall_sql

wait

fullback

wait

dumpfulback

wait

check_dir2

wait

backup_recover

wait

#######################################################