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

    mysql_fix_privilege_tables –password=fdsmdm4s3

    The pass­word is not between quotes.

    Then everything works quite great.

    I used your tips to pass from Mysql 3.23 to 4.0.24 (woody -> sarge of the moment).

Sorry, the comment form is closed at this time.