Mysql常用脚本

1. 批量删除数据库脚本

#!/bin/bash
CONN_INFO=rds_list
while read line
do
 
  HOST=`echo $line|awk -F: '{print $1}'`
  USER=`echo $line|awk -F: '{print $2}'`
  PASS=`echo $line|awk -F: '{print $3}'`
 
  DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;"  |grep  -Ev "__recycle_bin__|information_schema|mysql|performance_schema|sys")
 
  echo $DB_LIST
  for DB in $DB_LIST; do
    mysql -h$HOST -u$USER -p$PASS -s  -e "drop database $DB;"
    if [ $? -ne 0 ];then
        echo "实例:$HOST \n删除数据库:$DB 失败!" >> drop_err.log
        else
        echo "实例:$HOST \n删除数据库:$DB 成功!" >> drop_ok.log
    fi
  done
done < $CONN_INFO

rds_list 文件内容

rm-xxx:user:password

2. 数据库批量用户锁定

#!/bin/bash
[ ! -d rds_lock ] && mkdir rds_lock
while read line
do
        Host=`echo $line|awk -F: '{print $1}'`
        User=`echo $line|awk -F: '{print $2}'`
        Pw=`echo $line|awk -F: '{print $3}'`
        mysql -h$Host -u$User -p${Pw} -e "select concat('ALTER USER \'', user,'\'','@','\'',host,'\'  ACCOUNT LOCK;') as user_lock FROM mysql.user where user not in ('aliyun_root', 'replicator', 'mysql.session', 'mysql.sys', 'rds_load', 'root', 'aurora', 'aurora_proxy','qianyi');" > rds_lock/user_lock_${Host}.sql
        str="mysql  -h$Host -u$User -p${Pw} "
        echo $str
        sed -i '1d'  rds_lock/user_lock_${Host}.sql
        echo "$str <  rds_lock/user_lock_${Host}.sql" |bash
done < rds_ip_list

rds_ip_list文件内容

rm-xxx:user:password

3.数据库全量对象校验

#!/bin/bash
file=ip
 
#-----------------------------------------------------
function v1(){
echo "---------------------------------VIEW-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select TABLE_SCHEMA as "数据库名称",TABLE_NAME as "视图名称"
FROM  INFORMATION_SCHEMA.TABLES
WHERE table_type ='view'
and TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__')
group by TABLE_SCHEMA,TABLE_NAME;"
}
function func1(){
echo "---------------------------------FUNCTION-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select db as "数据库名称",name as "函数名称"
from mysql.proc
where db not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test')
and type = 'FUNCTION';"
}
function pro1(){
echo "-------------------------------- -PROCEDURE-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select db as "数据库名称",name as "存过名称"
from mysql.proc
where db not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test')
and type = 'PROCEDURE';"
}
 
function env1(){
echo "---------------------------------EVENT-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select  db as "数据库名称",name as "事件名称"
from  mysql.event
where db not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test'); "
}
 
 
function tr1(){
echo "---------------------------------TRIGGER-------------------------------------------"
mysql -h$1 -u$2 -p"$3"  -P $4 -e"
SELECT TRIGGER_SCHEMA as "数据库名称",TRIGGER_NAME as "触发器名称"
FROM information_schema.triggers
where TRIGGER_SCHEMA not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test') ;"
}
 
#-----------------------------------------目标端-------------------------------------------------
function v(){
echo "---------------------------------VIEW-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select TABLE_SCHEMA as "数据库名称",TABLE_NAME as "视图名称"
FROM  INFORMATION_SCHEMA.TABLES
WHERE table_type ='view'
and TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__')
group by TABLE_SCHEMA,TABLE_NAME;"
}
 
function func(){
echo "---------------------------------FUNCTION-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select db as "数据库名称",name as "函数名称"
from mysql.proc
where db not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test')
and type = 'FUNCTION';"
}
 
function pro(){
echo "-------------------------------- -PROCEDURE-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select db as "数据库名称",name as "存过名称"
from mysql.proc
where db not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test')
and type = 'PROCEDURE';"
}
 
