MySQL常用shell脚本

 

 

1. Rename database 重命名数据库
   

   #!/bin/env bash
   echo -e "\e[5mthe program is designed to rename database!\e[0m"
   read -p 'the source database: ' src
   read -p 'the destination database: ' dst
   
   echo -e "the source database: \e[7m$src\e[0m the destination datatabase: \e[7m$dst\e[0m do you want to continue? yes/no"
   read opt
   echo $opt
   if [[ $opt == "yes" ]];then
           mysql -e "create database if not exists $dst default character set utf8mb4 collate utf8mb4_general_ci"
           mysql --skip-column-names -e "select table_name from information_schema.tables where table_schema=\"$src\"" |\
    while read b;do
           printf 'rename \e[7m%-50s\e[0m to \e[7m%-50s\e[0m' $src.$b $dst.$b
           echo
           mysql -e "rename table $src.$b to $dst.$b"
           done
   fi
   mysql -e "drop database $src"

    

2. 批量重命名 character set & collation
   

   #!/bin/bash
   
   host=172.16.10.200
   user=root
   password='arbitrary0!'
   declare -a database
   database=(CJML_QxbEPC)
   
   function bacula() {
           #echo $1
           mysql -h"$host" -u$user --password=$password --skip-column-names -e "show full tables from $1 where table_type = 'BASE TABLE'" 2>/dev/null | awk '{print $1}' | while read b;do
                   #echo $b
                   #echo -e database $1 '\t\t' table "\e[7m$b\e[0m" '\t\t'convert to character set utf8mb4 collation utf8mb4_general_ci
                   printf '%s \e[5m%-15s\e[0m %s \e[7m%-30s\e[0m %s' database $1 table $b 'convert to character set utf8mb4 collation utf8mb4_general_ci'
                   echo
   
                   mysql -h$host -u$user --password=$password -e "set @@session.foreign_key_checks=0;alter table ${1}.$b convert to character set utf8mb4 collate utf8mb4_general_ci" 2>/dev/null;
           done
   }
   
   
   function main() {
           for b in ${database[@]};do
                   bacula $b
           done
   }
   
   main