MYSQL - Migration challenge: same name schemas

Question

We have 7 Linux servers (Redhat) and every linux server has a Mysql DB with 1 database (PROD_1) in it. (Every project has own server)

We want to migrate them all to one database server (redhat/mariadb). The only problem is all databases have the same name (PROD_1). Is there any solution except changing the name of databases?

• innodb_file_per_table is active on new mariadb

Answer 1

The short answer is: changing the schema names is probably the best solution.

Databases (or schemas as I prefer to call them, because "database" has multiple meanings depending on context) are basically a namespace for tables and other SQL identifiers.

Just like any other namespace, if you have two entities of the same name, they can't share a schema. Their names conflict if they're in the same schema. If they have different names, they don't conflict.

So if all the tables from one PROD_1 schema are different from the tables of another PROD_1 schema, then you can merge them. But it's not likely that all the tables will be different.

You could change the table names so tables from one project have p1_ prefixes, and tables from another project have p2_ prefixes, etc. But you'd have to change all the query code in all of your applications.

You could run more than one MySQL instance on your single server. In a way, this creates another level of namespacing, because schemas can have the same name if they are in different MySQL instances.

At my last job, we ran MySQL instances in Docker containers with a Docker network so each Docker pod had its own IP address, like a virtual server.

Alternatively, you could run multiple MySQL or MariaDB instances on the server without Docker, but you'd have to change the port, socket, and data directory that each one uses. See https://mariadb.com/kb/en/running-multiple-mariadb-server-processes/

• Bill Karwin: I don't disagree, but I have to assume they are doing this for some valid reason. For example, maybe most or all of the 7 have very low traffic. They didn't ask "is this a good idea?" they asked "how to make this work?"