Getting started with the mysql client: administer MySQL from the command line

You have installed MySQL, the service is running, systemctl status mysql shows active (running). Now you have to get inside. How do you list the existing databases? See a table's structure? Run a query, export a result to CSV, or launch an automated SQL script?

mysql is MySQL's official command-line client. It is the administrator's daily tool: exploring databases and tables, running queries, importing/exporting data, and batch scripting. Unlike psql, PostgreSQL's command-line client, the exploration commands go through SHOW statements and INFORMATION_SCHEMA views rather than through meta-commands.

Version used

This guide is based on the mysql 8.4.11 client (MySQL Community Server), the course's reference version. The outputs were captured with the official mysql:8.4 image (Oracle Linux 9), on a single sample database (labdb, clients table) replayed in the order of the page; connection identifiers, durations and dates will differ on yours. Two exceptions, which this image does not ship: mysqlimport and mysql_config_editor were replayed separately, on a Debian 13 virtual machine with Oracle's packages, on MySQL 8.4.13. Their outputs therefore carry that version, and the sections concerned say so. The commands shown work on all distributions and on all MySQL 8.x versions.

What you will learn

• Connect to MySQL locally (Unix socket) and over the network (TCP/IP), then store your credentials with login-path
• Explore the structure of an instance with SHOW, DESCRIBE and INFORMATION_SCHEMA
• Format the output: vertical mode (\G), pager, export to a file with tee
• Import and export data with LOAD DATA, SELECT INTO OUTFILE and mysqlimport
• Script administration tasks with batch mode (-e, redirection, source)

In what context?

You will use the mysql client in all of these situations:

• You have just installed MySQL and want to check what exists on the instance
• You must create a database and tables for a new application
• You administer a server and need to know the size of the databases, the active connections or the critical variables
• You must import a CSV file or export results for a colleague
• You automate administration tasks in a shell script (migration, report, cleanup)
• You must diagnose a performance or connection problem in production

What this guide does not cover

This guide stays focused on the command-line client, that is, on what you type once connected. The server's internal workings (architecture, the InnoDB engine, buffer pool, threads) are explained in Discover MySQL, which you should read to understand what the client queries.

The installation of the instance belongs to the Installation guide, tuning (InnoDB buffer pool, connections, binary log, slow query log) to the Configuration guide, and hardening (caching_sha2_password, TLS, roles, least privilege) to the Security guide. If you do not have a running instance yet, start with the installation.

Prerequisites

• MySQL installed and the service running (see the Installation guide)
• An account with sufficient rights (root or an administration account created during installation)

Quick check:

mysql --version

mysql  Ver 8.4.11 for Linux on x86_64 (MySQL Community Server - GPL)

Connecting to MySQL

MySQL accepts two connection channels: the Unix socket, a special file usable only from the local machine, and TCP/IP for everything else. The client picks the socket as soon as the host is localhost, which often surprises people: specifying 127.0.0.1 forces the opposite, going through the network. To avoid retyping your credentials, two storage methods are presented here, from the least to the most secure: the ~/.my.cnf file, then mysql_config_editor, to be preferred in production.

Local connection (Unix socket)

The most common method on the server itself. The connection goes through a Unix socket, no network, no encryption needed:

mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.11 MySQL Community Server - GPL

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

The mysql> prompt shows that you are connected. Unlike PostgreSQL, MySQL has no different prompt for superusers.

If you left the root password empty during installation on Debian, root authenticates through auth_socket: the server trusts the name of the system user, and mysql -u root -p is refused. In that case run sudo mysql, or use the labadmin account created by the Installation guide.

To connect directly to a specific database (the labdb database is created in the "Preparing a sample database" section further down: without it, omit the database name):

mysql -u root -p labdb

Network connection (TCP/IP)

For a connection from another server or by forcing TCP (useful when the socket is not accessible):

mysql -h 192.168.122.70 -P 3306 -u labadmin -p labdb

This command only succeeds when three conditions are met. The account must exist for the host you come from: 'labadmin'@'localhost', the first account created by the Installation guide, only matches a local connection through the socket and returns an access denied error (ERROR 1045) on a TCP connection. The server must listen on the network: if you applied bind-address = 127.0.0.1 as that same guide recommends, the connection is refused until the server's address is listed there. Finally, the firewall must let port 3306 through.

• On the account side, create 'labadmin'@'192.168.122.%' (your lab subnet) or the 'labadmin'@'%' that the installation guide suggests for an isolated lab.
• On the server side, add the server's address to bind-address, then restart the service.
• On the firewall side, open port 3306 only to the addresses that need it.

Option	Description
-h	Server address (hostname or IP)
-P	Port (3306 by default)
-u	User name
-p	Prompt for the password (no space after -p)
labdb	Database to select at startup

