bowtellj
Posts: 65
|
| Posted: 12/17/2006, 10:35 PM |
|
I have been running an app on my local machine and now I have been asked to put together a web app for a local sports group, to be hosted on a 3rd part server.
I know after I publish the app I will be asked for more changes. So what is the best practice for upgrading on a live site? Especially after they have put real data into the db.
Obviously Backup. Backup. Backup. is the most important?
I guess I am more concerned with merging any new tables (no data) into the existing mysql db.
Thanks
_________________
Thanks
James
Just another Newbie! - Apache 2.24, PHP 5.2.3, MySQL 5.0.41, Windows XP SP2. CCS 3.2.0.2/4.0.2
|
 |
 |
wayne186
Posts: 30
|
| Posted: 01/04/2007, 6:02 PM |
|
actually your best method of defense is not just backup, backup & backup but also test, test and test. 
I am currently running a 'live' version of an app on my server in the shop and running a new beta version on my laptop and the best thing I have found is to take a copy of the live database (I do this every week when I run payroll anyway) and the changes I need in the database are run against it (using phpmyadmin) I run the saved sql commands against the old database in order to allow my beta version to run as there are some tables that do not exist in the live version that are required in my beta version. For example here is the sql stuff I have saved in a simple text file which I run every time I bring over a copy of the live database.
ALTER TABLE `poochies_customer` ADD `Customer_Icon_id` INT(11) NOT NULL;
ALTER TABLE `poochies_Pet` ADD `Pet_Icon_id` INT(11) NOT NULL;
ALTER TABLE `poochies_Groomer` ADD `groomer_active` INT(11) NOT NULL;
UPDATE `poochies_customer` SET `Customer_Icon_id` = '9' WHERE `Customer_Icon_id` = '0' LIMIT 1000;
UPDATE `poochies_pet` SET `Pet_Icon_id` = '7' WHERE `Pet_Icon_id` = '0' LIMIT 1000;
#
# Table structure for table `poochies_pet_icon`
#
# Creation: Jun 19, 2006 at 10:52 AM
# Last update: Jun 24, 2006 at 02:13 PM
#
CREATE TABLE `poochies_pet_icon` (
`pet_icon_id` int(11) NOT NULL auto_increment,
`pet_icon_name` varchar(40) NOT NULL default '',
`pet_icon_url` varchar(100) NOT NULL default '',
PRIMARY KEY (`pet_icon_id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;
#
# Dumping data for table `poochies_pet_icon`
#
INSERT INTO `poochies_pet_icon` VALUES (1, 'An Excellent Dog', 'A1D.gif');
INSERT INTO `poochies_pet_icon` VALUES (2, 'A Good Dog', 'greatD.gif');
INSERT INTO `poochies_pet_icon` VALUES (3, 'An Old Dog', 'oldD.jpg');
INSERT INTO `poochies_pet_icon` VALUES (4, 'A Noisy Dog', 'barkD.jpg');
INSERT INTO `poochies_pet_icon` VALUES (7, 'This Pet should be Updated', 'update.gif');
INSERT INTO `poochies_pet_icon` VALUES (5, 'An Occasional Biting Dog', 'biteD.gif');
INSERT INTO `poochies_pet_icon` VALUES (6, 'A Vicious Dog', 'viciousD.jpg');
# Table structure for table `poochies_customer_icon`
#
# Creation: Jun 19, 2006 at 09:15 AM
# Last update: Jun 19, 2006 at 02:07 PM
#
CREATE TABLE `poochies_customer_icon` (
`customer_icon_id` int(11) NOT NULL auto_increment,
`customer_icon_name` varchar(40) default NULL,
`customer_icon_url` varchar(100) default NULL,
PRIMARY KEY (`customer_icon_id`)
) TYPE=MyISAM AUTO_INCREMENT=10 ;
#
# Dumping data for table `poochies_customer_icon`
#
INSERT INTO `poochies_customer_icon` VALUES (1, 'Excellent Customer', 'bestC.JPG');
INSERT INTO `poochies_customer_icon` VALUES (3, 'Good Tipper', 'tipC.JPG');
INSERT INTO `poochies_customer_icon` VALUES (4, 'Late and or Forgetful', 'lateC.JPG');
INSERT INTO `poochies_customer_icon` VALUES (2, 'Good Customer', 'greatC.JPG');
INSERT INTO `poochies_customer_icon` VALUES (5, 'Something missing inside the box', 'emptyC.JPG');
INSERT INTO `poochies_customer_icon` VALUES (8, 'New Customer', 'new.jpg');
INSERT INTO `poochies_customer_icon` VALUES (9, 'Please Update', 'update.gif');
This is doing a couple of things - adding some new tables and also adding some id fields into existing tables to link to primary id's of existing tables. Works perfect everytime and I stop the sql server before I copy over the live database - rename last weeks database on my laptop to poochies_backup and then copy the new copy of the live version over, restart the server and then run the commands above in phpmyadmin.
As for the app code - I save a copy of the live version and archive it in a zip file and work on the new beta version on the laptop. You really need two instances of mysql and try to keep things consistent ie the odbc name and parameters along with the server name - just enter an entry into the local hosts file on your development machine to make your app on your development environment look at your local mysql instance ie your live server name is called mydblive - enter a localhost address in your hosts file (c:\windows\system32\drivers\etc) of 127.0.0.1 mydblive and your development app will go straight to your local instance.
I hope that helps
wayne
|
|
|
|
|
