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:
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 :-)