Swipe to see more

Watch out for -p vs -ppassword

-p alone prompts for the password in interactive mode, without displaying it. -ppassword (attached, with no space) passes the password as an argument: it is then visible in ps aux and in the shell history. Never do that in production.

Options file ~/.my.cnf

Rather than typing the connection options every time, create a ~/.my.cnf file:

[client]
user=labadmin
password=StrongPassword!2026

Protect the permissions immediately:

chmod 600 ~/.my.cnf

On Unix, MySQL ignores configuration files that are writable by everyone (world-writable), so the chmod 600 is essential, not optional.

Then a simple command is enough:

mysql labdb

Password in clear text

The ~/.my.cnf file stores the password in clear text on disk. This is acceptable for a lab, but in production, prefer mysql_config_editor (see below), which stores the credentials encrypted, with a reversible encryption that does not stop whoever reads the file from recovering them. The Security guide extends this reflex with connection TLS encryption, roles and least privilege.

mysql_config_editor: encrypted credentials (login-path)

MySQL provides a dedicated tool to store credentials securely:

mysql_config_editor set --login-path=lab --user=labadmin --password

The tool asks for the password and stores it encrypted in ~/.mylogin.cnf. To use it:

mysql --login-path=lab labdb

To list the stored profiles:

mysql_config_editor print --all

[lab]
user = "labadmin"
password = *****

The password is masked in this display, and the user value comes out in quotes: that is the form mysql_config_editor writes itself, not a typo in this guide.

This profile contains no host: the client therefore uses the Unix socket, and the connection arrives as labadmin@localhost, the account created by the Installation guide. Adding --host=127.0.0.1 to mysql_config_editor set (or host=127.0.0.1 in ~/.my.cnf) would switch the connection to TCP, and MySQL then looks for a labadmin@127.0.0.1 account. With only the labadmin@localhost account, the response measured on MySQL 8.4 is ERROR 1045 (28000): Access denied for user 'labadmin'@'127.0.0.1' (using password: YES). For a TCP connection, create the account on the desired host, or use labadmin@'%' if you created it.

Prefer login-path to ~/.my.cnf

mysql_config_editor stores the connection parameters in ~/.mylogin.cnf as login paths. This file is used by the MySQL clients without exposing the password on the command line, and it is still taken into account even with --no-defaults: measured on MySQL 8.4.13, mysql --no-defaults --login-path=lab does connect as labadmin@localhost.

Beware of --no-login-paths, which does not merely ignore the file: it removes the option itself. The command mysql --no-defaults --no-login-paths --login-path=lab does not silently fall back to an anonymous connection, it fails with mysql: [ERROR] unknown variable 'login-path=lab'.

\s and status: checking your connection

Once connected, always check where you are:

\s

--------------
mysql  Ver 8.4.11 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          25
Current database:       labdb
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.4.11 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 1 min 37 sec

Threads: 2  Questions: 82  Slow queries: 0  Opens: 370  Flush tables: 3  Open tables: 290  Queries per second avg: 0.845
--------------

Connection: Localhost via UNIX socket confirms the local connection. Current database: labdb shows the active database: as long as you have not created or selected labdb (the "Preparing a sample database" section further down), this field shows NULL.

Exploring the instance

Unlike PostgreSQL, which uses meta-commands (\l, \dt, \d), MySQL relies on SHOW statements and views in INFORMATION_SCHEMA.

Preparing a sample database

A new instance only contains its system databases. So that the examples on this page have something to explore, create a labdb database with a clients table of five rows, from the mysql> prompt (an account that can create databases is enough: root or the labadmin from the installation). Its structure matches the one that SHOW CREATE TABLE displays further down.

CREATE DATABASE IF NOT EXISTS labdb CHARACTER SET utf8mb4;
USE labdb;
CREATE TABLE clients (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE,
  equipe VARCHAR(50),
  inscription DATE DEFAULT (CURDATE())
) ENGINE=InnoDB;
INSERT INTO clients (nom, email, equipe, inscription) VALUES
  ('Alice',  'alice@devops.lab',  'SRE',      '2026-01-15'),
  ('Bob',    'bob@devops.lab',    'Backend',  '2026-02-20'),
  ('Claire', 'claire@devops.lab', 'Frontend', '2026-03-10'),
  ('David',  'david@devops.lab',  'SRE',      '2026-03-28'),
  ('Eva',    'eva@devops.lab',    'DevOps',   '2026-04-01');

The last statement should report Records: 5 Duplicates: 0 Warnings: 0. All the outputs that follow were captured on this single database, in the order of the page: a clients table of five rows, nothing else. You therefore get the same figures, up to the import section, which adds two rows and brings the total to seven. Only the connection identifiers, the durations and the current dates change.

