Apple, Development

Using a MacMini as a database server

We've being using a MacMini as a database server forever. It was fast, reliable and easy to use. Until it died. Seeing as I now need to configure a new one I thought I'd document the process.

For ease, I'm assuming that like me you're using a brand new MacMini. We chose the base model as it's got more than enough power to do what we want.

Step 1. Get the database up running and accessible.

Download required applications

Download MySQL DMG (I chose the 64bit version)
Download Sequel Pro

Install the software

Run the MySQL installer and add the preference pane widget being sure to check the "Automatically Start MySQL Server on Startup".

Start MySQL server.

Screen Shot 2014-06-30 at 12.15.10.png

 

At this point MySQL is up and running and the default username is root without any password.

Add Sequel Pro to your applications folder and launch it.

Screen Shot 2014-06-30 at 12.19.16 pm.png

Configure the user

Once Sequel Pro has launched we need to configure the root user to have a password and allow access from other locations across the internal network.

First we need to add a password to the root user, this is pretty straight forward but does require terminal.

  1. Fire up terminal and login to mysql by typing "mysql -u root" and hitting return. If you get an error message like mysql not found don't worry, take a look at the advice here it's likely that the computer doesn't know where MySQL is.
    One other thing that may trip people up here is if your MySQL server hasn't been started already, if that's the case jum in to your System preferences, click the MySQL icon and start it.
  2. Once you're logged in type "SET PASSWORD FOR ‘root’@‘localhost' = PASSWORD('your_password');" and hit return. Replacing your_password with the plain text password you want to use.
  3. Once your password has been set you can exit mysql by typing "exit".

Network settings

Lets tackle the internal network issue now.

  1. Open Sequel Pro and select "mysql" from the database in the top left and select the user table.
  2. Duplicate the localhost / root record (typically the first record) and we'll change the host field.
  3. Depending on your security requirement there are a couple of options for the host value. If we assume that your local network IP range looks something like 192.168.1.X we can use the following to grant access to everyone on the network: 192.168.1.%.
    Alternatively if you are less concerned about security you could open it to everyone by just using % as the host value. 
  4. We now need to restart MySQL by opening System Preferences, select MySQL and stop and start the server.

Once this is done the database server will be accessible but unless we've set a static IP address for this computer it may change each time it's restarted. To solve this:

  1. Open up System Preferences and select network.
  2. Make a note of your IP address (assuming we want to use this address permanently) and click advanced. 
  3. Click the first tab (TCP/IP) and change the first option to be "Using DHCP with a manual address".
  4. A text box appears for you to add an IP address, enter the IP address from step 2 (or an alternate one if you'd prefer).
  5. Click ok and then apply.

At this point you should have a working MySQL database server that can be access via a network using the IP address of the computer.