Sunday, January 08, 2006

Installing MySQL 5.0 from RPM on Ubuntu 5.10 ("Breezy Badger") GNU/Linux

During the past two weeks, I've been quite busy getting to know Ubuntu 5.10 "Breezy Badger".

(In the remainder of this entry, the phrases within parentheses are probably a "duh!" to Ubuntu, Debian and other Linux users, but they may be of interest to Microsoft Windows users).

Non-linuxers and Microsoft Windows users (I am a Windows XP Professional User) particularly: Ubuntu is a Linux distribution that comes with a "Desktop" environment. The operating system and the applications can be controlled entirely using a Look and Feel that is quite similar to the Microsoft Windows Environment. Standard Ubuntu comes with the Gnome desktop environment. (I don't know how it is for other Linux Desktop environments, and I don't even know if there are differences between different Gnome deployments, but I've got a desktop with shortcuts, a taskbar, and a start menu. At any rate, enouh to get this Windows user going)

Ubuntu is free software: it's guaranteed to be free of charge forever, users are free to change and pass on the source code. Even the installation media are free! (I suspect that this could change soon. No harm done, the download will remain free for evermore).

Installing Software on Ubuntu


I browsed through some of the basic Linux stuff: mounting a drive, using the bash shell and some of the most common commands, getting to know the organization of directories and files, playing with desktops and upgrading/installing software.

Of course, all of these things can be important, depending upon the degree up to which you want to be in control of your system. However, the latter task, installing and upgrading software, is an important one for almost all kinds of users alike. Nearly everyone wants to be able to control his or hers computer up to a level that it does what you want it to do. And that nearly always means at least running the software that you want it to.

First, let me give a big thumbs up for the assorted software that comes with Ubuntu! (Yes, that's all free too!) A graphical file and directory browser (Nautilus, matches Explorer in Windows), a complete office Suite (Open Office, matches Microsoft Office and even allows file interchange with Microsoft Office), an Internet Browser (inevitably, Mozilla Firefox) and so much more. Among these many, many other pre-installed applications, Ubuntu comes with an autoupdate facility (like the Microsoft Windows update center) and the Synaptic Package Manager.

Now, this Package Manager (Synaptic is just the pre-instlalled flavour, there are lots of other ones) is a tool that allows you to review installed packages and install new ones. A package is just a bunch of files along with some information that describes installation of these files and the dependencies (if applicable). Usually, a package corresponds to an entire application, or a distinct module of an application. (Read more about package management in Ubuntu here.)

The OS has the utilities aboard to install the contents of the package. Also, a package database is maintained that records what files were installed and in what location. Package management also allows software packages to be uninstalled in a safe manner, that is without breaking the dependencies.

(As far as I can see now, there are about three different ways to install software on a linux system. In no particular order: 1: packages, 2: plain copying of files, usually after tar.gz extraction of a so called tarball, or 3: source code compilation, often accompanied by a ./configure configuration command shell script that generates the makefile for make.)

Package Management and Repositories


The Synaptic Package manager is just a front-end to the package management process. It's hooked up to one or more package repositories, offering a myriad of applications, or extensions to applications to be installed. Using the Synaptic Package Manager, installing an application is as easy as checking the packages of interest and confirming. (There's also a command line interface to access the repositories.) Depending upon the repository wherein the package resides, the necessary files are downloaded from the internet, or read from your CD.

The preconfigured Ubunutu repositories are really extensive. There's really a lot of stuff you can get there. I'm looking at 17821 distinct packages, in about a hundred categories. It's huge. Of course, some of these are different versions of the same packages, and some of these are things like language packs for a particular 'real' application, but there's really a lot of stuff available, ranging from Core System components to Games to Mathematical packages to Multimedia. There's just to much to sum it up here.

The disadvantadge of packages is that there must be some sort of authority that maintains the repositories and package products. This means that there will always be some sort of lag between the release of new software and the processes of packaging it and integrating the new packages with the repositories.

MySQL 5.0 for Ubuntu


Actually, you will encounter a lag the moment you're busy setting up a LAMP stack on your Ubuntu system. (The Ubuntu Wiki contains a topic on how to set it all up.) In doing so, I was very pleased indeed to see that I could install reasonably recent versions of both Apache (v.2.0.54, the single last 2.0 release at the time of writing) and PHP (v.5.0.5). However, the included MySQL version was only a 4.1 - not the one I'm settling for right now. The wiki topic also suggests you compile the MySQL 5.0 source yourself if your're not satisfied with 4.1.

Although challenging, I decided it was a good moment to take a look at what the MySQL reference manual had to say about all this. The whole of Chapter 2 is devoted entirely to installing MySQL on various platforms. Although I thought I'd read most of the manual, I discovered that I in fact never did read Chapter 2 (!). I simply never needed to review this documentation, as the installation process from the Windows Binary Distributions I used to download before is really self-explanatory.

Although the MySQL Reference Manual explains how to install MySQL using a source distribution, a installation using compiled binaries is recommended. Chapter 2.1.2, "Choosing a Distribution Format" does contain a discussion that points out in what cases one should choose the source distribution in favour of a binary distribution. It's probably safe to say that in most of cases, one should choose for a binary distribution.

After deciding to go for a binary installation, there are again two choices. Should you install a pre-packaged (RPM) binary distribution or a tarball (a gzip-compressed, tar-bundled distribution). Chapter 2.4, "Installing MySQL on Linux" is quite clear about this:

The recommended way to install MySQL on Linux is by using the RPM packages.


RPM's


I then reviewed what my copy of "Running Linux" had to say about this RPM format. RPM is an abbreviation of "RedHat Package Manager". (According to the book, RPM is not exclusively targeted towards the Redhat Linux distribution, despite it's name.) This is acually a tool just like the synaptic package manager, at least functionally: it is designed to install software distributions and to maintain a database of the installed files in order to manage dependencies, upgrades and deinstallations.

Having read the part on rpm's, I just went to the MySQL Downloads page, and downloaded all of the Linux x86 generic RPM Downloads. (There's a different RPM covering a particular part of funcitonality: server, testsuite, client, libraries and headers etcetera. You can decide for yourself what suits you best.) Then, I attempted to install the server package from the bash shell program that is installed by default:


roland@rpbdesktop:~$ rpm -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm
bash: rpm: command not found
roland@rpbdesktop:~$


The roland@rpbdesktop:~$ bit is my prompt, it is generated by the command shell. The prompt is your friend: it tells you what user is currently running the shell (roland), on what host the shell is actually running (rpbdesktop) and it also tells you what the current working directory is (in this case, ~, which is a shorthand for the current user's home directory). Finally, the $ tells you the current user is an ordinary user (it would've been # if I'd been the root user).

The rpm -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm bit is the command I typed. rpm is the executable I want to invoke, and the -iv is supposed to tell the executable to install (i) the package stored in the file (MySQL-server-5.0.18.glibc23.i386.rpm, one of the downloads, located on my Desktop directory residing directly under my home directory). (The v option stands for verbose, which is supposed to generate descriptive output concerning the installation process.)

The bash: rpm: command not found bit is returned by the bash command shell program. It informed me that the rpm tool was not found on my system. So, I searched the synaptic package manager for the string "rpm", and lo and behold, it returned me a list of packages, one of wich was titled "rpm". It was not checked, indicating is wasn't installed yet on my system. I marked it and applied the changes, and I gave it another crack:


roland@rpbdesktop:~$ rpm -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm
rpm: To install rpm packages on Debian systems, use alien. See README.Debian.
error: cannot open Packages index using db3 - No such file or directory (2)
error: cannot open Packages database in /var/lib/rpm


Ok, this is definitely an improvement: the rpm tool was in fact installed, and in a location where it could be seen by my command shell (namely, the usual place for this type of utility, the /usr/bin directory). However, it did not install the software. Here, I was getting errors too, but this time, the errors were generated by the rpm tool itself.

RPM for Ubuntu: alien


Apart from the errors, the tool also suggests using alien on Debian systems. Now, glancing over some pages from the Ubuntu website, I had already learned that Ubuntu is in fact based on Debian. So, I tried to invoke alien instead of rpm:


roland@rpbdesktop:~$ alien -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm
bash: alien: command not found
roland@rpbdesktop:~$


Ahah, but we know what to do next: search the Synaptic Package Manager for a tool called "alien". Of course, like rpm, alien wasn't yet installed. Like for rpm, installation of alien is just one click away. Later, I found out that while I was about to install rpm, I could've known I had to install alien too.

See, when you right click a package in the Synaptic Package Manager, a popup menu appears. From there, you can choose the "Properties" item (this is SO Windows!). A dialog is displayed, showing detailed information about the package. The info is organized in tabs, and when you click the "Dependencies" tab, you see a list of packages on which this package depens, as well as a list of packages that are marked "suggested". Now, the packages on which the current package depends are checked automatically when you decide to install the current package, just like it should. However, you should decide yourself if you want to install the suggested packages. In this case, it would've been the right choice.

Finally: Installing MySQL 5.0


Finally, I installed the MySQL packages using these lines:


cd ~/Desktop
sudo alien -iv MySQL-server-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-client-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-shared-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-devel-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-bench-5.0.18-0.glibc23.i386.rpm


(I omitted the promt and the output here. I pointed out already that one should decide for oneself what packages to install. According to the MySQL Reference manual a standard minimal installation of the server would comprise only the first two packages. Please review the pertinent manual page before you try this at home!).

There's one extra ingredient here in comparison to my previous alien command, and that's the sudo command prefixing the actual alien command. I think it is an acronym of Super User DO, and its function is to execute the following command (the alien command is this case) using the privileges of the root user. See this page from the Ubuntu wiki for more info on sudo, and the Ununtu specifics concerning it. (Tip: use gksudo for starting a gnome application with root privileges. I use this to place shortcuts, or launchers in the gnome lingo, to several configuration files on my desktop).

I put sudo there because the installation fails if you don't. You will get a bunch of errors indicating that you do not have appropriate permissions to copy files to here and there, or to create this or that directory. I don't know if using sudo is the appropriate solution, but hey,it works for me, at least for the time being.

Post-installation


After installation of the binaries, the installation is *NOT* completely finished. You still need to perform some post-installation tasks (this is very different from the windows installation procedure, which is more of a out-of-the-box experience). See also: Chapter 2.9.2 Unix Post-Installation Procedures from the MySQL Reference Manual.

First, you need to run the mysql_install_db script from a shell. It's located in /usr/bin.

sudo mysql_install_db --user=mysql


This installs the grant tables and creates the data directory.

I wanted to start the server automatically as part of the boot procedure. It took me a while to find out how to achieve that, and my impression is that there are quite some differences between the different Linux distributions. Finally, I found that this did the trick for me:


sudo cp support-files/mysql.server /etc/init.d/mysql
sudo chmod +x /etc/init.d/mysql
sudo update-rc.d mysql defaults


I didn't make it up myself, I copied it from the Unbuntu wiki, from the topic concerning the MySQL 5.0 Source installation procedure (see: MYSQL5FromSource, it's nearly at the bottom onder the title "Starting Mysql at boot time").

I thought they would never come: installation problems


After this otherwise succesful installation, I'm still left with one problem, and that's connections through a socket are not working. You can tell so by doing this:


roland@rpbdesktop:~$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)