SHOW DATABASES

List all the databases of the instance:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| labdb              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Database	Role
information_schema	Virtual catalog, metadata about all tables, columns, indexes
mysql	System tables: accounts, privileges, data dictionary
performance_schema	Internal instrumentation: queries, locks, I/O, threads
sys	Simplified views on top of performance_schema (human-readable)
labdb	Your application database

Swipe to see more

USE: switching database

USE sets the active database of the session, which saves you from prefixing every table name. The command returns no data and opens no transaction, it only changes the current context.

USE labdb;

Database changed

All following queries run in the context of labdb, no need to prefix table names.

SHOW TABLES and SHOW COLUMNS

List the tables of the active database:

SHOW TABLES;

+-----------------+
| Tables_in_labdb |
+-----------------+
| clients         |
+-----------------+

See a table's columns:

SHOW COLUMNS FROM clients;

+-------------+--------------+------+-----+-----------+-------------------+
| Field       | Type         | Null | Key | Default   | Extra             |
+-------------+--------------+------+-----+-----------+-------------------+
| id          | int          | NO   | PRI | NULL      | auto_increment    |
| nom         | varchar(100) | NO   |     | NULL      |                   |
| email       | varchar(200) | NO   | UNI | NULL      |                   |
| equipe      | varchar(50)  | YES  |     | NULL      |                   |
| inscription | date         | YES  |     | curdate() | DEFAULT_GENERATED |
+-------------+--------------+------+-----+-----------+-------------------+

DEFAULT_GENERATED flags a default computed by an expression (here CURDATE()), not a constant.

The DESCRIBE alias (or DESC) produces the same result:

DESC clients;

SHOW CREATE TABLE: the exact DDL

To see the exact CREATE TABLE statement, including the storage engine, the charset and the constraints: this is the DDL (Data Definition Language), the set of statements that define the structure of objects (CREATE, ALTER, DROP).

SHOW CREATE TABLE clients\G

*************************** 1. row ***************************
       Table: clients
Create Table: CREATE TABLE `clients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nom` varchar(100) NOT NULL,
  `email` varchar(200) NOT NULL,
  `equipe` varchar(50) DEFAULT NULL,
  `inscription` date DEFAULT (curdate()),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

This is the command to use to reproduce a table on another server or to understand its exact configuration (InnoDB engine, utf8mb4 charset, collation, that is, the rule for sorting and comparing text).

SHOW INDEX: a table's indexes

This command lists one record per indexed column, not per index: a composite index (covering several columns) therefore appears on several rows, distinguished by Seq_in_index. The Cardinality column estimates the number of distinct values; it is what the optimizer consults to decide whether to use the index, and it is refreshed with ANALYZE TABLE.

SHOW INDEX FROM clients\G

*************************** 1. row ***************************
        Table: clients
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: clients
   Non_unique: 0
     Key_name: email
 Seq_in_index: 1
  Column_name: email
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

INFORMATION_SCHEMA: querying the catalog

INFORMATION_SCHEMA is an SQL standard implemented by MySQL. It exposes metadata as virtual tables that you can query with classic SQL:

Database sizes:

SELECT table_schema AS base,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS taille_mb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY taille_mb DESC;

+--------------------+-----------+
| base               | taille_mb |
+--------------------+-----------+
| mysql              |      7.88 |
| labdb              |      0.03 |
| sys                |      0.02 |
| information_schema |      0.00 |
| performance_schema |      0.00 |
+--------------------+-----------+

Detailed size per table:

SELECT table_name,
       ROUND((data_length + index_length) / 1024, 2) AS taille_ko,
       table_rows AS lignes_estimees
FROM information_schema.TABLES
WHERE table_schema = 'labdb'
ORDER BY (data_length + index_length) DESC;

+------------+-----------+-----------------+
| TABLE_NAME | taille_ko | lignes_estimees |
+------------+-----------+-----------------+
| clients    |     32.00 |               5 |
+------------+-----------+-----------------+

The column names of the INFORMATION_SCHEMA views are rendered in uppercase (TABLE_NAME), even when the query writes them in lowercase; only the aliases you choose (taille_ko) keep their case. The 32 KB are the sum of the table (16 KB) and of the unique index on email (16 KB).

table_rows is an estimate

The table_rows column of INFORMATION_SCHEMA.TABLES is an estimate for InnoDB tables, not an exact count. For a precise count, use SELECT COUNT(*) FROM table. The approximation comes from InnoDB's MVCC (multi-version concurrency control): each transaction sees its own version of the rows, so a single total would be too expensive to maintain in real time.

