My perfect OS X development environment - Installing MySQL

>> Friday, 14 November 2008

Chances are if you're building websites you'll use some kind of data along the way, but where to keep it? the obvious choice of course is a database. Now there are a ton of databases out there that you could use but to get us started I'm going to use MySQL community edition a fantastic open source (and free!) database engine used by many hundreds of thousand sites across the web for their data storage needs.

Go to mysql.com and download mysql 5.0 for mac os x 10.5 (x86_64)

Once downloaded mount the file and run the mysql installer using the provided defaults.

When the installer is finished run the other installer for the mysqlstartupitem and again install using the defaults.

In the disk image you will also see a file called mysql.prefpane which is a handy preference pane that goes into your system preferences and lets you stop and start the mysql server with the push of a button.

To install it copy the file to /Library/PreferencePanes

Open system preferences and you'll see the new pane in the bottom row. click it and start the mysql server.

Now we need to secure the default mysql login so open terminal and run the following commands

First we need to add mysql bin to the path

$ vi ~/.profile

Add this line

export PATH=$PATH:/usr/local/mysql/bin

Save and quit then run the following script bundled with MySQL to secure the default installation

$ sudo /usr/local/mysql/bin/mysql_secure_installation

This will run you through securing your installation, select the following options:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...


All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Now that we have MySQL Installed and pretty well secured you can access it from the command line using

$ mysql -u root -p [enter]

Once logged in you'll see a prompt like this
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.67 MySQL Community Server (GPL)

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

mysql>
into which you can then run any commands you want to create databases, users etc.

However I would advise using a GUI tool so you can administer your databases in a much nicer and easier fashion, there are a number of them out there for the mac (like Aqua Data Studio, or MySQL.com's very own Mysql Administrator and Query Browser tools)

In the next post of this series we'll be configuring Apache to use PHP so we can install an extremely handy web-based MySQL administration script called phpMyAdmin which we can use on each of our sites to do pretty much any database task you may wish to think of.

Until then stay loose :)

3 comments:

steveeray 17 November 2008 04:33  

I've been following your posts as I'm setting up a new Macbook Pro with CF and mySql. Really appreciate the effort you're putting into this.

Looks like your PATH statement here is incorrect. Should be /usr and not /user.

Ciqala 17 November 2008 09:57  

Hey Steve,

I see you found my deliberate mistake :)

I was of course only testing to make sure you were paying attention or err something :)

But seriously thanks for the heads up on my typo. I've fixed it now.

Glad to hear someone is finding things useful, I'll try to increase the frequency of my posts now that I know I'm not just talking to myself.

Cheers
Ciqala

Michael 25 November 2008 18:24  

Running the mysql_secure_installation section kept giving me an error when I tried to run it as per your instructions. It get complaining that it could not find mysql on line 42.

To solve I had to remove export PATH=$PATH:/usr/local/mysql/bin from my .profile file and then add it again by doing the following:

touch ~/.profile

echo "export PATH=$PATH:/usr/local/mysql/bin" >> ~/.profile

source ~/.profile

Now the entry in my .profile file looks rather odd now (see below) but it all seems to work perfectly and the mysql_secure_installation script ran flawlessly.

How my .profile entry looks:

export PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin:/usr/local/mysql/bin