Before running any command we need to access MySQL Database for accessing we need some details like, username, hostname, password
# install client
sudo apt install mysql-client
# conect to Database
mysql -u username -p -h hostname
# it will prmpt for password , please enter
For showing and selecting DB
-- show all dbs in your MySQL server
SHOW DATABASES;
-- switch to any existing Database
use database_name
Create Delete Database;
-- create database
CREATE DATABASE katra;
-- Delete database
DROP DATABASE katra;
Create User , Other Options
-- from within localhost
CREATE USER 'usama'@'localhost' IDENTIFIED BY 'password';
-- from IP start with 10.0
CREATE USER 'user'@'20.0.%' IDENTIFIED BY ‘password’
-- from everywehre
CREATE USER 'user'@'%' IDENTIFIED BY ‘password’
-- show all users in mysql user
SELECT user,host FROM mysql.user;
Delete User
DROP USER 'username'@'localhost';
Grant permissions to a MySQL user
GRANT PRIVILEGE ON database.table TO 'username'@'host';
GRANT privilege ON privilege_level TO account_name;
for example type the following to grant SELECT and INSERT privileges to a usama on the katra database:
GRANT SELECT, INSERT ON katra.* TO ‘local_user’@’localhost’;
To create a user with the same privileges as the root user,
for example, grants global privileges to the user imran connecting from anywhere
GRANT ALL ON *.* TO 'imran'@'%' WITH GRANT OPTION;
The WITH GRANT OPTION clause allows users to grant their privileges to other users.
Revoke Permissions from an existing user
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';
REVOKE SELECT, INSERT ON strongdm.* FROM 'username'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'imran'@'localhost';
-- This command revokes all privileges, including the GRANT OPTION, from the user 'imran' connecting from 'localhost'. The GRANT OPTION allows the user to grant privileges to other users. So, by executing this command, you are revoking all privileges from 'imran' and also removing their ability to grant privileges to other users.
Show Grants for user
SHOW GRANTS FOR <username>@<host>;
SHOW GRANTS FOR test_user;
-- Without a hostname, the command checks for the default host '%'.
SHOW GRANTS;
Change MySQL user password
ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';
FLUSH PRIVILEGES;
Import
-- Syntax
mysql -u [username] -p [database name] < [file name].sql
-- Import example For example, to import the "mydb_backup.sql" file using the username app_db_user, and the database mydb you would use the following command: we can also define host option -h if you are using remote Database
mysql -u root -p mydb < mydb_backup.sql
--Note: For importing to Database please make sure the user you are using has access
Export
-- Syntax
-- After running below command it will prompt you for your Mysql user app_db_user password, also make sure this user have correct access
mysqldump -u [username] -p [database name] > [file name].sql
-- For example, to export the database katra using the username app__db_user, you would use the following command, mydb_backup.sql will be your backup file of database katra
mysqldump -u app__db_user -p katra > mydb_backup.sql
Bonus
SHOW PROCESSLIST: This command displays the currently executing queries and processes on the database server. It provides information about the connected users, the queries they are running, and the resources they are consuming.
SHOW STATUS: This command provides various status information about the database server, including metrics like uptime, number of queries executed, number of connections, and more. It can help you monitor the overall health and performance of the database server.
SHOW VARIABLES: This command displays the current configuration variables of the MySQL server. It includes important settings such as buffer sizes, timeouts, and other server parameters. Understanding these variables can help you optimize the database server for better performance.
EXPLAIN: This command is used to analyze and optimize query performance. By preceding a SELECT statement with EXPLAIN, you can obtain information about how MySQL executes the query, including the order in which tables are accessed, the use of indexes, and estimated rows to be examined.
SHOW ENGINE INNODB STATUS: This command provides detailed information about the InnoDB storage engine status, including transaction details, lock information, and buffer pool usage. It can be valuable when troubleshooting performance issues related to InnoDB.
SHOW GLOBAL VARIABLES: This command displays the current values of global variables, which are system-wide settings that affect the behavior of the database server. It can help you identify and understand the current server configuration.
SHOW GLOBAL STATUS: This command provides global status information about the database server, including metrics such as opened tables, queries per second, key cache hit ratio, and more. It helps you monitor the overall performance of the server.
SHOW TABLE STATUS: This command retrieves information about tables in a database, including their size, row count, average row length, and other useful statistics. It can be helpful for monitoring table sizes and identifying potential issues.