List a table's columns:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.COLUMNS
WHERE table_schema = 'labdb' AND table_name = 'clients'
ORDER BY ordinal_position;

+-------------+-----------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+-------------+-----------+-------------+----------------+
| id          | int       | NO          | NULL           |
| nom         | varchar   | NO          | NULL           |
| email       | varchar   | NO          | NULL           |
| equipe      | varchar   | YES         | NULL           |
| inscription | date      | YES         | curdate()      |
+-------------+-----------+-------------+----------------+

Performance Schema and sys: live diagnosis

Performance Schema instruments the server in real time. The sys database offers simplified views on top of it:

Active connections:

SELECT * FROM sys.session\G

Slowest queries (top 10):

SELECT query, exec_count, avg_latency, rows_examined_avg
FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;

Most accessed tables:

SELECT table_schema, table_name, rows_fetched, rows_inserted, rows_updated, rows_deleted
FROM sys.schema_table_statistics
WHERE table_schema = 'labdb'
ORDER BY rows_fetched DESC;

Start with the sys views

Direct queries on performance_schema are verbose and complex. Use the sys views first, they are designed to be human-readable. The Monitoring and maintenance guide explores them in detail, with EXPLAIN, slow queries and OPTIMIZE TABLE.

Summary table of exploration commands

Keep this table at hand: these are the eleven commands that are enough to explore an unknown instance. The last three concern the server rather than the data, they answer the questions "how is it configured" and "what is it doing right now".

Command	Description
SHOW DATABASES;	List databases
USE <base>;	Switch active database
SHOW TABLES;	List the tables of the active database
SHOW COLUMNS FROM <table>;	Columns, types, keys, defaults
DESC <table>;	Alias for SHOW COLUMNS
SHOW CREATE TABLE <table>\G	Full DDL (engine, charset, constraints)
SHOW INDEX FROM <table>\G	Indexes, cardinality, type
SHOW TABLE STATUS\G	Size, engine, estimated rows
SHOW VARIABLES LIKE 'pattern';	Server configuration variables
SHOW GLOBAL STATUS LIKE 'pattern';	Server status counters
SHOW PROCESSLIST;	Active connections and running queries

Swipe to see more

Formatting and display

The client adjusts its presentation to the context: ASCII borders in interactive mode, tab-separated values when the output is redirected. Knowing how to force one or the other changes everything, whether for reading a thirty-column table or for producing output a shell script can use.

Table mode (default) and vertical mode (\G)

The default mode displays results as a horizontal table. For wide results (many columns), the vertical mode with \G is far more readable:

-- Table mode (default)
SELECT * FROM clients WHERE id = 1;

+----+---------+-------------------+--------+-------------+
| id | nom     | email             | equipe | inscription |
+----+---------+-------------------+--------+-------------+
|  1 | Alice   | alice@devops.lab  | SRE    | 2026-01-15  |
+----+---------+-------------------+--------+-------------+

-- Vertical mode
SELECT * FROM clients WHERE id = 1\G

*************************** 1. row ***************************
         id: 1
        nom: Alice
      email: alice@devops.lab
     equipe: SRE
inscription: 2026-01-15

The \G replaces the ; at the end of the query, do not put both.

Pager and tee: output to a file

To page long results (like less in the shell):

pager less -S;
SHOW GLOBAL STATUS;
nopager;

To capture the whole session in a file (queries + results):

tee /tmp/session-mysql.log
-- your queries here...
notee

The /tmp/session-mysql.log file will contain all of your commands and their results, useful for documenting a diagnostic session.

Command-line formatting options

These options apply to the whole session and can be combined. The -B -N pair is the one for scripts: it produces raw values separated by tabs, directly usable by cut or awk.

Option	Effect
-t or --table	Forces table mode (default in interactive)
-E or --vertical	Forces vertical mode for all queries
-N or --skip-column-names	Removes column headers
-B or --batch	Batch mode (no table, tab-separated values)
--xml	XML output
--html	HTML output

Swipe to see more

Running SQL

The client works in two different regimes. In interactive mode, it waits for a terminator (; or \G) and lets you write a query over several lines. In batch mode, it reads its standard input or the argument of -e, executes and returns control, which makes it the basic building block of administration scripts and scheduled jobs.

Interactive queries

Type your SQL followed by ; or \G:

SELECT nom, equipe, inscription FROM clients ORDER BY inscription;

+---------+----------+-------------+
| nom     | equipe   | inscription |
+---------+----------+-------------+
| Alice   | SRE      | 2026-01-15  |
| Bob     | Backend  | 2026-02-20  |
| Claire  | Frontend | 2026-03-10  |
| David   | SRE      | 2026-03-28  |
| Eva     | DevOps   | 2026-04-01  |
+---------+----------+-------------+

