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 -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> use {databasename} 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;