Author: Harry Nguyen

1. Use MySQLWorkbench to connect to prod DB.

2. Click menu Sever > Data Export

3. Select the DB and tables, choose Dump Structure Only

4. Selection option Export to selft-contained file and change the name of exported file (eg: schema-backup.sql)

5. Repeat step 1 to step 4 but at step 3 choose Dump Data Only and change the name of exported file (eg: data-backup.sql)

6. Use MySQLWorkbench to create a new local DB.

7. Run schema-backup.sql and data-backup.sql to the newly created local DB to make it as same state as prod DB.

8. Install flyway if it’s not installed.

9. Clone this repo and follow the README.md file https://github.com/haintwork/flyway-db-migration-starter-kit, but stop before step 7 in README.md file.

10. Comment the migrate command in migrate-db-local.sh script, uncomment the baseline command.

11. Copy content of schema-backup.sql and replace to V1__Init_DB.sql file.

12. Run step 7 of README.md file and check the local DB has new table flyway_schema_history and column success value 1.

13. Create new sql file V1_0_1__Init_test.sql in sql folder of the code with content

    CREATE TABLE test (
        id serial PRIMARY KEY,
        text VARCHAR ( 50 ) UNIQUE NOT NULL
    );
    

14. Open script file migrate-db-local.sh and comment baseline command and uncomment migrate command

15. Run /bin/bash/migrate-db-local.sh and check if new test table is created in local DB, if yes then it’s fine now.

16. Clone file migrate-db-local.sh to migrate-db-prod.sh and update prod db info in the script.

17. Uncomment the baseline and comment migrate

18. Run /bin/bash/migrate-db-prod.sh and check if table flyway_schema_history is created in prod db.