在项目维护和开发中,经常需要知道两个数据库之间有哪些差异,如两个数据库中数据表有什么不同(表的名称,数量),相同的表字段是否相同(字段数目,约束,字段类型,大小等),有各种数据库管理工具可以实现这一点,但是别人的东西用起来总是没有自己的顺手(比如有的工具直接生成了修改的sql语句),鉴于此原因,决定自己写个简单的shell脚本来实现这个功能。
思想:按照库名从information_schema.TABLES里边查询两个库都有什么表,对比表名将不同的记录下来,相同的表名使用desc tableName来查看,对比每个字段,将不同的字段找出来。下面通过代码来进行说明。
#!/bin/sh
#第一个数据库的连接属性
DB01_NAME=testdb
DB01_HOST=192.168.72.129
DB01_PORT=3308
DB01_USER=root
DB01_PWD=12345678
#第二个数据库的连接属性
DB02_NAME=test
DB02_HOST=192.168.72.129
DB02_PORT=3308
DB02_USER=root
DB02_PWD=12345678
#临时文件目录
BASE_DIR=./tmp
DB01_FILE=${BASE_DIR}/db01_result.txt
DB02_FILE=${BASE_DIR}/db02_result.txt
RESULT_FILE=${BASE_DIR}/compare_result.txt
SAME_TABLES=${BASE_DIR}/same_tables.txt
#检查文件或者文件夹是否存在,如果不存在,则创建
# $1--需要检查的文件
function checkFileExists(){
if [ ! -e ${1} ]; then
parentDir=`dirname ${1}`
mkdir -p ${parentDir}
touch ${1}
fi
}
#通过数据库名称该库中所有表
# $1--数据库名称 $2--用户名 $3--密码
# $4--主机地址 $5--端口号 $6--查询结果输出文件
function getTablesByDbName(){
sql="select a.TABLE_NAME from information_schema.TABLES a where a.TABLE_SCHEMA='${1}'"
mysql -u${2} -p${3} -h${4} -P${5} -e"${sql}">${6}
#查询结果第一行是TABLE_NAME,此处需要删除
sed -i 1d ${6}
}
#读取某个文件中所有内容并且输出
# $1--需要读取的文件路径 $2--需要输出的文件路径
function readFileContent(){
while read line
do
echo ${line}>>${2}
done<${1}
}
#比较两个文件,输出不同内容,选择性记录相同内容
# $1--是否记录相同内容:yes/no $2--需要循环比对的基准文件 $3--需要被比对的文件
# $4--不同内容输出文件路径 $5--相同内容输出文件路径
function compareTwoFile(){
while read line
do
matchLine=`grep -i -w "${line}" ${3}`
if [[ -z ${matchLine} ]]; then
echo ${line}>>${4}
elif [[ ${matchLine} == ${line} && "yes" == ${1} ]]; then
existLine=`grep -i -w ${line} ${5}`
if [[ -z ${existLine} ]]; then
echo ${line}>>${5}
fi
fi
done<${2}
}
#按照库名和表名,查询某个表的表结构
# $1--库名 $2--表名 $3--用户名 $4--密码
# $5--主机 $6--端口号 $7--输出文件名
function queryTableInfo(){
sql="desc ${1}.${2}"
mysql -u${3} -p${4} -h${5} -P${6} -e"${sql}">${7}
}
function main(){
echo "开始比较..."
echo "开始查询${DB01_NAME}库和${DB02_NAME}库的所有表名..."
checkFileExists "${DB01_FILE}"
checkFileExists "${DB01_FILE}"
checkFileExists "${SAME_TABLES}"
checkFileExists "${RESULT_FILE}"
getTablesByDbName "${DB01_NAME}" "${DB01_USER}" "${DB01_PWD}" "${DB01_HOST}" "${DB01_PORT}" "${DB01_FILE}"
getTablesByDbName "${DB02_NAME}" "${DB02_USER}" "${DB02_PWD}" "${DB02_HOST}" "${DB02_PORT}" "${DB02_FILE}"
echo "开始找出${DB01_NAME}库和${DB02_NAME}库中不同的表以及相同的表..."
echo "##################">${RESULT_FILE}
echo "# 表名称对比结果 #">>${RESULT_FILE}
echo "##################">>${RESULT_FILE}
#如果某个库里边的表数量为0,则说明另外一个库中的表都是和这个库不同的表
db01_table_count=`wc -l ${DB01_FILE}|awk -F " " '{print $1}'`
db02_table_count=`wc -l ${DB02_FILE}|awk -F " " '{print $1}'`
if [[ ${db01_table_count} -eq 0 && ${db02_table_count} -gt 0 ]]; then
echo "以下表在${DB01_NAME}中不存在:">>${RESULT_FILE}
readFileContent "${DB02_FILE}" "${RESULT_FILE}"
elif [[ ${db02_table_count} -eq 0 && ${db01_table_count} -gt 0 ]]; then
echo "以下表在${DB02_NAME}中不存在:">>${RESULT_FILE}
readFileContent "${DB01_FILE}" "${RESULT_FILE}"
elif [[ ${db02_table_count} -eq 0 && ${db01_table_count} -eq 0 ]]; then
echo "数据库${DB01_NAME}和${DB02_NAME}中的表均为空"|tee ${RESULT_FILE}
else
echo "以下表在${DB02_NAME}中不存在:">>${RESULT_FILE}
compareTwoFile "yes" "${DB01_FILE}" "${DB02_FILE}" "${RESULT_FILE}" "${SAME_TABLES}"
echo "以下表在${DB01_NAME}中不存在:">>${RESULT_FILE}
compareTwoFile "yes" "${DB02_FILE}" "${DB01_FILE}" "${RESULT_FILE}" "${SAME_TABLES}"
fi
echo "开始对比相同表的表结构"
echo "##################">>${RESULT_FILE}
echo "# 表结构对比结果 #">>${RESULT_FILE}
echo "##################">>${RESULT_FILE}
while read line
do
tableName=${line}
queryTableInfo ${DB01_NAME} ${line} ${DB01_USER} ${DB01_PWD} ${DB01_HOST} ${DB01_PORT} ${DB01_FILE}
queryTableInfo ${DB02_NAME} ${line} ${DB02_USER} ${DB02_PWD} ${DB02_HOST} ${DB02_PORT} ${DB02_FILE}
echo "以下表字段及属性在${DB01_NAME}.${tableName}中是唯一的:">>${RESULT_FILE}
compareTwoFile "no" "${DB01_FILE}" "${DB02_FILE}" "${RESULT_FILE}"
echo "以下表字段及属性在${DB02_NAME}.${tableName}中是唯一的:">>${RESULT_FILE}
compareTwoFile "no" "${DB02_FILE}" "${DB01_FILE}" "${RESULT_FILE}"
done<${SAME_TABLES}
echo "比较完成,比较结果文件路径:${RESULT_FILE}"
}
main
运行时回显信息如下
按照提示路径找到compare_result.txt文件,比较结果如下:
这样,我们就可以看到两个库中那些表不存在,那些表表名相同,但是字段不同。