Make an ALTER TABLE script with MySQL Workbench
So you have two versions of a database sitting around - maybe you've been trying things out on your development box, and now you want to move it all over to production. If it's just a new table here and an extra column there, you could do it by hand, but if you've made lots of changes, it would be nice to let the computer do it for you.
Enter MySQL Workbench.
Workbench lets you import the structure from a dump file, or reverse engineer from a running server. Then you can synchronize it with a same-named database on another server, and it will sort out the required CREATE TABLE or ALTER TABLE ADD COLUMN for you. You don't even have to complete the step and actually let it do the synchronization, if all you want is to save the script for reference purposes.
Here's a little screencast showing the action.
I first import the old structure into the MySQL server using the command line client:
mysql < oldTest.sql
I then show connecting to the server to demonstrate that the server holds just two tables, `t1` and `t2`, and that t1 has only a single column, `id`.
Next, I switch to Workbench and import the modified file, newTest.sql. Note that both of the files include the USE dbname; statement so that Workbench knows which schema to use. The new schema includes a new table, `t3`, as well as adding a new column to `t1`, column `d`.
I synchronize the model with the running server, ignoring the `mydb` schema and updating the source for the `test` schema. The resulting script shows the CREATE TABLE statement for `t3`, and the ALTER TABLE statement to add `d` to `t1`. That script can be saved as a file, copied to the clipboard, or executed directly on the server.
March 23rd, 2010 - 11:31
Excellent guide, I got here from the mysql forums.
I haven’t managed to go through your other posts, and I apologize if this is not the right place to ask further: how would you handle Data migration from the older database to the new? (Can MySQL Workbench be of any help? What would be the approach that automates most of this task?)
Any advice would be appreciated.
ana
June 13th, 2010 - 21:40
Do you know of any way to get around the “same name” restriction for generating an alter script? We store our generation scripts without db names, relying on a ‘use db’ statement before executing the script. However, the only way we can generate an alter script for new changes is through a 10 step (or so) process of reverse engineering the old one, exporting it with a given db name, then recreating it with the same name.
June 13th, 2010 - 23:17
ana: Is there really much migration you have to do? Can you not just alter the db in place? Failing that, the old Migration Toolkit is still available in the archives, and can handle data migration between MySQL servers. Eventually that functionality will probably be added to the new Workbench.
Bart:
echo 'use dbName;' | cat - script.sql > scriptForWorkbench.sql, perhaps?August 10th, 2010 - 22:29
Yes the same database name is very annyoning anybody know a solution?