TopolearnThe mysql client is the universal way to inspect and fix a MySQL database, locally or in production.
MySQL is a client-server database: the server mysqld listens on port 3306, and clients connect to it. Quick ways to get a server:
- Docker:
docker run --name mysql -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -d mysql:8.4. - Official packages (APT/YUM repositories, the macOS and Windows installers) or Homebrew.
- Managed services: Amazon RDS/Aurora, Google Cloud SQL, Azure Database for MySQL.
Version note: 8.4 is the current long-term support (LTS) line; 9.x releases are short-lived "innovation" releases. Production systems usually run LTS.
Connect with mysql -h 127.0.0.1 -u root -p. A detail that trips people up: -h localhost makes the client use the local Unix socket file, not TCP, which matters for Docker and for user accounts defined per host.
Inside the client, statements end with ;. Ending with \G instead prints each row vertically — invaluable for wide output. Useful commands:
SHOW DATABASES;,USE shop;,SHOW TABLES;DESCRIBE orders;andSHOW CREATE TABLE orders\Gfor the full definitionSOURCE file.sqlruns a script; from the shell,mysql shop < file.sqlSELECT VERSION(), CURRENT_USER();
In MySQL, DATABASE and SCHEMA are synonyms: each database is a namespace of tables, and you can join tables across databases on the same server with db.table names.
Example
docker run --name mysql -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -d mysql:8.4
mysql -h 127.0.0.1 -P 3306 -u root -p
# mysql> SELECT VERSION(); -- 8.4.x
# mysql> CREATE DATABASE shop;
# mysql> USE shop;
# mysql> CREATE TABLE product (id INT PRIMARY KEY, name VARCHAR(100));
# mysql> SHOW TABLES;
# +----------------+
# | Tables_in_shop |
# | product |
# mysql> SHOW CREATE TABLE product\G
# ... ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# run a script non-interactively
mysql -h 127.0.0.1 -u root -p shop < schema.sqlRemember
- mysqld listens on 3306; mysql is the command-line client
- -h localhost uses the Unix socket; -h 127.0.0.1 uses TCP
- End a statement with \G for vertical output
Watch out for
- Connecting to a Docker container with -h localhost and getting a socket error instead of using 127.0.0.1.