MySQL Docker Container

Official Document

MySQL - Official Image | Docker Hub
MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
MySQL——mysqldump参数大全

Practice In Action

Use MySQL Image

[ ~ Approach I ~ ]

Start MySQL Server Instance

version: '3.1'
 
services:
  mysql:
    image: mysql:8.0
    restart: unless-stopped
    volumes:
      - ./{some/path/on/your/host}/datadir /var/lib/mysql
    ports:
      - {mapping_port}: 3306
    environment:
      MYSQL_ROOT_PASSWORD: {MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: {database}
      MYSQL_USER: {username}
      MYSQL_PASSWORD: {MYSQL_PASSWORD}

docker compose -f stack.yml up/down

Connect to MySQL

docker compose -f stack.yml start/restart/stop
docker compose -f stack.yml images/logs

[ ~ Approach II ~ ]

docker run [--security-opt="seccomp=unconfined"] -p {mapping_port}:3306 --name mysql -v ./{some/path/on/your/host}/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD={MYSQL_ROOT_PASSWORD} -d mysql:{tag}

Manage Image/Container

docker image ls -a

docker container ls -a
docker container create/run {IMAGE_ID/REPOSITORY}
docker container start/restart/stop/stats {CONTAINER_ID/NAMES}
docker container logs/rm {CONTAINER_ID/NAMES}
docker container prune   -- Remove unused containers

docker image history/rm {IMAGE_ID/REPOSITORY}
docker image prune   -- Remove unused images

Container Shell Access MySQL

docker logs {CONTAINER_ID/NAMES}
docker exec -it {CONTAINER_ID/NAMES} bash
root@{CONTAINER_ID}:/# mysqldump -u root -p {databasename} {tablename1} {tablename2} {...} {tablenameN} --no-create-info --complete-insert > /var/lib/mysql/{database}.sql
root@{CONTAINER_ID}:/# mysql -u root -p
 
mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'localhost';
mysql> FLUSH PRIVILEGES;
 
mysql> use {databasename};
mysql> set names utf8;
mysql> source /var/lib/mysql/{databasename}.sql;

docker exec {CONTAINER_ID/NAMES} sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > ./{some/path/on/your/host}/all-databases.sql
docker exec -i {CONTAINER_ID/NAMES} sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < ./{some/path/on/your/host}/all-databases.sql

MySQL

• Turn Off SQL_MODE - ONLY_FULL_GROUP_BY

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

• Get TABLE_ROWS/AUTO_INCREMENT

SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '{tablename}'
    and TABLE_ROWS > 0
ORDER BY TABLE_NAME;
 
SELECT TABLE_NAME, sum(TableA) TableA, sum(TableB) TableB FROM
    (SELECT TABLE_NAME, case when TABLE_SCHEMA='{tablenameA}' then AUTO_INCREMENT else 0 end TableA, case when TABLE_SCHEMA='{tablenameB}' then AUTO_INCREMENT else 0 end TableB
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA in ('{tablenameA}', '{tablenameB}')
        AND AUTO_INCREMENT > 1 group by TABLE_SCHEMA, TABLE_NAME) w 
group by TABLE_NAME order by TABLE_NAME;