Complete MySQL Commands Guide from Linux Terminal

While working on cloud server where PHPMyAdmin is not installed then using Linux terminal is the most efficient way to manage MySQL database and respective tables.

These are the all essential MySQL commands that can be executed from any Linux environment.

1. Login to MySQL Server

Login using root user:

mysql -u root -p

Login with host:

mysql -u username -p -h localhost

Login with port:

mysql -u username -p -P 3306

2. Database Management Commands

Show all databases

SHOW DATABASES;

Create a new database

CREATE DATABASE db_name;

Create database with charset

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Select a database

USE db_name;

Delete a database

DROP DATABASE db_name;

3. Table Management Commands

Show tables

SHOW TABLES;

Create table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Describe table structure

DESCRIBE users;

Show create table

SHOW CREATE TABLE users;

Rename table

RENAME TABLE users TO customers;

Delete table

DROP TABLE users;

4. Data Operations (CRUD)

Insert data

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

Insert multiple rows

INSERT INTO users (name, email)
VALUES 
('User1', 'user1@mail.com'),
('User2', 'user2@mail.com');

Select all records

SELECT * FROM users;

Select specific columns

SELECT name, email FROM users;

Update data

UPDATE users SET name='Jane Doe' WHERE id=1;

Delete data

DELETE FROM users WHERE id=1;

5. Filtering & Sorting

Where condition

SELECT * FROM users WHERE id=1;

LIKE search

SELECT * FROM users WHERE name LIKE '%John%';

Order by

SELECT * FROM users ORDER BY id DESC;

Limit results

SELECT * FROM users LIMIT 10;

6. User & Permission Management

Create user

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Grant privileges

GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';

Revoke privileges

REVOKE ALL PRIVILEGES ON db_name.* FROM 'user'@'localhost';

Apply changes

FLUSH PRIVILEGES;

Delete user

DROP USER 'user'@'localhost';

7. Backup & Restore

Backup database

mysqldump -u root -p db_name > backup.sql

Backup all databases

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

Restore database

mysql -u root -p db_name < backup.sql

8. Import & Export Operations

Import SQL file

mysql -u root -p db_name < file.sql

Export specific table

mysqldump -u root -p db_name table_name > table.sql

9. Run MySQL Commands Directly from Terminal

mysql -u root -p db_name -e "SELECT * FROM users;"

10. MySQL Service Commands (Linux)

Check status

systemctl status mysql

Start MySQL

sudo systemctl start mysql

Stop MySQL

sudo systemctl stop mysql

Restart MySQL

sudo systemctl restart mysql

11. Performance & Monitoring

Show running processes

SHOW PROCESSLIST;

Show variables

SHOW VARIABLES;

Show status

SHOW STATUS;

12. Useful Tips

• Always use WHERE in UPDATE and DELETE queries
• Take regular backups before major changes
• Use strong passwords for database users
• Monitor slow queries for performance tuning

Conclusion

Mastering MySQL commands from the Linux terminal gives you full control over database operations, improves efficiency, and helps in faster troubleshooting. Whether you’re managing databases, users, or backups, these commands are essential for every developer and system administrator.