This document covers how to upgrade to the latest available MySQL version. There are currently three versions of MySQL supported in Portage:

• 5.0 follows upstream releases in the stable tree and actively maintained.
• 4.1 follows upstream releases by implementing major bugfixes and security fixes, but no new features are added.
• 4.0 contains security fixes only.

There are two additional versions present, but are currently unsupported. This means that they may lack certain functionalities, and bug reports have very low priority. This document is not guaranteed to work with the following versions:

• 3.23 has been deprecated.
• 5.1 is currently under heavy development.

This step will require (re)moving some files from the running environment, so the first thing to do is backup the running database server, not the data. Once this is done, it's possible to remove the conflicting files with the slotted MySQL, and install the new version side by side with the current one.

# quickpkg dev-db/mysql
# rm -rf /usr/include/mysql /usr/bin/mysql_config
# for tmpfile in  /usr/lib/*mysql* 
  do
      mv "${tmpfile}" "${tmpfile}.TMP"
      ln -s "${tmpfile}.TMP" "${tmpfile}"
  done
# emerge -av =dev-db/mysql-5.0.18-r30

Be sure to run etc-update or dispatch-conf in order to update the /etc/init.d/mysql startup script.

Now we'll go ahead and dump the data. This will be imported into the new version of MySQL. mysqldump will be used from the slotted MySQL. Notice the -500 suffix to the mysqldump program name. This indicates it's from the 5.0.x version.

# mysqldump-500 \
  --defaults-file=/etc/mysql/my.cnf \
  --user=root \
  (Replace 'your_password' with your MySQL root password)
  --password='your_password' \
  --host=localhost \
  --all-databases \
  --opt \
  --allow-keywords \
  --flush-logs \
  --hex-blob \
  --master-data \
  --max_allowed_packet=16M \
  --quote-names \
  --result-file=BACKUP_MYSQL_4.0.SQL

A file named BACKUP_MYSQL_4.0.SQL is created, which can be used to recreate your data. The data is described in the MySQL dialect of SQL, the Structured Query Language.

Start the server without networking and user management and run the SQL script:

# mv /etc/conf.d/mysql /etc/conf.d/mysql.orig
# cat <<- EOF > /etc/conf.d/mysql
  NOCHECK=1
  DEBUG=3
  mysql_slot_500=(
    "skip-networking"
    "skip-grant-tables"
  )
  EOF
# /etc/init.d/mysql-500 start
# mysql-500 --defaults-file=/etc/mysql-500/my.cnf < BACKUP_MYSQL_4.0.SQL
# /etc/init.d/mysql-500 stop
# mv /etc/conf.d/mysql.orig /etc/conf.d/mysql

If there are applications still writing to the previous database, it's possible to setup a "Replication" relationship between the two databases. However, this document does not cover that procedure.

Please test applications against the newly installed server to make sure they work with it. Quite often every application has configuration settings to choose which port or socket to use for connecting to the database server. Simply start the server on an alternate port (for example 3307) and tell your application (or a test copy of it) to connect with those parameters. Please note that most applications will try to use the parameters found in the [client] section of /etc/mysql/my.cnf config file.

When you're satisfied with the results, remove every test setting, stop the old server and start the new one. Also, be sure to add /etc/init.d/mysql to the default runlevel, ensuring it starts at the next reboot.

# rc-update del mysql default
# rc-update add mysql-500 default
# /etc/init.d/mysql stop
# /etc/init.d/mysql-500 start

Next, unmerge the old version and make the new one the default. The unmerge command will be unable to remove some files, such as the ones moved in Code Listing 2.1. This is an intended behavior, and is meant to avoid breaking applications linked to the old MySQL version.

# emerge --unmerge --pretend mysql
# emerge --unmerge =dev-db/mysql-4.0.26
# cd /etc
# mv mysql mysql.$(date +%F_%H-%M)
# rm -rf /usr/lib/*.TMP
# for i in /usr/lib/*mysql* 
  do
    [[ -z "$( readlink -f $i )" ]] && [[ -L $i ]] && rm $i
  done

(emerge app-admin/eselect-mysql if you haven't already)
# eselect mysql list
# eselect mysql set 1
# eselect mysql show

After you remove your old MySQL installation, you can install the new version. Note that revdep-rebuild from app-portage/gentoolkit is necessary for rebuilding packages linked against MySQL.

# revdep-rebuild --soname libmysqlclient.so.12 -- -p -v
# revdep-rebuild --soname libmysqlclient.so.12

(Replace all instances of 'your_password' with your MySQL root password)
# mysql_fix_privilege_tables-500 \
     --defaults-file=/etc/mysql-500/my.cnf \
     --user=root \
     --password='your_password'
# mysql -uroot -p'your_password' mysql -e "FLUSH PRIVILEGES;"
# for tbl in $( mysql --silent -uroot -p'your_password' -e 'USE mysql ; SHOW TABLES LIKE "help%";' )
  do
    mysql -uroot -p'your_password' -e "use mysql ; TRUNCATE TABLE ${tbl};"
  done
# mysql -uroot -p'your_password' mysql < /usr/share/mysql/fill_help_tables.sql

If you encounter any problems during the upgrade process, please file a bug report.