DB Notes For Busy Developer
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