MySQL, upgrade and switch to slotted guide

1.6 2006-01-04 Upgrading from old versions of MySQL

This document cover how to upgrade to the latest version available. There are currently three version of MySQL supported in portage:

5.0 Follow upstream schedule (stable tree, active bugfixing)
4.1 Follow upstream schedule (only changes to a large userbase)
4.0 Security fix only

There are two additional version presently, but are currently unsupported meaning that bug reports have very low priority any may lack functionalities.
The current document is not guaranteed to work with them.

3.23 ancient
5.1 active development

The present document will use mysql-4.0.26 as start point and mysql-5.0.18-r30 as the target version
Replace any of these with your own.

Install the new version

This step require to (re)move some files from the running environment, so the first thing to do is a backup of the running database server, (still not the data).
Done that, 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 update the /etc/init.d/mysql startup script with etc-update or dispatch-conf.

Copy the data to the newly installed server

Lets go to to dump the data, this will be imported in the new version of mysql.
We are going to use mysqldump from the slotted MySQL.
Notice the "-500" suffix to the mysqldump program name, it's from the 5.0.x version.

# mysqldump-500 \
  --defaults-file=/etc/mysql/my.cnf
  -uroot \
  --password='your_password' \
  -hlocalhost \
  --all-databases \
  --opt \
  --allow-keywords \
  --flush-logs \
  --hex-blob \
  --master-data \
  --max_allowed_packet=16M \
  --quote-names \
  --result-file=BACKUP_MYSQL_4.0.SQL

Now a file named BACKUP_MYSQL_4.0.SQL exist, 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
# echo '
  NOCHECK=1
  DEBUG=3
  mysql_slot_500=(
    "skip-networking"
    "skip-grant-tables"
  )
  ' > /etc/conf.d/mysql
# /etc/init.d/mysql-500 start
# mysql-500 --defaults-file=/etc/mysql-500/my.cnf < BACKUP_MYSQL_4.0.SQL
# mv /etc/conf.d/mysql.orig /etc/conf.d/mysql
# /etc/init.d/mysql-500 stop

To convert the data to UTF-8 during this step you MUST remove "--hex-blob" from the mysqldump option, then filter the data through a converter like "iconv".
In most case this is done simply "piping" it like this:
"iconv -f ISO_8859-1 -t UTF8 BACKUP_MYSQL_4.0.SQL | mysql-500 --defaults-file=/etc/mysql-500/my.cnf"
Also manual tweaking of the SQL file could be needed, depending on the structure and the data contained in it. If there are applications still writing to the previous database, it's possible to setup a "Replication" relationship between the two databases, however this will not be covered here.

Switch to the new server

If you need to be sure that applications are working with the newly installed server, please test them against it, quite every application has configuration settings to chose which port or socket to use to connect 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.
Pay attention that most of them 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 testing setting, stop the old server and start the new one.
Also make it the server that will start at next reboot.

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

unmerge the old version, and make the new one the default. The unmerge command will be unable to remove some files, the ones moved in Backup old package and prepare installation This is an intended behavior, and avoid breaking application 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
# rm -rf /usr/lib/*.TMP
# for i in /usr/lib/*mysql* ; do
    readlink -f $i || [[ -L $i ]] && rm $i
  done
# eselect mysql list
# eselect mysql set 1
# eselect mysql show

Rebuild applications

After you got rid of your old MySQL installation, you can now install the new version. Note that revdep-rebuild is necessary for rebuilding packages linking against MySQL.

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

Depending on which really is the old version libmysqlclient.so number may be 10, 12, 14 or 15, chose the one of the old package.

Final touches

# 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 encountered any problems during the upgrade process, please report them at our Bugzilla.