The problem does not occur when the command line client is forced to connect using tcp/ip:


roland@rpbdesktop:~$ mysql -uroot -hrpbdesktop
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>


I tried to find information on that, but it all leads to a dead end. Especially on the MySQL installation forum there are some threads that mention this, and at least some of them are Debian users (see: http://forums.mysql.com/read.php?11,9689,56886). I found out that I could connect via tcp/ip myself, but there are some people that offer this as a solution in their posts, apparantly unaware that the underlying protocol is different.

However, I'm convinced it's just something silly, such as having a different actual location for the .sock file than is assumed by the client, or maybe missing or inappropriate (Linux File) permissions for that file. At least, that's what I'm investigating now (thanks, Felix, your post here: http://forums.mysql.com/read.php?51,52107,52111#msg-52111 gave me that idea!)

16 comments:

Giuseppe Maxia said...

I gave up on RPMs for MySQL long ago. For reason of my trade I need to have on my machine multiple versions of MySQL, sometimes with several instances running at the same time. Not easy to achieve with RPM.
I recommend using the binary installation packages (Linux non rpm package downloads). The procedure is quite more difficult than RPM, of course, but the additional benefit is that once you know how to install it in Ubuntu, you install it just as well in any other Linux flavour (or almost any other Unix system).
Another bonus is the fact that you can install several versions at once, referring to the current one by means of a symbolic link, and before getting rid of an older one you can try the newest release, and if any problem arises you just switch back to the working version, without need to uninstall and reinstall anything.

Giuseppe

Anonymous said...

Isn't the RPM installation supposed to run mysql_install_db by itself ?

rpbouman said...

Hi Giuseppe,

thanks for your comments! I find your arguments in favor of binary installation packages very convincing.

Actually, I accidentally broke my initial installation while trying to fix the socket problem. In retrospect, I think it was a typical newbie error: just one

sudo chown -R root .

to much in the wrong directory. After that, I could not even start the daemon. It just kept complaining about /var/log/mysql/mysql-bin.index (or so...I really should start taking notes)

I then decided to reinstall, so I uninstalled the packages, which went remarkably well. I could not detect any left-behinds.

Alas, Reinstallation did not solve that problem.

Then, I received your comment, and I decided to go right ahead and try out your suggestions. Again, I got the same error (/var/log/mysql/mysql-bin.index)

To me, that was a indication that my previous mistake with the chown command broke something fundamental on my system making it alltogether impossible to run mysqld regardless of the installation procedure.

Another observation that supports that is that I eventually did compile MySQL from source - still, this error remained.

But now, everything seems to be hunky dory again! I just made mysql owner of the data dir (/var/lib/mysql), the dir with the run files (/var/run/mysql) and of the log dir (/var/log/mysql).

What I really should do now is uninstall it all, and retry your suggestion. And of course, the alien/rpm installation too, just to see if it is reproducible.

I think I will pursue this soon, but not right away. For now, I'm way to glad I finally got it working again. So I'll just indulge myself in that for a little while.

BTW, I got a private email (thank you, Ben!) which suggested I install from a debian package here: http://packages.qa.debian.org/m/mysql-dfsg-5.0.html
(I would've if I knew they existed for MySQL 5 already).

Anyway, I guess I learned 2 important lessons in general:
1) spend some more time researching alternatives, and seek an expert's advice *BEFORE* trying
2) take better notice of the commands I'm ramming into the linux terminal, both before and after issuing them.

Needless to say - I learned a bunch of things that are less sharply to define by running into problems. Actually, I quite enjoyed it - this time that is.

Anyway, Giuseppe, Ben, thanks a lot for the feedback, it is most appreciated.

Roland

Anonymous said...

Yay!!!! Linux is growing and so is Roland.

Anonymous said...

Hi

I found your mySQL blog a real help... think I've got it working... can access from phpmyadmin, but not from my development machine.. sure it's something small. Try this mySQL version. http://www.backports.org/debian/pool/main/m/mysql-dfsg-5.0/

It seems to work

rpbouman said...

Hi!

thanks for the link to the Debian package. Just for the record, is the problem you're experiencing (..not from the development machine...) with this intallation? Or with the one I'm suggesting?
Is your development machine also the machine on which the database is running? Is phpmyadmin installed on the machine where the database is running (sorry, me no phpmyadmin user). Anyway, my hunch is you should look for the protocol that connects to mysql. If phpmyadmin connects to the socket, you should look into the tcp/ip protocol (maybe your database host has got a firewall, blocking the mysql port). If i's the other way around, you should look into the permissions set for the socket file like I did (see my reply on Giuseppe's comment on the top of this list of comments)

