MySQL

Connection

mysql -h <host> -P <port> -u <user> -p<passowrd>      # no space between "-p" and "password"

# example
mysql -h localhost -P 3306 -u alex -pmypassword

SHOW DATABASES; – List DBs

\q – quit this CLI

Backup

Dump DB with Trigger, dump date, Events, Stored Procedures and Functions, and drop DB if exist:

mysqldump -h [host] -P [port] -u [user] -p[password] --triggers --dump-date --events --routines --add-drop-database --databases [db] > dump.sql

# example
mysqldump -h localhost -P 3306 -u alex -ppassword --triggers --dump-date --events --routines --add-drop-database --databases customers > dump.sql

# without dump date
mysqldump -h localhost -P 3306 -u alex -ppassword --triggers --skip-dump-date --events --routines --add-drop-database --databases customers > dump.sql

Restore

PostgreSQL

Connection

# Connect and get into the DB console
psql postgresql://<db_user>:<db_password>@<db-instance-address>:5432/<db_name>

# Run a command and exit
psql postgresql://<db_user>:<db_password>@<db-instance-address>:5432/<db_name> -c "<SQL command or CLI command>"

# List tables in DB customer and exist
psql postgresql://uu:pp@localhost:5432/customers -c "\d" 

# Execute a SQL file and exit
psql postgresql://uu:pp@localhost:5432/customers < backup.sql

# List all DBs and exit
psql postgresql://<db_user>:<db_password>@<db-instance-address>:5432/<db_name> -l 

\? – psql commands, used in this CLI

\h – SQL commands, such as “select, create table”

\l[+] – List DBs, + print more detail

\d – List current DB tables

\c <db-name> – connect/switch to another DB

\q – quit this CLI

Backup

Using pg_dump tool to -d dump DB creating and data inserting scripts:

pg_dump -d postgresql://<db_user>:<db_password>@<db-instance-address>:<port>/<db_name> > file.sql

# example:
pg_dump -d postgresql://uu:pp@localhost:5432/customers > backup.sql

psql postgresql://<db_user>:<db_pass>@<localhost>:<db_port>/<db_name> < <file_path>

# example:
psql postgresql://uu:pp@localhost:5432/customers < backup.sql

SQLite

MongoDB