upgrading mysql client and server

[Matthew Seaman]

Dave wrote:

>    I've got mysql server and client v4.1 running on a FreeBSD6.x box.
> They were installed via ports not packages using custom make options
> which i have in portupgrade's pkgtools.conf file. I now would like to
> upgrade both of them to v5, without causing dependency issues with
> installed apps or with my databases. What i was wondering is is there an
> easy way of doing this? I am concerned about my databases, i've done
> mysqldumps on them, but i have to manually go in and create information.
> I'd rather feed the mysql command a complete file and have it
> automatically regenerate everything. I'm also concerned about any
> compatibility issues. Any help appreciated.

This isn't an amazingly difficult upgrade.  However there are a few
gotchas you should be aware of.

    *) MySQL 5.0.x is pretty much backwards compatible with 4.1.x -- ie.
       everything that's in 4.1.x is also in 5.0.x (but the converse is
       not true).  On disk data formats may be slightly changed, but you
       should be able to run the mysql_upgrade script to convert everything
       to 5.0.x style.  mysql_upgrade comes as part of the mysql-client-5.0.x
       port/package

       You should certainly take good backups of your 4.1.x databases,
       but if everything goes according to plan, you won't actually need
       to load them into the upgraded MySQL server.

    *) Upgrade the MySQL client port and its dependencies first.  MySQL
       client 5.0 will interoperate pretty well with MySQL server 4.1 but
       not necessarily vice versa.  You will also need to recompile
       anything that links against the MySQL shlibs, as the ABI has
       changed between the versions.  Using portupgrade makes that fairly
       simple, and portupgrade's feature of preserving any shlibs removed
       from old ports in /usr/local/lib/compat/pkg means the old 4.1
       dependent stuff will still continue to work even after you've updated --
       again, not that you should be planning to use mysql dependent
       programs while upgrading, but having that capability is useful if
       you run into problems.

    *) Make sure to shut down the mysql daemon before attempting to upgrade
       it.  I know this is bleeding obvious, but it's also easy to forget.
       Not doing this can lead to data corruption, and in fact the port will
       automatically kill any running mysql at re-install time. But do be sure
       to double check.

The sequence of thing you'll need to do is as follows:

    0) You've got good backups, haven't you?

    i) Examine the output of 'pkg_info -R mysql-client-\*' and note down
       any daemon processes that depend on MySQL.  You should shut those down
       for the duration of the upgrade process.  Alternatively you could
       drop to single user mode to carry out this whole procedure.

   ii) Edit /etc/make.conf and insert in it:

         WITH_MYSQL_VER= 50

       You can put extra compile time flags for the mysql port into
       /etc/make.conf at the same time.  eg. I tend to use something
       like this, which you should modify to suit your own needs:

         .if ${.CURDIR:M*/databases/mysql*}
         WITH_CHARSET=latin1
         WITH_XCHARSET=none
         WITH_COLLATION=latin1_swedish_ci
         WITH_OPENSSL=yes
         BUILD_OPTIMIZED=yes
         WITH_INNODB=yes
         WITH_ARCHIVE=yes
         WITH_FEDERATED=yes
         WITH_NDB=yes
         .endif 

       Or you can use the MAKE_ARGS array in pkgtools.conf to achieve a similar
       effect.  (Note that pkgtools.conf only applies to portupgrade et al,
       /etc/make.conf applies generally to any way of using the ports)

  iii) Make sure the database is shut down:

         /usr/local/etc/rc.d/mysql-server stop

   iv) Upgrade mysql-client, replacing mysql-client-4.1.x in the dependencies
       between your installed ports:

        portupgrade -o databases/mysql50-client -f mysql-client-\*

   iv) Upgrade mysql-server, replacing mysql-server-4.1.x in the dependencies
       between your installed ports:

        portupgrade -o databases/mysql50-server -f mysql-server-\*

    v) Force a recompile of everything else that depends on mysql-client so
       that they all link against the upgraded shlib:

        portupgrade -Nfi -r mysql-client-\* -x mysql-client -x mysql-server

       Certain MySQL related ports are specific to one mysql version, such
       as eg. p5-DBD-mysql50-3.0006 or mysql-scripts-5.0.24 and you may have
       to use the 'portupgrade -o port/name -f pkg' style of command to get
       them upgraded properly.

   vi) Restart mysql-server.  Immediately run the mysql-upgrade program:

        mysql_upgrade -v -b /usr/local -d /var/db/mysql -u root

       (Will prompt you for the root at localhost password to the DB)
       That should fix up anything that needs fixing, especially the user
       grant tables.

       Examine /var/db/mysql/$HOSTNAME.err to see if there are any obvious
       problems, and do whatever tests you need to on the contents of your
       DB to assure yourself it has come through intact.

  vii) Restart all mysql client programs.  Test that they function correctly.

 viii) Make haste to nearest public house and order yourself a pint of
       your favourite beer.  You've earned it.

	Cheers,

	Matthew

-- 
Dr Matthew J Seaman MA, D.Phil.                       7 Priory Courtyard
                                                      Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey         Ramsgate
                                                      Kent, CT11 9PW

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 250 bytes
Desc: OpenPGP digital signature
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20060902/c2264842/signature-0001.pgp