Developing software in the Real World

Using Docker to create a MySQL server

When working on test code on my computer, I usually use the built-in PHP server (php -S) which works nicely. Every so often, I need access to MySQL and I use Docker to temporarily create a MySQL server for me. This is how I do it.

The magic command is:

$ docker run --name mysql \
  -e MYSQL_USER=rob -e MYSQL_PASSWORD=123456 -e MYSQL_DATABASE=bookshelf \
  -p 3306:3306 -d mysql/mysql-server:5.7

This creates a Docker container called “mysql” on port 3306. We pass three environment variables: MYSQL_USER, MYSQL_PASSWORD & MYSQL_DATABASE which are our credentials and the database name.

Client access to the database

If you have the MySQL command line client installed, you can access your database like this:

$ mysql --protocol=TCP -u rob -p123456 

We need to use TCP protocol as there’s no socket in play here. If, like me, you’re too lazy to type --protocol=TCP each time, then set it in your ~/.my.cnf file like this:

~/.my.cnf:

[client]
protocol=TCP

One of the easier ways to get a MySQL command line client on Mac, is to install MySQL WorkBench and add /Applications/MySQLWorkbench.app/Contents/MacOS to the path as Homebrew seems to want to install the server too.

Controlling the container

Stop the container using: docker stop mysql and restart it again with docker start mysql. When you’re finished with it, you can delete it with docker rm mysql.

Restoring a dump file

As these MySQL instances are temporary for me, I install a database schema using:

cat seed-mysql.sql | mysql --protocol=TCP -u rob -p123456 rob bookshelf

(Obviously, you’d use same credentials as you ran your container with!)

Fin

That’s all there is to having a temporary MySQL install running locally for development.

3 thoughts on “Using Docker to create a MySQL server

  1. If you don't have the mysql client installed, you can just use the one in the container:

    docker exec -it mysql mysql -u rob -p123456

  2. Worked for me really well, only issue is not having root access. Tried to create a new database in the server with the user 'rob' and I was getting access issues. I've tried to follow the mysql docker hub page and it seems to not be working for me.

Thoughts? Leave a reply

Your email address will not be published. Required fields are marked *