1

This can be a silly/invalid question but I am going to ask it anyway. I want to setup a testing environment where many developers can run their functional test against a single common database.

Now, how can I ensure that the developers don't mess up with the test data when they run their test simultaneously? Is there a way to partition the db based on the user accessing it? Partitioning may be the wrong word here but I think you get what I mean.

1
  • In MySQL what they call a "database" is in fact a schema, that is, a namespace for tables and such. All "databases"/schemas are part of a single physical database instance, and you should indeed set up separate "databases"/schemas for each developer. – mustaccio Nov 2 '17 at 15:50
1

Set up a DATABASE of the shared data. GRANT users only SELECT permissions. (And maybe a few other non-destructive privs.)

Set up another DATABASE for each user. GRANT ALL PRIVILEGES TO user1_db.* ON 'user1'@...;

Then set up a way to initialize their db with a copy of the readonly copy. Perhaps it could be a Stored Proc. Or maybe a shell script.

Another approach is to set up a Docker instances, one per user. Again, there needs to be a script to initialize their data.

OR...

If each dev does not need to mess with the existing data, consider having a single DATABASE with permissions GRANTed at the TABLE level.

2
  • So you're saying to create a db per developer? If yes, is there any way to accomplish this with a single db? – Rehna Roy Nov 2 '17 at 15:25
  • 1
    I added to my Answer. – Rick James Nov 2 '17 at 15:33

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.