MySQL常用shell脚本

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

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