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;