TLDR: just looking for the code? Skip to the end.
This document guides you through the setup of Docker and MySQL.
Why would you want to do it this way?
- Isolated environment. Need to upgrade from version 5.7 to 8.0 and are just developing locally? Previous configurations breaking your installation? Toss your old image and get a new one.
- Preparation for real world development. Local databases are typically for coding and testing. Developers don't normally run a live production database from their own machine. We just need a database we can quickly test and develop on. Docker is perfect for that.
Concepts to understand:
- Docker image: immutable file containing source code, libraries, dependencies for the application. Think of it as a template that is read-only, i.e. we don't run it.
- Docker container: an isolated virtualized environment built using a template (the image). Since containers are isolated, anything that breaks inside the container do not affect your system.
Download Docker Desktop here.
Make sure Docker is running.
The following command will pull an image of mysql if one doesn't exist, then create a container for it.
docker run --name mysql-docker -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8.0
Explanation of the flags above
--name: container name. In the example above,mysql-dockeris the name of your container. We typically only need one database container, but if you want your containers to be project based, consider something likegroceries-db-e: for environment variables.MYSQL_ROOT_PASSWORDis self explanatory.-p: port mapping<host_port>:<container_port>. MySQL runs on port3306inside the container, i.e. port3306on your localhost doesn't automatically connect to the database. If you want your local port3306to point to the container's port3306, you need to specify it.
If containerization was successful, you should see the container when you run docker ps -a from your terminal.
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
b1a8a4bd6bbb mysql:8.0 "docker-entrypoint.s…" 9 seconds ago Up 7 seconds 3306/tcp, 33060/tcp mysql-docker
At this point you will have a running container with MySQL running inside of it. You can connect to the database using your terminal or from your local node application. If you don't know how, the next sections detail how to connect to your container as well as to your database.
The following command start the container's CLI and allow you to run commands inside your container. This is also useful for when you need to CRUD files in the container. You don't need to worry about CRUDing files for now.
Assume the same mysql-docker container name as before. Note that you can also open the CLI from Docker Desktop.
docker exec -it mysql-docker bash
With the container's CLI running, you can login to your MySQL database with the password you created earlier for MYSQL_ROOT_PASSWORD. You can do so and create a test product.
$ docker exec -it mysql-docker bash
root@b1a8a4bd6bbb:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE grocerylist;
Query OK, 1 row affected (0.01 sec)
mysql> USE grocerylist;
Database changed
mysql> CREATE TABLE groceries ( item VARCHAR(50), quantity INT );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO groceries VALUES ('banana', 5);
Query OK, 1 row affected (0.01 sec)
You should now also be able to connect directly to the containerized database from your terminal via remote connection, or from node.
Remote connection
mysql -h 127.0.0.1 -P 3306 -u root -p
Node If you have a node application, you can connect it to your Dockerized database. When creating the connection, you may need to specify:
- port
- user
- password
- database
You can put this code wherever you connect your database, e.g. in database/index.js. The example below is based on the example data above, so do not copy verbatim.
const mysql = require('mysql2');
// create the connection to database
const connection = mysql.createConnection({
host: 'localhost',
port: 3306,
user: 'root',
password: 'my-secret-pw',
database: 'grocerylist'
});
connection.query(
'SELECT * FROM `groceries`',
function(err, results, fields) {
if (err) {
console.log(err)
} else {
console.log(results); // results contains rows returned by server
}
}
);
Your data will be saved even when you stop your containers.
The only thing you do need to worry about is when you remove your containers, e.g. docker rm <container id>. This is the equivalent of uninstalling the database, so data may not persist then.
You shouldn't need to remove your database container unless you're upgrading/downgrading database versions. Persisting data across such scenarios is done with volumes, which are outside of scope of this installation guide for now.
Example error message when using mysql2:
Error: connect ECONNREFUSED 127.0.0.1:3306
at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1145:16) {
errno: -61,
code: 'ECONNREFUSED',
syscall: 'connect',
address: '127.0.0.1',
port: 3306,
fatal: true
}
Your container may have stopped.
- Confirm container statuses with:
docker ps -a - Restart the relevant container if it has status
Exitedwith:docker start <CONTAINER ID>
$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
ceb81e1251a7 mysql:8.0 "docker-entrypoint.s…" 7 days ago Exited (0) About a minute ago mysql-docker
$ docker start ceb81e1251a7
ceb81e1251a7
If you have a local installation (not installed with Docker) of your database, it may be running on the same port 3306.
- Confirm services running on port:
lsof -i tcp:<PORT NUMBER> killunneeded services:kill <PID>- Restart our Dockerized container:
docker restart <CONTAINER ID>
$ lsof -i tcp:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
com.docke 16558 ericdo 27u IPv6 0xf8014bcbf52e51b7 0t0 TCP *:mysql (LISTEN)
mysqld 47728 ericdo 21u IPv4 0xf8014bda58af3fbf 0t0 TCP localhost:mysql (LISTEN)
$ kill 47728
$ docker restart 1b5dea6eb2f0
Note in the example above, mysqld is running on 3306. We only want our Dockerized instance of the database running, so kill the mysqld process then restart the Dockerized container.
Whether you want a separate isolated instance of a MySQL database, or want to install a separate database (e.g. Mongo, PSQL), the flow is basically the same.
You only really need to have multiple instances of the same DBMS if they are for completely different business purposes. For now, do not worry about that.
If this is your first time reading this document, do not go directly to this section unless you just want to get lost, break things, and learn the hard way.
- Install Docker
docker run --name mysql-docker -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8.0mysql -h 127.0.0.1 -P 3306 -u root -p- Run SQL commands
- Install Docker
docker run --name mysql-docker -p 3306:3306 MYSQL_ALLOW_EMPTY_PASSWORD=1 -d mysql:8.0mysql -h 127.0.0.1 -P 3306 -u root- Run SQL commands
docker ps -a
docker start <CONTAINER ID>
docker restart <CONTAINER ID>
docker stop <CONTAINER ID>
docker rm -rf <CONTAINER ID> (completely destroys container. see note below)
You can remove a container anytime as long as you've stopped it. You should not remove a container unless you want to delete that database instance or want to upgrade/downgrade database versions.
docker exec -it <CONTAINER NAME> bash
Via container
docker exec -it <CONTAINER NAME> bash
mysql -u root -p
Remotely
mysql -h 127.0.0.1 -P 3306 -u root -p