Anonymous said...

The RPM installation works just fine. Keep a close eye on the /var/lib/mysql/(yourhostname).err file, you'll be able to see what's failing when the server tries to start.

After installing the RPMs with alien, the mysql server hasn't been started yet, so running mysql -uroot will fail (giving you the "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'" error).

The first thing I did was look "mysql" under /etc/init.d/. It was there, so I issues this command:

/etc/init.d/mysql start

It attempted to start the server, but failed. Then I checked the error file I mentioned above, and found this error:

[ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists!

So I issued the following command:

useradd mysql

And tried to start the server again. It failed again! Checking the error file, I found a new error:

060303 15:09:08 mysqld started
060303 15:09:08 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
060303 15:09:08 mysqld ended

Okay, so the mysql user needs to be the owner of the /var/lib/mysql directory and sub-directories. So I issued this command:

chown -R mysql /var/lib/mysql

At this point, I went ahead and tried to run the "mysql_install_db --user=mysql" command. I'm not 100% sure if this was necessary or not, but the command executed successfully. It's possible that the RPM automatically issues that command, but even if it did, I didn't have a mysql user yet so i'm sure it didn't work. And after I ran the script,

/etc/init.d/mysql start

started the server successfully, and

mysql -uroot

connected me to mysql!
Hope this helps someone else out!

-Eric Fowler

rpbouman said...

Hi Eric,

thanks for your most helpful comments. I realise now that I indeed did forget to write down some essential pieces of information. Maybe I should've repeated the procedure a couple of times in order to present a complete list of actions.

I indeed created a mysql user, and a corresponding usergroup manually. I did this before even founding out about alien. This is because I initially followed the procedure as described in chapter 14 of Pro MySQL (By Michael Kruckenberg and Jay Pipes, Apress books). This start right away with creating the mysql user and group.

Anyway, thanks for taking the time to post your experiences here. I'm sure they will be of great help to those that need to intall the MySQL RPM's on Ubuntu or another Debian based Linux flavour.

Anonymous said...

ROLAND! Excellent. Thank you for posting this. I am a windows user migrating to linux. I chose Ubuntu 5.10 somewhat randomly: at the end of 2005 I saw an article about a linux OS that had recently won an award, i can't remember the specifics, but that was enough. My interest in Linux began in 1999 because of frustration with the Monopoly of MS, but i didn't have the motivation/resources until recently to actually try Linux. Get to the point: I've been trying to install MySQL but i'm having trouble; your posting is very helpful. I'm going to re-try the installation using this info. I have picked up bits and pieces from the Ubuntu support pages and linuxquestions.org, but this posting was written at the right level for a windows convert. Thanks again, Rob K, Duluth, MN USA.

rpbouman said...

Rob,

thanks for your kind comments. I hope you'll manage the installation.

Some other people had some trouble still, but they gave feedback in the comments section, so you might want to read these too and see if you pick up new information.

By the same token, I would be most grateful if you could post back your comments and insights when you finish your installation, especially if you find something else I left out.

kind regards, and happy MySQLin'

Roland.

Anonymous said...

Hi Roland, great directions! I'm following them step by step and have only hit a couple of snags.

1) For running mysql on reboot: I had no support-files directory anyplace, but I did have mysql.server in init.d already, so no worries there.

