Upgrading MySQL on Debian

 Technology  4 Responses »
Feb 242005
 

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 Debi­an 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 Debi­an because it was set up on stable in the first place and I’ve nev­er really needed to change that and I’d rather have the secur­ity patches be applied auto­mat­ic­ally. Except for, the Debi­an 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 Debi­an works. This is because Debi­an Does Everything Dif­fer­ently (DDED). I’m sure there is lots of logic in how Debi­an does things, I just have nev­er quite taken the time to fig­ure it out. And up till now, I did­n’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 success.

So here’s the step-by-step upgrade details I needed. Debi­an experts have no need to read any further…

  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 contents
    Package: *
    Pin: release a=backports.org
    Pin-Priority: 995
  7. apt-get upgrade now works! Hurrah!
  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 does­n’t like that. Even hard­cod­ing the pass­word in the script does­n’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.
/* ]]> */