Sunday, May 2, 2010

Install latest Version of MySQL on Ubuntu

Some days ago I installed the latest Subversion Version to Ubuntu 8.04 (Hardy). It wasn't quite easy, so I was afraid that installing the up-to-date version of MySQL would be a similar trouble.

Luckily, it turns out that it's easy to do. Here we go:

First check the version that is available currently:
root@dev:~# apt-cache madison mysql-server
mysql-server | 5.0.51a-3ubuntu5.5 | hardy-updates/main Packages

Ok, this one too old. We need at least version 5.1.45. Fortunately we just need to add the Lenny debian repository:
root@dev:~#vi /etc/apt/sources.list
add --> deb lenny all
root@dev:~# apt-get update
Fetched 2380kB in 1s (2140kB/s)
Reading package lists... Done
root@dev:~# apt-cache madison mysql-server
mysql-server | 5.1.45-0.dotdeb.0 | lenny/all Packages
mysql-server | 5.0.51a-3ubuntu5.5 | hardy-updates/main Packages

Install the new version of MySQL server (it removes an old MySQL version automatically):
root@dev:~# apt-get install mysql-server-5.1
Important: Select always 'y' on questions during the installation process. This includes the question to override the /etc/mysql/my.cnf. Otherwise you need to adjust it manually, because the "skip-bdb" option doesn't work with version 5.1.

Finally check:
root@dev:~# mysql --version
mysql  Ver 14.14 Distrib 5.1.45, for debian-linux-gnu (x86_64) using readline 5.2

Finishing basic MySQL setup:

Now you need to configure your DB-system as described in the MySQL manual:

root@dev:~# mysql_install_db --user=mysql
Installing MySQL system tables...
ERROR: 1136  Column count doesn't match value count at row 1
100502 13:26:07 [ERROR] Aborting
100502 13:26:07 [Note] /usr/sbin/mysqld: Shutdown complete
Installation of system tables failed!  Examine the logs in
/var/lib/mysql for more information.

Ok, there are databases in the old 5.0.x format, let's upgrade them:
root@dev:~# /etc/init.d/mysql stop
root@dev:~# /usr/sbin/mysqld --skip-grant &
root@dev:~# mysql_upgrade
root@dev:~# mysql_install_db --user=mysql

Alternativly, delete the stuff in your mysql-data folder (in case you setup a fresh DB):
root@dev:~# /etc/init.d/mysql stop
root@dev:~# rm -R /var/lib/mysql/*
root@dev:~# mysql_install_db --user=mysql

Now follow the MySQL documentation:
root@dev:~# mysqld_safe --user=mysql &
root@dev:~# mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.1.45, for debian-linux-gnu on x86_64
root@dev:~# mysql -e "SELECT * FROM user" mysql
root@dev:~# mysql -e "select version();" mysql

And secure and setup some accounts:
root@dev:~# mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypasswd');
mysql> SET PASSWORD FOR 'root'@'' = PASSWORD('mypasswd');
mysql> CREATE USER 'debian-sys-maint'@'localhost' IDENTIFIED BY 'wze0EguNSaU52jCH';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' WITH GRANT OPTION;

If you need external access to your MySQL (instead of just allowing it from localhost) do something like this:
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'mypasswd';
root@dev:~# vi /etc/mysql/my.cnf
           --> Change:  "bind-address" to ""
root@dev:~# /etc/init.d/mysql restart