It's Me, Tommy

Docker: MySQL

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

Tommy Elmesewdy

,
, ,