MySql installation on Mac

In week 4 we started working with MySql in the Intro to Computing & Web Technologies module.  As MySql was already installed on the college's PC, we were able to get straight into creating databases, generating tables & inserting data into the tables.  At home, on my mac computer, it was a different story.  The following is a revision of a post I made on a college forum on how to install it on a mac.

After downloading the MySql Community Edition from the MySql downloads page, I opened the folder containing the installation files.  
 

Opened the MySql 5.5.24.pkg, Startupitems.pkg, & prefPane & followed the instructions to install each of them.  I then went to System Preferences, opened the installed MySql Pref Pane, and clicked on Start MySql server.  I also ticked the 'Automatically Start MySql Server on Startup' tickbox.

Using the sudo command in a terminal window, type the following:

:~$ sudo /library/startupitems/mysqlcom/mysqlcom start

(sudo is a command that allows you to assume the privileges of a superuser to run a program - for that reason it prompts you to enter a password, as I'm the administrator on the computer, I put my usual login password in). A message appears saying the server is started.

To log on to the server, type the following at the terminal prompt:

:~$ /usr/local/mysql/bin/mysql

At this point, rather than having to type out that command each time I wanted to log on to the server, I tried setting up a shortcut for the '/usr/local/mysql/bin/mysql' path.  To do this, in terminal press Ctrl D - this will close the MySql client. At the command line type the following:

:~$ alias mysql=/usr/local/mysql/bin/mysql

However, it looks like this only works within the same terminal session.  If I quit terminal, the alias settings seem to get lost.  (edit: I think I've found a solution, I'll test it out & update here later).

Initially, once I had started the MySql client, & tried creating databases, I was getting the following error:

  ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'john'

After a trawl through the relevant manual, I think I have an understanding of what caused that error. When you install the mysql server, it creates a number of users - the 'root' user & an anonymous user ''.  By default, none of these users have passwords set up for them.  When you go to'/usr/local/mysql/bin/mysql' in the terminal, unless you specify a username, you will be logged in as the anonymous user.  If, when you try to create a database, you are getting the message "ERROR 1044 (42000): Access denied for user ''@'localhost' to database", it means that the anonymous user '' does not have the correct privileges to do whatever you're trying to do.

Instead of logging in as the anonymous user, you could try logging in as the 'root' user.  To do this, when you type in '/usr/local/mysql/bin/mysql', add in -u root -p after it, i.e. to look like this:

 :~$ /usr/local/mysql/bin/mysql -u root -p

This will prompt you for a password.  Because there are no passwords set up during the installation, you can just press return to proceed.  You should be logged in now as the 'root' user & should be able to create & amend databases.

To see what other users are set up on the server, type the following at the mysql prompt:

mysql> SELECT User, Host, Password FROM mysql.user;

 You should get a list of users like this:

The anonymous user can be seen there as a blank in the 'user' column .  I've since added my own user there 'John', without a password.  To add a user, I followed the instructions here, but I left out the password bit.  See below:

So now when I log on to the server, I specify that I want to log in as myself rather than the 'root' user or the anonymous user (by typing '/usr/local/mysql/bin/mysql -u John').  I can check my privileges by typing 'show grants;' at the mysql> prompt.

You can set a password too for any of the users (root, anonymous or whoever) by following these instructions.