database - How can I automatically merge two different schemas in MySQL? -
i have mysql database in production (i'll call db1) lots of data already.
in dev, made several changes it's structure , added data testing purposes. i'll call db2.
i need merge db2's schema db1's without losing db1's data , without copying data db2.
is there way automatically (using scripts, procedures or workbench's built-in functionality)?
- dump schema need without data mysqldump --no-data
- load dump new database.
- create stored procedure dynamic sql , cursor on information schema.tables. basically, each table belongs original database, construct insert ... select query inserts data new database. notice may need lock out entire database lock tables statement if want prevent data being modified while you're copying it. can construct lock tables statement dynamic sql well.
- done.
if step 3 looks complicated, insert... select data manually. there no automatic way in mysql: depending on changes made original schema, e.g. added columns/indexes/changed field types, different insert...select query may needed.