Slotted MySQL Upgrade and Migration Guide

-5 -2006-02-17 +6 +2006-03-10 -Upgrading Older MySQL Versions +Upgrading to an unslotted MySQL version from a slotted one

+Introduction

-This document covers how to upgrade to the latest available MySQL version. There -are currently three versions of MySQL supported in Portage: +Due to the negative response from our user base, the MySQL team has decided to +go back to unslotted MySQL. In other words, installing different versions of +MySQL on the same system is not supported anymore.

- 5.0 follows upstream releases in the stable tree and is 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: +If you had upgraded to the slotted version, this guide should help you upgrade +to the unslotted version.

3.23 has been deprecated
5.1 is currently under heavy development

- - -mysql-4.0.26 will be used as the starting point, and -mysql-5.0.18-r30 as the target version. Replace any of these versions -with your own. - -

- - - - -Installing the New Version

+"Unslotting"

-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. +The following script should do most of the work:

-# 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
-

+echo " building a unslotted copy of mysql "
+emerge --buildpkgonly '=dev-db/mysql-5.0.18-r60'
 
-
-Be sure to run etc-update or dispatch-conf in order to update the
-/etc/init.d/mysql startup script.
-
+echo " stopping the server  "
+/etc/init.d/mysql stop
 
-
-

- +echo " removing most of symlinks " +eselect mysql remove - -Copying Data to the New Server -
- +echo " config files " +mv /etc/mysql /etc/mysql.old +mv /etc/mysql-500 /etc/mysql -
-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. -
+echo " removal of remaining symlinks, and eselect stuff " +rm /usr/lib/mysql /usr/include/mysql /usr/bin/mysql_config /usr/lib/libmysql* +rm -rf /var/lib/eselect/mysql/ /usr/lib/mysql-[45]0[01] - -If you are upgrading between version revisions (ie. dev-db/mysql-5.0.18 -to dev-db/mysql-5.0.18-r30), you can simply stop the database, move from -one data directory to the other, and restart the server. This allows you to -skip this section entirely. - - -
-# 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 \ (If binary logs are enabled, use --master-data=0 instead) - --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" - "socket=/tmp/mysqld.sock" - ) - EOF -# /etc/init.d/mysql start -# mysql-500 --defaults-file=/etc/mysql-500/my.cnf --socket=/tmp/mysqld.sock < BACKUP_MYSQL_4.0.SQL -# /etc/init.d/mysql stop -# mv /etc/conf.d/mysql.orig /etc/conf.d/mysql -
- - -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 cases this is done by simply "piping" it -like so: iconv -f ISO_8859-1 -t UTF8 BACKUP_MYSQL_4.0.SQL | mysql-500 ---defaults-file=/etc/mysql-500/my.cnf. Manual adjustments of the SQL file -could be required, depending on the structure and the data contained within. - - -
-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. -
- - -
- - - -Migrating to the New Server -
- - -
-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. -
- -
-Before you switch to the new MySQL server, you will have to adjust the following -variable inside the /etc/conf.d/mysql. Its value might be empty: -
- -
-# nano -w /etc/conf.d/mysql -(Add the following line:) -mysql_slot_500=( ) -
- -
-When you're satisfied with the results, remove every test setting, stop the old -server and start the new one: -
- -
-# killall mysqld -# /etc/init.d/mysql stop zap 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 -
- - -
- - - -Rebuilding Applications -
- - -
-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 -
- - -Depending on the older MySQL version, the libmysqlclient.so version -may be 10, 12, 14 or 15. Please choose the correct -version of the old package. - - - -
- - - -Final Touches -

- +echo " unmerge slotted mysql " +emerge -C \ +=dev-db/mysql-4.0.26-r30 \ +=dev-db/mysql-4.1.15-r30 \ +=dev-db/mysql-4.1.16-r30 \ +=dev-db/mysql-4.1.18-r30 \ +=dev-db/mysql-5.0.15-r30 \ +=dev-db/mysql-5.0.16-r30 \ +=dev-db/mysql-5.0.17-r30 \ +=dev-db/mysql-5.0.18-r30 \ +=dev-db/mysql-5.1.3_alpha-r30 \ +=dev-db/mysql-5.1.4_alpha-r30 \ +=dev-db/mysql-5.1.6_alpha-r30 \ +app-admin/eselect-mysql -

-(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
+echo " ready to merge again MySQL "
+emerge --usepkgonly '=dev-db/mysql-5.0.18-r60'

-If you encounter any problems during the upgrade process, please file a bug report. +It is also recommended that you move data directories listed in +/etc/mysql/my.cnf. If you do that, don't forget to edit +/etc/mysql/my.cnf as well.

-- gentoo-doc-cvs@gentoo.org mailing list