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

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;