2024-01-12 09:39:13
浏览数 (1)
# 过滤某些表数据导出
代码语言：javascript
复制#!/bin/bash
MYSQL='/usr/bin/mysql'
MYSQL_DUMP='/usr/bin/mysqldump'
BACKUP_DIR='/tmp/scripts'
MYSQL_HOST='地址'
MYSQL_USER=账号
MYSQL_PWD=密码
MYSQL_PORT=3306
DATE=$(date %Y%m%d)
#参数说明
#mysqldump --skip-add-drop-table --no-create-info只备份数据而不包含表结构，不包含删除表的指令
#1. 获取所有表
#2. 遍历使用mysqldump获取数据，遍历过程判断哪些表数据不需要
# test11主库导出
TABLES=$(mysql -u${MYSQL_USER} -h${MYSQL_HOST} -P${MYSQL_PORT} -p${MYSQL_PWD} -D test11 -e "show tables;" | awk '{print $1}' | grep -v '^Tables')
for table in $TABLES
do
#if [[ " ${IGNIOR_TABLES[@]} " =~ " ${table} " ]];then
# echo "$table don't need to be backup"
# continue
#else
echo "use test11;" >> test11_data.sql
mysqldump -u ${MYSQL_USER} -h${MYSQL_HOST} -P${MYSQL_PORT} -p${MYSQL_PWD} test11 $table --skip-add-drop-table --no-create-info >> test11_data.sql
echo "Backup $table successful"
#fi
done
# 不导出的表
declare -a IGNIOR_TABLES=("user11" "user22" "ueser33")
#遍历所有co数据库
DB_LIST=$($MYSQL -u${MYSQL_USER} -p${MYSQL_PWD} -P${MYSQL_PORT} -h${MYSQL_HOST} -e "show databases;" | awk -F '|' '/co/{print $1}'|xargs)
for DB_NAME in ${DB_LIST};do
echo "Starting backup DB: ${DB_NAME}"
#1. 遍历分库，导出所有表数据
CO_TABLES=$(mysql -u${MYSQL_USER} -h${MYSQL_HOST} -P${MYSQL_PORT} -p${MYSQL_PWD} -D ${DB_NAME} -e "show tables;" | awk '{print $1}' | grep -v '^Tables')
echo "use $DB_NAME;" >> ${DB_NAME}.sql
for table in ${CO_TABLES[@]};do
#检查当前表是否在排除外
skip_table=false
for IGNIOR_TABLE in "${IGNIOR_TABLES[@]}";do
if [ "$table" = "$IGNIOR_TABLE" ];then
skip_table=true
break
fi
done
#如果表在排除列表中，则跳过导出
if [ "$skip_table" = true ];then
echo "跳过表$table"
else
#echo "truncate $table" >> ${DB_NAME}.sql
mysqldump -u $MYSQL_USER -h${MYSQL_HOST} -P${MYSQL_PORT} -p${MYSQL_PWD} ${DB_NAME} $table --skip-add-drop-table --no-create-info >> ${DB_NAME}.sql
echo "Backup $table successful"
fi
done
echo "Done ${DB_NAME}!"
done
# 清理一些带日期的表
代码语言：javascript
复制#!/bin/bash
# MySQL数据库连接信息
MYSQL_USER="账号"
MYSQL_PASSWORD="密码"
MYSQL_HOST="地址"
MYSQL_PORT="端口"
MYSQL_DATABASE="数据库"
# 清除 user11_ 日期的所有数据【2023年以前】
DELETE_QUERY="select table_name from information_schema.tables where table_schema = '数据库' and table_name like 'user11_%' and substring(table_name, -8) <= '20221231';"
TABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -N -e "$DELETE_QUERY")
#执行删除操作
for TABLE in $TABLES
do
DELETE_QUERY="TRUNCATE TABLE $TABLE"
echo $DELETE_QUERY
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -D$MYSQL_DATABASE -e "$DELETE_QUERY"
done
# 备份一些表数据，清理一些表数据
代码语言：javascript
复制#!/bin/bash
DB_USER="账号"
DB_PASS="密码"
DB_NAME="数据库"
DB_HOST="地址"
#备份表
backup_table(){
table_names=("user11" "user22")
for table_name in "${table_names[@]}";do
backup_file="$table_name-$(date '%Y%m%d%H%M%S').sql"
mysqldump -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" "$table_name" > "$backup_file"
if [ $? -eq 0 ];then
echo "备份表 $table_name 成功，备份文件名：$backup_file"
else
echo "备份表 $table_name 失败"
fi
done
}
#清空表数据
clear_table(){
table_names=("user11" "user22")
for table_name in "${table_names[@]}";do
mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "truncate table $table_name" "$DB_NAME"
if [ $? -eq 0 ];then
echo "清除表 $table_name 成功"
else
echo "清除表 $table_name 失败"
fi
done
}
backup_table
clear_table
