mysql常用的脚本集合

#!/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

#!/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

#!/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