Running several versions of a MySQL database in parallel? We have a central read-only MySQL database that contains geo data, etc. It
is used by various projects. As part of a refactoring, I am thinking about
setting up a system for versioning the database. The rough idea so far:


The current database is version 1. It is available at:

1.database.example.com:3306/database_1

When in the future the database structure is changed in an incompatible
way, then the version number is increased to 2, and the new database is
made available at:

2.database.example.com:3306/database_2


That allows updating projects one by one from interfacing with
1.database.example.com to interfacing with 2.database.example.com. Due
to the different domain name, it is possible to use a different
database server for 2.database.example.com, even one not based on
MySQL.


Does that make sense? Is there a standard or recommended way for “versioning” a
read-only database?
One must separate: What do you want to "version"? The interface or the data?

Versioning the interface

If you only want to version the interface, you should (must) manage one single base of data and provide it in different formats at different interfaces. Otherwise you will run into consistency issues, if your data should change (and even geo data changes -- the rarity of changes makes it even more vulnerable to consistency issues, because this is done so infrequent that humans tend to do mistakes).

In this case I would use one single database and VIEWs for different interfaces. This is harder to setup, but saves headache in the long run.

An alternative would be to replicate the data periodically by a cronjob, which might be a waste of resources if this changes very infrequently.

You could also implement a script and make somehow sure it is always executed if some data changes. (Vulnerable to human errors this is.)

Versioning the data

If you want to version everything including the data, your approach seems fine to me. Although it might be more efficient to just use another database instead of a completely different server instance.