Multi-line queries are supported, MySQL waits for the final ;:

SELECT equipe, COUNT(*) AS nb_clients
FROM clients
GROUP BY equipe
ORDER BY nb_clients DESC, equipe;

+----------+------------+
| equipe   | nb_clients |
+----------+------------+
| SRE      |          2 |
| Backend  |          1 |
| DevOps   |          1 |
| Frontend |          1 |
+----------+------------+

Batch mode: -e and redirection

To run a query from the shell (scripts, cron, CI/CD):

mysql -u root -p labdb -e "SELECT nom, equipe FROM clients;"

+---------+----------+
| nom     | equipe   |
+---------+----------+
| Alice   | SRE      |
| Bob     | Backend  |
| Claire  | Frontend |
| David   | SRE      |
| Eva     | DevOps   |
+---------+----------+

For a result without a table (usable by a script):

mysql -u root -p labdb -BNe "SELECT COUNT(*) FROM clients;"

5

Option	Effect
-B	Batch mode (tab separator, no borders)
-N	No column headers
-e "..."	Run the query and quit

Swipe to see more

To redirect a whole SQL file:

mysql -u root -p labdb < /tmp/rapport.sql

In batch mode, the client stops at the first error: it prints the message with the number of the faulty line (ERROR ... at line 4) and does not run the following statements. The --force (-f) option makes it continue despite errors. Reserve it for exploration, because it hides failures in a migration script.

source: running an SQL file from the client

From the mysql> prompt:

source /tmp/mon-script.sql

Or with the shortcut:

\. /tmp/mon-script.sql

This is the MySQL equivalent of \i in psql. The file is read and executed line by line, and as in batch mode execution stops at the first statement in error: the client names the file and the line (ERROR ... at line 4 in file: '/tmp/mon-script.sql'). Measured on MySQL 8.4.11: a file whose second statement is faulty runs the first one, then stops with ERROR 1064 (42000) at line 2 in file: '/tmp/mon-script.sql' without running the third, with mysql -e "source ...".

Creating and managing objects

Creating a database, populating it, going back: these daily operations hold a few surprises for anyone coming from PostgreSQL. The main one is MySQL's non-transactional DDL, which implicitly commits everything that precedes it. This section also explains the autocommit mode, active by default, which decides whether your changes are saved immediately or not.

Creating a database and tables

Two details are worth writing explicitly in any DDL: the utf8mb4 character set, the only one that covers all of Unicode (emojis included), and the InnoDB storage engine, which brings transactions and foreign keys.

CREATE DATABASE mon_projet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mon_projet;

CREATE TABLE utilisateurs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  login VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(200) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

Always specify ENGINE=InnoDB

InnoDB has been the default engine since MySQL 5.5, but stating it explicitly in the DDL documents your intent and preserves compatibility if someone changes the default engine in the configuration.

Transactions: BEGIN, COMMIT, ROLLBACK

MySQL with InnoDB supports ACID transactions: a group of statements that succeeds or fails as a whole (atomicity, consistency, isolation, durability).

The previous section left the session in the mon_projet database, which does not contain clients: first go back to the sample database with USE labdb;.

USE labdb;
BEGIN;
INSERT INTO clients (nom, email, equipe) VALUES ('Test Rollback', 'test@lab.dev', 'QA');
SELECT COUNT(*) AS total FROM clients;

+-------+
| total |
+-------+
|     6 |
+-------+

The table held five customers: the insertion is visible within the transaction, which sees six. Let's cancel it:

ROLLBACK;
SELECT COUNT(*) AS total FROM clients;

+-------+
| total |
+-------+
|     5 |
+-------+

You find the five customers you started with. A trace of the cancelled row remains, though: the AUTO_INCREMENT counter has advanced. A new SHOW CREATE TABLE clients displays AUTO_INCREMENT=7 instead of 6, and the next customer inserted will receive identifier 7. InnoDB does not give back the numbers consumed by a rolled-back transaction, which explains identifiers 7 and 8 in the import section.

DDL and transactions in MySQL

Many MySQL DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE) cause an implicit COMMIT: if you are in a transaction and run a DROP TABLE, all the previous changes are committed automatically.

MySQL 8.4 supports atomic DDL for many InnoDB operations (a multi-table DROP TABLE is entirely rolled back if it fails midway), but that does not mean the DDL is transactional as in PostgreSQL.

autocommit: understanding the default behavior

By default, MySQL runs each query in its own transaction, this is the autocommit mode:

SELECT @@autocommit;

+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

Each INSERT, UPDATE or DELETE is committed immediately: a ROLLBACK issued right afterwards therefore has nothing to cancel. To group operations in a transaction, use BEGIN / START TRANSACTION:

