Technical Exhaustion

Tech tips from the weary
  • rss
  • Home
  • About

Upgrading mySQL 4 to mySQL 5 in FreeBSD

February 13, 2009 | 11:26 pm

First a caveat : the following steps worked well on a lightly used mySQL server in production (~5 queries per second average, heavily biased to select’s - spread over around 30 individual databases) but your experience may vary on more heavily loaded servers. This also uses the ports build and the portupgrade tool. mySQL 5 also brings changes to table structures, new features and changed features within the server. Ensure your applications or other database users are compatible with 5 before proceeding. Test accordingly!

Canonical steps :

  • Backup
  • Upgrade client first
  • Upgrade server
  • Start new server
  • Run post install update script
  • Test


  • Backup

  • - Use mysqldump or whatever other backup process you have in place to take a backup before you begin. Once the backup has been taken move it somewhere safe and mark it as being taken immediately before the upgrade from 4 to 5. The upgrade process from 4 to 5 will examine/alter the structure and elements of *all* your databases so this is a significant backup milestone to keep.

  • Upgrade client

  • - We have to upgrade the client libraries first. As the mySQL 4 and mySQL 5 ports are completely seperate in the ports tree, we will use portupgrade to install 5, replacing 4 along the way. This is a different process than simply updating a port as you would normally.

    cd /usr/ports
    portupgrade -o databases/mysql51-client mysql-client

    This should install the upgraded console and libraries. You can verify the version with :

    # mysql --version
    mysql Ver 14.14 Distrib 5.1.30, for portbld-freebsd6.2 (i386) using 5.0

    A good idea at this point is to check your server is still happy - run some queries and poke it a little bit.

  • Upgrade server

  • - The big one! We essentially do the same steps as the client upgrade. This will upgrade whilst the server continues to run. However, for some reason, the port install script stops mysql during the process - so this is the start of your, hopefully short, period of downtime.
    cd /usr/ports
    portupgrade -o databases/mysql51-server mysql-server

  • Start new server

  • - If all has gone well you can now start your new mySQL 5 server.
    # /usr/local/etc/rc.d/mysql-server start
    The rc.conf options have not changed so this will start right up. Login and confirm the new servers version. Handily this also checks that you *can* still login - which is an excellent first step to pass!

    # mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 2966
    Server version: 5.1.30 FreeBSD port: mysql-server-5.1.30

  • Run post install update script

  • - This updates the mySQL table structure and various other housekeeping tasks to migrate your data from mySQL 4 to 5 format. The official definition is thus :

    mysql_upgrade program that checks all tables for incompatibilities with the current version of MySQL Server and repairs them if necessary.

    Strangely we do need to have the server running (with the old out of date formatted data) to perform this update - but mySQL seems quite happy to do so. Be warned this does change the data in your main mySQL database and it also parses and updates all your other databases where necessary. Hope you took that backup!
    #mysql_upgrade -u root -p –datadir=/var/db/mysql
    Obviously amend the datadir path if necessary - although the above is the FreeBSD default. This will grind away for potentially some time depending on the size of your data.

  • Test

  • - That’s it, you’re all done! Now test, test and test. Check the data integrity and check your applications are still behaving as expected. You should also take the chance to update the client tools of other servers who may access this to version parity. It would also be worth rebuilding any ‘middleware’ ports you have that interface with mySQL (php5-mysql, p5-mysql etc).

    Have fun and good luck!

    Comments
    1 Comment »
    Categories
    FreeBSD, mySQL
    Tags
    FreeBSD, mySQL, ports, upgrade
    Comments rss Comments rss
    Trackback Trackback

    Navigation

    • Bacula
    • FreeBSD
    • Linux
    • mySQL
    • SGE
    • Solaris

    Search

    rss Comments rss valid xhtml 1.1 design by jide powered by Wordpress get firefox