Diffing MySQL/MariaDB Schemas with Basic Tools Quickly

May 16, 2023

If you don't have the perfect migration history or there were other manual changes or scripts applied then it's possible for the schema between two or more of your MySQL or MariaDB databases to differ. Here we cover a quick way to see the differences using readily available commands for free.

First a clean dump needs to be generated for the A and B databases using mysqldump (provided with the mysql-clients/common package for your distro):

mysqldump --compact --add-drop-table --no-data --quick dbA > a.sql

then the same for dbB > b.sql. At this point you are ready for diff'ing but things like AUTO_INCREMENTs will cause some unnecessary diffs so some further cleanup can be done with

sed -i 's/sAUTO_INCREMENT=[0-9]+//g' a.sql b.sql

This will now provide a much cleaner diff when you do diff a.sql b.sql or vimdiff a.sql b.sql for a cleaner syntax highlighted version. This only applies to the DDL statements but in a future post we'll look at how this can be scripted to find the differences in records present.

In case it's helpful below are a list of six GUI tools that can help do the same. Schema diff'ing is usually a premium feature:

1. MySQL Workbench: A visual database design tool that integrates SQL development, administration, database design, creation, and maintenance into a single development environment. It has a built-in schema comparison and synchronization tool.

2. dbForge Schema Compare for MySQL: This tool provides the ability to compare and synchronize database schemas quickly and generate comparison reports. It also has an integrated data comparison and synchronization tool.

3. Navicat Premium: This tool allows you to perform structure synchronization, which helps you manage your database in a glance and catch any changes in your database structure.

4. ApexSQL Diff: This is another schema comparison tool which can compare and synchronize both the schema and data. It supports several databases, including MySQL.

5. SQLyog: SQLyog provides a powerful Schema Synchronization tool. You can compare and synchronize schema and data quickly with this tool.

6. Toad for MySQL: Toad is a popular database management tool. The Toad for MySQL version includes a database schema comparison tool.