START TRANSACTION;
-- your changes here...
COMMIT;     -- or ROLLBACK;

Dropping objects

DROP removes an object:

DROP TABLE IF EXISTS mon_projet.utilisateurs;
DROP DATABASE IF EXISTS mon_projet;

IF EXISTS avoids an error if the object does not exist, recommended in scripts.

No automatic CASCADE on DROP TABLE

In PostgreSQL, DROP TABLE ... CASCADE also removes the constraints that depend on the table. MySQL accepts the CASCADE keyword but without effect: as long as another table carries a foreign key to the targeted table, the DROP fails. SET FOREIGN_KEY_CHECKS = 0; lets you force it, but the constraint then remains declared in the child table and points to a missing table. Only disable FOREIGN_KEY_CHECKS in controlled migration scripts, and re-enable it afterwards.

Importing and exporting data

Four tools share the task, and the first criterion for choosing is the location of the file: on the server or on your workstation. The second constraint is the secure_file_priv variable, which restricts the allowed directories and flatly blocks these operations when it is NULL. Check it before looking for why an import fails.

LOAD DATA INFILE: import from a file on the server

LOAD DATA INFILE is the fastest method for loading bulk data. The file must be on the server, and in the directory designated by secure_file_priv. Read it first:

SHOW VARIABLES LIKE 'secure_file_priv';

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

A file placed elsewhere, in /tmp for example, is refused:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

So create the sample file in /var/lib/mysql-files/, readable by the mysql user. It contains a header and two new customers:

printf '"nom","email","equipe"\n"Fanny","fanny@devops.lab","SRE"\n"Gaston","gaston@devops.lab","Backend"\n' > clients.csv
sudo install -o mysql -g mysql -m 640 clients.csv /var/lib/mysql-files/clients.csv

The load then targets this file. IGNORE 1 ROWS skips the header line, and the two customers receive identifiers 7 and 8 (6 was consumed by the ROLLBACK of the transactions section):

LOAD DATA INFILE '/var/lib/mysql-files/clients.csv'
INTO TABLE clients
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(nom, email, equipe);

LOAD DATA LOCAL INFILE

LOAD DATA LOCAL INFILE reads the file from the client (your machine). It is more convenient but must be allowed on the server side (local_infile=ON) and on the client side (--local-infile=1). For bulk imports in production, prefer LOAD DATA INFILE (without LOCAL), the data does not travel over the network.

SELECT INTO OUTFILE: export to a file on the server

This is the symmetric operation of LOAD DATA INFILE. The destination file must not exist: MySQL refuses to overwrite a file, which avoids erasing a previous export but forces you to clean up before rerunning the command.

SELECT nom, email, equipe
FROM clients
INTO OUTFILE '/var/lib/mysql-files/export_clients.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The file is created on the server with the permissions of the MySQL user (mysql), and only in the secure_file_priv directory: the same command aimed at /tmp returns the error 1290 seen above. Rerun as is, it fails too, since the file exists: ERROR 1086 (HY000): File '/var/lib/mysql-files/export_clients.csv' already exists.

To add a CSV header, combine with UNION. The INTO OUTFILE clause goes last, after the FROM of the last SELECT:

SELECT 'nom', 'email', 'equipe'
UNION ALL
SELECT nom, email, equipe
FROM clients
INTO OUTFILE '/var/lib/mysql-files/export_clients_header.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Measured on 8.4.11, the old writing, with INTO OUTFILE placed before the FROM of the second SELECT, is still accepted, but it raises warning 3962: The INTO clause is deprecated inside query blocks of query expressions and will be removed in a future release. Please move the INTO clause to the end of statement instead. Placing INTO last avoids the warning and survives its removal. The resulting file starts with "nom","email","equipe".

secure_file_priv and LOCAL INFILE

LOAD DATA INFILE and SELECT ... INTO OUTFILE are subject to secure_file_priv:

• if the variable contains a directory (measured on 8.4.11: /var/lib/mysql-files/), the files must be located there, otherwise the error 1290 above appears;
• if it is empty, there is no restriction;
• if it is NULL, file import/export operations are disabled.

The query that reads it is the one in the LOAD DATA INFILE section above. For LOAD DATA LOCAL INFILE, both the client and the server must in addition allow LOCAL (local_infile=ON on the server + --local-infile=1 on the client).

mysqlimport: command-line import

mysqlimport is a shell wrapper around LOAD DATA INFILE. The file name must match the table name. Without --local, the file is read by the server and therefore obeys secure_file_priv, like LOAD DATA INFILE:

