Best way to update database structure on production server?
Im building an application and would like to know what is the best way to update the production database.
Right now i deploy my code (CakePHP Application via a GIT Repository )
just like this tutorial https://www.digitalocean.com/community/tutorials/how-to-set-up-automatic-deployment-with-git-with-a-vps
But what is the propper way to update the database structure? not data.
Lets say I create a new table or alter some fields in another table in debug, right now i export the query and then ssh into server and connect to MySql and inject the query in the database.
but im sure there must be another way not so complicated.
3 Solutions collect form web for “Best way to update database structure on production server?”
What I like to do is write a set of functions for converting my data into the new fields/tables and so on.
For example I recently had to make a change to my database where I store the transaction dates because the format I stored the dates in doesn’t evaluate correctly during a query.
- I wrote a PHP function that does the conversion for each field in
- I grabbed a backup copy of the live production database.
- I tested my function using a dry run on the backup copy of the live database.
- Once everything checked out okay, I put my application/site into a temporal maintenance mode and grabbed another copy of the live database. To be sure that any interactions between my customers and database were in tact and there would be no gap.
- I ran the functions on that copy of the database and re-uploaded to my database sever.
- I took my site/application out of maintenance mode.
The result of having everything prepared in advance and testing ensured only 3-5 minutes of downtime for deployment.
Database migrations is that what you need. If you are using CakePHP v2.x read this:
If CakePHP 3.x this:
post-receive hook you have to set some trigger to run migrations on your production server
Why wouldn’t you use migrations as it’s built into Cake. That would allow you to create them locally and then when the code is pushed via git, run the migration in production.