MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language. - Source
Setting up MySQL for development on your Mac can be tricky and stressful at times, a lot of developers I know don’t like to go through that hassle. This prompted me to write this article to walk you through the steps I take to set up MySQL on my Mac.
- A Macbook
- Homebrew installed on your Mac (Installation guide can be found here)
I usually install MySQL via Homebrew. Confirm you have Homebrew installed by running the command below in terminal:
Once you’ve confirmed you have Homebrew installed, the next step is to install MySQL using the command
brew install mysql
This command will install the latest version of MySQL (v8.0.21 as at the time of writing) on your Mac. Once the installation is done, you can start MySQL as a background service with the command
brew services start mysql
if you don’t need it as a background service.
Once MySQL has been installed we need to create a user and assign roles to that user. The first step we will take is to access the
mysql shell using the
root user - to do this we will use the command
mysql -u root
This will take you to the
mysql shell and you should see the
The prompt should look similar to this 👇🏽:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.21 Homebrew Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Now that we are in the
mysql shell, we will be writing MySQL statements to perform several actions for us.
We will approach the setup in three phases:
- Create a new user
- Grant privileges to the new user
- Access the shell using the newly created user
To create a new MySQL user, we will use the command
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_password';
Remember to replace
user_password with the username and password you’d like to access MySQL with and also ensure you terminate the statement with a semicolon (
;), else the statement won’t be executed upon by MySQL.
Now that we’ve created our user, we need to grant privileges, this will determine how much power this new user wields. Here’s a breakdown of the different types of privileges in MySQL:
ALL PRIVILEGES: Gives the said user access to all privileges.
CREATE: Allows a user to be able to create a database(s).
DROP: Allows a user to delete a database(s).
DELETE: Allows a user to delete rows from a table.
INSERT: Allows a user insert rows into a table.
SELECT: Allows a user query for records in a table.
UPDATE: Allows a user to update rows of records in a table.
GRANT OPTION: Allows a user to grant/remove other user’s privileges.
Depending on whichever privilege you prefer to grant the newly created user, you can actually grant the privilege with the command:
GRANT privilege ON *.* TO ‘user_name’@'localhost’;
An example is
GRANT ALL PRIVILEGES ON *.* TO 'bolaji'@'localhost'; to grant the user with the name
bolaji all privileges.
Once you’re done granting permissions, you can use the
SHOW GRANTS command to show the privileges assigned to any user, an example is shown below:
SHOW GRANTS FOR 'bolaji'@'localhost';
Now that we’ve created a user and granted the said user some privileges. Let’s quit our current mysql session by entering the command
quit in the mysql shell.
We can start another session of mysql using the user we just created. This time we will be using a different command
mysql -u user_name -p
-p flag means that we’d be prompted for a password once we hit the enter key. For super users like
root, we didn’t need to enter the
-p flag because no password was set for the
Once we are in, we can confirm the current user with the command below:
Now that we are done, you can use mysql for any of your dev project using the credentials you created your user with.
If this was helpful, feel free to share and/or drop a comment.
If you’ve got questions, feel free to share them in the comment section or reach out to me on twitter.