To run MySQL server in Docker container which is not detached (does not run in background):
% docker stop my-mysql-container
my-mysql-container
This does not remove the stopped/exited container which can be verified by listing all containers:
% docker container ls -all
If we don't want to re-use (restart) that container, we can remove it:
% docker rm my-mysql-container
my-mysql-container
We can make sure container is removed automatically by passing --rm to docker run.
Once MySQL Docker container is running we can see some of its details if we list all containers:
% docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9287fcb65268 mysql:8.0 "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:3306->3306/tcp, 33060/tcp my-mysql-container
We can see that it's bound to IP address 0.0.0.0 and port 3306.
To connect to this DB instance we can use mysql client.
We can use brew to install only mysql client (which also includes mysqldamp application):
% brew install mysql-client
Installation can be verified by checking apps' versions:
% /opt/homebrew/opt/mysql-client/bin/mysql --version
/opt/homebrew/opt/mysql-client/bin/mysql Ver 8.1.0 for macos13.3 on arm64 (Homebrew)
% /opt/homebrew/opt/mysql-client/bin/mysqldump --version
mysqldump Ver 8.1.0 for macos13.3 on arm64 (Homebrew)
We can now connect to DB:
% /opt/homebrew/opt/mysql-client/bin/mysql -uroot -p -h0.0.0.0
Enter password: <--enter 'ok'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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>
We didn't need to pass port number as we're using a default MySQL port (3306) but we had to pass a host (-h).
To see which users are created for this brand new MySQL instance:
mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.02 sec)
MySQL here actually shows "accounts" which come in format: username@host. So we have here two root accounts, one for connection from localhost only and one for connections from anywhere.
To list current databases:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec)
MySQL Docker container automatically creates and mounts a volume:
% docker inspect my-mysql-container
[
{
"Id": "a8a275aa4b15b49702a0fcf080950b0b92b0f7239742ecad0ff3a1fc84e87ba3",
"Created": "2023-10-05T15:06:55.21292468Z",
"Path": "docker-entrypoint.sh",
"Args": [
"mysqld"
],
"State": {
"Status": "running",
"Running": true,
"Paused": false,
"Restarting": false,
"OOMKilled": false,
"Dead": false,
"Pid": 1632,
"ExitCode": 0,
"Error": "",
"StartedAt": "2023-10-05T15:06:55.430691972Z",
"FinishedAt": "0001-01-01T00:00:00Z"
},
"Image": "sha256:659ee6cc1dd35b909daead51fbc9010e2252ce16bc260b56b6d709facb844a7b",
"ResolvConfPath": "/var/lib/docker/containers/a8a275aa4b15b49702a0fcf080950b0b92b0f7239742ecad0ff3a1fc84e87ba3/resolv.conf",
"HostnamePath": "/var/lib/docker/containers/a8a275aa4b15b49702a0fcf080950b0b92b0f7239742ecad0ff3a1fc84e87ba3/hostname",
"HostsPath": "/var/lib/docker/containers/a8a275aa4b15b49702a0fcf080950b0b92b0f7239742ecad0ff3a1fc84e87ba3/hosts",
"LogPath": "/var/lib/docker/containers/a8a275aa4b15b49702a0fcf080950b0b92b0f7239742ecad0ff3a1fc84e87ba3/a8a275aa4b15b49702a0fcf080950b0b92b0f7239742ecad0ff3a1fc84e87ba3-json.log",
"Name": "/my-mysql-container",
"RestartCount": 0,
"Driver": "overlay2",
"Platform": "linux",
"MountLabel": "",
"ProcessLabel": "",
"AppArmorProfile": "",
"ExecIDs": null,
"HostConfig": {
"Binds": null,
"ContainerIDFile": "",
"LogConfig": {
"Type": "json-file",
"Config": {}
},
"NetworkMode": "default",
"PortBindings": {
"3306/tcp": [
{
"HostIp": "",
"HostPort": "3306"
}
]
},
"RestartPolicy": {
"Name": "no",
"MaximumRetryCount": 0
},
"AutoRemove": false,
"VolumeDriver": "",
"VolumesFrom": null,
"ConsoleSize": [
83,
318
],
"CapAdd": null,
"CapDrop": null,
"CgroupnsMode": "private",
"Dns": [],
"DnsOptions": [],
"DnsSearch": [],
"ExtraHosts": null,
"GroupAdd": null,
"IpcMode": "private",
"Cgroup": "",
"Links": null,
"OomScoreAdj": 0,
"PidMode": "",
"Privileged": false,
"PublishAllPorts": false,
"ReadonlyRootfs": false,
"SecurityOpt": null,
"UTSMode": "",
"UsernsMode": "",
"ShmSize": 67108864,
"Runtime": "runc",
"Isolation": "",
"CpuShares": 0,
"Memory": 0,
"NanoCpus": 0,
"CgroupParent": "",
"BlkioWeight": 0,
"BlkioWeightDevice": [],
"BlkioDeviceReadBps": [],
"BlkioDeviceWriteBps": [],
"BlkioDeviceReadIOps": [],
"BlkioDeviceWriteIOps": [],
"CpuPeriod": 0,
"CpuQuota": 0,
"CpuRealtimePeriod": 0,
"CpuRealtimeRuntime": 0,
"CpusetCpus": "",
"CpusetMems": "",
"Devices": [],
"DeviceCgroupRules": null,
"DeviceRequests": null,
"MemoryReservation": 0,
"MemorySwap": 0,
"MemorySwappiness": null,
"OomKillDisable": null,
"PidsLimit": null,
"Ulimits": null,
"CpuCount": 0,
"CpuPercent": 0,
"IOMaximumIOps": 0,
"IOMaximumBandwidth": 0,
"MaskedPaths": [
"/proc/asound",
"/proc/acpi",
"/proc/kcore",
"/proc/keys",
"/proc/latency_stats",
"/proc/timer_list",
"/proc/timer_stats",
"/proc/sched_debug",
"/proc/scsi",
"/sys/firmware"
],
"ReadonlyPaths": [
"/proc/bus",
"/proc/fs",
"/proc/irq",
"/proc/sys",
"/proc/sysrq-trigger"
]
},
"GraphDriver": {
"Data": {
"LowerDir": "/var/lib/docker/overlay2/45fab3456bae181cec9ccd9958f491dc51594ebee0d8f2c05ea913dfc216373a-init/diff:/var/lib/docker/overlay2/554caa8009fcffb10545ec19f3647ff8f6e4efb76bb6968f88ce4aa4e5a99d93/diff:/var/lib/docker/overlay2/b0504363136d9025c59462da707577865b082f00e32a89bbf904acd2e8fcb0dd/diff:/var/lib/docker/overlay2/1d752f3ab2539a06342fbc75736758e9bed9419abe8c12337b475513a550dbe2/diff:/var/lib/docker/overlay2/dafcf26ab1425cac1c2cbb027b6d98b1c0a3c2219ff017fd60eda1a3cbcc82a5/diff:/var/lib/docker/overlay2/942525d090959c9e107a63b340e3d9ef2cfc562e7db3f4a50baacbdb84e290aa/diff:/var/lib/docker/overlay2/fbbdc662d593c7e1a3129b3d07b579adeca19f6b4eb1a07644baeeb69d08d5d7/diff:/var/lib/docker/overlay2/1b05ad572a1376ace845fde6a4504d0c4fb3feb61c553ec8bec7683ae1462233/diff:/var/lib/docker/overlay2/1150f0ce41e94b651bcb633e3f9c65f3e729d10e1f496473ee3549de929b6c71/diff:/var/lib/docker/overlay2/0b83049e1887b1c36619fff3f47336edc894acb612eb9ab6a8af78f065ac839d/diff:/var/lib/docker/overlay2/211c588f45135714ed24353659529c1d1913e4e47f51654140dce11ad6cb9781/diff:/var/lib/docker/overlay2/119cde52e1eb9760d4ed3e94482b22aa5c02ce7d6082cf7b7cf872e6f243238a/diff",
"MergedDir": "/var/lib/docker/overlay2/45fab3456bae181cec9ccd9958f491dc51594ebee0d8f2c05ea913dfc216373a/merged",
"UpperDir": "/var/lib/docker/overlay2/45fab3456bae181cec9ccd9958f491dc51594ebee0d8f2c05ea913dfc216373a/diff",
"WorkDir": "/var/lib/docker/overlay2/45fab3456bae181cec9ccd9958f491dc51594ebee0d8f2c05ea913dfc216373a/work"
},
"Name": "overlay2"
},
"Mounts": [
{
"Type": "volume",
"Name": "dc3ba28061ef18cc37f4472804ac3d7a30b305fea0f364815e5efcd55ad71401",
"Source": "/var/lib/docker/volumes/dc3ba28061ef18cc37f4472804ac3d7a30b305fea0f364815e5efcd55ad71401/_data",
"Destination": "/var/lib/mysql",
"Driver": "local",
"Mode": "",
"RW": true,
"Propagation": ""
}
],
"Config": {
"Hostname": "a8a275aa4b15",
"Domainname": "",
"User": "",
"AttachStdin": false,
"AttachStdout": true,
"AttachStderr": true,
"ExposedPorts": {
"3306/tcp": {},
"33060/tcp": {}
},
"Tty": false,
"OpenStdin": false,
"StdinOnce": false,
"Env": [
"MYSQL_ROOT_PASSWORD=ok",
"PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
"GOSU_VERSION=1.16",
"MYSQL_MAJOR=8.0",
"MYSQL_VERSION=8.0.34-1.el8",
"MYSQL_SHELL_VERSION=8.0.34-1.el8"
],
"Cmd": [
"mysqld"
],
"Image": "mysql:8.0",
"Volumes": {
"/var/lib/mysql": {}
},
"WorkingDir": "",
"Entrypoint": [
"docker-entrypoint.sh"
],
"OnBuild": null,
"Labels": {}
},
"NetworkSettings": {
"Bridge": "",
"SandboxID": "d43214351095b716cc00d79d20f32389e95e3b62808d252c42036b567903b9c0",
"HairpinMode": false,
"LinkLocalIPv6Address": "",
"LinkLocalIPv6PrefixLen": 0,
"Ports": {
"3306/tcp": [
{
"HostIp": "0.0.0.0",
"HostPort": "3306"
}
],
"33060/tcp": null
},
"SandboxKey": "/var/run/docker/netns/d43214351095",
"SecondaryIPAddresses": null,
"SecondaryIPv6Addresses": null,
"EndpointID": "cdc7d50bcb9a5c0be31aba3c3d2fd5726996bd5870801675250f48b02eaef278",
"Gateway": "172.17.0.1",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"IPAddress": "172.17.0.2",
"IPPrefixLen": 16,
"IPv6Gateway": "",
"MacAddress": "02:42:ac:11:00:02",
"Networks": {
"bridge": {
"IPAMConfig": null,
"Links": null,
"Aliases": null,
"NetworkID": "a2a205d498dcc308e419b7785da1e2ebce4da2f962df0b83f5c98f8aef9a5759",
"EndpointID": "cdc7d50bcb9a5c0be31aba3c3d2fd5726996bd5870801675250f48b02eaef278",
"Gateway": "172.17.0.1",
"IPAddress": "172.17.0.2",
"IPPrefixLen": 16,
"IPv6Gateway": "",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"MacAddress": "02:42:ac:11:00:02",
"DriverOpts": null
}
}
}
}
]
If we want to prevent manual typing of the password but also not specifying it in the terminal command (so it does not show in history) we can create a config file where mysql will be sourcing username and password for MySQL server.
% vi ~/.my.cnf
[mysqldump]
user=user1
password=pass1
[mysql]
user=user1
password=pass1
We can now run mysql without specifying credentials in command line:
% /opt/homebrew/opt/mysql-client/bin/mysql --defaults-extra-file=~/.my.cnf ...
Similarly, we can run mysqldump:
% /opt/homebrew/opt/mysql-client/bin/mysqldump --host=... --port=3306 --all-databases --skip-triggers > db-dump.sql
---
...
