0

I am working on a project that combine multiple MySQL ports under the same port; each port has a shared databases with names (database name and tables), so I though of the following idea. if I created user1, user2, and user3, on the new machine and separate all user databases by adding prefix (userid_) to the database name `.

example:

port (3306) one is located under machine 1.1.1.1, if I use the following command I will get the following result:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customers          |
| meetings           |
| mysql              |
| performance_schema |
| test1              |
| training           |
| feedback           |
+--------------------+

port (3307) is located under 2.2.2.2 if I use the following command I will get the following result:

show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | customers          |
    | management         |
    | mysql              |
    | performance_schema |
    | meeting            |
    | employee           |
    | feedback           |
    +--------------------+

port (3308) is located under 3.3.3.3 if I use the following command I will get the following result:

show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | customers          |
    | meetings           |
    | mysql              |
    | performance_schema |
    | test1              |
    | training           |
    | feedback           |
    | management         |
    +--------------------+

the new machine 4.4.4.4 port (1111) should have (note its already done):

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| user1_customers    |
| user1_meetings     |
| mysql              |
| performance_schema |
| user1_test1        |
| user1_training     |
| user1_feedback     |
| user2_customers    |
| user2_management   |
| user2_meetings     |
| user2_employee     |
| user2_feedback     |
| user3_customers    |
| user3_meetings     |
| user3_test1        |
| user3_training     |
| user3_feedback     |
| user3_management   |
+--------------------+

My question here can I make a rule for the three user when they created a database should start with their name ( if user3 create a database the database will have user3_ as prefix) or to add it automatically.

3
  • If you end up with thousands of databases, you will have performance issues because the OS does not like such.
    – Rick James
    Mar 10 '15 at 17:12
  • I know but, in the application I am using the max database can be created by any user is 10 databases, at max 30 custom databases from all user, the new server contain a huge amount of RAM. Mar 11 '15 at 8:20
  • Each database is an OS directory living in one directory. I have seen performance problems whenever a directory has more than, say, 10K entries. That would probably translate into slow USE statements and slow connection establishment if that includes an implicit USE.
    – Rick James
    Mar 11 '15 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.