How to change data structure in mysql using mysqldump without deleting files

Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, but the problem is the tables. I want to sync and alter their structures on the production server using mysqldump (or any other way that you can propose) without altering any existing data.

If it helps, I only want to add more columns, not remove any existing ones. Also, I am using mysqlyog.

Is there any way to do this?

As long as you don't rename tables and columns and you are not using partitions you might use schemasync .

Basic usage is:

schemasync mysql://user:pass@dev-host:3306/dev_db mysql://user:pass@prod-host:3306/prod_db

This will produce two SQL scripts - one for "patching" the production database, and another one for reverting the changes.

Please note that schema altering will lock the tables being affected, so you will have some downtime anyway. There are tools to avoid this: pt-online-schema-change from Percona and oak-online-alter-table from openarkkit.

Also, as with any possibly destructive changes, test the procedure on a staging (or other non-production use, but production data and schema) database.

StackExchange Database Administrators Q#27235, answer score: 5