Skip to content

Databases

MySQL

Reference

Commands

Simple Connection String: 

shell> mysql -h <HOSTNAME> -u <USER> -p


shell> mysqldump -h <HOSTNAME> -u <USER> <DATABASE> -p \
    --hex-blob --single-transaction --set-gtid-purged=OFF \
    --default-character-set=utf8mb4 | gzip > dump.sql.gz

shell> mysql -h <HOSTNAME> -u <USER> <DATABASE> -p < dump.sql
MGMT Queries

MySQL 5.x 

mysql> CREATE DATABASE cosckoyadb;
mysql> GRANT ALL PRIVILEGES ON cosckoyadb.* TO 'cosckoya'@'%' IDENTIFIED BY 'пароль';
mysql> FLUSH PRIVILEGES;
MySQL 8.x 
mysql> CREATE DATABASE cosckoyadb;
mysql> GRANT ALL PRIVILEGES ON cosckoyadb.* TO 'cosckoya'@'%' IDENTIFIED BY     'пароль';
mysql> FLUSH PRIVILEGES;

Show User & ACLs 

mysql> SELECT * FROM mysql.user

Create User Where % is any IP and localhost is only from 127.0.0.1 

mysql> CREATE USER 'cosckoya'@'%' IDENTIFIED BY 'пароль';

Delete Users 

mysql> DROP USER 'cosckoya'@'%';

PostgreSQL

Info

Commands

· Help 

postgre> \?

· List databases 

postgre> \l

· Connect database 

postgre> \l


psql --host=<HOST> --port=5432 --username=<USER> --dbname=<DBNAME>

pg_dump --host=<HOST> --port=5432 --username=<USER> --dbname=<DBNAME> | gzip > <FILE>

psql --host=<HOST> --port=5432 --username=<USER> --dbname=<DBNAME> < <FILE>
MGMT Queries 
postgre> CREATE DATABASE cosckoyadb;
postgre> CREATE USER cosckoya WITH ENCRYPTED PASSWORD 'пароль';
postgre> GRANT ALL PRIVILEGES ON DATABASE cosckoyadb TO cosckoya;
--- Alternative
postgre> GRANT CONNECT ON DATABASE cosckoyadb TO cosckoya;
postgre> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA cosckoyadb TO cosckoya;

postgre> DROP DATABASE cosckoya;