Installing MySQL in MacOS w/ Docker

 

Installing MySQL in macOS

Installing MySQL in macOS is sometimes a pain. Especially, setting up the root user with user defined password. And most of the time after lots of retries we end up with lot of garbage folders and services which can block the updates and upgrade of MySQL over the time.

Below are some steps that I followed to have MySQL as a Docker Container to avoid all hassles that installing it in macOS can cause.

Installing a MySQL Docker Container

Setting up a database in Docker is simply building a container based on a MySQL image. Follow the steps outlined below to get your MySQL container up and running.

Step 1: Pull the MySQL Docker Image

1. Start by pulling the appropriate Docker image for MySQL. You can download a specific version or opt for the latest release as seen in the following command:

docker pull mysql/mysql-server:latest

If you want a particular version of MySQL, replace latest with the version number.

2. Verify the image is now stored locally by listing the downloaded Docker images:

docker images

The output should include mysql/mysql-server among the listed images.

Step 2: Deploy the MySQL Container

1. Once you have the image, move on to deploying a new MySQL container with:

docker run -p 3306:3306 -p 33060:33060 --name=mysql-service -e MYSQL_ROOT_PASSWORD=MyPassword1234 -d --restart unless-stopped mysql/mysql-server:latest

• Replace --name=mysql-service with the name of your choice. And replace -e MYSQL_ROOT_PASSWORD=MyPassword1234 with the password of your choice If you do not provide a name, Docker generates a random one.
• The -d option instructs Docker to run the container as a service in the background.
• In the command above, we used the latest version tag. This may differ according to the image you downloaded.

2. Then, check to see if the MySQL container is running:

docker ps

You should see the newly created container listed in the output. It includes container details, one being the status of this virtual environment. The status changes from health: starting to healthy, once the setup is complete.

Step 3: Connect to the MySQL Docker Container

1. Before you can connect the MySQL server container with the host, you need to make sure the MySQL client package is installed:

brew install mysql-client

For macOS, I would like to use Homebrew package manager.

2. Then, start a MySQL client inside the container by typing:

docker exec -it mysql-service mysql -uroot -pMyPassword1234

3. To avoid typing the password in the command you can run below command:

docker exec -it mysql-service mysql -uroot -p

Provide the root password, when prompted. With that, you have connected the MySQL client to the server.

Start, Stop, and Restart MySQL Container

The container automatically stops when the process running in it stops.

• To start the MySQL container run:

docker start mysql-service

• Stop the MySQL container, use the command:

docker stop mysql-service

• To restart the MySQL container run:

docker restart mysql-service

Delete MySQL Container

Before deleting a MySQL container, make sure you stop it first.

Then, remove the docker container with:

docker rm mysql-service

Resources

• MySQL Docker Container Image from DockerHub
• Blog

Using MySQL Workbench with Containerized MySQL Service

By default after deployment MySQL has following connection restrictions:

mysql> select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

For security purpose you will not be able to connect to it outside of the docker image. If you need to change that to allow root to connect from any host then follow these steps:

1. Start the mysql image with all port mappings required:

docker run -p 3306:3306 -p 33060:33060 --name= mysql-service -e MYSQL_ROOT_PASSWORD=MyPassword1234 -d --restart unless-stopped  mysql/mysql-server:latest

2. Connect using mysql client directly to the mysqld in docker:

docker exec -it mysql-service mysql -uroot -pMyPassword1234

3. Run SQL:

UPDATE mysql.user SET host = '%' WHERE user='root';

4. Quit the mysql client.
5. Restart the container:

docker restart mysql-service

Now we can connect the mysql service with MySQL Workbench. To connect use:

host:0.0.0.0
port:3306
password:MyPassword1234

After all the changes the query it will show:

select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | healthchecker |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+

If there is an error connecting to mysql service containing the following message, "Authentication plugin 'caching_sha2_password' cannot be loaded", please run the following SQL command.

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

Postman Pre-Request: How to get Auth Token at Collection level?

Prerequisite:

You need latest Postman application installed.

You can download Postman application from here.

Steps:

• Create a new collection.
• Open the collection and click on Pre-request Script.
• Paste below code there:

pm.sendRequest({

url: [AUTH_TOKEN_URL],

method: 'POST',

header: {

'Content-Type': 'application/x-www-form-urlencoded',

},

body: {

mode: 'urlencoded',

urlencoded: [

{ key: "client_id", value: [CLIENT_ID_VALUE] },

{ key: "client_secret", value: [CLIENT_SECRET_VALUE] },

{ key: "resource", value: [RESOURCE_VALUE] },

{ key: "grant_type", value: [GRANT_TYPE_VALUE] }

]

}

}, function (err, response) {

if (null != err) {

const jsonResponse = response.json();

// Save token to env variable.

pm.environment.set('env_auth_token', jsonResponse.token);

} else {

console.log("Cannot fetch Auth token");

pm.environment.set('env_auth_token', '');

}

});

• Replace all values as per your requirements.