Working with MySQL Clients

Concept

You interact with MySQL through clients. The three official ones:

mysql command-line client Available everywhere, scriptable, ideal on servers and in CI

Visual SQL editor with result grids, schema browser, ER diagrams, visual EXPLAIN, server administration, data import/export MySQL Shell (mysqlsh)

SQL, JavaScript, and Python modes; dump/load utilities; InnoDB Cluster administration; upgrade checker; JSON import

Why It Matters

Knowing client commands, script execution, output formats, and safety options makes you productive and prevents accidents such as deleting all rows by mistake.

Syntax

Client-side commands (interpreted by the mysql client, not the server):

List client commands Change default database Connection and server status source file.sql Execute SQL from a file tee out.txt / notee Log session output to a file \G (statement terminator) Discard the current input exit / quit Leave the client Run a shell command (Linux/macOS) delimiter //

Change statement delimiter (stored programs) Page long output (Linux/macOS) Show warnings after every statement

Example

3.1 Everyday statements DESCRIBE customers; output:

3.2 Vertical output with \G 3.3 Running SQL scripts and batch mode Inside the client:

3.4 Safety options With safe updates on, UPDATE/DELETE without a key-based WHERE or LIMIT is rejected (error 1175). MySQL Workbench enables safe updates by default.

3.5 MySQL Workbench Navigator: schemas, tables, views, routines; server status and administration.

SQL Editor: run statements (Ctrl+Enter current statement, Ctrl+Shift+Enter all), result grid with inline editing, export results to CSV/JSON.

Visual Explain: graphical execution plans.

Data Modeling: create EER diagrams, forward-engineer to SQL, reverse-engineer from a database, synchronize models.

Administration: users and privileges, server logs, options file, data export/import (uses mysqldump), performance reports.

3.6 MySQL Shell (util.checkForServerUpgrade() / util.dumpSchemas() in JavaScript mode.)

SHOW DATABASES;
USE eh_company;
SHOW TABLES;
DESCRIBE customers;                        -- or: DESC customers; SHOW COLUMNS FROM customers;
SHOW CREATE TABLE customers\G              -- full DDL, vertical format
SHOW INDEX FROM orders;
SHOW TABLE STATUS LIKE 'orders'\G
SELECT DATABASE();                         -- current default database
SHOW WARNINGS;                             -- warnings from the previous statement
SHOW PROCESSLIST;                          -- current connections and running statements

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| customer_id | int unsigned | NO   | PRI | NULL    |       |
| name        | varchar(100) | NO   |     | NULL    |       |
| email       | varchar(100) | YES  |     | NULL    |       |
| city        | varchar(50)  | NO   |     | NULL    |       |
| country     | varchar(50)  | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

SELECT * FROM employees WHERE emp_id = 1\G

*************************** 1. row ***************************
    emp_id: 1
first_name: Asha
 last_name: Rao
     email: [email protected]
   dept_id: 1
manager_id: NULL
 job_title: CTO
    salary: 250000.00
 hire_date: 2015-03-01

mysql -u root -p eh_company < schema.sql                 # run a script
mysql -u root -p -e "SELECT COUNT(*) FROM eh_company.orders"   # one statement
mysql -u eh_app -p -B -N -e "SELECT name, city FROM eh_company.customers" > customers.tsv   # tab-separated, no headers
mysql -u eh_app -p --table -e "SELECT * FROM eh_company.departments"   # force table borders in batch mode
mysql -u eh_app -p -H -e "SELECT * FROM eh_company.departments" > departments.html   # HTML output

SOURCE /path/to/seed.sql;

mysql --safe-updates -u eh_app -p eh_company     # alias: --i-am-a-dummy

SET SQL_SAFE_UPDATES = 1;
DELETE FROM orders;   -- ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysqlsh root@localhost:3306 --sql        # SQL mode
mysqlsh root@localhost:3306 --py         # Python mode

MySQL  localhost:3306 ssl  SQL > \use eh_company
MySQL  localhost:3306 ssl  eh_company  SQL > SELECT COUNT(*) FROM orders;
MySQL  localhost:3306 ssl  JS > \py
MySQL  localhost:3306 ssl  Py > util.check_for_server_upgrade()        # pre-upgrade checks
MySQL  localhost:3306 ssl  Py > util.dump_schemas(["eh_company"], "/backups/eh_company_dump")   # parallel logical dump

Expected Result

mysql -u eh_app -p -B -N -e "SELECT COUNT(*) FROM eh_company.orders"

Common Mistakes

Forgetting the semicolon; the client waits with -> prompt. Type ; or \c to cancel.

Unclosed quote: the prompt changes to '> or ">; close the quote, then \c.

Using \G and ; together (\G;) — produces a harmless "No query specified" error.

Running scripts against the wrong database because no USE or database argument was given.

Best Practices

Enable --safe-updates for interactive sessions on important databases.

Use \G for wide rows, -B -N for scripting.

Keep SQL in files under version control and run them with mysql < file.sql or migration tools.

Use tee to record sessions when making production changes.

Interview Relevance

Low to medium: "How do you run a SQL script from the command line?", "What does \G do?", "What is MySQL Shell used for?".

Practice Questions

Show the full CREATE TABLE statement of order_items in vertical format.

Export all product names and prices to a tab-separated file using batch mode.

Turn on safe updates and try an UPDATE without WHERE. What error do you get?

Use MySQL Workbench to reverse-engineer the eh_company schema into an ER diagram.