You can always move the MySQL form and data files between
different versions on the same architecture as long as you have the same
base version of MySQL. The current base version is
3. If you change the character set by recompiling MySQL (which may
also change the sort order), you must run
isamchk -r -q on all tables.
Otherwise your indexes may not be ordered correctly.
If you are paranoid and/or afraid of new versions, you can always rename your
mysqld to something like
mysqld then does something unexpected, you can simply shut it
down and restart with your old
When you do an upgrade you should also backup your old databases, of course.
Sometimes it's good to be a little paranoid!
After an upgrade, if you experience problems with recompiled client programs,
Commands out of sync or unexpected core dumps, you probably have
used an old header or library file when compiling your programs. In this
case you should check the date for your `mysql.h' file and
`libmysqlclient.a' library to verify that they are from the new
MySQL distribution. If not, please recompile your programs!
If you get some problems that the new
mysqld server doesn't want to
start or that you can't connect without a password, check that you don't
have some old `my.cnf' file from your old installation! You can
check this with:
program-name --print-defaults. If this outputs
anything other than the program name, you have a active
file that will may affect things!
It is a good idea to rebuild and reinstall the
distribution whenever you install a new release of MySQL,
particularly if you notice symptoms such as all your
dumping core after you upgrade MySQL.
MySQL 3.23 supports tables of the new
MyISAM type and
NISAM type. You don't have to convert your old tables to
use these with 3.23. By default, all new tables will be created with
MyISAM (unless you start
mysqld with the
--default-table-type=isam option. You can change an
table to a
MyISAM table with
ALTER TABLE or the Perl script
3.22 and 3.21 clients will work without any problems with a 3.23 server.
The following lists what you have to watch out for when upgrading to 3.23:
DELAYED are now reserved words.
FLOAT(8) are now true floating point types.
- When declaring
DECIMAL(length,dec) the length argument no
longer includes a place for the sign or the decimal point.
TIME string must now be of one of the following formats:
[[[DAYS] [H]H:]MM:]SS[.fraction] or
LIKE now compares strings using the same character
comparison rules as
'='. If you require the old behavior, you
can compile MySQL with the
REGEXP is now case insensitive for normal (not binary) strings.
- When you check/repair tables you should use
MyISAM tables (
isamchk for ISAM
- If you want your
mysqldumps to be compatible between
MySQL 3.22 and 3.23, you should not use the
--full option to
- Check all your calls to
DATE_FORMAT() to make sure there is a `%'
before each format character.
mysql_fetch_fields_direct is now a function (it was a macro) and
it returns a pointer to a
MYSQL_FIELD instead of a
mysql_num_fields() can no longer be used on a
MYSQL* object (it's
now a function that takes
MYSQL_RES* as an argument. You should now
MySQL 3.22, the output of
SELECT DISTINCT ... was
almost always sorted. In 3.23, you must use
GROUP BY or
ORDER BY to obtain sorted output.
SUM() now returns
NULL, instead of 0, if there is no matching
rows. This is according to ANSI SQL.
New restricted words:
CASE, THEN, WHEN, ELSE and END
Nothing that affects compatibility has changed between 3.21 and 3.22. The
only pitfall is that new tables that are created with
columns will use the new way to store the date. You can't access these new
fields from an old version of
After installing MySQL 3.22, you should start the new server and
then run the
mysql_fix_privilege_tables script. This will add the new
privileges that you need to use the
GRANT command. If you forget
this, you will get
Access denied when you try to use
CREATE INDEX or
DROP INDEX. If your MySQL root
user requires a password, you should give this as an argument to
The C API interface to
mysql_real_connect() has changed. If you have
an old client program that calls this function, you must place a
db argument (or recode the client to send the
element for faster connections). You must also call
mysql_real_connect()! This change was done to allow
mysql_options() function to save options in the
If you are running a version older than 3.20.28 and want to
switch to 3.21.x, you need to do the following:
You can start the
mysqld 3.21 server with
--old-protocol to use it with clients from the 3.20 distribution.
In this case, the new client function
mysql_errno() will not
return any server error, only
CR_UNKNOWN_ERROR, (but it
works for client errors) and the server uses the old password() checking
rather than the new one.
If you are NOT using the
--old-protocol option to
mysqld, you will need to make the following changes:
All client code must be recompiled. If you are using ODBC, you must get
the new MyODBC 2.x driver.
scripts/add_long_password must be run to convert the
Password field in the
mysql.user table to
All passwords must be reassigned in the
mysql.user table (to get 62-bit
rather than 31-bit passwords).
The table format hasn't changed, so you don't have to convert any tables.
MySQL 3.20.28 and above can handle the new
user table format
without affecting clients. If you have a MySQL version earlier than
3.20.28, passwords will no longer work with it if you convert the
table. So to be safe, you should first upgrade to at least 3.20.28 and then
upgrade to 3.21.x.
The new client code works with a 3.20.x
mysqld server, so
if you experience problems with 3.21.x, you can use the old 3.20.x server
without having to recompile the clients again.
If you are not using the
--old-protocol option to
old clients will issue the error message:
ERROR: Protocol mismatch. Server Version = 10 Client Version = 9
The new Perl
DBD interface also supports the old
mysqlperl interface. The only change you have to make if you use
mysqlperl is to change the arguments to the
The new arguments are:
password arguments have changed
The following changes may affect queries in old applications:
HAVING must now be specified before any
ORDER BY clause.
The parameters to
LOCATE() have been swapped.
There are some new reserved words. The most notable are
If you are using MySQL 3.23, you can copy the
.MYI and the
.MYD files between different architectures
that support the same floating point format. (MySQL takes care of
any byte swapping issues).
The MySQL data `*.ISD' and the index files `*.ISM'
files) are architecture-dependent and in some case OS-dependent. If you
want to move your applications to another machine that has a different
architecture or OS than your current machine, you should not try to move
a database by simply copying the files to the other machine. Use
mysqldump will create a file full of SQL statements. You
can then transfer the file to the other machine and feed it as input to the
mysqldump --help to see what options are available.
If you are moving the data to a newer version of MySQL, you should use
mysqldump --opt with the newer version to get a fast, compact dump.
The easiest (although not the fastest) way to move a database between two
machines is to run the following commands on the machine on which the
database is located:
shell> mysqladmin -h 'other hostname' create db_name
shell> mysqldump --opt db_name \
| mysql -h 'other hostname' db_name
If you want to copy a database from a remote machine over a slow network,
you can use:
shell> mysqladmin create db_name
shell> mysqldump -h 'other hostname' --opt --compress db_name \
| mysql db_name
You can also store the result in a file, then transfer the file to the
target machine and load the file into the database there. For example,
you can dump a database to a file on the source machine like this:
shell> mysqldump --quick db_name | gzip > db_name.contents.gz
(The file created in this example is compressed.) Transfer the file
containing the database contents to the target machine and run these commands
shell> mysqladmin create db_name
shell> gunzip < db_name.contents.gz | mysql db_name
You can also use
mysqlimport to accomplish
the database transfer.
For big tables, this is much faster than simply using
In the commands shown below,
DUMPDIR represents the full pathname
of the directory you use to store the output from
First, create the directory for the output files and dump the database:
shell> mkdir DUMPDIR
shell> mysqldump --tab=DUMPDIR db_name
Then transfer the files in the
DUMPDIR directory to some corresponding
directory on the target machine and load the files into MySQL
shell> mysqladmin create db_name # create database
shell> cat DUMPDIR/*.sql | mysql db_name # create tables in database
shell> mysqlimport db_name DUMPDIR/*.txt # load data into tables
Also, don't forget to copy the
mysql database, since that's where the
grant tables (
host) are stored. You may have
to run commands as the MySQL
root user on the new machine
until you have the
mysql database in place.
After you import the
mysql database on the new machine, execute
mysqladmin flush-privileges so that the server reloads the grant table