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.