If you need to run MySQL on your Mac (e.g. for development) one the good ways to do it with Docker. It is pretty straightforward to install and use. You can have multiple MySQL instances running side by side. Most importantly they can run different MySQL versions, including version 8. Which is still not supported by other popular option, MAMP. Here is a short guide how to get it up and running in no time.
Installing Docker on macOS is pretty straightforward these day. They provide a desktop application with a convenient UI and native means to install it. Grab it here: https://docs.docker.com/docker-for-mac/install/.
Create a container
Docker runs apps in containers. Create one for your MySQL instance with the following command in terminal. You might want to alter certain parameters (more on that below).
docker run \ --name local-mysql \ -p 33061:3306 \ -e MYSQL_ROOT_PASSWORD=p4ssw0rd \ -d mysql:latest
— name — defines a name for a running instance. You can pick anything. It should be unique in the scope of your desktop.
-p — this flag sets up port forwarding. MySQL inside the container hangs on the stdard port 3306. It is specified after the colon. The port before the colon is the port on your desktop to expose MySQL on. It can be anything, including 3306, as long as it is not busy with someting else. If you plan to run multiple MySQL instances, it might be a good idea to attach each to a different port in the range of 33061–33069.
-e MYSQL_ROOT_PASSWORD= — this is a password for MySQL root user. It can be anything.
-d mysql:latest — here you can specify an exact MySQL version you want to launch. Or if you don’t care, you can specify latest to pick whatever latest version is available on Docker Hub at the moment of the container creation.
That’s it, the instance is up and running. More information on configuration options can be found here. The instance is running as long as Docker is running, you can start and stop individual instances from Docker Dashboard. Data is persisted between the launches.
It is worth mentioning that Docker on macOS is relatively heavy on resources. Especially on RAM. This is because under the hood it runs a virtualized Linux OS. This is barely noticeable if you have relatively performant Mac with at least 16 GB of RAM. But for slower models with less RAM running Docker and specifically MySQL in Docker might be inconvenient.
And one more thing as a bonus.
Authentication plugin ‘caching_sha2_password’ cannot be loaded
This is an issue that you might experience with MySQL 8, while connecting with some clients. Notable Sequel Pro and node.js mysql package. This is because by default MySQL root user uses a new mode of authentication which is not supported by your client. The solution is to change the mode for the root user.
First, connect to MySQL.
docker exec -it local-mysql bash mysql -uroot -pp4ssw0rd
local-mysql — name of your container.
p4ssw0rd — root user password.
Now you should get an access to MySQL console. Run:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'p4ssw0rd2';
Note that this will change the password of you root user to p4ssw0rd2. The “alter user” command requires that we specify a different password from what is used, otherwise it will fail. If you want you can call the same command once again with a previous password to change it back.
Originally published at https://www.factorialcomplexity.com.