Setting Up Your First Database for macOS

The biggest hurdle in my coding journey has been learning how to set up my development environment. There is a plethora of information out there telling you how to do the things but there are a lot of factors that aren’t apparent to beginners.

For my project, I decided I wanted to set up a MySQL database. I have worked with MySQL and PostgreSQL. Understanding both, I decided to go with the more mainstream option however, it is worth noting that PostgreSQL has caught up with MySQL over the last couple of years.

I began my research and came up with a few tutorials to work with. Unfortunately when I began trying the commands in my terminal I got some dreaded error messages. With a bit more research I found that I was using Linux commands which were not compatible with my Mac. Rather than go through the process of making my Mac compatible I found an alternate route. It worked for me so maybe it will work for you!

1. Download Docker Desktop

Docker is a tool that allows you to containerize your repositories.

2. Download MySQL Docker Image

Enter the following command to download the MySQL Docker image:

$ docker pull mysql

You should see this message in the terminal:

3. Set Up Your MySQL Instance

You have to set up an instance of MySQL. In this case, an instance is a MySQL server running somewhere that you’ll be able to connect to with the credentials you set below. Your username for this instance will be root by default and the password will be whatever you set in the command line below.

Enter the following command:

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
#Replace some-mysql with name for your container
#Replace my-secret-pw with a password for your root account
#Replace tag with latest to get the latest version of mysql

4. Run Commands Within Your New Docker Container

The docker exec command allows you to run commands inside a Docker container. The following command line will give you a bash shell inside your mysql container you just created:

$ docker exec -it some-mysql bash
#Replace some-mysql with name you assigned to container above

Once you execute this command, you will now be inside your container and you will be prompted to run a new command within the container. Execute the following command to access the MySQL shell:

$ mysql -u root -p

This command will prompt you to enter your password you setup above. Once you enter the password, you will be in the MySQL shell. You should see this message in the terminal.

5. Create a new user in MySQL

Enter the following command in the terminal to create a new user:

mysql> CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘user_password’;
#Replace ‘newuser’@’localhost’ with the username you would like to use for your user account. 
#Set your user account password with ‘user_password’
Example Command:
mysql> CREATE USER 'lisabrown@%' IDENTIFIED BY 'password1';

6. Grant Privileges for Your New User

Enter the following command in the terminal to grant permissions for your new user:

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
#Replace database_name with your database or simply input * in place of database_name to indicate all databases.
#Replace 'database_user'@'localhost' with your username.
Example Command: 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'lisabrown@%';

To check which permissions a user has, enter the following command:

mysql> SHOW GRANTS FOR 'database_user'@'localhost';

To get more specific about granting user privileges, visit the MySQL documentation.