shell脚本实现mysql数据库表和表结构的对比

迷南。 2022-06-06 08:35 582阅读 0赞

在项目维护和开发中，经常需要知道两个数据库之间有哪些差异，如两个数据库中数据表有什么不同(表的名称，数量)，相同的表字段是否相同（字段数目，约束，字段类型，大小等），有各种数据库管理工具可以实现这一点，但是别人的东西用起来总是没有自己的顺手（比如有的工具直接生成了修改的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

运行时回显信息如下

![在这里插入图片描述][20190309220708284.png]  
按照提示路径找到compare\_result.txt文件，比较结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70]  
这样，我们就可以看到两个库中那些表不存在，那些表表名相同，但是字段不同。

[20190309220708284.png]: /images/20220606/c9fb6eb7b6044f9ab1f24b6382239fe6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70]: /images/20220606/0c7d215276764f9fb6e33296ce918fae.png