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