Clients and tools

One idea: a client is a messenger

The server owns the data: mysqld.exe for MySQL, postgres.exe for PostgreSQL. A client is anything that connects, sends a statement and reads the result, so the mysql prompt, Workbench, DBeaver and your company's web application are all clients. What differs between them is convenience; chapter 2 traced the rest of the path.

MySQL clients

# One statement, then exit. -p with no value prompts for the password.
mysql -u root -p shop -e "SELECT id, name, city FROM customers"

MySQL Shell (mysqlsh): the modern one

Behind \js and \py are the utilities a DBA installs it for. util.dumpInstance(), util.dumpSchemas() and util.loadDump() write parallel, compressed dumps, which Oracle's manual prefers to mysqldump for anything large (chapter 15). util.checkForServerUpgrade() finds what will break in the next version, and dba.* and cluster.* build InnoDB Cluster, ClusterSet and ReplicaSet.

mysqlsh root@localhost:3306/shop
SELECT status, COUNT(*) FROM orders GROUP BY status;
\js
util.dumpSchemas(["shop"], "C:/backups/shop-2026-09-27", {threads: 4})
util.checkForServerUpgrade()
\sql

Paid Enterprise tools, and the free alternatives

MySQL Enterprise Backup (mysqlbackup, hot physical InnoDB backups) is paid Enterprise Edition only, and MySQL Enterprise Monitor reached end of life on January 1, 2025. Your free backup options are mysqldump, the Shell dump utilities, the Clone plugin and the binary log; mysqlpump was removed in 8.4 and Percona XtraBackup does not run on Windows. Free monitoring is the Performance Schema and sys schema (chapter 16), plus PMM for graphs.

PostgreSQL clients

psql: the command line, with backslash commands

Anything that starts with a backslash is a meta-command that psql handles itself; everything else goes to the server. They are wrappers over the catalog tables, like MySQL's SHOW TABLES.

psql -U postgres -d shop
\dt
\d orders
SELECT id, customer_id, status, total FROM orders WHERE customer_id IS NULL;
\q

Three Windows details, because Linux advice will mislead you. There is no postgres operating-system user (the service runs as Network Service), so you always run psql -U postgres with a password. A plain console prints a code-page warning, so run chcp 1252 first. And psql connects over TCP to localhost, so only the host ... 127.0.0.1/32 and ::1/128 lines of pg_hba.conf matter locally.

Connecting: the five things every client asks for

Every dialog and command line asks the same five questions, as flags or packed into one URI. MySQL adds a twist: an account is a user and a host pattern, so 'app'@'localhost' and 'app'@'%' are two different accounts. Chapter 14 covers that and connecting from another machine.

# Flags, then a URI. Both clients fall back to plain text unless you require TLS.
mysql -h 127.0.0.1 -P 3306 -u app -p --ssl-mode=REQUIRED shop
mysqlsh app@db01.example.internal:3306/shop
mysql_config_editor set --login-path=local --host=localhost --user=root --password
# psql: lowercase -p is the port, -U is the user
psql -h 127.0.0.1 -p 5432 -U app -d shop
psql "postgresql://app@db01.example.internal:5432/shop?sslmode=require"

Grids run real SQL: editing a cell and clicking Apply sends an UPDATE that autocommit commits on the spot (chapter 9). Browse production with a read-only account, and give that connection its own color.

Sample databases worth loading

An empty server teaches nothing. Load shop first, and a bigger one when you want to feel what an index does on a million rows.

The full MySQL list, including the 625 MB airportdb, is at dev.mysql.com/doc/index-other.html. Each sample creates its own database; sakila comes as two files, and employees must be run from inside its unzipped folder, because it pulls in data files by relative path.

# MySQL. The course dataset creates database "shop".
cmd.exe /c "mysql -u root -p < assets\sample-shop.mysql.sql"
cmd.exe /c "mysql -u root -p < sakila-schema.sql"
cmd.exe /c "mysql -u root -p < sakila-data.sql"
cmd.exe /c "mysql -u root -p < employees.sql"

# PostgreSQL. shop and Chinook create their own database, so no -d.
psql -U postgres -f assets/sample-shop.postgres.sql
psql -U postgres -c "CREATE DATABASE pagila"
psql -U postgres -d pagila -f pagila-schema.sql
psql -U postgres -d pagila -f pagila-data.sql
# dvdrental is a pg_dump archive, so pg_restore, not psql
psql -U postgres -c "CREATE DATABASE dvdrental"
pg_restore -U postgres -d dvdrental C:\samples\dvdrental.tar

Throwaway instances: Docker and online sandboxes

Your installed server is where you practice DBA work. Sometimes you want a second one to test a restore, run 9.7 next to 8.4, or break something on purpose.

# MySQL 8.4 LTS on host port 3307 (tags mysql:9.7 and mysql:26.7 also exist)
docker run --name mysql84 -e MYSQL_ROOT_PASSWORD=ChangeMe1 -p 3307:3306 -d mysql:8.4
mysql -h 127.0.0.1 -P 3307 -u root -p
# PostgreSQL 18 on host port 5433 (postgres:17 for the older major)
docker run --name pg18 -e POSTGRES_PASSWORD=ChangeMe1 -p 5433:5432 -d postgres:18
psql -h 127.0.0.1 -p 5433 -U postgres
# Done experimenting? This deletes the containers and their data.
docker rm -f mysql84 pg18

Docker Desktop uses WSL 2 under the hood. Map the container port to a spare host port if your real service owns 3306 or 5432, then connect with your normal client. Oracle builds its MySQL images for Linux and calls running them elsewhere "at your own risk", fine for a learning instance. Image pages: hub.docker.com/_/mysql and hub.docker.com/_/postgres.

Browser sandboxes answer "does this syntax work in 8.4?" without installing anything. They are query-only, and none runs the 26.x line yet, so treat them as scratch paper; chapter 18 has the gamified practice sites.

Skip W3Schools' "MySQL Tryit": it is not a MySQL server, and modern browsers only give it a read-only fallback.

Which tool when

If a task is not in this table, it is still SQL or a command-line utility, so ask: "MySQL 9.7 on Windows 11, which tool and which exact command does this?"