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...
how to update mysql structure from a host
-
- Posts: 18
- Joined: Mon Feb 06, 2012 7:54 pm
- Jackolantern
- Posts: 10891
- Joined: Wed Jul 01, 2009 11:00 pm
Re: how to update mysql structure from a host
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.
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
-
- Posts: 18
- Joined: Mon Feb 06, 2012 7:54 pm
Re: how to update mysql structure from a host
If I get a backup to my computer and add structure to existing tables locally, will that be a problem?
- hallsofvallhalla
- Site Admin
- Posts: 12023
- Joined: Wed Apr 22, 2009 11:29 pm
Re: how to update mysql structure from a host
what kind of structure changes are you wanting to do?
-
- Posts: 18
- Joined: Mon Feb 06, 2012 7:54 pm
Re: how to update mysql structure from a host
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?
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?
Re: how to update mysql structure from a host
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!
Back it up though. Simply because back it up!
- Jackolantern
- Posts: 10891
- Joined: Wed Jul 01, 2009 11:00 pm
Re: how to update mysql structure from a host
That would probably be much better, since you could do extensive testing locally to make sure the changes didn't break anythingEddy Parera wrote:If I get a backup to my computer and add structure to existing tables locally, will that be a problem?
The indelible lord of tl;dr