mysqldiffè¯¥å·¥å ·æ¯å®æ¹mysql-utilitieså·¥å ·éçä¸ä¸ªèæ¬ï¼å¯ä»¥ç¨æ¥å¯¹æ¯ä¸åæ°æ®åºä¹é´ç表ç»æï¼æè å个æ°æ®åºé´ç表ç»æ
å¦æå¨windowsä¸ï¼ç´æ¥ä¸è½½mysql-utilitieså®è£ å°±å¯ä»¥äºï¼ç¶åè¿è¡åï¼ä¼è·å°å½ä»¤è¡ä¸ï¼
1) åºæ¬ç¨æ³
mysqldiff --server1=admin:[email protected] --server2=admin:[email protected]
--difftype=differ test:test
è¿æ ·å°±æ¯è¾ä¸¤ä¸ªä¸ååºä¸çæ°æ®åºtestï¼å设两个åºä¸åºåé½æ¯test
ä¹å¯ä»¥æ¯è¾ä¸åç表
mysqldiff [email protected] [email protected] testdb.table1:anotherdb.anothertable
å°±æ¯ä½¿ç¨åºå.表åçæ¹å¼å°±å¯ä»¥äº
ä¸é¢æ¯åºæ¥çä¸ä¸ªææ
# mysqldiff --force --server1=root:[email protected]:21489 --server2=root:[email protected]: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:[email protected] --server2=admin:[email protected]
--difftype=sql test:test
mysqldiffçæ ¸å¿æ¯ä½¿ç¨INFORMATION_SCHEMA
Java代ç
SHOWÂ 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';