For a single MySQL table and a batch table conversion engine script convert_engine.sh

The company's recent MySQL always has a large number of lock tables. After analysis, it basically uses the MYISAM Table engine. A large number of reads and writes to a table by MYISAM will cause the entire MySQL table to be locked, as a result, dynamic content cannot read data in a timely manner, which has a huge impact on the user experience. The principle of INNODB is to lock a single row record (row lock) of the table, without affecting other row records in the same table. And write the following SHELL script to convert a single table and the whole database engine...

#! /Bin/sh

# Arg1:-d dbname
# Arg2:-t [tables]
# Arg3:-e engine type (myisam | innodb)

User = "root"
Pwd = "666666"
MYSQLbin = "/usr/local/mysql/bin/mysql-u $ User-p $ Pwd-e"
TmpFile = '/tmp/table. tmp'
Usage ()
{
Echo "Usage (): $0-d dbname [-t tbname]-e engine (myisam | innodb )"
}
If [$ #-eq 0]; then
Usage
Exit 1
Fi
While getopts d: t: e: h OPTION
Do
Case $ OPTION in
D)
{
DBName = $ OPTARG
DBExists = '$ MYSQLbin "show databases;" | grep "$ DBName "'
If ["$ DBExists" = ""]; then
Echo "$ DBName database not exists! "
Exit 1
Fi
};;
T)
{
TBName = $ OPTARG
TBExists = '$ MYSQLbin "use $ DBName; show tables" | grep $ tbname'
If ["$ TBExists" = ""]; then
Echo "$ TBName table not exists! "
Exit 1
Fi
};;
E)
{
EngineName = 'echo $ OPTARG | tr A-Z a-Z'
If ["$ EngineName "! = "Myisam"] & ["$ EngineName "! = "Innodb"]; then
Usage
Echo "Engine $ EngineName is no exists! "
Exit 1
Fi
};;
? | H)
Usage
Exit 0
;;
Esac
Done

If ["$ EngineName" = ""]; then
Usage
Echo "Lose '-e (innodb | myisam )'! "
Exit 1
Fi

If ["$ TBName "! = ""]; Then
CurrentEngine = '$ MYSQLbin "use $ DBName; show table status like' $ tbname' \ G" | grep Engine | awk '{print $2}' | tr A-Z a-Z'
If ["$ CurrentEngine" = "$ EngineName"]; then
Echo-e "\ 033 [31 m Current Table $ TBName is already of type $ EngineName; Ignored! \ 033 [0 m"
Exit 0
Fi
$ MYSQLbin "use $ DBName; alter table $ TBName engine = $ EngineName"
Else
$ MYSQLbin "use $ DBName; show tables" | sed 1d> $ TmpFile
While read Table
Do
CurrentEngine = '$ MYSQLbin "use $ DBName; show table status like' $ table' \ G" | grep Engine | awk '{print $2}' | tr A-Z a-Z'
If ["$ CurrentEngine" = "$ EngineName"]; then
Echo-e "\ 033 [31 m Current Table $ Table is already of type $ EngineName; Ignored! \ 033 [0 m"
Else
$ MYSQLbin "use $ DBName; alter table $ Table engine = $ EngineName ;"
Echo-e "\ 033 [32 m $ DBName Table $ Table Convert $ EngineName is Sucessfull! \ 033 [0 m"
# Continue
Fi
Done <$ TmpFile
Fi