MySQL database setup & user creation commands

MySQL database setup & user creation commands

Login as root user with the below command.
mysql -u root -p

Create a database with the below command.
CREATE DATABASE db_name;

Check the database using the command.
show databases;

Create a user using the command.
CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’;

Grant privileges to the created user.
GRANT ALL PRIVILEGES ON db_name.* TO ‘username’@’%’ WITH GRANT OPTION;
than
FLUSH PRIVILEGES;

Check grants using the command.
SHOW GRANTS FOR ‘username’@’%’;

————————————–

Logging in a specific DB port
mysql -h hostname -P port -u username -p db_name

show table information, like engine, version, row_format_rows, etc.
SHOW TABLE STATUS WHERE Name = ‘tbl_name’

change table storage engine
ALTER TABLE tbl_name ENGINE=NDBCLUSTER;

Show current DB Port:
SHOW VARIABLES WHERE Variable_name = ‘port’;
or
netstat -nat |grep :3306
(Instead 3306 you can mention the port number that you are finding)

Show the current MySQL user:
SELECT USER();