MySQL CLI

MySQL CLI cheatsheet — connect, query, manage databases and users. mysql -u root -p, SHOW TABLES, mysqldump, GRANT privileges. Full MySQL command-line reference.

6 min read

What it is

The mysql command-line client is the primary interactive interface for interacting with MySQL and MariaDB databases, allowing you to execute SQL queries, manage databases, and perform administrative tasks.

Installation

Linux (Debian/Ubuntu)

sudo apt update
sudo apt install mysql-client

Linux (Fedora/CentOS/RHEL)

sudo yum update
sudo yum install mysql-community-client

sudo dnf update
sudo dnf install mysql-community-client

macOS (Homebrew)

brew update
brew install mysql-client

Windows

Download the MySQL Installer from the official MySQL website: https://dev.mysql.com/downloads/installer/ During installation, select the "Client" component.

Core Concepts

Server: The MySQL database system that stores and manages your data.
Client: The mysql command-line tool you are using to connect to and interact with the server.
Database: A logical container for tables and other database objects.
Table: A collection of related data organized in rows and columns.
User: An account with specific privileges to access and manipulate databases.
SQL (Structured Query Language): The language used to communicate with the MySQL server.

Commands / Usage

Connecting to a Server

Connect to a local server with default credentials:
```
mysql
```
Connects to localhost with the current OS username. You’ll be prompted for the MySQL root password if authentication is required.
Connect specifying a user:
```
mysql -u root
```
Connects to localhost as the root user. You’ll be prompted for the root user’s password.
Connect specifying user and password (use with caution):
```
mysql -u myuser -p'mypassword'
```
Connects to localhost as myuser using the provided password. Note: Directly including the password on the command line is insecure and not recommended for production environments. It’s better to omit the password and be prompted.
Connect to a remote server:
```
mysql -h 192.168.1.100 -u adminuser -p
```
Connects to the server at 192.168.1.100 as adminuser. You’ll be prompted for the password.
Connect to a specific port:
```
mysql -h localhost -P 3307 -u myuser -p
```
Connects to localhost on port 3307 as myuser.
Connect to a specific database:
```
mysql -u myuser -p mydatabase
```
Connects to localhost as myuser and automatically selects mydatabase for use.

Executing SQL Queries

Execute a single SQL statement from the command line (non-interactive):
```
mysql -u myuser -p -e "SELECT COUNT(*) FROM users;"
```
Executes the SELECT statement and prints the result to standard output, then exits.
Execute SQL statements from a file:
```
mysql -u myuser -p < /path/to/my_queries.sql
```
Reads and executes all SQL statements from the specified file.
Execute SQL statements interactively:
```
mysql -u myuser -p
```
Opens an interactive MySQL prompt (e.g., mysql>). You can then type SQL statements, ending each with a semicolon (;).

Inside the Interactive MySQL Prompt (`mysql>`)

Show available databases:
```
SHOW DATABASES;
```
Select a database to use:
```
USE mydatabase;
```
Sets mydatabase as the current active database. Subsequent queries will operate on this database by default.
Show tables in the current database:
```
SHOW TABLES;
```
Show table structure (columns, types, indexes):
```
DESCRIBE users;
```
or
```
SHOW COLUMNS FROM users;
```

Execute any valid SQL query:

SELECT id, username FROM users WHERE status = 'active';

Exit the interactive prompt:
```
EXIT;
```
or
```
QUIT;
```
or press Ctrl+D.

Administrative Tasks

Create a new database:

mysql -u root -p -e "CREATE DATABASE new_app_db;"

Drop an existing database:
```
mysql -u root -p -e "DROP DATABASE old_database;"
```
Caution: This permanently deletes all data within the database.

Create a new user:

mysql -u root -p -e "CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strongpassword';"

Grant privileges to a user:
```
mysql -u root -p -e "GRANT ALL PRIVILEGES ON mydatabase.* TO 'appuser'@'localhost';"
```
Grants all permissions on all tables within mydatabase to appuser connecting from localhost.
Flush privileges (reload grant tables):
```
mysql -u root -p -e "FLUSH PRIVILEGES;"
```
Often needed after changing user privileges.

