Database
MySQL CLI
MySQL client commands for database management.
34 commands
Windows
MacOS
Linux
#mysql
#sql
Connection
Connect as root with password prompt
mysql -u root -p
Connect to a specific host and database
mysql -h 127.0.0.1 -P 3306 -u myuser -p mydb
Execute a command and exit
mysql -u root -p -e 'SHOW DATABASES'
Connect using a credentials file
mysql --defaults-file=~/.my.cnf
Connect with SSL enforced
mysql -u root -p --ssl-mode=REQUIRED
Database Operations
List all databases on the server
SHOW DATABASES;
Create a database with UTF-8 support
CREATE DATABASE mydb CHARACTER SET utf8mb4;
Delete a database permanently
DROP DATABASE mydb;
Switch to a specific database
USE mydb;
Show the CREATE statement for a database
SHOW CREATE DATABASE mydb;
Table Operations
List all tables in current database
SHOW TABLES;
Show column details for a table
DESCRIBE users;
Show the full CREATE TABLE statement
SHOW CREATE TABLE users;
Display table metadata and row counts
SHOW TABLE STATUS;
List all indexes on a table
SHOW INDEX FROM users;
Add a new column to a table
ALTER TABLE users ADD COLUMN email VARCHAR(255);
Query Execution
Execute SQL from a file
mysql -u root -p mydb < script.sql
Run a query from the command line
mysql -u root -p -e 'SELECT * FROM users'
Output without headers or borders
mysql -u root -p -N -B -e 'SELECT id FROM users'
Output query results as HTML
mysql -u root -p --html -e 'SELECT * FROM users'
Execute a SQL file from within mysql
SOURCE /path/to/script.sql;
Import/Export
Dump a database to SQL file
mysqldump -u root -p mydb > backup.sql
Dump a single table
mysqldump -u root -p mydb users > users.sql
Restore a database from SQL dump
mysql -u root -p mydb < backup.sql
Dump all databases
mysqldump -u root -p --all-databases > all_backup.sql
Export query results to TSV
mysql -u root -p -e 'SELECT * FROM users' mydb > out.tsv
User Management
Create a new local user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'pass';
Grant full privileges on a database
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
Show grants for a user
SHOW GRANTS FOR 'myuser'@'localhost';
Reload privilege tables
FLUSH PRIVILEGES;
Delete a user account
DROP USER 'myuser'@'localhost';
Quick Commands
Connect to a MySQL database as root
mysql -u root -p mydb
Export a database to a SQL backup file
mysqldump -u root -p mydb > backup.sql
List all tables in the current database
SHOW TABLES;