Does MySQL 5.5 support shared database with separate schema?

Question

I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single database for all tenant but different schema for each tenant.

I am not sure whether MySQL does support it.

Your answer would help me a lot.

Thanks in advance!

Answer 1

My understandng of MySQL is that what they call a "database" is a schema on other db's. So the answer to your question is "yes" or "no" depending on the specifics you are asking. In fact "create schema" just maps to "create database" on MySQL.

So yes, you can do one schema per tenant but that is not any different on MySQL than one database per tenant. So not sure if that is a yes or no.

Answer 2

Strictly in terms of MySQL, a Database and a Schema are one and the same.

From the wording of your question

• What you call a single database is really the mysqld server process
• What you call a schema in just an OS subfolder managed by the mysqld process

The way mysqld creates a database is straightforward: It simply makes a subfolder under the current datadir. For example, the default datadir for Linux is /var/lib/mysql. Whenever you execute:

CREATE DATABASE zaver;

It simply executes mkdir /var/lib/mysql/zaver. You can create multiple databases under the mysqld instance. Simply call

CREATE DATABASE zaver1;
CREATE DATABASE zaver2;
CREATE DATABASE zaver3;

When you run SHOW DATABASES;, you will see information_schema (all memory database of MySQL instance metadata), mysql (user grant tables), and tenants created via CREATE DATABASE.

If you want a database in /var/lib/mysql to exist on a different disk, you will have to perform the database creation in the OS.

Suppose you ran the df -h command on you gave this output

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             129G  2.3G  120G   2% /
tmpfs                  12G     0   12G   0% /dev/shm
/dev/sdb1             135G   76G   53G  59% /data
/dev/sdc1             135G  188M  128G   1% /backups

Let's say you want to make /data (mounted on /dev/sdb1) the location of the zaver database. Instead of doing CREATE DATABASE zaver; you would do the following:

chown -R mysql:mysql /data
ln -s /var/lib/mysql/zaver /data 
chown mysql:mysql /var/lib/mysql/zaver

That's it. The mysqld process will autodetect the presence of the new database.

CAVEAT

Please keep in mind that all tenant databases share usage of three commodities:

• InnoDB Buffer Pool
• MyISAM Key Cache
• Database Connections

If you have some databases that are going to hog resources (RAM, Disk I/O, OS, etc.) that will affect the performance o fother tenants, consider setting up another DB Server to run MySQL for tenants that have condominiums and penthouse needs of server resources.