Output Formatting

Disable automatic pagination (useful for scripting):
```
mysql --no-pager -u myuser -p -e "SELECT * FROM large_table;"
```
Prevents output from being piped through a pager like less.
Set output to tab-separated (TSV):
```
mysql --batch -u myuser -p -e "SELECT id, name FROM products;"
```
Outputs results with tabs separating columns.

Set output to CSV:

mysql --csv -u myuser -p -e "SELECT id, name FROM products;"

Outputs results in CSV format.

Change default prompt:
```
mysql --prompt="MySQL [\\u@\\h] \\d> " -u myuser -p
```
Customizes the interactive prompt.

Other Useful Flags

--host or -h: Specifies the hostname or IP address of the MySQL server.
--port or -P: Specifies the TCP/IP port number.
--user or -u: Specifies the username for connecting.
--password or -p: Prompts for the password. If followed immediately by the password (e.g., -pmypassword), it’s used directly (insecure).
--database or -D: Specifies the default database to use upon connection.
--execute or -e: Executes a single SQL statement and exits.
--batch or -B: Produces tab-separated output.
--vertical or -E: Prints results vertically, one column per line. Useful for wide tables.
--skip-column-names or -N: Suppresses column names in the output.
--skip-pager or -P: Disables the pager.
--defaults-file=<file>: Use the specified option file.

Common Patterns

Exporting a table to CSV:
```
mysql -u myuser -p --batch mydatabase -e "SELECT * FROM mytable;" > mytable_export.tsv
```
(Note: --batch gives TSV. For true CSV, you might need to process this further or use specific SQL functions like INTO OUTFILE from within the SQL query itself.)
Importing data from a TSV file:
```
mysql -u myuser -p mydatabase < data.tsv
```
(Assumes data.tsv contains SQL INSERT statements or is formatted correctly for LOAD DATA INFILE if used within a SQL script.)
Running a script and logging output:
```
mysql -u myuser -p mydatabase < schema.sql > schema_output.log 2>&1
```
Executes schema.sql and redirects both standard output and standard error to schema_output.log.
Checking MySQL server status (if you have process privileges):
```
mysqladmin -u root -p status
```
mysqladmin is a separate utility often installed with mysql-client.
Executing a query and piping output to grep:
```
mysql -u myuser -p -e "SHOW PROCESSLIST;" | grep 'my_long_query'
```
Finds specific queries currently running on the server.
Backing up a database (using mysqldump):
```
mysqldump -u root -p mydatabase > mydatabase_backup_$(date +%Y%m%d).sql
```
mysqldump is a separate utility for creating database backups.

Gotchas

Password on the command line: -p'mypassword' is insecure because the password can be seen in the process list (ps aux). Always prefer to omit the password and be prompted.
CREATE USER syntax: The syntax for creating users and granting privileges has evolved. For newer MySQL versions, IDENTIFIED BY is standard. Older versions might use IDENTIFIED WITH mysql_native_password BY.
FLUSH PRIVILEGES necessity: While often needed after GRANT or REVOKE statements, it’s not always strictly required if the server configuration reloads grants automatically. However, it’s a good habit to include it to ensure changes take effect immediately.
Character sets and collation: If you encounter issues with data encoding (e.g., ? appearing instead of special characters), ensure your client connection, database, and table character sets are configured correctly (e.g., utf8mb4). You can set this during connection:
```
mysql --default-character-set=utf8mb4 -u myuser -p
```
Permissions and GRANT: If you can connect but get "Access denied" errors for specific operations, it’s almost always a permissions issue. Double-check the GRANT statements for the user you are connecting with. Remember that GRANT statements are specific to the host from which the user connects ('user'@'localhost' is different from 'user'@'%').
LOAD DATA LOCAL INFILE security: Importing data using LOAD DATA LOCAL INFILE requires both the client and server to be configured to allow it. If disabled, you’ll get an error.