I’ve been wait­ing patiently for the updated Word­Press to come out. Once it did, I decided I should really update MySQL as well, so that I could finally serve pages prop­erly as UTF-8. I’d been run­ning MySQL 3.23, and so I needed to upgrade to 4.0 to get real UTF-8 sup­port. I also con­tem­plated upgrad­ing to MySQL 4.1.

The pro­cess was just com­plic­ated enough that I figured I should do the backup brain thing and dump the pro­ced­ure here; it might help someone else to have the gory details doc­u­mented, and it will cer­tainly help me when I come to upgrade!

I haven’t really used a Unix sys­tem since I had to move to Win­dows in 1996; for­tu­nately Debian makes upgrad­ing and installing really easy (who­ever came up with the apt-get sys­tem deserves lots of praise). I run the stable ver­sion of Debian because it was set up on stable in the first place and I’ve never really needed to change that and I’d rather have the secur­ity patches be applied auto­mat­ic­ally. Except for, the Debian stable ver­sion of MySQL is, of course, 3.23 and not 4.0. The best solu­tion to this seemed to be to install the back­port of MySQL 4.0 from backports.org. In the­ory this is really easy, of course in prac­tice it does take a cer­tain know­ledge of how Debian works. This is because Debian Does Everything Dif­fer­ently (DDED). I’m sure there is lots of logic in how Debian does things, I just have never quite taken the time to fig­ure it out. And up till now, I didn’t need to. I did have one big advant­age though, Edd Dum­bill was online and was able to help me fig­ure out those little details that make the dif­fer­ence between frus­tra­tion and suc­cess.

So here’s the step-by-step upgrade details I needed. Debian experts have no need to read any fur­ther…

1. Add the line deb http://www.backports.org/debian stable mysql-dfsg into your /etc/apt/sources.list file (I don’t know why it’s called dfsg and I’m not sure I want to know).
2. Run apt-get update and then apt-get upgrade, or apt-get dist-upgrade.
3. Find that although the pack­age list gets updated, noth­ing hap­pens and the mes­sage is “0 to install”
4. Edd sug­gests check­ing the policy with apt-cache policy mysql-server.
5. This shows that Sur­prise! the upgrade isn’t being installed.
6. To increase the pri­or­ity, we need to pin the back­port. Edd tells me the magic incant­a­tion, which is to cre­ate a /etc/apt/preferences file with the con­tents
Package: * Pin: release a=backports.org Pin-Priority: 995
7. apt-get upgrade now works! Hur­rah!
8. Next to run mysql_fix_privilege_tables to fix the GRANT tables etc. Except for, the MySQL data­base root account has spaces in the pass­word and this script doesn’t like that. Even hard­cod­ing the pass­word in the script doesn’t work.
9. Use mysqladmin to change the root pass­word so it no longer has spaces in it (BTW, does any­one else find it annoy­ing that some MySQL things use executable u=username and oth­ers use executable u username? For­tu­nately PHPMy­Ad­min makes most of my MySQL admin duties much easier).
10. Suc­cess! The blog still works, the tables appear to have been fixed, and life goes back to nor­mal. I decide to not upgrade to MySQL 4.1 just yet, since I don’t really need to. This box in the base­ment acts as my fire­wall, DSL con­nec­tion, and web­site host; stick­ing with a stable con­fig­ur­a­tion strikes me as being a good idea most of the time.

### 4 Responses to “Upgrading MySQL on Debian”

1. […] ation­san­lei­tung für mysql-4 auf debian stable(wo msql-3 noch Stand der Dinge ist) hat mir Upgrad­ing MySQL on Debian sehr geholfen.

[…]

2. I enjoyed the art­icle, but there is a cor­rec­tion:

Add the line deb http://www.backports.org/debian stable mysql-dsfg into your /etc/apt/sources.list file (I don’t know why it’s called dsfg and I’m not sure I want to know).

The folder is actu­ally mysql-dfsg, not mysql-dsfg, and it apt-get update won’t work if you don’t make the change.

3. Fixed. Thanks for the cor­rec­tion!

4. I would add the syn­tax if the root pass­word is required: