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.
