Shell脚本录
对比两个数据库索引差别:
select new_table_name, new_columu_name, new_index_name, case new_non_unique when '0' then 'yes' when '1' then 'no' end, new_index_type
from(select
table_name as new_table_name, column_name as new_columu_name, group_concat(index_name) as new_index_name, group_concat(non_unique) as new_non_unique, group_concat(index_type) as new_index_type
from
information_schema.statistics
where
table_schema = '${ORIGINDATABASE}' and index_name <> 'PRIMARY'
group by
new_table_name, new_columu_name) new
left join (
select
table_name as old_table_name, column_name as old_columu_name, group_concat(index_name) as old_index_name, group_concat(non_unique) as old_non_unique, group_concat(index_type) as old_index_type
from
information_schema.statistics
where
table_schema = '${TARGETDATABASE}' and index_name <> 'PRIMARY'
group by
old_table_name, old_columu_name) old
on new.new_table_name = old.old_table_name and new.new_columu_name = old.old_columu_name
where old.old_index_name is null
#脚本连接Mysql执行sql语句并将结果输出到文件中
${MYSQL} -h${HOSTNAME} -P${POST} -u${USERNAME} -p${PASSWORD} -s -e"sql语句" > filename
#将${PATH}/${OUTSQL}路径的文件导入mysql
PATH="`pwd`"
${MYSQL} -h${HOSTNAME} -P${POST} -u${USERNAME} -p${PASSWORD} <${PATH}/${OUTSQL}
#多行注释
:<<!
#被注释的内容
!
更新旧数据库的索引,需要新数据库的索引结构文件
#!/bin/bash
MYSQL="/usr/bin/mysql" #mysql 程序
HOSTNAME="172.16.0.24" #mysql 主机名
POST="3306" #mysql 端口
USERNAME="root"
PASSWORD="root"
DATABASE="eds_manager_update_index" #待更新数据库
TARGETINDEX="newIndex.txt" #最新的索引结构文件
INDEX="index.txt" #待更新数据库的索引结构文件
OUTDIFFTXT="diff.txt" #输出差异文件
OUTSQL="update.sql" #可执行sql脚本文件
if [[ -e `pwd`/${OUTDIFFTXT} ]]
then
echo `pwd`/${OUTDIFFTXT}文件已存在
exit
fi
if [[ -e `pwd`/${OUTSQL} ]]
then
echo `pwd`/${OUTSQL}文件已存在
exit
fi
# 生成待更新数据库的索引结构文件
${MYSQL} -h${HOSTNAME} -P${POST} -u${USERNAME} -p${PASSWORD} -s -e"select
table_name, column_name, group_concat(index_name) as index_name, case group_concat(non_unique) when '1' then 'no' when '0' then 'yes' end as non_unique, group_concat(index_type) as index_type
from
information_schema.statistics
where
table_schema = '${DATABASE}' and index_name <> 'PRIMARY'
group by
table_name,column_name" > ${INDEX}
# 生成差异文件
while read line
do
array=($line)
a=`grep -w ${array[0]} ${INDEX}| grep -cw ${array[1]}`
if [[ ${a} -eq 0 ]]
then
echo $line >> ${OUTDIFFTXT}
fi
done < ${TARGETINDEX}
if [[ ! -e `pwd`/${OUTDIFFTXT} ]]
then
echo ${DATABASE}索引已是最新
exit
fi
declare -a prev
tableName=""
indexName=""
column=""
index=0
# 根据差异文件生成可执行的sql语句
while read line
do
cur=(${line})
if [[ ${tableName} == ${cur[0]} ]] && [[ ${indexName} == ${cur[2]} ]]
then
column="${column}, \`${cur[1]}\`"
else
if [[ ${index} -ne 0 ]]
then
if [[ ${prev[3]} == ${UNI} ]]
then
echo alter table \`${DATABASE}\`.\`${prev[0]}\` add unique index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
else
echo alter table \`${DATABASE}\`.\`${prev[0]}\` add index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
fi
else
let "index+=1"
fi
column="\`${cur[1]}\`"
fi
tableName="${cur[0]}"
indexName="${cur[2]}"
prev=(${line})
done < ${OUTDIFFTXT}
if [[ ${prev[3]} == ${UNI} ]]
then
echo alter table \`${DATABASE}\`.\`${prev[0]}\` add unique index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
else
echo alter table \`${DATABASE}\`.\`${prev[0]}\` add index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
fi
#默认直接执行差异索引的sql文件
${MYSQL} -h${HOSTNAME} -P${POST} -u${USERNAME} -p${PASSWORD} <`pwd`/${OUTSQL}
对比一个服务器下的两个数据库的索引结构,更新旧数据库
#!/bin/bash
HOSTNAME="172.16.0.24" #Mysql服务器主机名/IP
POST="3306" #Mysql 端口
USERNAME="root" #Mysql用户名
PASSWORD="root" #Mysql用户密码
MYSQL="/usr/bin/mysql" #Mysql程序
PATH="`pwd`"
ORIGINDATABASE="eds_manager_init_index" #源数据库
TARGETDATABASE="eds_manager_update_index" #目标数据库
PATH="`pwd`" #文件路径
OUTDIFFTXT="diff.txt" #对比差异文件
OUTSQL="update.sql" #可执行脚本
UNI="yes" #判断是否是唯一索引
declare -a prev #文件前一行的内容
tableName=""
indexName=""
column=""
index=0
if [[ -e ${PATH}/${OUTDIFFTXT} ]]
then
echo ${PATH}/${OUTDIFFTXT}文件已存在
exit
fi
if [[ -e ${PATH}/${OUTSQL} ]]
then
echo ${PATH}/${OUTSQL}文件已存在
exit
fi
${MYSQL} -h${HOSTNAME} -P${POST} -u${USERNAME} -p${PASSWORD} -s -e"select new_table_name, new_columu_name, new_index_name, case new_non_unique when '0' then 'yes' when '1' then 'no' end, new_index_type
from(select
table_name as new_table_name, column_name as new_columu_name, group_concat(index_name) as new_index_name, group_concat(non_unique) as new_non_unique, group_concat(index_type) as new_index_type
from
information_schema.statistics
where
table_schema = '${ORIGINDATABASE}' and index_name <> 'PRIMARY'
group by
new_table_name, new_columu_name) new
left join (
select
table_name as old_table_name, column_name as old_columu_name, group_concat(index_name) as old_index_name, group_concat(non_unique) as old_non_unique, group_concat(index_type) as old_index_type
from
information_schema.statistics
where
table_schema = '${TARGETDATABASE}' and index_name <> 'PRIMARY'
group by
old_table_name, old_columu_name) old
on new.new_table_name = old.old_table_name and new.new_columu_name = old.old_columu_name
where old.old_index_name is null" > ${OUTDIFFTXT}
while read line
do
cur=(${line})
if [[ ${tableName} == ${cur[0]} ]] && [[ ${indexName} == ${cur[2]} ]]
then
column="${column}, \`${cur[1]}\`"
else
if [[ ${index} -ne 0 ]]
then
if [[ ${prev[3]} == ${UNI} ]]
then
echo alter table \`${TARGETDATABASE}\`.\`${prev[0]}\` add unique index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
else
echo alter table \`${TARGETDATABASE}\`.\`${prev[0]}\` add index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
fi
else
let "index+=1"
fi
column="\`${cur[1]}\`"
fi
tableName="${cur[0]}"
indexName="${cur[2]}"
prev=(${line})
done < ${OUTDIFFTXT}
if [[ ${prev[3]} == ${UNI} ]]
then
echo alter table \`${TARGETDATABASE}\`.\`${prev[0]}\` add unique index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
else
echo alter table \`${TARGETDATABASE}\`.\`${prev[0]}\` add index \`${prev[2]}\`\(${column}\) using ${prev[4]}\; >> ${OUTSQL}
fi
#默认直接执行差异索引的sql文件
#若想自定义执行,注释下面这行语句再运行脚本
#之后查看${PATH}/${OUTSQL}这个路径的脚本文件自主选择
#最后进入mysql 执行 source ${PATH}/${OUTSQL} 语句
${MYSQL} -h${HOSTNAME} -P${POST} -u${USERNAME} -p${PASSWORD} <${PATH}/${OUTSQL}
注意事项 :
-
if 语句第二个括号要与语句之间保持一个空格
-
pwd 要在 grep 后面出现,不然会出现grep command not found
-
`要用反斜杠
-
cat /dev/null > filename 清空文件
-
touch filename 创建文件 或者 vim filename
-
运行前用chmod 777 filename.sh 给权限 然后在用./filename.sh
-
允许用户root从ip为172.16.x.xx的主机连接到mysql服务器,并使用root作为密码
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.x.xx' IDENTIFIED BY 'root' WITH GRANT OPTION; FLUSH PRIVILEGES;