hi,
on production server, while developing, I did some changes on some
tables (I added few new columns on several tables).
now, after the new web app is finished and tested, I have to upgrade
(php) the app on production server.

what steps do you recommend to do the tables update on the production
database?
1. backup
2. ???
3. ???
....

thanks for any .

-afan

> what steps do you recommend to do the tables update on the production
> database?

When I work on our site DB, I follow the following steps:

1. Trash the existing dev DB and replace with the current live version
2. Develop!
3. Dump all the tables I have changed (structure and/or data) using
mysqldump:

mysqldump --default-character-set=latin1 --add-drop-table -u <user> -p
<dbname> <list of tables to dump> > output.sql

4. Zip the output, FTP to live server and unzip.
5. SSH to live server and backup DB with mydqldump.
6. Import the uploaded SQL file:

mysql --database=<dbname> -u <user> -p < output.sql

The import usually takes less than a second, so there is practically no
downtime.

Note that this works well as most of my DB is 'read-only'. I have to be more
careful when updating any tables that are written to via the app.

HTH,
Edward

What I do here is:

Back up the production DB.
Restore it on the development server.
Do the development.
When a schema change is needed I write SQL to alter(upgrade) the
development DB to match development needs.
When development is done, restore the production DB to the development
server again.
Make sure the new code works.
Now I can run the upgrade SQL on the production box.

> -----Original Message-----
> From: Edward Kay [mailto:edward@labhut.com]
> Sent: Thursday, September 06, 2007 7:24 AM
> To: Afan Pasalic; mysql@lists.mysql.com
> Subject: RE: changes in tables (developemnt -> production)
>
>
> > what steps do you recommend to do the tables update on the
> production
> > database?
>
> When I work on our site DB, I follow the following steps:
>
> 1. Trash the existing dev DB and replace with the current
> live version 2. Develop!
> 3. Dump all the tables I have changed (structure and/or data) using
> mysqldump:
>
> mysqldump --default-character-set=latin1 --add-drop-table -u
> <user> -p <dbname> <list of tables to dump> > output.sql
>
> 4. Zip the output, FTP to live server and unzip.
> 5. SSH to live server and backup DB with mydqldump.
> 6. Import the uploaded SQL file:
>
> mysql --database=<dbname> -u <user> -p < output.sql
>
> The import usually takes less than a second, so there is
> practically no downtime.
>
> Note that this works well as most of my DB is 'read-only'. I
> have to be more careful when updating any tables that are
> written to via the app.
>
> HTH,
> Edward
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=ppower@oneeighty.com
>
>