merge design of mysql between localhost and server?

Question

I'm kinda new to this kind of problem. I'm developing a web-app and changing DB design trying to improve it and add new tables. well since we had not published the app since some days ago, what I would do was to dump all the tables in server and import my local version but now we've passed the version 1 and users are starting to use it. so I can't dump the server, but I still would need to update design of server DB when I want to publish a new version. What are the best practices here?

• I like to know how I can manage differences between local and server in mysql?
• I need to preserve data in server and just change the design, data on local DB are only for test.
• Before this all my other apps were small and I would change a single table or column but I can't keep track of all changes now, since I might revert many of them later and managing all team members on this is impossible.

Answer 1

Assuming you are not using a framework that provides a migration tool for database, you need to keep track of the changes manually.

• Create a folder sql_upgrades (or whatever name you name) in your code repository
• Whenever a team member updates the SQL schema, he creates a file in this folder with the corresponding ALTER statements, and possibly UPDATE, CREATE TABLE etc. So basically the file contains all the statements used to update the dev database.
• Name the files so that it's easy to manage, and that statements for the same feature are grouped together. I suggest something like YYYYMMDD-description.sql, e.g. 20150825-queries-for-feature-foobar.sql
• When you push to production, execute the files to upgrade you SQL schema in production. Only execute the files that have been created since your last deployment, and execute them in the order they have been created.
• Should you need to rollback a file, check the queries it contains, and write queries to undo what was done (drop added columns, re-create dropped columns, etc.). Note that this is "non-trivial", as many changes cannot be rolled back fully (e.g. you can recreate a dropped column, but you will have lost the data inside).

Many web frameworks (such as Ruby of Rails) have tools that will do exactly that process for you. They usually work together with the ORM provided by the framework. Keeping track of the changes manually in SQL works just as well.