mysqldiff该工具是官方mysql-utilities工具集的一个脚本,可以用来对比不同数据库之间的表结构,或者同个数据库间的表结构
如果在windows下,直接下载mysql-utilities安装就可以了,然后运行后,会跑到命令行下:1) 基本用法
mysqldiff --server1=admin:123456@192.168.79.10 --server2=admin:123456@192.168.79.11 --difftype=differ test:test
这样就比较两个不同库中的数据库test,假设两个库中库名都是test 也可以比较不同的表mysqldiff --server1=user@host1 --server2=user@host2 testdb.table1:anotherdb.anothertable
就是使用库名.表名的方式就可以了
下面是出来的一个效果# mysqldiff --force --server1=root:msandbox@127.0.0.1:21489 --server2=root:msandbox@127.0.0.1:21490 employees:employees# WARNING: Using a password on the command line interface can be insecure.# server1 on 127.0.0.1: ... connected.# server2 on 127.0.0.1: ... connected.# Comparing `employees` to `employees`
[PASS]# Comparing `employees`.`departments` to `employees`.`departments`
[FAIL]# Object definitions differ. (--changes-for=server1)#--- `employees`.`departments`+++ `employees`.`departments`@@ -1,6 +1,6 @@ CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,-
`dept_name` varchar(40) NOT NULL,+
`dept_name` varchar(256) DEFAULT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `dept_name` (`dept_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
可以看到,默认-号代表左边要比较的server1的变化,+号代表右边server2的不同,如果要交换,可以使用参数:
–changes-for=server2
如果要在比较不同的时候,强行继续比较下去,要增加--force选项2) 可以生成差异化的SQL,因为方法1)中,只产生了不同的地方,但如为了生产考虑,要生成达到server2的脚本,则可以使用参数
--difftype=sql,则:
mysqldiff --server1=admin:123456@192.168.79.10 --server2=admin:123456@192.168.79.11 --difftype=sql test:test mysqldiff的核心是使用INFORMATION_SCHEMASHOW CREATE TABLE `departments`;SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION,
SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD,
PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments' AND
REFERENCED_TABLE_SCHEMA IS NOT NULL;SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION,
SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD,
PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';