MYSQL – Migration challenge: same name schemas

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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/

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply