# mysql — The MySQL/MariaDB Command-Line Client

> Practical guide to the mysql client: connect, query, import/export, manage users and administer MySQL and MariaDB servers from the terminal.

Source: https://www.jpkc.com/db/en/cheatsheets/build-languages/mysql/

<!-- PROSE:intro -->
`mysql` is the classic command-line client for MySQL and the compatible MariaDB – you use it to connect to the database server and run SQL queries interactively at a prompt. It works just as well non-interactively: with `-e '<query>'` you execute single statements, and through pipes and redirection (`<`, `|`) you feed whole SQL files and dumps straight in – perfect for scripts, backups and cron jobs. This guide walks you through the essentials, from connecting and managing databases and users to import, export and server monitoring.
<!-- PROSE:intro:end -->

## Connect

`mysql -u <user> -p` — Connect with username and password prompt.

```bash
mysql -u root -p
```

`mysql -u <user> -p <database>` — Connect directly to a specific database.

```bash
mysql -u admin -p myapp_db
```

`mysql -h <host> -P <port> -u <user> -p` — Connect to a remote server on a specific port.

```bash
mysql -h db.example.com -P 3306 -u admin -p
```

`mysql -u <user> -p -e '<query>'` — Execute a query and exit.

```bash
mysql -u root -p -e 'SHOW DATABASES;'
```

`mysql -u <user> -p --socket=<path>` — Connect via Unix socket.

```bash
mysql -u root -p --socket=/var/run/mysqld/mysqld.sock
```

## Database & Table Operations

`SHOW DATABASES;` — List all databases.

```bash
SHOW DATABASES;
```

`USE <database>;` — Switch to a database.

```bash
USE myapp_db;
```

`SHOW TABLES;` — List all tables in the current database.

```bash
SHOW TABLES;
```

`DESCRIBE <table>;` — Show table structure (columns, types, keys).

```bash
DESCRIBE users;
```

`SHOW CREATE TABLE <table>;` — Show the CREATE TABLE statement.

```bash
SHOW CREATE TABLE users\G
```

`CREATE DATABASE <name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` — Create a database with UTF-8 support.

```bash
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

`DROP DATABASE <name>;` — Delete a database.

```bash
DROP DATABASE old_db;
```

## User Management

`CREATE USER '<user>'@'<host>' IDENTIFIED BY '<password>';` — Create a new user.

```bash
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'secretpass';
```

`GRANT ALL PRIVILEGES ON <db>.* TO '<user>'@'<host>';` — Grant all privileges on a database.

```bash
GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'localhost';
```

`GRANT SELECT, INSERT, UPDATE ON <db>.* TO '<user>'@'<host>';` — Grant specific privileges.

```bash
GRANT SELECT, INSERT, UPDATE ON myapp_db.* TO 'readonly'@'%';
```

`FLUSH PRIVILEGES;` — Reload privilege tables.

```bash
FLUSH PRIVILEGES;
```

`SHOW GRANTS FOR '<user>'@'<host>';` — Show privileges for a user.

```bash
SHOW GRANTS FOR 'appuser'@'localhost';
```

`DROP USER '<user>'@'<host>';` — Delete a user.

```bash
DROP USER 'olduser'@'localhost';
```

## Import & Export

`mysqldump -u <user> -p <database> > <file>` — Export a database to a SQL file.

```bash
mysqldump -u root -p myapp_db > backup.sql
```

`mysqldump -u <user> -p --all-databases > <file>` — Export all databases.

```bash
mysqldump -u root -p --all-databases > all-dbs.sql
```

`mysqldump -u <user> -p <database> <table> > <file>` — Export a single table.

```bash
mysqldump -u root -p myapp_db users > users.sql
```

`mysql -u <user> -p <database> < <file>` — Import a SQL file into a database.

```bash
mysql -u root -p myapp_db < backup.sql
```

`mysqldump -u <user> -p --single-transaction <database> > <file>` — Export with consistent snapshot (InnoDB, no lock).

```bash
mysqldump -u root -p --single-transaction myapp_db > backup.sql
```

## Status & Monitoring

`SHOW PROCESSLIST;` — Show currently running queries and connections.

```bash
SHOW PROCESSLIST;
```

`SHOW STATUS;` — Show server status variables.

```bash
SHOW GLOBAL STATUS LIKE 'Threads%';
```

`SHOW VARIABLES LIKE '<pattern>';` — Show server configuration variables.

```bash
SHOW VARIABLES LIKE 'max_connections';
```

`SHOW TABLE STATUS;` — Show info about tables (engine, rows, size).

```bash
SHOW TABLE STATUS FROM myapp_db;
```

`SELECT version();` — Show the MySQL/MariaDB version.

```bash
SELECT version();
```

## Common Patterns

`mysql -u <user> -p -N -B -e '<query>'` — Execute query with no headers, tab-separated (for scripting).

```bash
mysql -u root -p -N -B -e 'SELECT id, name FROM myapp_db.users'
```

`mysqldump ... | gzip > <file>.gz` — Export and compress in one step.

```bash
mysqldump -u root -p myapp_db | gzip > backup-$(date +%F).sql.gz
```

`zcat <file>.gz | mysql -u <user> -p <database>` — Import a gzipped SQL dump.

```bash
zcat backup.sql.gz | mysql -u root -p myapp_db
```

`KILL <process_id>;` — Kill a running query by its process ID.

```bash
KILL 1234;
```

<!-- PROSE:outro -->
## Conclusion

The `mysql` client has been the Swiss Army knife for MySQL and MariaDB for years: interactive for quick queries, scripted for backups and migrations. Watch out for password safety – `-p<password>` typed directly on the command line lands in your shell history and is visible in the process list. Enter the password interactively at the `-p` prompt instead, or store it in a `~/.my.cnf` file with `chmod 600`; the same applies to `mysqldump` calls in cron jobs. And remember: `DROP DATABASE`, `DROP TABLE`, `TRUNCATE` and a `DELETE` without a `WHERE` clause are irreversible – a current backup is your best safety net.

## Further Reading

- [MySQL Reference Manual](https://dev.mysql.com/doc/) – official documentation for client and server
- [MariaDB Knowledge Base](https://mariadb.com/kb/en/) – official MariaDB documentation
- [MySQL on Wikipedia](https://en.wikipedia.org/wiki/MySQL) – background, history and context
<!-- PROSE:outro:end -->

## Related Commands

- [artisan](https://www.jpkc.com/db/en/cheatsheets/build-languages/artisan/) – Laravel's CLI for migrations, tinker and maintenance
- [cargo](https://www.jpkc.com/db/en/cheatsheets/build-languages/cargo/) – build and package manager for Rust
- [composer](https://www.jpkc.com/db/en/cheatsheets/build-languages/composer/) – dependency manager for PHP projects