Large scale MySQL changes to active sites

Question

Hello

Just some pointers here. I am making fairly extensive modifications to a site, including the MySQL database.

My plan is to do everything on my development server, export the new MySQL structure for the db and import it onto the clients server.

Basically I need to know that performing a structure only import will not overwrite/delete existing data. I am not making changes to the data type or field length.

Answer 1

In my experience, when you export a database (through phpMyAdmin for instance), part of the SQL script that is created includes a "DROP TABLE IF EXISTS 'table_name';" before doing a "CREATE TABLE 'table_name'...;" to build the new table.

My guess is that this is not what you want to do! Certainly use the dev system to alter the structure in order to make everything correct, but then look around for a database synchronisation routine where you can provide the old structure, the new structure, and the software will create the appropriate "ALTER TABLE 'table_name'...;" scripts to make the required changes.

You should then really examine these change files before executing them on the live database, and of course BACKUP the live database, and ensure you are able to fully recover from the backup before starting any of the alterations!

Answer 2

I've had to do this a lot, and it always goes like this:

1. Make a backup of the live database, complete with data.
2. Make a backup of the live database schema only.
3. Calculate the differences between the old (live) schema and the new (devel) schema.
4. Create all of the 'ALTER TABLE ...' DDL statements necessary to upgrade from the old schema to the new one. Keep in mind that if you rename a field, you probably won't be able to just rename it -- you'll need to create the new field, copy the data from the old field, and then drop the old field.
5. If you changed relationships between tables, you'll probably need to drop indexes and foreign key relationships first, and then add them back afterwards.
6. You'll need to populate any new fields based upon their default values, if any.
7. Once you've got all the pieces working, you'll need to combine them into one large script, and then run it on a copy of the live database.
8. Dump the schema and compare it against the desired new schema -- if they don't match, go back to step 3 and repeat.
9. Dump the data and compare it against the expected changes -- again, if they don't match, go back to step 3 and repeat.

You're going to learn a lot more about SQL DDL/DML during this process than you ever thought you'd learn. (For one project, where we were switching from natural keys to UUID keys for 50+ tables, I ended up writing programs to generate all of the DDL/DML.)

Good luck, and make frequent backups.

Answer 3

I'd recommend to prepare a sql script for every change you do on development server, so you will be able to reproduce it on development. You shouldn't get to the point where you need to calculate differences between database structures

This is how I do it, all changes are reflected in sql scripts, and I can reconstruct the history of my database running all these files if needed.

Test the final release version on a "staging" mysql server. Make a copy of your production server on another machine and test your script to make sure everything's ok.

Of course, preliminary database backup is a must.