How to compare the table structures of two databases through mysql

In the process of development and debugging, we need to compare the differences between the old and new code. We can use version control tools such as git/svn Make a comparison. There are also differences in the database table structures of different versions. We also need to compare the differences and obtain the sql statement to update the structure.

Related mysql video tutorial recommendations: "mysql tutorial"

For example, the same set of code works fine in the development environment, but problems occur in the test environment. At this time, except Check the server settings and compare the database table structures of the development environment and the test environment to see if there are any differences. After finding the differences, you need to update the database table structure of the test environment until the database table structure of the development and test environments is consistent.

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

1.mysqldiff installation method

The mysqldiff tool is in the mysql-utilities software package, and running mysql-utilities requires the installation of the dependency 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/

Since I am using a mac system, I can directly use

brew to install it.

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

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

mysqldiff --versionMySQL 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 指定数据库1--server2 指定数据库2

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

--difftype 差异信息的显示方式

unified (default) Display unified format output

context Display context format output

differ Display different styles of format output

sql Display SQL conversion statement output

If you want to obtain sql For conversion statements, it is most suitable to use the sql display method.

--character-set 指定字符集--changes-for 用于指定要转换的对象，也就是生成差异的方向，默认是server1--changes-for=server1 表示server1要转为server2的结构，server2为主。--changes-for=server2 表示server2要转为server1的结构，server1为主。--skip-table-options 忽略AUTO_INCREMENT, ENGINE, CHARSET的差异。--version 查看版本

For more information on how to use mysqldiff parameters, please refer to the official documentation:

https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html

3. Example

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 server2 should be converted 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)

Execute mysqldiff again for comparison. There is no difference in structure. Only AUTO_INCREMENT has differences.

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 perform difference comparison. Through

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.

This article explains how MySQL compares the table structures of two databases. For more related knowledge, please pay attention to the PHP Chinese website.

Related recommendations:

Explain how to use mysql binlog

Explain how PHP uses the token bucket algorithm to achieve traffic based on redis Related content of control

How to create a QR code class with logo through php

##

The above is the detailed content of How to compare the table structures of two databases through mysql. For more information, please follow other related articles on the PHP Chinese website!