mysqlimport -u root -p --ignore --columns=nom,email,equipe \
  --fields-terminated-by=',' --fields-enclosed-by='"' \
  --lines-terminated-by='\n' --ignore-lines=1 \
  labdb /var/lib/mysql-files/clients.csv

mysqlimport strips the file extension and uses the remaining name as the table name. Here, /var/lib/mysql-files/clients.csv → clients table of the labdb database.

Since the file was already loaded by LOAD DATA, the --ignore option makes it skip the two duplicate rows instead of stopping on the uniqueness constraint of email. Replayed on MySQL 8.4.13 (Debian 13, Oracle packages), the report is explicit and the command returns 0:

labdb.clients: Records: 2  Deleted: 0  Skipped: 2  Warnings: 2

Skipped: 2 is the measurement that matters: the two rows were read then discarded, and the table stays at seven rows. Without --ignore, the same command fails on the first collision, naming the offending value and the key, and returns 1:

mysqlimport: Error: 1062, Duplicate entry 'fanny@devops.lab' for key 'clients.email', when using table: clients

With --local, the file is read on the client side and local_infile=ON is needed on the server: without it, the server answers ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides (measured with LOAD DATA LOCAL INFILE, on which --local relies).

mysqldump: full SQL export

To export a whole database (structure + data) as SQL:

mysqldump -u root -p labdb > /tmp/labdb_backup.sql

To export only the structure (without the data):

mysqldump -u root -p --no-data labdb > /tmp/labdb_schema.sql

To export a single table:

mysqldump -u root -p labdb clients > /tmp/clients_backup.sql

The Backup and restore guide explores all the options of mysqldump in detail, then the alternatives for large databases: MySQL Shell, point-in-time recovery (PITR) through the binary log (the journal of changes) and XtraBackup.

MySQL Shell (mysqlsh): the modern alternative

MySQL Shell is the next-generation client provided by Oracle. It brings features that the classic mysql client lacks:

Feature	mysql (classic)	mysqlsh
SQL mode	Yes	Yes
JavaScript mode	No	Yes
Python mode	No	Yes
Advanced completion	Basic	Yes (tables, columns, functions)
Parallel export	No	util.dumpInstance() multi-thread
Parallel import	No	util.loadDump() multi-thread
AdminAPI (InnoDB Cluster)	No	Yes
X Protocol	No	Yes (port 33060)

Swipe to see more

To install it:

sudo apt install mysql-shell    # Debian/Ubuntu
sudo dnf install mysql-shell    # RHEL/Rocky

Connecting in SQL mode:

mysqlsh --sql -u root -p -h localhost

When to use mysqlsh?

For daily administration (SHOW, SELECT, INSERT), the classic mysql client is more than enough. MySQL Shell becomes indispensable for parallel dump/restore (much faster than mysqldump on large databases), InnoDB Cluster administration and automation scripts in Python/JavaScript.

The util.dumpInstance(), util.loadDump() utilities, etc. are only available in JavaScript and Python modes, not in SQL mode.

Common administration queries

Here are the queries you will type most often in operations: reading the server's effective configuration, seeing who is connected, and measuring the space used. They modify nothing, so you can run them in production.

Variables and status

Do not confuse the two notions: a variable is a configuration parameter, modifiable, whereas a status is an activity counter fed by the server. The GLOBAL keyword applies to the whole instance, without it you only get the values of your own session.

-- See a configuration variable
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- See a status counter
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- Several variables at once
SHOW VARIABLES WHERE Variable_name IN ('max_connections', 'wait_timeout', 'interactive_timeout');

These commands only read the effective configuration. To change it durably (InnoDB buffer pool, number of connections, binary log, slow query log), refer to the Configuration guide.

Active connections

SHOW PROCESSLIST is the first reflex when the server slows down. The Time column gives the duration in seconds of the current state, and Info the running query, truncated to 100 characters in the short form.

SHOW PROCESSLIST;

+----+-----------------+-----------+-------+---------+------+------------------------+------------------+
| Id | User            | Host      | db    | Command | Time | State                  | Info             |
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL  | Daemon  |  162 | Waiting on empty queue | NULL             |
| 47 | root            | localhost | labdb | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+

The first row is the event_scheduler, the thread of the server's event scheduler, which is waiting for work. The second is your own session, running SHOW PROCESSLIST. The identifiers and the elapsed time change from one instance to another.

For a fuller view (long queries, locks):

SHOW FULL PROCESSLIST\G

The performance_schema.threads table exposes the same connections with more columns, and is queried with ordinary SQL. It is only populated if the Performance Schema is active.

Disk space per database

This query adds up the data and indexes of each database from the catalog. The values are estimated for InnoDB tables and do not include freed space not returned to the system, so a gap with the real size of the data directory is normal.

