Creating a user,creating a database and connecting a database in Mysql

Last Updated On Friday 29th Oct 2021

Create user

  • Create User
	CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password';
	
  • Grant permissions
	GRANT ALL ON *.* TO 'dbuser'@'localhost' WITH GRANT OPTION;
	
  • Reset password REQUIRED
	ALTER USER 'dbuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
	
	flush privileges;
	

Create database

  • Create database
	create database dbname;
	
  • Grant permissions to ‘dbname’
	GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
	

Connecting to database

	mysql://<username>:<password>@<host>:<port>/<db_name>
	

Show all foreign keys constraint on table

	SELECT TABLE_NAME, COLUMN_NAME,  CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'table' group by constraint_name;
	

Truncate table with foreign key

	
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;
SET FOREIGN_KEY_CHECKS = 1;```