Riajul Kashem

Riajul Kashem

DB Notes For Busy Developer

Subscribe to my newsletter and never miss my upcoming articles

Create Database

CREATE DATABASE db_name_here;

Create User

Mysql

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Postgresql

CREATE USER 'username' WITH PASSWORD 'password';

Postges Database create with existing user

CREATE DATABASE db_name WITH OWNER username;

Grant DB Permission

Mysql

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON db_name_here.* TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON db_name_here.* TO 'username'@'localhost';

Postgresql

GRANT permissions ON DATABASE dbname TO username;

Backup Database

MySQL

mysqldump -P 3306 -h host_name -u user_name -p db_name > db_backup_file_name.sql

PostgreSQL

psql -U user_name -h host_name -p 5432 db_name >  db_backup_file_name.sql

Restore Backup Database

Mysql

mysql -u user_name -p database_name < db_backup_file_name.sql

PostgreSQL

psql -U user_name -d db_name -f db_backup_file_name.psql
 
Share this