function env(){
echo "---------------------------------EVENT-------------------------------------------"
mysql -h$1 -u$2 -p"$3" -P $4 -B -e"
select  db as "数据库名称",name as "事件名称"
from  mysql.event
where db not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test'); "
}
 
 
function tr(){
echo "---------------------------------TRIGGER-------------------------------------------"
mysql -h$1 -u$2 -p"$3"  -P $4 -e"
SELECT TRIGGER_SCHEMA as "数据库名称",TRIGGER_NAME as "触发器名称"
FROM information_schema.triggers
where TRIGGER_SCHEMA not in ('sys','mysql','information_schema','performance_schema','restore','__recycle_bin__','test') ;"
}
 
function diff(){
        grep -vwf $1 $2
}
file2=src
file3=dst
file4=diff
echo $file
[ ! -d $file2 -a ! -d $file3 -a ! -d $file4 ] && mkdir -p $file{2..4}
#mkdir -p $file{2..4}
while read line
do
        H=`echo $line|awk -F'[:/]' '{print $1}'`
        src_ip=`echo $line|awk -F'[:/]' '{print $2}'`
        src_user=`echo $line|awk -F'[:/]' '{print $3}'`
        src_pw=`echo $line|awk -F'[:/]' '{print $4}'`
        src_port=`echo $line|awk -F'[:/]' '{print $5}'`
#-----------------------------------------
        dst_ip=`echo $line|awk -F'[:/]' '{print $6}'`
        dst_user=`echo $line|awk -F'[:/]' '{print $7}'`
        dst_pw=`echo $line|awk -F'[:/]' '{print $8}'`
        dst_port=`echo $line|awk -F'[:/]' '{print $9}'`
        src_file=${file2}
        dst_file=${file3}
        for i in v1 func1 pro1 env1 tr1
        do
                $i $src_ip  $src_user $src_pw $src_port > ${src_file}/${H}-${i}
        done
#-----------------------------------------------
        for i in v func pro env tr
        do
                $i $dst_ip $dst_user $dst_pw $dst_port > ${dst_file}/${H}-${i}
        done
#-----------------------------------------------
        for i in v func pro env tr
        do
                j=${i}1
                diff  ${dst_file}/${H}-${i}  ${src_file}/${H}-${j}   > $file4/${H}-${i}
        done
 
done < $file

ip文件内容

实例名称:rm-xxxxxx:user:password:3306/rm-xxxxxx:user:password:3306

4. 数据库批量备份脚本

#!/bin/bash
CONN_INFO=rds_list
DATE=$(date +%F_%H-%M-%S)
while read line
do
  #CLUSTER=`echo $line|awk '{print $4}'|awk -F "#" '{print $2}'`
  #TENANT_TYPE=`echo $line|awk '{print $1}'`
  #TENANT_NAME=`echo $line|awk '{print $4}'|awk -F "@" '{print $2}'|awk -F "#" '{print $1}'`
  HOST=`echo $line|awk '{print $2}'|awk -F "-h" '{print $2}'`
  USER=`echo $line|awk '{print $3}'|awk -F "-u" '{print $2}'`
  PASS=`echo $line|awk '{print $4}'|awk -F "-p" '{print $2}'`
 
 
  BACKUP_DIR="/data/db_backup/$HOST"
  mkdir -p $BACKUP_DIR
  DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;"  |grep  -Ev "__recycle_bin__|Database|information_schema|mysql|performance_schema|sys")
 
  echo $DB_LIST
  for DB in $DB_LIST; do
    BACKUP_NAME=$BACKUP_DIR/${DB}_${DATE}.sql
    mysqldump -h$HOST -u$USER -p$PASS --single-transaction --set-gtid-purged=OFF  --databases $DB > $BACKUP_NAME ;
    if [ $? -ne 0 ];then
        echo "$BACKUP_NAME 备份失败!" >> back_err.log
    fi
  done
done < $CONN_INFO

rds_list 文件内容

rm-xxx:user:password