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:
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.
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.
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.
ApexSQL Diff: This is another schema comparison tool which can compare and synchronize both the schema and data. It supports several databases, including MySQL.
SQLyog: SQLyog provides a powerful Schema Synchronization tool. You can compare and synchronize schema and data quickly with this tool.
Toad for MySQL: Toad is a popular database management tool. The Toad for MySQL version includes a database schema comparison tool.