Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
If you end up with thousands of databases, you will have performance issues because the OS does not like such. –  Rick James 2 days ago
    
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. –  Ahmad Abuhasna 2 days ago
    
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 yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.