Updating databases in mysql, how to do it right?

Question

MySQL

There is a database with 50 tables and data. There is a new version of it, without data... Autoincrements, indexes, etc. have been added to the new version of the database. How to do it in such a way that at the output we get a database with data and a new structure?

Answer 1

You need to have a “new version of the database” in the form of a sequence of SQL commands that make a new version (migrations) from the old version: add, delete fields, indexes, change field types, convert data, etc.
Some frameworks support such migrations out of the box, for example guides.rubyonrails.org/migrations.html

Answer 2

Unload data from the old version, unload from the new structure. Rename the old one for backup. From the structure you create a database, roll data into it. With little blood, a part of the data will fly out, the structure of which did not match for some reason. These tables (and you will see their list by zero rows) fill with pens as “insert select from backup” with specific fields listed.
This is a frontal one-time solution if you have not prepared migration data. =/ You can also look for differences in each table and write alters =)

Answer 3

There is an opportunity to get a migration, for example, in dbForge.
You deploy a new database structure, then through the menu "Comparison -> New schema comparison ..." compare it with the old one and get all the differences in the structures. Highlight the elements you want to update, right-click on them and select "Show Schema Update Script". As a result, you will get all the necessary ALTERs and CREATEs. But still, be careful, maybe something will have to be updated manually.

Answer 4

If the fields have not been renamed, then simply upload the data with an expanded insert, where the fields are listed before the rate. And after roll on a DB with new structure. Should basically roll +). Unless later, check the value of the next increment in the table.

Answer 5

generally there are two ways
1. to make DDL sql which from the circuit of initial basis will make necessary. Usually this is done immediately and all modifications are atomic as DDL scripts. You can automatically search for tools that do structure migration or compare bases. The dumbest way is to export the structure from the old database (without data) and stupidly use the diff text from which to make the necessary script to change DDL
2. Take the data from the old one and transfer it to the new one. That is, we export data from the old one and upload it to the new one.
In both cases, the structure can be changed so that there will be problems, usually unique or external indexes do not rise (the data is not filled in). There are two ways here - turn off all checks and triggers and do insert ignore, and then compare and look for what didn’t flood and why, then enable and check consistency. Or fill in as is and see why it took off. With DDL scripts, the only option is the second.
I usually export / import data in the same situation, because indexes are being rebuilt, additional checks are being made, and in general I use the old database as a backup, although there are always DDL scripts (they synchronize the databases on the local and external dev servers).
If there are problems, then you have to write data editing in the merged dump or disable features in the new one, change the data and turn it back on.

Answer 6

Try the script at www.mysqldiff.org
But most likely you will have to work with your hands.

Answer 7

Use this
https://github.com/IvanShabanov/MySQL-structure-updater