Upgrading a MySQL Application
by Russell Dyer10/16/2003
Most developers design MySQL databases for their own use or for the use of their employers. Occasionally, though, a developer will design a database for use by others, for sale as an application. Since an application developer usually isn't present when his application is installed and used, he must consider many factors when designing the database.
Despite excessive planning, though, an application is never perfect when it's first released. Invariably, tables will need to be changed and scripts will need to be replaced. This is fairly easy for a developer to do on his own server. The difficulty comes when creating an upgrade program that will make these changes, a program that non-technical clients can run on their own. Careful planning of an upgrade is required to keep from destroying the client's data.
In this article I will examine such a quandary and offer some suggestions for putting together an upgrade to a MySQL application. I'll provide examples of how such an upgrade might unfold. Finally, I'll also comment on how one might update the data in a reference table of an application.
The Application
For the examples in this article I will use the scenario of a developer who has created a MySQL application that runs on Linux. The application is designed for used-car dealers to track their inventory, sales, and other related information. It's accessed through the web browser; the interfaces are written in Perl using the CGI module. In addition to HTML pages for employee use, there are also pages for the public to view automobiles for sale, on-line.
|
Related Reading 
|
The developer has sold several copies of his application over the Web. His clients each pay a quarterly subscription fee for regular updates. Now that it's time for an update, the developer wants to make some changes to improve the program.
For one thing, two tables, sales_staff
and employees, contain redundant information. The
sales_staff table holds the commission pay-out rate, sales
ranking, and other information, including each salesperson's name and telephone
numbers (home and mobile). The employees table contains personnel
data on all of a client's employees, not just salespeople, also including
contact information. When a client has to add new salespeople, he must add
them to both tables. To change a telephone number, he must make telephone
number changes in both. Naturally, clients often forget to change both.
As part of the upgrade, the developer wants to eliminate the name
and telephone columns from sales_staff, instead adding a
column for the employee id to link the records
to employees. He'll also have to change the Perl scripts
that talk to these tables, but as this is primarily a MySQL article,
we'll gloss over those parts. The upgrade will contain several other
improvements, but this one change is sufficient for our example.
Let's now go through each step of the process in the order in which
the upgrade would be performed.
Playing it Safe
Since clients, particularly used-car dealers, can be pretty rude
when their data is lost, the developer has tested the upgrade
extensively. Additionally, he wants to take the precaution of having
the upgrade program make a backup of the client's data before
upgrading the application. There are a few MySQL statements and tools
that may be used to backup data. However, since many of his clients
have installed the application on virtual domains (i.e., on sites
rented from web hosting companies), they havu limited
choices. Therefore, the developer has decided to
use mysqldump to backup the effected tables.
A client must first upload the upgrade package—a tarred and
gzipped file containing the upgrade program (upgrade.plx)
and other related files—to his web server. He then must log
onto the server with administrative access to install the package and
to run
upgrade.plx. The upgrade package creates
a backup sub-directory beneath the installation directory
to store backups of data and scripts. After some opening fanfare, the
user is prompted to provide an administrative username and password
for MySQL. The user is also asked for the database name (if different
from the default) for the dealership application; some web hosting
companies assign names to their customers' databases. The upgrade
program will store and export this client information as
bash environment variables like this:
export USR='username'
export PW='password'
export DB='database'
export UP_DIR=`pwd`
export SCRIPT_DIR='/var/cgi/cardealer'This method sets up variables that can be used by all of the
scripts contained in the upgrade program. Of course, the actual
username and other values will contain the client's specific
information. As for the upgrade directory (UP_DIR), that
is set by the pwd command. This could be incorrect,
though, if the user started upgrade.plx from a different
directory. Therefore, it's better to have the user confirm this value
before proceeding. All of these shell variables can be retrieved by
bash when needed by adding a dollar-sign in front of the variable
name like this: $PW. For Perl, the variables are part of an
environment hash and can be retrieved specifically like this:
$ENV{PW}.
Getting back to the backup, for good measure, the user is then
asked if the MySQL tables may be locked before backing up the data.
This is to ensure that the data isn't changed during the backup. If
the user answers affirmatively, a table can be locked using
the bash variables like this:
$ mysql --user="$USR" --password="$PW" \
-e "LOCK TABLES sales_staff READ" $DB
$ mysql --user="$USR" --password="$PW" \
-e "FLUSH TABLES" $DBThese statements should be entered from the Linux command line, not
the
mysql client. The SQL statements (i.e., LOCK and
FLUSH) could be invoked by Perl using the DBI module. However, since a mysqldump statement will follow, we'll stay on the command line
for now.
The -e option indicates that what follows should be
executed in MySQL. Within the quotes is a LOCK statement:
the table that's to be locked is given; to lock more tables,
just list them right after the first table, with spaces in
between. A READ lock prevents users from writing to the
tables during the backup while still allowing them to read the
data. By the way, these command lines, like many in this article, are
wrapped for nicer display. However, they may be entered on the same
line or with a backslash as shown here at the end of each incomplete
line to let the shell know that more follows. Once the table is
locked, the upgrade program can back it up:
$ mysqldump --user="$USR" --password="$PW" \
$DB sales_staff > ./backup/sales_staff.sql
$ mysql --user="$USR" --password="$PW" \
-e "UNLOCK TABLES" $DBThe mysqldump utility creates a plain text file
containing a
CREATE TABLE statement and a series of INSERT
statements to restore the data if necessary. The table name is given here and
the output is redirected to the backup path and the backup filename.
Incidentally, --add-locks could have been added to the
mysqldump statement here instead of running the LOCK
and FLUSH statements before, and so an UNLOCK
statement would not have to be run later. Choose the approach you prefer.
If it's necessary to restore the data, the developer has written a
separate program called restore.plx to restore the data
(see last section below). In addition to backing up the data, the
upgrade program will also make copies of the Perl scripts that will be
replaced. This will be done using
tar like this:
tar -cvzf $UP_DIR/backup/scripts.tar.gz \
$SCRIPT_DIR/car_db/*The c option creates an archive and the f
option a
tar file. The v option verbosely lists the files
being archived. The z option compresses the file with
gzip. Finally, the value of the $UP_DIR variable
contains the installation directory of the upgrade determined at the beginning.
The $SCRIPT_DIR is another environment variable that contains the
directory of the application's CGI pages.
Pages: 1, 2 |
