MySQL CLI Commands

Essential CLI Commands

mysql -u username -p
mysql -h hostname -u username -p

exit;
quit;
\q

help;
\h
help contents;

status;
\s

🔹 Connecting to MySQL

Establish connections to MySQL servers using various authentication methods. Connect locally or remotely by specifying hostname, username, password, and optional port numbers. Use different flags to customize your connection settings and security options.

# Basic connection
mysql -u root -p

# Connect to specific host
mysql -h localhost -u root -p

# Connect to specific database
mysql -u root -p database_name

# Connect with port
mysql -h localhost -P 3306 -u root -p

# Connect without password prompt (not recommended)
mysql -u root -pYourPassword

🔹 Database Commands

Manage databases directly from the command line. Create new databases, list all available databases, switch between databases, and remove databases. These commands provide complete control over your database environment through simple text commands.

-- Show all databases
SHOW DATABASES;

-- Create database
CREATE DATABASE my_app;

-- Select database
USE my_app;

-- Show current database
SELECT DATABASE();

-- Drop database
DROP DATABASE my_app;

🔹 Table Management

View and manage table structures using CLI commands. Display all tables in the current database, examine detailed table structures including column types and constraints, and view the exact SQL used to create tables for reference or replication.

-- Show all tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;
DESC users;

-- Show create table statement
SHOW CREATE TABLE users;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

🔹 Executing SQL Files

Run SQL scripts from external files to automate database operations. Execute files from outside MySQL or from within the MySQL prompt. This is useful for running migrations, importing data, or executing complex multi-statement scripts efficiently.

# Execute SQL file from command line
mysql -u root -p database_name < script.sql

# Execute from MySQL prompt
mysql> source /path/to/script.sql;
mysql> \. /path/to/script.sql;

# Execute with output to file
mysql -u root -p database_name < input.sql > output.txt

🔹 User Management

Create and manage MySQL user accounts with specific privileges. View existing users, create new accounts with passwords, grant permissions on databases and tables, and revoke access when needed. Essential for database security and access control.

-- Show all users
SELECT User, Host FROM mysql.user;

-- Create new user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- Grant privileges
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

-- Show user privileges
SHOW GRANTS FOR 'newuser'@'localhost';

-- Revoke privileges
REVOKE ALL PRIVILEGES ON database_name.* FROM 'newuser'@'localhost';

-- Delete user
DROP USER 'newuser'@'localhost';

-- Reload privileges
FLUSH PRIVILEGES;

🔹 Import and Export

Transfer data between MySQL and external files using mysqldump and mysql commands. Export entire databases or specific tables to SQL files for backups, and import data from SQL files to restore or migrate databases between servers.

# Export database
mysqldump -u root -p database_name > backup.sql

# Export specific table
mysqldump -u root -p database_name table_name > table_backup.sql

# Export all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Import database
mysql -u root -p database_name < backup.sql

# Import with create database
mysql -u root -p < backup.sql

🔹 Useful CLI Shortcuts

Speed up your workflow with MySQL CLI shortcuts and special commands. Clear the screen, edit commands, view warnings, and control output formatting. These shortcuts make working in the command line more efficient and user-friendly for daily database tasks.

# Clear screen
\! clear    # Linux/Mac
\! cls      # Windows

# Show warnings
SHOW WARNINGS;

# Show errors
SHOW ERRORS;

# Edit command in editor
\e

# Display results vertically
SELECT * FROM users\G

# Disable pager
\n

# Enable pager
\P less