Working with a database is pretty much a given for most of the projects I've worked on lately, which means that in order to get any work done locally, I've had to install a MySQL server, configure it, add the users and databases for each project, and grant the permissions accordingly. Because I didn't want to have to go through the hassle of installing MySQL and then writing a bunch of SQL commands each time I needed to create a database + user with permissions for a new project, I decided to do things a little unorthodox and use Docker instead. The official MySQL Docker image does a lot of the setup for you with just a couple of environment variables, which makes it pretty convenient to quickly get a server up and running without much fuss. Here's how it works:
You'll first need to pull the MySQL image. I've chosen version 5.7, but you can use any of the versions listed on the Docker Hub page.
docker pull mysql/mysql-server:5.7
This'll probably take a minute while the image is downloaded. While that's downloading, you can optionally set up some nice shortcuts to quickly start and stop the different servers for your apps. Just add this to your shell profile file and I'll break it down in a second. On macOS this would be the ~/.profile file and on Linux it's usually the ~/.bashrc file.
start-mysql()
{
APP_NAME="$1"
if [ -z "$APP_NAME" ]; then
echo "Please provide an argument for APP_NAME";
return 1;
fi
docker run \
-d \
--rm \
-p 3306:3306 \
-e MYSQL_ROOT_HOST=172.17.0.1 \
-e MYSQL_DATABASE="$APP_NAME" \
-e MYSQL_USER="$APP_NAME" \
-e MYSQL_PASSWORD="$APP_NAME" \
-v "$APP_NAME-mysql":/var/lib/mysql \
mysql/mysql-server:5.7
}
stop-mysql()
{
docker stop $(docker ps | grep mysql | grep 3306 | cut -f1 -d' ')
}
dmysql()
{
APP_NAME="$1"
if [ -z "$APP_NAME" ]; then
echo "Please provide an argument for APP_NAME";
return 1;
fi
mysql -h127.0.0.1 -u"$APP_NAME" -p"$APP_NAME" "$APP_NAME"
}
The above snippet basically creates three new commands you can run on the command line: start-mysql, stop-mysql, and dmysql (shorter than docker-mysql). In short, start-mysql and dmysql take in a parameter called APP_NAME, which will be used as the database name, user, and password. Obviously this is horribly insecure but it's probably fine for your local development environment. The first couple of lines for each method here:
APP_NAME="$1"
if [ -z "$APP_NAME" ]; then
echo "Please provide an argument for APP_NAME";
return 1;
fi
... are just checking that the APP_NAME variable isn't empty, or aborting if it is. Moving on, we can break down the next part of the start-mysql method. We're going to use docker to run a container image...
docker run \
... and detach from it once it's started up. You can exclude this flag if you want to keep the logs open while the MySQL server runs (though you could also do that with docker logs -f):
-d \
We're also going to destroy this container once we stop it. Since the data is persisted outside of the container, there's no need to keep it around:
--rm \
MySQL by default runs on 3306, so we'll need to allow Docker to expose that port to us (though it won't be exposed to the rest of your network unless you've done extra configuration on your computer):
-p 3306:3306 \
And then comes the MySQL configuration. This first line allows you to connect to your MySQL server from outside of the Docker network. You see, by default, MySQL will only allow connections from localhost. Since the MySQL server is technically running on a different server (a VM running on top of your computer), you're not on localhost anymore, and you'll need to allow connections through the Docker NAT.
-e MYSQL_ROOT_HOST=172.17.0.1 \
Next, we set up the default database, user, and password using the APP_NAME parameter that was passed in earlier for all three values. As I mentioned before, this isn't secure, so please don't do this in production:
-e MYSQL_DATABASE="$APP_NAME" \
-e MYSQL_USER="$APP_NAME" \
-e MYSQL_PASSWORD="$APP_NAME" \
Lastly, we configure a volume to persist the data between starts/stops. If your app handles schema creation on startup, or you don't want to persist data between server restarts, then you can skip this line and it'll work just fine. If you ever need to delete this volume, it's just named after the value given for APP_NAME plus a -mysql suffix.
-v "$APP_NAME-mysql":/var/lib/mysql \
Lastly we tell Docker which container image we want to run.
mysql/mysql-server:5.7
And voilà, we now have a quick command to start up a MySQL server that is independent of any other servers for other apps we're working on locally. To connect to this server with the command line, you can just use the dmysql command if you've so chosen to add it, or type it out each time. To connect from your application, say Spring Boot, to a database called "test", your application.properties file would look as follows:
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=test
spring.datasource.password=test
Finally, the stop-mysql command does what you would expect, but this is the only of the three that doesn't require any arguments. Since you can only have a single MySQL server running at a time on port 3306, I have chosen to just stop whatever other server I have running in order to start up a new one. stop-mysql simply finds a MySQL container image running on port 3306 and stops it.
That is all for now! If you've got other tips and tricks for getting your local development environment set up quickly and painlessly, don't hesitate to reach out to me and share them!