Download
docker pull mysql/mysql-server:latest
Create
Option 1 – docker run
docker run -d \ --name mysql-itsmetommy \ -p 3306:3306 \ --env="MYSQL_ROOT_PASSWORD=password" \ mysql/mysql-server:latest
Option 2 – docker-compose
cat <<EOF > docker-compose.yml
version: '3.8'
services:
db:
image: mysql:latest
restart: always
environment:
MYSQL_DATABASE: 'db'
# Optional user account
MYSQL_USER: 'tommy'
# Password for optional user account
MYSQL_PASSWORD: 'password'
# Password for root user
MYSQL_ROOT_PASSWORD: 'password'
ports:
# <Port exposed> : < MySQL Port running inside container>
- '3306:3306'
expose:
# Opens port 3306 on the container
- '3306'
container_name: mysql-itsmetommy
EOF
Create.
docker-compose up -d
Logs.
docker-compose logs -f
Password
If you didn’t specify a password for root, one was generated for you. You can view the root password by running the following.
docker logs mysql-itsmetommy 2>&1 | grep GENERATED
Test connection
nc -zv localhost 3306
Example
nc -zv localhost 3306 Connection to localhost port 3306 [tcp/mysql] succeeded!
Connect
Option 1 – docker exec
Connect to the pod.
docker exec -it mysql-itsmetommy bash
Then connect to mysql from within the pod.
# mysql -uroot -p [ENTER_ROOT_PASSWORD]
Example
docker exec -it mysql-itsmetommy bash bash-4.4# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.26 Copyright (c) 2000, 2021, 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>
Option 2 – mysql Client
You can install mysql client using the following methods.
# Mac brew install mysql # CentOS yum install mysql # Ubuntu apt-get install mysql-client
Connect.
mysql -uroot -ppassword -h127.0.0.1 -P3306
Setup
Warning: If you created your container using docker run, you may run into the following issue.
mysql> CREATE DATABASE guestbook; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
To fix this, you need to change your MySQL root password by running.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
Create database
In this case, I will create a database called guestbook.
mysql> CREATE DATABASE guestbook;
Create user
If not already created, create a user.
CREATE USER tommy WITH PASSWORD '12345'; mysql> CREATE USER 'tommy'@'localhost' IDENTIFIED BY 'password';
Give user permissions
Option 1 – Give access to all databases
mysql> GRANT ALL PRIVILEGES ON * . * TO 'tommy'@'localhost';
Option 2 – Give access to a specific database
mysql> GRANT ALL PRIVILEGES ON guestbook.* TO 'tommy'@'localhost';
Reload all the privileges to apply permissions.
mysql> FLUSH PRIVILEGES;
Create table
Switch the database.
mysql> use guestbook;
Create table.
mysql> CREATE TABLE users (
userid SERIAL PRIMARY KEY,
name TEXT,
age INT,
location TEXT
);
Verify
mysql> show tables; mysql> describe users;
Example
mysql> show tables; +---------------------+ | Tables_in_guestbook | +---------------------+ | users | +---------------------+ 1 row in set (0.00 sec) mysql> describe users; +----------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+----------------+ | userid | bigint unsigned | NO | PRI | NULL | auto_increment | | name | text | YES | | NULL | | | age | int | YES | | NULL | | | location | text | YES | | NULL | | +----------+-----------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
Clean up
Option 1 – If you used docker run
docker stop mysql-itsmetommy && docker rm mysql-itsmetommy
Option 2 – If you used docker-compose
docker-compose down
