How To: Install MySQL on Mac OS X El Capitan

TL;DR;

A guide for those who need this working now.

Download MySQL

Download the appropriate installer MySQL Community Server (free)

DMG MySQL Server Download Community Edition

Install MySQL

Once the download is complete open the file. In my case it was the DMG.

Go ahead and open your Notes app or Text Edit with a new document handy.

Follow the steps in the installer. Towards the end you will see an alert with the temporary password, It will look something like this, root@localhost: lQhtaX6,gW+j.

Copy the password it gives you and paste it in the new document or your Notes app for now.

If you lose the password you're on your own... or just reinstall.

Start MySQL

Easiest way to do this is in System Preferences

System Preferences Panel Pointing out MySQL Icon in Last Row

The last item of the last row should be MySQL. Choose MySQL and click
the Start MySQL Server button.

MySQL System Preferences Pane Showing Start/Stop Button

You can now close System Preferences.

Add MySQL to your $PATH

This step allows you to access mysql and mysqladmin in the command line.

In your home directory open the .bash_profile file

$ vi ~/.bash_profile

If there's already stuff in this file you can add it on a new line at the end of the file.

Once you move your cursor to end of file use the i key to enter insert mode. Now you
should be able to edit the file. Hit return for a new line then type or paste the lines below.

    # add mysql to path var
    export PATH=${PATH}:/usr/local/mysql/bin

To save the file and quit vi hit esc then shift ZZ.

FYI: Close the current Terminal window and open a new one to make sure these changes will be recognized.

Change MySQL Root Password from Default

Now that you can access mysql from anywhere in the Terminal you can change the password.

    $ mysql -h localhost -u root -p

Enter the temporary password provided during install. (the one I told you to paste somewhere)

    Enter password:

Now that you are logged into the MySQL shell you can change the password.
There are a few ways to do this but I found this to work and I could
show it in one line of text.

    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YOUR-NEW-PASSWORD-HERE');

Create a New MySQL Database

    mysql> CREATE DATABASE testdatabase;

    mysql> USE testdatabase;

    mysql> CREATE TABLE user (
            id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            firstname VARCHAR(20) NOT NULL,
            lastname VARCHAR(30) NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE
            );

    mysql> INSERT INTO user (firstname, lastname, email) VALUES ('john', 'doe', 'jdoe@gmail.com');

    mysql> SELECT * FROM user;

You should now see output of the query.

Example Output of Creating a New Database with MySQL in Terminal

Create a New MySQL User and Allow Access to New Database

The idea here is you would make a user for access to a specific database so if
the user is somehow compromised it wouldn't have access to all your databases.

    mysql> CREATE USER 'test user'@'localhost' IDENTIFIED BY 'testPwd';

    mysql> GRANT ALL PRIVILEGES ON testdatabase.* TO 'testuser'@'localhost';

    mysql> FLUSH PRIVILEGES;

Go ahead and exit mysql

    mysql> \q

Now we can test the new user.

    $ mysql -h localhost -u testuser -p

Enter your testuser password.

    mysql> SHOW DATABASES;

Here you should see some output with a list containing testdatabase.

Now you should be good to go.

Remove this Junk

You can delete the test database and user by logging back in as root and running the following.

    mysql> DROP DATABASE test database;

Verify deleted database.

    mysql> SHOW DATABASES;

Now delete the user.

    mysql> DROP USER 'test user'@'localhost';

Verify user has been deleted.

mysql> SELECT User FROM mysql.user;

MySQL Show Users Table

Congrats... you're now ready to do some real work locally on your Mac.


If you have found an error or part of this post doesn't make sense please
let me know so I can make any corrections. I made this post as I installed
it myself to explain to a coworker and to refresh my memory. It's possible
I forgot a step here or there.