mysqlschemadatabase-migration

How to merge multi schema data into single schema in mysql?


I have one application with multi-schema and single schema both running on differnt server.We use MySQL database with multiple schemas, each containing several tables, stored procedule and other objects. I need to consolidate all these schemas into a single schema for better management and efficiency. I'm looking for guidance on the best approach to achieve this migration.


Solution

  • It's actually quite simple:

    1. Use RENAME TABLE to move tables from one schema to another.
    2. Use CREATE PROCEDURE to add a procedure to the new schema. There's no command to move a procedure, so you just have to create a new procedure in the schema you want, even if it's identical to the old procedure.
    3. If any procedures reference old schema names or use qualified table names, you may have to rewrite them. Inspect your procedure code on a case by case basis.

    When you're done, there will be no difference in performance. Schemas don't have anything to do with performance.

    The benefits to consolidating schemas will be:

    I'm not sure these small benefits justify the work it takes to consolidate the schemas, but that's up to you.