MySQL Commands Cheat Sheet in Linux Server

MySQL Commands Cheat Sheet

Installation

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation
        

Start MySQL Service

sudo systemctl start mysql
        

Enable MySQL Service on Boot

sudo systemctl enable mysql
        

Log in to MySQL

mysql -u root -p
        

Show Databases

SHOW DATABASES;
        

Create a Database

CREATE DATABASE dbname;
        

Use a Database

USE dbname;
        

Show Tables in a Database

SHOW TABLES;
        

Create a Table

CREATE TABLE tablename (
 id INT AUTO_INCREMENT PRIMARY KEY,
 column1 VARCHAR(255) NOT NULL,
 column2 INT NOT NULL
);
        

Show Table Schema

DESCRIBE tablename;
        

Insert Data into a Table

INSERT INTO tablename (column1, column2) VALUES ('value1', 10);
        

Select Data from a Table

SELECT * FROM tablename;
        

Update Data in a Table

UPDATE tablename SET column1 = 'newvalue' WHERE id = 1;
        

Delete Data from a Table

DELETE FROM tablename WHERE id = 1;
        

Administrative Commands

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

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
        

SHOW GRANTS FOR 'username'@'localhost';
        

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';
        

DROP USER 'username'@'localhost';
        

Backup and Restore

mysqldump -u root -p dbname > dbname_backup.sql
        

mysql -u root -p dbname < dbname_backup.sql
        

Performance and Maintenance

SHOW STATUS;
        

SHOW PROCESSLIST;
        

OPTIMIZE TABLE tablename;
        

CHECK TABLE tablename;
REPAIR TABLE tablename;
        

Security Commands

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
        

SELECT user, host FROM mysql.user;
        

EXIT;
        

Additional Useful Commands

SELECT NOW();
        

SELECT VERSION();