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

Question

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?

Answer 1

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.

Answer 2

Since you are trying to update only the table structure from the sandbox to the production, you need to ensure that you do not accidentally damage the production database:

a) Dump the production database structure into a staging database

mysqldump -R --add-drop-table --no-data -h productionhostname -u productionUSER -pproductionPASSWORD productiondbname | mysql -u stagingUSER -pstagingPASSWORD -h staginghostname stagingdatabasename

b) Use a tool like SQL Yog or MySQL Workbench to generate a script to update the structure of the staging database from the sandbox

c) Test the generated script on the staging database

d) Execute the generated script on the production database.

I am not sure if this process can be automated since at times phyiscal database changes need accompanying data updates

Answer 3

1. export data from parent server and remove the table.
2. import only table structure from sandbox into production.

3. now once again import data into production

   keep backup of your db ;