How to modify multiple databases structure simultaneously?

Question

I have multiple databases on the same server which have the same structure as each other. whenever I want to modify a table or a procedure or add, edit or delete something on one of them I should run the modifications manually on all the others too!

Is there a way to modify the structure of all of the databases simultaneously and preferably automatic?

I use MySQL workbench tool to connect to my databases.

Answer 1

It's the job of your app. Database Schema Migration frameworks help you with that.

For Java based Apps Flyway is an example tool. For PHP based Apps Laravel Database: Migrations

Migrations are like version control for your database, allowing your team to easily modify and share the application's database schema. Migrations are typically paired with Laravel's schema builder to easily build your application's database schema. If you have ever had to tell a teammate to manually add a column to their local database schema, you've faced the problem that database migrations solve.

such Frameworks exists for every language and is the state of the art way to DDL statements in an automated and versioned fashion.

simple example with Flyway and Grails howto deploy a procedure:

path .../grails-app/conf/db/migration/V1_0_7__remove_service_ref_from_service_instance.sql

DELIMITER //
CREATE PROCEDURE normalize_service_instance()
 BEGIN
 SET @col_exists = 0;
 SELECT 1
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'service_instance'
 AND column_name = 'service_id'
 AND table_schema = database()
 INTO @col_exists;
 IF @col_exists = 1
 THEN
 ALTER TABLE service_instance
 DROP FOREIGN KEY FK_c2o824f4t90lp1v2df5dxp2r8;
 ALTER TABLE service_instance
 DROP COLUMN service_id;
 END IF;
 END//

Other popular tool for Java land is Liquibase

Liquibase is an open source database-independent library for tracking, managing and applying database schema changes. It was started in 2006 to allow easier tracking of database changes, especially in an agile software development environment.

• Afrooz: I don't mean Migration at all. Instead, if I add a table or drop a procedure or edit the body of a procedure in my test environment and then I decide to apply these changes to all databases on my Production environment, I should apply the changes one by one and manually. I want to make it automatic and perform it only once. What do you suggest?
• Afrooz: I only knew about the Data migration, but I figured out that we have another form of migration "schema Migrate". I also found that I can use Version Control for my Database. Do you have any suggestions? We are using Git for our application Versioning.
• user1822: And don't t forget Liquibase as well