Two simple ways to version control your MariaDB schema changes

Published on 2023-01-21. Modified on 2023-01-22.

Version controlling (also called revision control or source control) your database data is pretty easy as long as you don't have any binary content stored in the database, you just commit your SQL dump to the version control software, it's just SQL in plain text. But what about database schema changes? In this small tutorial I'll address two simple ways you can version control your MariaDB schema changes without using any third party tools.

Using scripts

One simple way to store database schema changes is to always and only use scripts. You put your SQL schema changing commands into scripts, using a programming language of your choice, and then commit those to the version control software before you even run them on the database.

You can also just save your SQL commands in SQL files and then keep those in your version control together with a "changelog" table in the database (in order to keep track of what changes has been implemented on the database). Such a table could look like this:

idtimestampwhofilename
12022-11-21 08:00:00foo001_split_name_into_firstname_and_lastname.sql
22022-11-29 12:00:00foo002_add_comment_table.sql
32022-12-14 04:20:00foo003_add_price_field_to_inventory_table.sql

Using MariaDB's binary log

Another method is to use MariaDB's binary log. The binary log contains a record of all changes to the databases, both data and structure. It consists of a set of binary log files and an index. Statements like CREATE, ALTER, INSERT, UPDATE and DELETE will be logged. Statements that have no effect on the data, such as SELECT and SHOW, will not be logged.

The binary log is stored in a binary format (not plain text), but MariaDB includes mysqlbinlog, a commandline tool for plain text processing of binary logs.

In order to use the binary log you first have to enable it in my.cnf like so:

log_bin

You can specify which databases to log:

binlog-do-db=foodb
binlog-do-db=bardb

In the following example I have a database running on an OpenBSD server on my local network. From my development desktop (another machine), where I have my application code in version control, I use mysqlbinlog to connect to the database server and extract schema changes which then gets committed to the same repository as my application code repository. You can use multiple repositories, or branches, or whatever you feel fulfill your needs best.

$ mysqlbinlog --no-defaults -R -u foo -p -d foodb -h db.home.arpa mysqld-bin.000001 > foodb.txt

This is a small (shortened) example of the output in which I have created a small table, altered the table, and then eventually deleted the table:

*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;
use `foodb`/*!*/;
SET TIMESTAMP=1674272954/*!*/;
...
CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `text` text NOT NULL
) ENGINE='InnoDB'
/*!*/;
SET TIMESTAMP=1674273096/*!*/;
ALTER TABLE `test`
ADD `comment` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL
/*!*/;
SET TIMESTAMP=1674273108/*!*/;
SET @@session.pseudo_thread_id=999999999/*!*/;
DROP TABLE `test` /* generated by server */
/*!*/;
DELIMITER ;
# End of log file
...

Whether you work as a single developer, or you work on a team, using the MariaDB binary log to version control your database schema changes makes it easy to remember getting changes committed. If you use something like Git, you can create a hook that runs the mysqlbinlog automatically. You can also relatively easy merge changes from other developers.

The script method is old-school and really simple, however you have to remember (and make it a habit) to only use scripts for schema changes.

Using the MariaDB binary log is more convenient as you can simply run your SQL commands as you normally would.

Have a nice one!