Joe Freeman's Weblog Musings on Software Development, etc

PHP Script to Compare MySQL Database Schemas

Introduction

I’ve been working on a project recently that has a rather messy deployment strategy. This resulted in a minor glitch occurring during a recent deployment to a fairly high-profile site when a database table wasn’t updated.

Clearly the best solution to this problem would be to come up with a better deployment strategy, which is something we have been looking into. However, the problem has inspired me to write a small tool which collects information on the schema of a database so that it can be compared with other databases.

A slight twist to the problem comes from the requirement that the databases are more than likely to be hosted on different servers. Since the database servers are setup to only allow connections locally, we aren’t able to access both databases at the same time. Additionally, some of these servers are running PHP 4, so are lacking some newer PHP features.

Fetching Schema Information from MySQL

The MySQL queries to get information about tables are fairly straight-forward.

To get a list of the tables in a database, we can do:

SHOW TABLES

To get information about the fields in a specific table, we can do:

SHOW COLUMNS FROM table_name

This returns a collection of information on each field:

• the names of fields,
• the data type,
• whether null values can be stored,
• whether the column is indexed,
• the default value of the field, and
• whether the field uses auto_increment

Full details can be found on the manual page.

The PHP Script

The tool consists of:

• a simple class for extracting the schema and performing the comparison between extracted schemas
• a user interface to the class

Since we need to be able to access databases on different hosts, the solution I am using is to serialise the schema information and give it back to the user. The user is then required to copy and paste this data from separate instances back into a form so that the comparison can be performed.

The user interface provides the user with a list of pre-configured databases to choose from, and also allows the user to enter connection details in the form. Arguably, the first option is more convenient and more secure.

The actual comparison of schemas works on three levels:

1. We check both databases have the same tables
2. For each table, we check that the same fields are present
3. For each field, we check that the field’s parameters are the same

Any discrepancies are then displayed to the user on a per-table basis.

Download this Tool

If you have a use for this tool, or are just interested to see how it works, feel free to download the code.