mysql导数据比对原库表结构

1 写sell脚本通过新库原库同时和生成表结构文件并通过diff命令比对两个文件差异

# cat checkdata.sh 
#!/bin/sh

FILE=/tmp/check.sql
CKSQL="select concat('select \"', table_name, '\", count(*) from ', TABLE_SCHEMA, '.',table_name,';') from information_schema.tables where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys');"

#第一个数据库的连接属性（源端）
DB01_HOST=10.10.10.151
DB01_PORT=3306
DB01_USER=root
SRC_PWD='rootmysql'
SRC_FILE=/tmp/src.txt

#第二个数据库的连接属性（目标端）
DB02_HOST=10.10.10.153
DB02_PORT=3306
DB02_USER=root
DEST_PWD='rootmysql'
DEST_FILE=/tmp/dest.txt

#从源端生成查询表记录语句
echo "======= start src db count ========"
mysql -h ${DB01_HOST} -u ${DB01_USER} -p${SRC_PWD} -N -s -e "$CKSQL"  > $FILE 2>&1;

#删除第一行
sed -i "1d" $FILE

#源端执行查询表记录语句
echo "======= start dest db count ========"
mysql -h ${DB01_HOST} -u ${DB01_USER} -p${SRC_PWD} -N -s -e "source $FILE" > $SRC_FILE 2>&1;

#目标端执行查询表记录语句
mysql -h ${DB02_HOST} -u ${DB02_USER} -p${DEST_PWD} -N -s -e "source $FILE" > $DEST_FILE 2>&1;

echo "======= start diff file ========"
diff ${SRC_FILE} ${DEST_FILE}
if [ $? -eq 0 ];then
    echo "file all same!"
else
    echo "file have different!!!"
fi

2 通过查询系统表（如 information_schema）中获取含有主键(PRIMARY KEY)的表

SELECT t.table_schema,
       t.table_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='your_database_name';  -- 替换为你的数据库名

3 查询含有外键（FOREIGN KEY）的表

 SELECT   
    TABLE_NAME AS 'Table Name',  
    COLUMN_NAME AS 'Column Name',  
    CONSTRAINT_NAME AS 'Constraint Name',  
    REFERENCED_TABLE_NAME AS 'Referenced Table',  
    REFERENCED_COLUMN_NAME AS 'Referenced Column'  
FROM   
    information_schema.KEY_COLUMN_USAGE  
JOIN   
    information_schema.TABLE_CONSTRAINTS   
ON   
    information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME = information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME  
WHERE   
    information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'  
    AND information_schema.KEY_COLUMN_USAGE.TABLE_SCHEMA = 'your_database_name';  -- 替换为你的数据库名