mysql How to Compare Two Database Table Structures

In the process of development and debugging, we need to compare the differences between old and new codes. We can use version control tools such as git/svn for comparison. However, there are differences in database table structure among different versions, so we also need to compare the differences and obtain sql statements for updating the structure.

For example, the same set of codes is normal in the development environment and has problems in the test environment. At this time, in addition to checking the server settings, it is necessary to compare whether there is any difference in the database table structure between the development environment and the test environment. After finding the difference, we need to update the database table structure of the test environment until the database table structure of the development and test environment is 1.

We can use the mysqldiff tool to implement the sql statement to compare the database table structure and obtain the updated structure.

1. mysqldiff installation method

The mysqldiff tool is in the mysql-utilities package, and running mysql-utilities requires an installation that relies on mysql-connector-python

mysql-connector-python Installation

Download address: https://dev.mysql.com/downloads/connector/python/

mysql-utilities Installation

Download address: https://downloads.mysql.com/archives/utilities/

Because I use mac system, I can use brew directly to install it.

brew install caskroom/cask/mysql-connector-python
brew install caskroom/cask/mysql-utilities

After installation, execute the View Version command. If the version can be displayed, the installation is successful

mysqldiff --version
MySQL Utilities mysqldiff version 1.6.5 
License type: GPLv2

2. How to use mysqldiff

Command:

mysqldiff --server1=root@host1 --server2=root@host2 --difftype=sql db1.table1:dbx.table3

Parameter description:

--server1 Specified Database 1
--server2 Specifies Database 2

The alignment can be for a single database, and only specifying the server1 option can compare different table structures in the same library.

--Display mode of difftype difference information

unified (default)
Display system 1 format output

context
Show context format output

differ
Displays different styles of formatting output

sql
Display SQL conversion statement output

If you want to get the sql conversion statement, the display mode using sql is most suitable.

--character-set Specified Character Set

--changes-for is used to specify the object to be converted, that is, the direction in which differences are generated, and the default is server1

--changes-for=server1 indicates that server1 will be transformed into server2, and server2 is the main structure.

--changes-for=server2 indicates that server2 will be transformed into server1, and server1 is the main structure.

--skip-table-options ignores the differences between AUTO_INCREMENT, ENGINE and CHARSET.

-version View Version

Please refer to the official document for more parameters of mysqldiff:
https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html

3. Examples

Create test database tables and data

create database testa;
create database testb;

use testa;

CREATE TABLE `tba` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(25) NOT NULL,
 `age` int(10) unsigned NOT NULL,
 `addtime` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

insert into `tba`(name,age,addtime) values('fdipzone',18,1514089188);

use testb;

CREATE TABLE `tbb` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `age` int(10) NOT NULL,
 `addtime` int(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `tbb`(name,age,addtime) values('fdipzone',19,1514089188);

Perform difference comparison, set server1 as the main one, and change server2 to server1 database table structure

mysqldiff --server1=root@localhost --server2=root@localhost --changes-for=server2 --difftype=sql testa.tba:testb.tbb;
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing testa.tba to testb.tbb                 [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `testb`.`tbb` 
 CHANGE COLUMN addtime addtime int(10) unsigned NOT NULL, 
 CHANGE COLUMN age age int(10) unsigned NOT NULL, 
 CHANGE COLUMN name name varchar(25) NOT NULL, 
RENAME TO testa.tba 
, AUTO_INCREMENT=1002;

# Compare failed. One or more differences found.

Execute the update sql statement returned by mysqldiff

mysql> ALTER TABLE `testb`.`tbb` 
  ->  CHANGE COLUMN addtime addtime int(10) unsigned NOT NULL, 
  ->  CHANGE COLUMN age age int(10) unsigned NOT NULL, 
  ->  CHANGE COLUMN name name varchar(25) NOT NULL;
Query OK, 0 rows affected (0.03 sec)

mysqldiff is performed again for comparison, and there is no difference in structure, only AUTO_INCREMENT has difference

mysqldiff --server1=root@localhost --server2=root@localhost --changes-for=server2 --difftype=sql testa.tba:testb.tbb;
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing testa.tba to testb.tbb                 [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `testb`.`tbb` 
RENAME TO testa.tba 
, AUTO_INCREMENT=1002;

# Compare failed. One or more differences found.

Set to ignore AUTO_INCREMENT and then compare the differences, and the comparison passes

mysqldiff --server1=root@localhost --server2=root@localhost --changes-for=server2 --skip-table-options --difftype=sql testa.tba:testb.tbb;
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing testa.tba to testb.tbb                 [PASS]
# Success. All objects are the same.