Help hurricane victims by donating to the American Red Cross.

MysqlUpgrade

From Ollie's Web Site

1 Upgrading from mysql-4.1.11a-4sa (debian) to mysql-standard-5.0.19-linux-i686-glibc23

Upgrading from mysql-4.1.11a-4sa (debian) to mysql-standard-5.0.19-linux-i686-glibc23

Upgrading mysql, especially leaving the somewhat customized debian world for a standard binary, is a bit of a trial. Not that there is anything truly hidden or of a "gotcha" nature, but that it takes a while to fit the standard distribution to where everything is in debian.

Here's what I did to upgrade:

[edit]

Get Distribution and Unpack

First, I downloaded the standard tar via the mysql download page. Then I unzipped and untarred it into the /usr/local structure, followed by making a symlink to the directory called /usr/local/mysql.

ln -s /usr/local/mysql-standard-5.0.19-linux-i686-glibc /usr/local/mysql

This latter step saved a lot of needless typing in config files, as well as will make future upgrades easier by requiring only that I reset the symlink to the newer version without having to tamper with the various config files.

[edit]

Configure mysql

The next big steps involved setting up the my.cnf. Debian puts most of the config information into /etc/mysql, while the standard distribution looks for my.cnf in /etc. Again, a symlink to the rescue!

ln -s /etc/mysql/my.cnf

I then made the following changes: (note that the manual is pretty complete, but sometimes hard to find things in. The options for mysqld that you can put into my.cnf are found here.

basedir = /usr/local/mysql
language = /usr/local/mysql/share/mysql/english
character-sets-dir = /usr/local/mysql/share/mysql/charsets

Other variables to check are:

datadir
pid
port
socket
tmpdir
user

[edit]

Other Pre-startup Work

It's now time to move the old startup routines out of the way:

mv /etc/init.d/mysql /etc/init.d/mysql41

Then copy the new Sys V startup file (/usr/local/mysql/support-files/mysql.server) to /etc/init.d and set up a symlink to it named /etc/init.d/mysql so that the existing bootup startup routines will automatically start the version 5 server:

cp /usr/local/mysql/support-files/mysql.server /etc/init.d
ln -s /etc/init.d/mysql.server /etc/init.d/mysql

Debian stored all the old program binaries in /usr/bin, so I moved all of them to a new subdirectory (mysql41) and symlinked the new binaries into /usr/bin.

cd /usr/bin
mkdir /usr/bin/mysql41
mv /usr/bin/my* /usr/bin/mysql41
ln -s /usr/local/mysql/bin/my* /usr/bin

Similarly, for mysqld:

mv /usr/sbin/mysqld /usr/sbin/mysqld41
ln -s /usr/local/mysql/bin/mysqld /usr/sbin/mysqld

Finally, stop the old version of mysql and then start the new one:

/etc/init.d/mysql41 stop
/etc/init.d/mysql start

One warning: make sure you do the start from the shell's command prompt. I ran into problems doing so from a shell underneath midnight commander.

[edit]

Startup

Assuming no errors are reported, confirm the server's operation with:

ps aux --cols 120 | less

(you should see several mysqld processes). Then use

mysql --version

to confirm the right version server is running.

[edit]

Check Tables and Upgrade System Tables

The manual recommends running two steps next:

mysql_fix_privilege_tables; and
mysql-upgrade.

I had to modify the fix privilege tables script to include the correct basedir and socket, since it didn't appear to use /etc/my_cnf. Also, I inserted my root password into the script, since the -p option does not seem to work and I didn't want to put it on the command line:

cd /usr/local/mysql/bin
./mysql_fix_privilege_tables

Then I removed the root password from the script.

Running mysqlcheck seemed to go smoother:

mysqlcheck --check-upgrade --all-databases --auto-repair -p

Note the -p on the end to get the password prompt.

I did these successfully and then confirmed operation with phpmyadmin (this is an excellent web-based admin program for mysql). It showed the appropriate versions for both the server and the client, as well as showed the upgraded tables in the mysql database.

[edit]

Security

Now is a good time to review security options for your setup:

Read the manual section Securing the Initial MySQL Accounts, giving specific attention to (1) removing the anonymous login accounts; and (2) adding good passwords to the root account.