Ads by ProfitSence
Close

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

Last Updated on Wednesday 5th Oct 2022

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;```