SELECT table_schema AS 'Base',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Taille (Mo)',
       COUNT(*) AS 'Tables'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

Put it into practice

The lab gives you a running instance and asks you to drive it without ever typing a password: the credentials go into a login-path, the application's objects are created, the Performance Schema (the internal instrumentation seen above) is turned back on and MySQL Shell is installed. The validation requires a connection that really succeeds without a password and without a terminal, reads the catalog, and checks a startup variable that a SET does not change. It grades the usage rather than the presence of the file, because a login-path can exist with the wrong account or the wrong host and be of no use.

The Performance Schema is the tricky part: its performance_schema variable does not accept a SET, the server reads it at startup. It is therefore set in a configuration file (or with SET PERSIST_ONLY), then a restart takes it into account; the Configuration guide details these two routes. As long as it is OFF, the sys views stay silent.

The "without a terminal" is not a whim, and it cost two 1,800-second timeouts before it was understood: mysql_config_editor reads /dev/tty as soon as a controlling terminal exists, and it then waits for an input that nobody will give. Any automation that goes through ssh -tt, as Ansible does, provides one. The workaround is setsid, which runs the command in a new session, without a controlling terminal, and no documentation mentions it because the symptom looks like slowness.

Guided lab Practice

Learn how to do it

Drive MySQL from the client, without a password on the command line

$ dsoxlab run mysql-02-client-admin

Environment: VM·Duration: ~35 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start mysql-02-client-admin checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run mysql-02-client-admin
dsoxlab check mysql-02-client-admin

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Troubleshooting

The error messages below are the ones you will meet most often with the client. Remember the logic of the numbers: 1xxx and 3xxx errors come from the server (rights, missing object, disabled function), 2xxx errors come from the client, which failed to establish the connection.

Symptom	Likely cause	Fix
ERROR 1045 (28000): Access denied for user 'root'@'localhost'	Incorrect password	Check the password or reset it (see the Installation guide)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket	MySQL service not started or socket missing	sudo systemctl start mysql
ERROR 1045 (28000): Access denied for user 'labadmin'@'127.0.0.1' (or the client's IP address)	TCP connection, whereas the account only exists for localhost	Remove --host / host= to go back through the socket, or create the account for the desired host ("Network connection" section)
ERROR 2003 (HY000): Can't connect to MySQL server on 'host'	Network connection refused	Check bind_address (the Installation guide recommends restricting it to 127.0.0.1) and the firewall
ERROR 1049 (42000): Unknown database 'xxx'	The database does not exist	SHOW DATABASES; to list the available databases
ERROR 3948 (42000): Loading local data is disabled	local_infile disabled	SET GLOBAL local_infile = ON; + reconnect with --local-infile=1
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option	The file is outside the directory designated by secure_file_priv (for example in /tmp)	SHOW VARIABLES LIKE 'secure_file_priv'; then place the file in that directory (/var/lib/mysql-files/ in our measurements)
ERROR 1086 (HY000): File '...' already exists	INTO OUTFILE refuses to overwrite an existing file	Delete or rename the export file, then rerun
The prompt shows -> instead of mysql>	Unterminated query (missing ;)	Type ; to finish or \c to cancel

Swipe to see more

Key takeaways

• The mysql client is the daily administration tool, the SHOW commands and INFORMATION_SCHEMA replace psql's meta-commands.
• The local connection goes through a Unix socket, no need for a network or, on some configurations, a password.
• A MySQL account is defined by user and host: 'labadmin'@'localhost' is not 'labadmin'@'127.0.0.1'. A TCP connection requires an account created for the host it comes from, a server that listens on the network and an open port.
• mysql_config_editor stores credentials in ~/.mylogin.cnf, reversibly encrypted: prefer it to a clear-text password in ~/.my.cnf, and keep its permissions strict.
• The vertical mode \G replaces the ; and makes wide results readable.
• Batch mode (-BNe) produces output usable by shell scripts, indispensable for automation.
• In batch mode as with source, the client stops at the first error; --force makes it continue, but hides failures.
• MySQL DDL causes an implicit COMMIT, no ROLLBACK is possible after a CREATE TABLE or a DROP TABLE. MySQL 8.4 supports atomic DDL (a failed DDL is entirely rolled back), but it is not transactional DDL in the PostgreSQL sense.
• LOAD DATA INFILE is the fastest method for bulk imports, but it is subject to secure_file_priv, to be checked first when an import fails.
• The sys database offers simplified views on top of performance_schema, use it first for diagnosis.

Knowledge check

Eight questions on an account's host, where @localhost only designates the socket, credentials stored off the command line, autocommit making a ROLLBACK pointless, and batch mode that stops at the first error, except with --force.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →