Oops, this isn't right!

MySQL on OSX

16 May 2016

I have recently been learning about PHP, and MySQL, and I wanted to document the steps I had to go through to get up to speed. Here is how to get MySQL going on OSX:

  1. Install homebrew
  2. Install MySQL
  3. Starting and stopping MySQL server
  4. Connect to MySQL server
  5. Add a user
  6. Create database
  7. Give user permissions
  8. Reconnect to MySQL using new user
  9. Create new table
  10. Do whatever else you want

Install homebrew

Easy. Open up your terminal and type

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Install MySQL

Even easier. With homebrew installed type

brew install mysql

Start MySQL server

Also easy. From your terminal with MySQL installed type

mysqld

The d means it will open as a daemon process. To shut down the server, type

mysqladmin -u root shutdown

Connect to MySQL server

From terminal…in fact, just assume everything is in the terminal from now on.

mysql -u root

Add user

Assuming all has gone well, you should now be in a mysql shell. If you see mysql> then you are. There is some misinformation on the internet regarding this process. Don’t be fooled. You shouldn’t be opening up tables and adding crap to them. To add a user is simple. Let’s set up a user named guest with password guest123.

create user 'guest'@'localhost' identified by 'guest123';

Incidentally, it’s simple to remove users

drop user 'guest'@'localhost';

Create database

Now, this user can’t do much currently. In fact, guest can’t do anything right now. We need to create a database.

create database example;

Give user permissions

We need to add permission for guest to do stuff to the database, which we named example.

grant select, insert, update, delete, create, drop on example.* to 'guest'@'localhost' identified by 'guest123';

Finally, and this IS IMPORTANT, we need to flush privileges. Privileges are kinda like poop, and it needs to be flushed from time to time.

flush privileges;

Reconnect to MySQL using new user

Now, it’s time to see if we did everything right. Go ahead and exit the MySQL shell.

exit

Now let’s try to login with our new user

mysql -u guest -p

Now type in your password at the prompt: guest123 Alternatively, you can just include the password as an argument, although you will be yelled at, called various names, and be told that doing so is insecure.

mysql -u guest -pguest123

NOTE: there is no space between p and the password

Create new table

If all went well, you should now be logged back in. Let’s check out what databases are available to us.

show databases;

Hopefully you see our example database. Let’s use it.

use example;

Let’s add a simple table.

create table stuff (id int not null primary key auto_increment);

This table doesn’t do much, but who cares. Let’s make sure it’s actually there.

show tables;

Do whatever else you want

There’s lots of stuff you can do with MySQL, including getting the crap SQL injected out of you. Here are some good places to look: