Syncing remote DB with a local one

You are developing a new version of an existing website locally. Along with that you are making some changes to the database schema for that website - again locally (through phpMyAdmin).

The new version is ready 开发者_C百科and you want to upload it to the remote server.

You upload the files through FTP (that's easy) and now the files are up to date.

How do you update the database schema and make sure it is in sync with the local one?

We save the SQL statements required for the database change in a file, tagged by the version. Whenever a new version must be enrolled, then we import it into the database. First into a test server (an exact replica of our live server minus the live data; this is substituted with random values). When everything works properly, we can move on to our live server.

Summarized: At every version update this entire file is imported into the live database. The file is version controlled as well so it's easy to roll back any improper statements.

If you get lost and don't know anymore what changes are required on your production server, you can use Mysql Workbench (free tool from Mysql). This tool allow to load the schemas of your 2 databases, will compare them and generate a script to go from one to the other. This way you can be sure that the 2 schema will be identical.

I guess there is other tools to do the same, but Mysql workbench did a good job for me a few years back.

But don't get me wrong: this is not a good way to manage the change, just a way to get out of trouble. The solution with source control is a much better practice.

While its fairly straightforward to implement this in your own code (DESC and SHOW return tables of data just like SELECT) in practice its a very bad idea to automate schema changes; usually these will lock the table for the duration of the operation - so it makes a lot more sense to identify the changes then apply them in a controlled release (while the web server is disabled / amended to serve up 'under construction' / running from an alternate database).