Alternative for creating Database and inside Database having schemes like Oracle in MySQL

Posted on

Question :

In oracle we have database inside database we have schemes and inside schemas we have tables.
For Example consider the following scenario
I have 2 databases like Database1 and Database2.
In Database1 it have Schema-A and Schema-B and users u1 and u2 these 2 schemas have tables.
In Database2 it has same schema-A and schema-B and users u1 and u2 and these schemas have some tables.

In MySQL both database and Schema are same. So How can do the above scenario in MySQL.

Answer :

You can get extra levels in the hierarchy by:

  • running multiple instances of MySQL servers – you can run multiple MySQL servers on the same host using different ports and sockets
  • using prefixes (e.g. a_, b_) in the names of tables or databases

The database or table name prefixes can be used mostly for visual purposes, although you can also give grants based on prefixes, e.g.:

GRANT SELECT, INSERT, UPDATE, DELETE ON `db1_%`.* TO 'user1'@'%';

In the above grant, user1 is allowed to run DML queries on all databases with names starting with db1_.

Leave a Reply

Your email address will not be published.