mysql备份脚本二
1.mysqldump备份方式
1.1 脚本全备到一个sql文件
# mysql_k8s-prod_bak-all-databases.sh
#!/bin/bash
##### by jwgod 2023
##### mysql_k8s-test mysql全库/单库备份
day=`date +'%Y-%m-%d'`
anynowtime="date +'%Y-%m-%d %H:%M:%S'"
NOW="echo [\`$anynowtime\`][PID:$$]"
##### 可在脚本开始运行时调用，打印当时的时间戳及PID。
#function job_start
job_start()
{
echo "`eval $NOW` job_start"
}
##### 可在脚本执行成功的逻辑处调用，打印当时的时间戳及PID。
job_success()
{
MSG="$*"
echo "`eval $NOW` job_success:[$MSG]"
exit 0
}
##### 可在脚本执行失败的逻辑分支处调用，打印当时的时间戳及PID。
job_fail()
{
MSG="$*"
echo "`eval $NOW` job_fail:[$MSG]"
exit 1
}
job_start
##### 可在此处开始编写您的脚本逻辑代码
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="xxxx"
mysql_host="192.168.1.xx"
mysql_port="3306"
mysql_charset="utf8mb4"
# 备份文件存放地址(根据实际情况填写)
#backup_location=/home/sql_bak/`date +%Y%m%d%H`
backup_location=/nfs_mysql_back/mysql_back/`date +%Y%m%d%H`
test -d "$backup_location" || mkdir -p $backup_location
# 是否删除过期数据
expire_backup_delete="ON"
expire_days=30
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
# 判断mysql实例是否正常运行
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR:MySQL is not running! backup stop!"
exit
else
echo $welcome_msg
fi
##### 删除30天之前备份
del_bak()
{
# 删除过期数据
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
`find $backup_location -type f -mtime +$expire_days | xargs rm -rf`
if [ $? -eq 0 ] ; then
echo "Expired backup data delete complete!"
job_success "$*"
else
job_fail "$*"
fi
fi
}
##### 全库备份
all_bak()
{
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --all-databases --single-transaction --quick --master-data > $backup_dir/mysql_all-databases_backup-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
# tar -zcPpf $backup_dir/mysql_$1_backup-$backup_time.zip --warning=no-file-changed --directory / $backup_dir
zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
echo "rm -rf $backup_dir/mysql_all-databases_backup-$backup_time.sql"
rm -rf $backup_dir/mysql_all-databases_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_all-databases-backup-$backup_time.sql"
job_success "$*"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
}
##### 单库备份
one_bak()
{
# 备份指定数据库中数据(此处假设数据库是test)
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
echo "rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql"
rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
job_success "$*"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
}
job_shell()
{
case "$1" in
0)
del_bak
;;
1)
all_bak
;;
test)
one_bak $1
;;
nacos_test)
one_bak $1
;;
xxl_job_test)
one_bak $1
;;
*)
echo "Usage: $0 {0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]}"
exit 1
esac
exit 0
}
all_bak
#one_bak
##### 作业平台中执行脚本成功和失败的标准只取决于脚本最后一条执行语句的返回值
##### 如果返回值为0，则认为此脚本执行成功，如果非0，则认为脚本执行失败
#read -r -p "请选择全库/单库备份：｛0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]｝" bak_sh
#job_shell "$bak_sh"1.2 脚本全备到每个库一个sql文件（定时任务）
# mysql_k8s-prod_bak-all.sh
#!/bin/bash
##### by jwgod 2023
##### mysql_k8s-test mysql全库/单库备份
day=`date +'%Y-%m-%d'`
anynowtime="date +'%Y-%m-%d %H:%M:%S'"
NOW="echo [\`$anynowtime\`][PID:$$]"
##### 可在脚本开始运行时调用，打印当时的时间戳及PID。
#function job_start
job_start()
{
echo "`eval $NOW` job_start"
}
##### 可在脚本执行成功的逻辑处调用，打印当时的时间戳及PID。
job_success()
{
MSG="$*"
echo "`eval $NOW` job_success:[$MSG]"
exit 0
}
##### 可在脚本执行失败的逻辑分支处调用，打印当时的时间戳及PID。
job_fail()
{
MSG="$*"
echo "`eval $NOW` job_fail:[$MSG]"
exit 1
}
job_start
##### 可在此处开始编写您的脚本逻辑代码
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="xxxxxx"
mysql_host="192.168.1.xx"
mysql_port="3306"
mysql_charset="utf8mb4"
# 备份文件存放地址(根据实际情况填写)
#backup_location=/home/sql_bak/`date +%Y%m%d%H`
backup_location=/nfs_mysql_back/mysql_back/`date +%Y%m%d%H`
test -d "$backup_location" || mkdir -p $backup_location
# 是否删除过期数据
expire_backup_delete="ON"
expire_days=30
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
# 判断mysql实例是否正常运行
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR:MySQL is not running! backup stop!"
exit
else
echo $welcome_msg
fi
##### 删除30天之前备份
del_bak()
{
# 删除过期数据
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
`find $backup_location -type f -mtime +$expire_days | xargs rm -rf`
if [ $? -eq 0 ] ; then
echo "Expired backup data delete complete!"
job_success "$*"
else
job_fail "$*"
fi
fi
}
##### 全库备份
all_bak()
{
# 备份的数据库数组
db_arr=$(echo "show databases;" | mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password |grep -v "sys"|grep -v "Database"|grep -v "information_schema" |grep -v "performance_schema")
echo "show databases： $db_arr"
# 不需要备份的单例数据库
nodeldb="test"
# 循环备份
for dbname in ${db_arr}
do
if [ $dbname != $nodeldb ]; then
echo "mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
# tar打包所有的sql文件
#tar -zcPpf $backup_dir/mysql_${dbname}_backup-$backup_time.zip --warning=no-file-changed --directory / $backup_dir
zip -jr $backup_dir/mysql_${dbname}_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
rm -rf $backup_dir/mysql_${dbname}_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
MSG="$*"
echo "`eval $NOW` job_success:[$MSG]"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
fi
done
}
##### 单库备份
one_bak()
{
# 备份指定数据库中数据(此处假设数据库是test)
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
echo "rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql"
rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
job_success "$*"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
}
job_shell()
{
case "$1" in
0)
del_bak
;;
1)
all_bak
;;
test)
one_bak $1
;;
nacos_test)
one_bak $1
;;
xxl_job_test)
one_bak $1
;;
*)
echo "Usage: $0 {0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]}"
exit 1
esac
exit 0
}
all_bak
#one_bak
##### 作业平台中执行脚本成功和失败的标准只取决于脚本最后一条执行语句的返回值
##### 如果返回值为0，则认为此脚本执行成功，如果非0，则认为脚本执行失败
#read -r -p "请选择全库/单库备份：｛0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]｝" bak_sh
#job_shell "$bak_sh"1.3 备份库时排除部分表
#!/bin/bash
##### by jw 20230928
##### mysql_k8s-test mysql全库/单库备份
day=`date +'%Y-%m-%d'`
anynowtime="date +'%Y-%m-%d %H:%M:%S'"
NOW="echo [\`$anynowtime\`][PID:$$]"
##### 可在脚本开始运行时调用，打印当时的时间戳及PID。
#function job_start
job_start()
{
echo "`eval $NOW` job_start"
}
##### 可在脚本执行成功的逻辑处调用，打印当时的时间戳及PID。
job_success()
{
MSG="$*"
echo "`eval $NOW` job_success:[$MSG]"
exit 0
}
##### 可在脚本执行失败的逻辑分支处调用，打印当时的时间戳及PID。
job_fail()
{
MSG="$*"
echo "`eval $NOW` job_fail:[$MSG]"
exit 1
}
job_start
##### 可在此处开始编写您的脚本逻辑代码
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="xxxxxx"
mysql_host="192.168.1.xx"
mysql_port="3306"
mysql_charset="utf8mb4"
# 排除多个表
excluded_tables=("info_t_temp" "info_t_temp1")
# 备份文件存放地址(根据实际情况填写)
backup_location=/home/sql_bak/`date +%Y%m%d%H`
test -d "$backup_location" || mkdir -p $backup_location
# 是否删除过期数据
expire_backup_delete="ON"
expire_days=30
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
# 判断mysql实例是否正常运行
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR:MySQL is not running! backup stop!"
exit
else
echo $welcome_msg
fi
##### 删除30天之前备份
del_bak()
{
# 删除过期数据
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
`find $backup_location -type f -mtime +$expire_days | xargs rm -rf`
if [ $? -eq 0 ] ; then
echo "Expired backup data delete complete!"
job_success "$*"
else
job_fail "$*"
fi
fi
}
##### 全库备份
all_bak()
{
# 备份的数据库数组
db_arr=$(echo "show databases;" | mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password |grep -v "sys"|grep -v "Database"|grep -v "information_schema" |grep -v "performance_schema")
echo "show databases： $db_arr"
# 不需要备份的单例数据库
nodeldb="test"
# 循环备份
for dbname in ${db_arr}
do
if [ $dbname != $nodeldb ]; then
if [ $dbname == "bright_member_center_test_data" ]; then
# 循环排除表
command="mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data"
for table in "${excluded_tables[@]}"
do
command+=" --ignore-table=$dbname.$table"
done
command+=" > $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
echo "$command"
eval $command
else
#echo "mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql
flag=`echo $?`
fi
if [ $flag == "0" ];then
# tar打包所有的sql文件
#tar -zcPpf $backup_dir/mysql_${dbname}_backup-$backup_time.zip --warning=no-file-changed --directory / $backup_dir
zip -jr $backup_dir/mysql_${dbname}_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
rm -rf $backup_dir/mysql_${dbname}_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
MSG="$*"
echo "`eval $NOW` job_success:[$MSG]"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
fi
done
}
##### 单库备份
one_bak()
{
# 备份指定数据库中数据(此处假设数据库是test)
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
echo "rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql"
rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
job_success "$*"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
}
job_shell()
{
case "$1" in
0)
del_bak
;;
1)
all_bak
;;
test)
one_bak $1
;;
nacos_test)
one_bak $1
;;
xxl_job_test)
one_bak $1
;;
*)
echo "Usage: $0 {0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]}"
exit 1
esac
exit 0
}
all_bak
#one_bak
##### 作业平台中执行脚本成功和失败的标准只取决于脚本最后一条执行语句的返回值
##### 如果返回值为0，则认为此脚本执行成功，如果非0，则认为脚本执行失败
#read -r -p "请选择全库/单库备份：｛0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]｝" bak_sh
#job_shell "$bak_sh"
2.strabackup方式
2.1 strabackup_db备份库
# strabackup_db.sh
#!/bin/bash
DB_NAME=erpp
DB_USER=root
DB_PASSWD=xxxxx
DB_BAK_DIR=/data/db_strabackup/
DB_BAK_DATE=`date +%Y%m%d`
DB_cnf=/etc/my.cnf.d/server.cnf
#DB_DATABASES="tongxun"
#DB_DATABASES="test1"
FULL_DB_BAK=full-db-${DB_BAK_DATE}
FULL_OLD_BAK=full-db-` date +%Y%m%d -d '-10 day'`
FULL_DB_TAR=full-db-${DB_BAK_DATE}.tar.gz
LOG=$(cd "$(dirname "${BASH_SOURCE[0]}")"; pwd)/"$(basename ${BASH_SOURCE[0]})".log
ulimit -n 65535
function mysql_bak(){
echo "------------`date +%y%m%d.%H%M%S` $0 begin ... ">>${LOG}
echo "............." >>${LOG}
rm -rf ${DB_BAK_DIR}${FULL_OLD_BAK}
/usr/bin/innobackupex --defaults-file=${DB_cnf} --user=${DB_USER} --password=${DB_PASSWD} --no-timestamp ${DB_BAK_DIR}${FULL_DB_BAK}
#/usr/bin/innobackupex --defaults-file=${DB_cnf} --stream=tar --user=${DB_USER} --password=${DB_PASSWD} --databases="tongxun" --no-timestamp ${DB_BAK_DIR}${FULL_DB_BAK} |gzip > ${DB_BAK_DIR}${FULL_DB_TAR}
echo "------------`date +%y%m%d.%H%M%S` $0 end ... ">>${LOG}
}
mysql_bak2.2 strabackup_db_recovery 恢复
# strabackup_db_recovery.sh
#!/bin/bash
DB_NAME=erpp
DB_USER=root
DB_PASSWD=xxxx
DB_BAK_DIR=/home/db_strabackup/
SCP_DB_BAK_DIR=/data/db_strabackup/
DB_BAK_DATE=`date +%Y%m%d`
DB_cnf=/etc/my.cnf.d/server.cnf
#DB_DATABASES="erpp group supply tongxun"
#DB_DATABASES="test1"
#FULL_DB_BAK=full-db-20210526
FULL_DB_BAK=full-db-${DB_BAK_DATE}
FULL_OLD_BAK=full-db-` date +%Y%m%d -d '-1 day'`
#FULL_OLD_BAK=full-db-` date +%Y%m%d -d '-3 day'`.tar.gz
FULL_DB_TAR=full-db-${DB_BAK_DATE}.tar.gz
LOG=$(cd "$(dirname "${BASH_SOURCE[0]}")"; pwd)/"$(basename ${BASH_SOURCE[0]})".log
ulimit -n 65535
function mysql_recovery(){
echo "------------`date +%y%m%d.%H%M%S` $0 begin ... ">>${LOG}
echo "............" >>${LOG}
#rm -rf ${DB_BAK_DIR}${FULL_OLD_BAK}
rm -rf ${DB_BAK_DIR}full-db-*
scp -r -P22 192.168.2.12:${SCP_DB_BAK_DIR}${FULL_DB_BAK}/ ${DB_BAK_DIR}
if [ $? == 0 ]; then
## tar xzf ${DB_BAK_DIR}${FULL_DB_TAR} -C ${DB_BAK_DIR}
systemctl stop mariadb
rm -rf /maxec/mysql_data/*
innobackupex --apply-log ${DB_BAK_DIR}${FULL_DB_BAK}
innobackupex --defaults-file=${DB_cnf} --copy-back ${DB_BAK_DIR}${FULL_DB_BAK}
chown -R mysql.mysql /maxec/mysql_data/
systemctl restart mariadb
## find ${DB_BAK_DIR} -type d -name "${FULL_DB_BAK}" |grep -v "tar.gz" |xargs rm -rf {} \;
## rm -rf ${DB_BAK_DIR}${FULL_OLD_BAK}
echo "------------`date +%y%m%d.%H%M%S` $0 end ... ">>${LOG}
fi
}
mysql_recovery
3. 表数据同步
3.1 同步A库查询到的sql导入到B库price_tag
#!/bin/bash
DB_NAME=erp
DB_USER=erp
DB_PASSWD=erpxxxxx
DB_SOURCE_HOST=192.168.2.12
DB_SOURCE_PORT=3306
QUERY_SQL=source_query_old.sql
QUERY_RESULT="source_result.sql"
DB_USER_44=food
DB_PASSWD_44=foodxxxx
DB_TARGET_HOST=192.168.2.44
DB_TARGET_PORT=3306
TABLE=price_tag
INSERT_SQL="truncate table ${TABLE}; load data infile '/data/source_result.sql' into table ${TABLE} character set utf8;"
cd /data
echo "------------`date +%y%m%d.%H%M%S` $0 begin ... ">> sync_log.log
mysql --user=${DB_USER} --password=${DB_PASSWD} --host=${DB_SOURCE_HOST} --port=${DB_SOURCE_PORT} <${QUERY_SQL} -N >${QUERY_RESULT}
scp ${QUERY_RESULT} ${DB_TARGET_HOST}':/data/'${QUERY_RESULT}
mysql --user=${DB_USER_44} --password=${DB_PASSWD_44} --host=${DB_TARGET_HOST} --port=${DB_TARGET_PORT} --local-infile=1 ${DB_NAME} -e "${INSERT_SQL}"
echo "------------`date +%y%m%d.%H%M%S` $0 end ... ">> sync_log.log3.2 同步A库sys_job导入到B库sys_job
#!/bin/bash
DB_NAME=sys_job
# A库
A_DB_USER=root
A_DB_PASSWD=xxxxx
A_DB_HOST=192.168.2.12
A_DB_PORT=3306
# B库
B_DB_NAME=sys_job
B_DB_USER=root
B_DB_PASSWD=xxxxx
B_DB_HOST=192.168.2.44
B_DB_PORT=3306
echo "------------`date +%y%m%d.%H%M%S` $0 begin ... ">>/tmp/sync_sys_job_tables-v1
mysqldump --user=${A_DB_USER} --password=${A_DB_PASSWD} --host=${A_DB_HOST} --port=${A_DB_PORT} ${DB_NAME} |mysql --user=${B_DB_USER} --password=${B_DB_PASSWD} --host=${B_DB_HOST} --port=${B_DB_PORT} ${DB_NAME}
echo "------------`date +%y%m%d.%H%M%S` $0 end ... ">>/tmp/sync_sys_job_tables-v1
echo "################################## ">>/tmp/sync_sys_job_tables-v1本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 悩姜！