2) I received the exact same sockets problem you did. I think it may be a rights problem though.

When I ran:
joe@ubuntu:~$ mysql -uroot

I received the same error as you did:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

However if I ran:
sudo mysql -uroot

Everything works as expected. I think maybe the rights on /var/lib/mysql/ may be a little too strict?

I'm not sure as I'm new to the linux game myself.

Joe Cairns

rpbouman said...

Hi Joe,

thanks for your kind feedback. As a matter of fact, I think you are right about the permissions for /var/lib/mysql.
Eric Fowler has posted a helpful comment about this here which confirms your point exactly.

Anonymous said...

Hey there Roland, yeap, I read Eric Fowler's wonderful comments and followed those as well. Between both of your posts I got just about everything working to 95%.

However I still encountered the problem I documented. In fact where Eric could just:
mysql -uroot

and connect, I could not. I had to:
sudo mysql -uroot

That's what got me thinking about the /var/lib/mysql directory. My ubuntu user. "joe", could not even read the contents of the directory.

I couldn't use any of the local administrative tools I had to connect to the DB.

I ened up loosening the security on that directory by givinig others read and execute privlidges like so:
sudo chmod o+rx mysql

Everything seems to be going 100% for me now. Thanks again for posting this great entry!

-Joe Cairns

rpbouman said...

Hi Joe,

thanks again for the feedback!

It's people like you that keep this entry alive, and add value to it so that other people can enjoy a smoother experience still installing MySQL.

Right now, I'm swamped, but when I've got some time, I think I will rerun all the steps myself, and see if I can make a summary of all the feedback you guys provided.

Thanks again!

gumuruhsspj said...

but the links given by you... now is not available anymore.

Could u give more step once we have
done GIving Command using Alien for this mySQL Server n Client installation?

i have the rpm in my disk
but have no internet connection for it.

any ideas?

Matt Slay said...

What a great, detailed, and educational post this is. Thank your for taking so much time to cover this RPM issue in such great detail. As an Ubuntu newbie, I really learned a lot from you by reading this entire article.

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...