how to update mysql structure from a host

Got a kewl website or have a website question? Hosting? Servers?
Post Reply
Eddy Parera
Posts: 18
Joined: Mon Feb 06, 2012 7:54 pm

how to update mysql structure from a host

Post by Eddy Parera »

I'm finishing a website and I want to put it online, but I'll still update with new features...

Now, I don't know how I can update mysql structure without losing any data...
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: how to update mysql structure from a host

Post by Jackolantern »

You have to change the structure of data very carefully if it is running live. And ALWAYS do a back-up before doing it. Because yes, you can very easily corrupt your data and cause anomalies when you are changing the structure while live.

This is where breaking your data structure down into discreet tables that are all related really comes in handy. When you add new features, you simply create a new table, relate it back to your players or whatever it needs to be related to, propagate it with sensible default values, and then start using it. This way there is more of a one-way flow of data writing from that table. New data gets written to it, but little writes to the rest of the db occur based on data reads from it. Adding new structure onto existing tables is a recipe for disaster.
The indelible lord of tl;dr
Eddy Parera
Posts: 18
Joined: Mon Feb 06, 2012 7:54 pm

Re: how to update mysql structure from a host

Post by Eddy Parera »

If I get a backup to my computer and add structure to existing tables locally, will that be a problem?
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: how to update mysql structure from a host

Post by hallsofvallhalla »

what kind of structure changes are you wanting to do?
Eddy Parera
Posts: 18
Joined: Mon Feb 06, 2012 7:54 pm

Re: how to update mysql structure from a host

Post by Eddy Parera »

adding new columns or changing some, like I do in phpmyadmin...

My ideia is upgrading by versions the website, like 1.01, 1.02, and in each version I'll introduce new features like: Now, user will be able to upload pictures for products which wasn't possible until now, and on the products table, I add the columns to put there the location and name of the picture: e.g images/computer.jpg.

I would test on my localhost website, and if it goes right, I would upload the entire folder of pages to the host again, and I would copy to the website database every new column that I introduced to my localhost database...

My question is, after doing the backup, can I change database on the host without any problem or should I download the backup and add on my localhost phpmyadmin and upload again?
User avatar
Callan S.
Posts: 2043
Joined: Sun Jan 24, 2010 5:43 am

Re: how to update mysql structure from a host

Post by Callan S. »

I'd be inclined to think if you use php to add new columns to user entries, that's similar to adding a new table. It just adds on. Certainly when I was making a game I added on new values over time using php to do so (I didn't upload the database structure, just added new columns one at a time) and I didn't lose anything.

Back it up though. Simply because back it up!
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: how to update mysql structure from a host

Post by Jackolantern »

Eddy Parera wrote:If I get a backup to my computer and add structure to existing tables locally, will that be a problem?
That would probably be much better, since you could do extensive testing locally to make sure the changes didn't break anything :)
The indelible lord of tl;dr
Post Reply

Return to “WebSite”