Gentoo MySQL 5.1 Upgrade

Written by Peter Davies on 12 February 2009.

I recently had a particular need for query caching, but as usual its never quite that simple in Gentoo; the MySQL community edition that contains the query caching functionality is still BETA in the Gentoo portage and it couldn't be run on the same server as the web application because of conflicting blocks with PHP and Apache.

The following article/tutorial is from an adapted original source: http://www.gentoo.org/doc/en/mysql-upgrading.xml

STEP 1 - Backup data

Backup the existing data, or in my case do a VMware snapshot before performing any removals/builds. This was subsequently solved by creating a new 'clone' of an existing VM that already had an up-to-date portage (and a copy of the database that I was working on).

Cloning a VM introduces multiple issues including updating network settings etc - see this article

mysqldump \
  -uroot \
  --password='pass' \
  -hlocalhost \
  --all-databases \
  --opt \
  --allow-keywords \
  --flush-logs \
  --hex-blob \
  --master-data \
  --max_allowed_packet=16M \
  --quote-names \
  --result-file=BACKUP_MYSQL_5.0.SQL

STEP 2 - remove unwanted packages

Then run the following, on the VM that I cloned I no longer needed Apache, PHP or postfix so they needed removing:

emerge -C apache php postfix

Then the more usual stopping of MySQL, last chance backup followed by uninstallation:

/etc/init.d/mysql stop
tar cjpvf ~/mysql.$(date +%F"T"%H-%M).tar.bz2 /etc/mysql/my.cnf /var/lib/mysql/
emerge -C mysql

Use the following ls to check that a backup has been created, then remove old data:

ls -l ~/mysql.*
rm -rf /var/lib/mysql/ /var/log/mysql

STEP 3 - Install MySQL 5.1

Now install MySQL community, but you need to remove the package mask relating to "mysql-community" and also the entry for the virtual package for MySQL 5.1:

nano /usr/portage/profiles/package.mask

Now you can run the emerge on the community package:

ACCEPT_KEYWORDS="~amd64" emerge -pv =dev-db/mysql-community-5.1.21_beta
etc-update
revdep-rebuild

NB: I spotted a subsequent block doing a revdep-rebuild which was asking for an installation of MySQL 5.0 which is a pain - no time to solve this now... but a little fidderling with package.mask means I can at least get it to ignore <= MySQL 5.1 packages.

STEP 4 - Setup MySQL to run

You'll need to get MySQL to create the base tables by running the default install script like so:

/usr/bin/mysql_install_db --force
/etc/init.d/mysql reload
/usr/bin/mysqladmin -u root password 'pass'
ps -aef | grep mysql

STEP 5 - Add old database files

Then do the following to copy the old data back over the top of the new:

cat BACKUP_MYSQL_5.0.SQL \
     | mysql \
     -uroot \
     --password='pass' \
     -hlocalhost \
     --max_allowed_packet=16M

mysql_fix_privilege_tables \
     --defaults-file=/etc/mysql/my.cnf \
     --user=root \
     --password='pass'

STEP 6 - Modify config to allow network access from other machines

Update the bind-address where possible, making sure that the skip-networking is not enabled:

nano /etc/mysql/my.cnf
/etc/init.d/mysql restart

Log into mysql and execute the GRANT ALL (update: add grant option at the end) command followed by a flush:

mysql -uroot -p
GRANT ALL ON *.* TO root@'192.168.12.1' IDENTIFIED BY 'pass' WITH GRANT OPTION;
FLUSH PRIVILEGES;

STEP 7 - Add mysql to boot process

Finally, add mysql to the start-up applications if it does not already exist:

rc-update add mysql default

Finally I could then modify a remote phpMyAdmin script to access the DB remotely, then update my web application to do the same. I now have some query caching tests to perform :-)

< Prev Next >