How to run MySQL server and client using Docker on MacOS

image source: https://factorialcomplexity.com/

To install Docker on MacOS follow these instructions: Install Docker Desktop on Mac | Docker Docs

To run MySQL server in Docker container which is not detached (does not run in background):

% docker run \

--name my-mysql-container \

-e MYSQL_ROOT_PASSWORD=ok \

-p3306:3306 \

mysql:8.0

2023-10-05 14:53:01+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.34-1.el8 started.
2023-10-05 14:53:01+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-10-05 14:53:01+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.34-1.el8 started.
2023-10-05 14:53:01+00:00 [Note] [Entrypoint]: Initializing database files
2023-10-05T14:53:01.934152Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-10-05T14:53:01.934254Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.34) initializing of server in progress as process 80
2023-10-05T14:53:01.939114Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-10-05T14:53:02.171938Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-05T14:53:02.830346Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2023-10-05 14:53:05+00:00 [Note] [Entrypoint]: Database files initialized
2023-10-05 14:53:05+00:00 [Note] [Entrypoint]: Starting temporary server
2023-10-05T14:53:05.321074Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-10-05T14:53:05.324515Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34) starting as process 124
2023-10-05T14:53:05.335777Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-10-05T14:53:05.428502Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-05T14:53:05.544258Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-10-05T14:53:05.544283Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-10-05T14:53:05.545082Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2023-10-05T14:53:05.553518Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2023-10-05T14:53:05.553570Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.34' socket: '/var/run/mysqld/mysqld.sock' port: 0 MySQL Community Server - GPL.
2023-10-05 14:53:05+00:00 [Note] [Entrypoint]: Temporary server started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
2023-10-05 14:53:06+00:00 [Note] [Entrypoint]: Stopping temporary server
2023-10-05T14:53:06.611355Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.34).
2023-10-05T14:53:08.163907Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.34) MySQL Community Server - GPL.
2023-10-05 14:53:08+00:00 [Note] [Entrypoint]: Temporary server stopped
2023-10-05 14:53:08+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
2023-10-05T14:53:08.821603Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-10-05T14:53:08.822305Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.34) starting as process 1
2023-10-05T14:53:08.825981Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-10-05T14:53:08.895159Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-05T14:53:09.030902Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-10-05T14:53:09.030930Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-10-05T14:53:09.031870Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2023-10-05T14:53:09.039049Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-10-05T14:53:09.039091Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.34' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2023-10-05T14:54:46.771871Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.34).
2023-10-05T14:54:47.855145Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.34) MySQL Community Server - GPL.

If we want to stop this container, we need to open another terminal and execute:

% 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

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

---

...

My Public Notepad

Pages

Thursday 5 October 2023

How to run MySQL server and client using Docker on MacOS

No comments: