David R. Heffelfinger

  Ensode Technology, LLC

 

Installing and Configuring a New MySQL Installation on Ubuntu


I recently got involved in a project that uses MySQL as its RDBMS. I am not a DBA, nor I pretend to be, but I wanted to set up a local MySQL database on my Ubuntu 8.10 Intrepid Ibex laptop.

I ran into some issues that were primarily because of my lack of experience installing or administering MySQL, I assume others trying to do the same will run into the same issues, therefore I decided to write this entry recording what I had to do to get MySQL going for the benefits of others (and possibly some time in the future I might forget the solutions my issues, therefore I can refer to this entry myself if I ever need to setup MySQL again).

The first problem I ran into was that MySQL does not show up in the "Add/Remove Applications" GUI tool, sure there are MySQL clients, but the RDBMS itself is nowhere to be found.

I tried "sudo apt-get mysql" and it turned out there was no package mysql to be installed, at this point I started to suspect that the Desktop version of Ubuntu I have does not include MySQL, not even in the repositories.

Luckily I turned out to be wrong, after some research I found out that the correct package to install is mysql-server, a "sudo apt-get mysql-server" took care of installing MySQL.

Creating a new database worked without issue. I issued the following query to create the database:

CREATE DATABASE somedatabase;

It worked without issues.

The next thing I wanted to do was to create a regular, non administrator user to use MySQL. Following the instructions at the MySQL 5.0 Reference Manual created a user allright.

The following query:

CREATE USER someuser IDENTIFIED by 'password';

did the trick "someuser" is the new mysql login name for the new user "password" is the user's mysql password. Originally I forgot to enclose the password in single quotes, rookie mistake, I corrected the mistake and the query worked without a problem.

Next I wanted to grant the newly created user all privileges on my newly created database, in order to do so, I issued the following query:

GRANT ALL on 'somedatabase' to 'newuser';

At this point I thought I was "good to go", I attempted to log in to mysql by issuing the following command in the command line:

mysql -u newuser -p

At which point I was prompted for a password, I entered the correct password and was denied access.

After a lot of googling and hair pulling, I found out that my grant statement did not work for "localhost", I had to issue a different grant statement to be able to log in using my newly created user.

The following query did the trick:

GRANT ALL on somedatabase to newuser@localhost

It turns out I had to specify use the username@hostname syntax to be able to log in from the same server where mysql is running.

After doing this I was finally able to log in as my newly created user and create the tables I needed for the application I am working on.

 
 
 
 
 

« November 2008 »
SunMonTueWedThuFriSat
      
4
5
6
7
9
11
12
14
17
18
20
21
22
23
24
25
26
27
28
29
      
Today

 
© David R. Heffelfinger