Skip to main content

​
MySQL User Management

​
Create a User and Define Login Host

To create a new user with access from a specific host or IP address: 
CREATE USER 'sammy'@'localhost/ip-of-server' IDENTIFIED BY 'password';

​
Grant Permissions

​
Grant Specific Permissions

To grant specific permissions on a particular database and table: 
GRANT (PRIVILEGE) ON [database].[table] TO 'username'@'host/ip';
Example: 
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

​
Grant All Privileges

To grant all privileges on all databases and tables: 
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

​
Update Database Permissions

To refresh the privileges: 
FLUSH PRIVILEGES;

​
Revoke Permissions

To remove a specific permission from a user: 
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

​
Show User Permissions

To display the permissions granted to a user: 
SHOW GRANTS FOR 'username'@'host';

​
Delete a User

To delete a user from MySQL: 
DROP USER 'username'@'localhost';

​
MySQL Database Management

​
Basic Commands

​
Clear Console Screen

To clear the console screen in MySQL: 
mysql> system clear;

​
Create a Database

To create a new database: 
CREATE DATABASE [IF NOT EXISTS] database_name;

​
Change Database

To switch to a different database: 
USE database_name;

​
Drop/Delete Database

To delete an existing database: 
DROP DATABASE [IF EXISTS] database_name;

​
Show All Databases

To list all databases on the server: 
SHOW DATABASES;

​
Get Information About a Table

To describe the structure of a table: 
DESCRIBE animal;

​
Show All Tables

To list all tables in the current database: 
SHOW TABLES;

​
Create a New Table

To create a new table with specified columns: 
CREATE TABLE [IF NOT EXISTS] table_name (
  column_list
);

​
Rename a Table

To change the name of a table: 
ALTER TABLE animal RENAME pet;

​
Add a New Column

To add a new column to a table: 
ALTER TABLE table 
ADD [COLUMN] column_name;

​
Drop a Column

To remove a column from a table: 
ALTER TABLE table_name
DROP [COLUMN] column_name;

​
Drop a Table

To delete a table: 
DROP TABLE [IF EXISTS] table_name;

​
Show Columns of a Table

To list all columns in a table: 
DESCRIBE table_name;

​
Show Information About a Column

To get information about a specific column: 
DESCRIBE table_name column_name;

​
Delete All Rows in a Table

To delete all rows from a table: 
DELETE FROM table_name;

​
Export and Import Database

​
Export Database

To back up a database: 
mysqldump -u [username] -p [database_name] > data_backup.sql

​
Import Database

To restore a database from a backup: 
mysql -u [username] -p [database_name] < data_backup.sql

​
MySQL Server Installation

​
Install MySQL Server

To install MySQL server on a Linux system: 
sudo apt install mysql-server

​
Secure MySQL Installation

To secure the MySQL installation: 
sudo mysql_secure_installation

​
Access MySQL

To enter the MySQL shell as root: 
sudo mysql
To enter the MySQL shell with a specific user: 
mysql -u username -p

​
Enter a Specific Database

To select and use a specific database: 
mysql -u root -p
USE database_name;

​
Help and Commands

To access MySQL help: 
mysql> help;
This guide provides essential commands and steps for managing MySQL databases, including user management, database operations, and backup/restore procedures.
This documentation covers the necessary commands for managing MySQL users, databases, and backup/restore operations, as well as basic usage of MySQL and related tools.