mysql — The MySQL/MariaDB Command-Line Client

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.

Connect

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

mysql -u root -p

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

mysql -u admin -p myapp_db

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

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

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

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

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

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

Database & Table Operations

SHOW DATABASES; — List all databases.

SHOW DATABASES;

USE <database>; — Switch to a database.

USE myapp_db;

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

SHOW TABLES;

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

DESCRIBE users;

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

SHOW CREATE TABLE users\G

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

CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP DATABASE <name>; — Delete a database.

DROP DATABASE old_db;

User Management

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

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

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

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

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

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

FLUSH PRIVILEGES; — Reload privilege tables.

FLUSH PRIVILEGES;

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

SHOW GRANTS FOR 'appuser'@'localhost';

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

DROP USER 'olduser'@'localhost';

Import & Export

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

mysqldump -u root -p myapp_db > backup.sql

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

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

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

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

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

mysql -u root -p myapp_db < backup.sql

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

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

Status & Monitoring

SHOW PROCESSLIST; — Show currently running queries and connections.

SHOW PROCESSLIST;

SHOW STATUS; — Show server status variables.

SHOW GLOBAL STATUS LIKE 'Threads%';

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

SHOW VARIABLES LIKE 'max_connections';

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

SHOW TABLE STATUS FROM myapp_db;

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

SELECT version();

Common Patterns

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

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

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

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.

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

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

KILL 1234;

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.