Banco de Dados

SGBD

MySQL Server

Servidor (3306)
- MySQL Server (install mysql)
- Docker MySQL
Cliente
- MySQL Workbench
- DBeaver
- Adminer (docker adminer)
- mysql (CLI)
- VSCode:

$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  mysql:8.3
 
$ docker exec -it mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.3.0 MySQL Community Server - GPL
 
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker stop $(docker ps -a -q -f name=mysql ) && docker rm $(docker ps -a -q -f name=mysql )

$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  mysql:8.3
 
$ docker exec -it mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.3.0 MySQL Community Server - GPL
 
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker stop $(docker ps -a -q -f name=mysql ) && docker rm $(docker ps -a -q -f name=mysql )

Volume:

$ docker volume create mysql-data
 
$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  -v mysql-data:/var/lib/mysql \
  mysql:8.3

$ docker volume create mysql-data
 
$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  -v mysql-data:/var/lib/mysql \
  mysql:8.3

Docker Compose (MySQL, Adminer):

/codes/database/mysql-server/docker-compose.yml

services:
 
  mysql:
    image: mysql:8.3
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-tier
    ports:
      - 3306:3306
    volumes:
      - mysql_data:/var/lib/mysql
      # - ./data:/var/lib/mysql
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - mysql
 
networks:
  app-tier:
    driver: bridge
 
volumes:
  mysql_data:

/codes/database/mysql-server/docker-compose.yml

services:
 
  mysql:
    image: mysql:8.3
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-tier
    ports:
      - 3306:3306
    volumes:
      - mysql_data:/var/lib/mysql
      # - ./data:/var/lib/mysql
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - mysql
 
networks:
  app-tier:
    driver: bridge
 
volumes:
  mysql_data:

Ao criar este arquivo .env.example renomei para .env:

/codes/database/mysql-server/.env.example

MYSQL_ROOT_PASSWORD=secret
MYSQL_DATABASE=example
MYSQL_USER=devuser
MYSQL_PASSWORD=secret

/codes/database/mysql-server/.env.example

MYSQL_ROOT_PASSWORD=secret
MYSQL_DATABASE=example
MYSQL_USER=devuser
MYSQL_PASSWORD=secret

Instalar docker compose plugin e verifique localmente:

$ docker compose version
Docker Compose version vxxxx
 
$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                docker:default
[+] Running 3/3
 ✔ Network mysql-server_app-tier     Created                     0.0s
 ✔ Container mysql-server-mysql-1    Started                     0.1s
 ✔ Container mysql-server-adminer-1  Started                     0.1s
 
$ docker compose ps
NAME                     IMAGE         COMMAND                  SERVICE   CREATED          STATUS          PORTS
mysql-server-adminer-1   adminer:4.7   "entrypoint.sh docke…"   adminer   11 seconds ago   Up 10 seconds   0.0.0.0:8080->8080/tcp
mysql-server-mysql-1     mysql:8.0     "docker-entrypoint.s…"   mysql     11 seconds ago   Up 10 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp
 
$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

$ docker compose version
Docker Compose version vxxxx
 
$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                docker:default
[+] Running 3/3
 ✔ Network mysql-server_app-tier     Created                     0.0s
 ✔ Container mysql-server-mysql-1    Started                     0.1s
 ✔ Container mysql-server-adminer-1  Started                     0.1s
 
$ docker compose ps
NAME                     IMAGE         COMMAND                  SERVICE   CREATED          STATUS          PORTS
mysql-server-adminer-1   adminer:4.7   "entrypoint.sh docke…"   adminer   11 seconds ago   Up 10 seconds   0.0.0.0:8080->8080/tcp
mysql-server-mysql-1     mysql:8.0     "docker-entrypoint.s…"   mysql     11 seconds ago   Up 10 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp
 
$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

Create Database

$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE DATABASE monitor_db;
Query OK, 1 row affected (0.01 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
| monitor_db         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)
 
mysql> USE monitor_db;
Database changed
 
mysql> CREATE TABLE hosts (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(255) NOT NULL,
    ->   address VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| hosts                |
+----------------------+
1 row in set (0.00 sec)
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   |     | NULL    |                |
| address | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE DATABASE monitor_db;
Query OK, 1 row affected (0.01 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
| monitor_db         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)
 
mysql> USE monitor_db;
Database changed
 
mysql> CREATE TABLE hosts (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(255) NOT NULL,
    ->   address VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| hosts                |
+----------------------+
1 row in set (0.00 sec)
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   |     | NULL    |                |
| address | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

Backup

$ docker compose exec mysql mysqldump -u root -p monitor_db > database.sql
 
$ docker compose exec mysql mysqldump -u root -p --all-databases > database.sql

$ docker compose exec mysql mysqldump -u root -p monitor_db > database.sql
 
$ docker compose exec mysql mysqldump -u root -p --all-databases > database.sql

Recovery

$ docker compose exec mysql mysql -u root -p monitor_db < database.sql
 
$ docker compose exec mysql mysql -u root -p < database.sql

$ docker compose exec mysql mysql -u root -p monitor_db < database.sql
 
$ docker compose exec mysql mysql -u root -p < database.sql

Criar usuário:

-- Criar usuário local
CREATE USER 'nome_usuario'@'localhost' IDENTIFIED BY 'senha';
 
-- Criar usuário para qualquer host
CREATE USER 'nome_usuario'@'*' IDENTIFIED BY 'senha';
 
-- Exemplo
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev123';

-- Criar usuário local
CREATE USER 'nome_usuario'@'localhost' IDENTIFIED BY 'senha';
 
-- Criar usuário para qualquer host
CREATE USER 'nome_usuario'@'*' IDENTIFIED BY 'senha';
 
-- Exemplo
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev123';

Deletar usuário:

DROP USER 'nome_usuario'@'localhost';
 
-- Exemplo
DROP USER 'developer'@'localhost';

DROP USER 'nome_usuario'@'localhost';
 
-- Exemplo
DROP USER 'developer'@'localhost';

Grant (Conceder Permissões):

-- Conceder todas as permissões em um banco
GRANT ALL PRIVILEGES ON nome_banco.* TO 'nome_usuario'@'localhost';
 
-- Conceder permissões específicas
GRANT SELECT, INSERT, UPDATE, DELETE ON nome_banco.* TO 'nome_usuario'@'localhost';
 
-- Conceder permissões em tabela específica
GRANT SELECT ON nome_banco.tabela TO 'nome_usuario'@'localhost';
 
-- Conceder todas as permissões em todos os bancos
GRANT ALL PRIVILEGES ON *.* TO 'nome_usuario'@'localhost';
 
-- Exemplos
GRANT ALL PRIVILEGES ON monitor_db.* TO 'developer'@'localhost';
GRANT SELECT, INSERT, UPDATE ON monitor_db.usuarios TO 'developer'@'localhost';

-- Conceder todas as permissões em um banco
GRANT ALL PRIVILEGES ON nome_banco.* TO 'nome_usuario'@'localhost';
 
-- Conceder permissões específicas
GRANT SELECT, INSERT, UPDATE, DELETE ON nome_banco.* TO 'nome_usuario'@'localhost';
 
-- Conceder permissões em tabela específica
GRANT SELECT ON nome_banco.tabela TO 'nome_usuario'@'localhost';
 
-- Conceder todas as permissões em todos os bancos
GRANT ALL PRIVILEGES ON *.* TO 'nome_usuario'@'localhost';
 
-- Exemplos
GRANT ALL PRIVILEGES ON monitor_db.* TO 'developer'@'localhost';
GRANT SELECT, INSERT, UPDATE ON monitor_db.usuarios TO 'developer'@'localhost';

Revoke (Revogar Permissões):

-- Revogar todas as permissões em um banco
REVOKE ALL PRIVILEGES ON nome_banco.* FROM 'nome_usuario'@'localhost';
 
-- Revogar permissões específicas
REVOKE SELECT, INSERT, UPDATE, DELETE ON nome_banco.* FROM 'nome_usuario'@'localhost';
 
-- Revogar permissões em tabela específica
REVOKE DELETE ON nome_banco.tabela FROM 'nome_usuario'@'localhost';
 
-- Exemplos
REVOKE DELETE ON monitor_db.* FROM 'developer'@'localhost';
REVOKE UPDATE ON monitor_db.usuarios FROM 'developer'@'localhost';

-- Revogar todas as permissões em um banco
REVOKE ALL PRIVILEGES ON nome_banco.* FROM 'nome_usuario'@'localhost';
 
-- Revogar permissões específicas
REVOKE SELECT, INSERT, UPDATE, DELETE ON nome_banco.* FROM 'nome_usuario'@'localhost';
 
-- Revogar permissões em tabela específica
REVOKE DELETE ON nome_banco.tabela FROM 'nome_usuario'@'localhost';
 
-- Exemplos
REVOKE DELETE ON monitor_db.* FROM 'developer'@'localhost';
REVOKE UPDATE ON monitor_db.usuarios FROM 'developer'@'localhost';

Aplicar alterações:

-- Recarregar as permissões
FLUSH PRIVILEGES;
 
-- Verificar permissões de um usuário
SHOW GRANTS FOR 'nome_usuario'@'localhost';
 
-- Exemplo
SHOW GRANTS FOR 'developer'@'localhost';

-- Recarregar as permissões
FLUSH PRIVILEGES;
 
-- Verificar permissões de um usuário
SHOW GRANTS FOR 'nome_usuario'@'localhost';
 
-- Exemplo
SHOW GRANTS FOR 'developer'@'localhost';

Listar usuários:

SELECT user, host FROM mysql.user;

SELECT user, host FROM mysql.user;

Postgres Server

Server
- Postgres (install)
- Docker Postgres
Client
- pgAdmin (dpage/pgadmin4)
- DBeaver
- Adminer (docker adminer)
- psql (CLI)
- VSCode
  - SQLTools
  - PostgreSQL

Postgres Docker:

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  postgres:16.2
 
$ docker exec -it postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  postgres:16.2
 
$ docker exec -it postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q

Volume:

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16.2

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16.2

Docker Compose (Postgres, PgAdmin 4):

/codes/database/postgres-server/docker-compose.yml

services:
 
  postgres:
    container_name: postgres
    image: postgres:16.2
    restart: always
    environment:
      # POSTGRES_USER: postgres # default
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    networks:
      - app-tier
    ports:
      - 5432:5432
    volumes:
      - postgres_data:/var/lib/postgresql/data
      # - ./data:/var/lib/postgresql/data
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - postgres
 
  # pgadmin:
  #   container_name: pgadmin
  #   image: dpage/pgadmin4:8.3
  #   restart: always
  #   environment:
  #     PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL}
  #     PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
  #   networks:
  #     - app-tier
  #   ports:
  #     - 8080:80
  #   depends_on:
  #     - postgres
 
volumes:
  postgres_data:
 
networks:
  app-tier:
    driver: bridge

/codes/database/postgres-server/docker-compose.yml

services:
 
  postgres:
    container_name: postgres
    image: postgres:16.2
    restart: always
    environment:
      # POSTGRES_USER: postgres # default
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    networks:
      - app-tier
    ports:
      - 5432:5432
    volumes:
      - postgres_data:/var/lib/postgresql/data
      # - ./data:/var/lib/postgresql/data
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - postgres
 
  # pgadmin:
  #   container_name: pgadmin
  #   image: dpage/pgadmin4:8.3
  #   restart: always
  #   environment:
  #     PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL}
  #     PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
  #   networks:
  #     - app-tier
  #   ports:
  #     - 8080:80
  #   depends_on:
  #     - postgres
 
volumes:
  postgres_data:
 
networks:
  app-tier:
    driver: bridge

/codes/database/postgres-server/.env.example

POSTGRES_PASSWORD=secret

PGADMIN_EMAIL=user@email.com
PGADMIN_PASSWORD=secret

/codes/database/postgres-server/.env.example

POSTGRES_PASSWORD=secret

PGADMIN_EMAIL=user@email.com
PGADMIN_PASSWORD=secret

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

PostgreSQL Client Applications

clusterdb — cluster a PostgreSQL database
createdb — create a new PostgreSQL database
createuser — define a new PostgreSQL user account
dropdb — remove a PostgreSQL database
dropuser — remove a PostgreSQL user account
ecpg — embedded SQL C preprocessor
pg_amcheck — checks for corruption in one or more PostgreSQL databases
pg_basebackup — take a base backup of a PostgreSQL cluster
pgbench — run a benchmark test on PostgreSQL
pg_config — retrieve information about the installed version of PostgreSQL
pg_dump — extract a PostgreSQL database into a script file or other archive file
pg_dumpall — extract a PostgreSQL database cluster into a script file
pg_isready — check the connection status of a PostgreSQL server
pg_receivewal — stream write-ahead logs from a PostgreSQL server
pg_recvlogical — control PostgreSQL logical decoding streams
pg_restore — restore a PostgreSQL database from an archive file created by pg_dump
pg_verifybackup — verify the integrity of a base backup of a PostgreSQL cluster
psql — PostgreSQL interactive terminal
reindexdb — reindex a PostgreSQL database
vacuumdb — garbage-collect and analyze a PostgreSQL database

psql

Command	Description
`help`, `\h`	Help
`\q`	Quit
`\l`	List databases
`\c <database>`	Connect to a database
`\d`	List tables in database
`\d <table>`	Show table
`\d+ <table>`	More detailed table definition
`\dt .`	List tables from all schemas
`\du`	List users
`\dn`	List schemas
`\df`	List functions

Create Database

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# CREATE DATABASE monitor_db;
 
monitor_db=# \l
 monitor_db | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 postgres   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 
postgres=# \c monitor_db
 
monitor_db=# CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);
 
monitor_db=# \dt public.*
 public | hosts | table | postgres
 
monitor_db=# \d hosts
 id       | integer                |           | not null | nextval('hosts_id_seq'::regclass)
 name     | character varying(255) |           | not null |
 address  | character varying(255) |           | not null |
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# CREATE DATABASE monitor_db;
 
monitor_db=# \l
 monitor_db | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 postgres   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 
postgres=# \c monitor_db
 
monitor_db=# CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);
 
monitor_db=# \dt public.*
 public | hosts | table | postgres
 
monitor_db=# \d hosts
 id       | integer                |           | not null | nextval('hosts_id_seq'::regclass)
 name     | character varying(255) |           | not null |
 address  | character varying(255) |           | not null |
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

Backup

$ psql -l -U postgres
 
$ pg_dump database_name_here > database.sql
 
$ pg_dump -Fc database_name_here > database.bak # compressed binary format
 
$ pg_dump -Ft database_name_here > database.tar # tarball

$ psql -l -U postgres
 
$ pg_dump database_name_here > database.sql
 
$ pg_dump -Fc database_name_here > database.bak # compressed binary format
 
$ pg_dump -Ft database_name_here > database.tar # tarball

Recovery

$ psql -d database_name_here < database.sql
 
$ psql -d database_name_here -f database.sql
 
$ pg_restore database.sql
 
$ pg_restore -Fc database.bak # restore compressed binary format
 
$ pg_restore -Fc -C database.bak # create the database before restoring into it
 
$ pg_restore -Ft database.tar # restore tarball

$ psql -d database_name_here < database.sql
 
$ psql -d database_name_here -f database.sql
 
$ pg_restore database.sql
 
$ pg_restore -Fc database.bak # restore compressed binary format
 
$ pg_restore -Fc -C database.bak # create the database before restoring into it
 
$ pg_restore -Ft database.tar # restore tarball

Criar usuário:

-- Criar usuário
CREATE USER nome_usuario WITH PASSWORD 'senha';
 
-- Criar usuário com opções
CREATE USER nome_usuario WITH PASSWORD 'senha' CREATEDB CREATEROLE;
 
-- Exemplo
CREATE USER developer WITH PASSWORD 'dev123';

-- Criar usuário
CREATE USER nome_usuario WITH PASSWORD 'senha';
 
-- Criar usuário com opções
CREATE USER nome_usuario WITH PASSWORD 'senha' CREATEDB CREATEROLE;
 
-- Exemplo
CREATE USER developer WITH PASSWORD 'dev123';

Deletar usuário:

DROP USER nome_usuario;
 
-- Ou reassumir propriedade antes de deletar
DROP USER IF EXISTS nome_usuario;
 
-- Exemplo
DROP USER developer;

DROP USER nome_usuario;
 
-- Ou reassumir propriedade antes de deletar
DROP USER IF EXISTS nome_usuario;
 
-- Exemplo
DROP USER developer;

Grant (Conceder Permissões):

-- Conceder todas as permissões em um banco
GRANT ALL PRIVILEGES ON DATABASE nome_banco TO nome_usuario;
 
-- Conceder permissões específicas em um banco
GRANT CONNECT, CREATE ON DATABASE nome_banco TO nome_usuario;
 
-- Conceder permissões em esquema
GRANT ALL PRIVILEGES ON SCHEMA public TO nome_usuario;
 
-- Conceder permissões em tabela específica
GRANT SELECT, INSERT, UPDATE, DELETE ON nome_banco.tabela TO nome_usuario;
 
-- Conceder todas as permissões em tabelas
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO nome_usuario;
 
-- Exemplos
GRANT ALL PRIVILEGES ON DATABASE monitor_db TO developer;
GRANT SELECT, INSERT, UPDATE ON public.hosts TO developer;

-- Conceder todas as permissões em um banco
GRANT ALL PRIVILEGES ON DATABASE nome_banco TO nome_usuario;
 
-- Conceder permissões específicas em um banco
GRANT CONNECT, CREATE ON DATABASE nome_banco TO nome_usuario;
 
-- Conceder permissões em esquema
GRANT ALL PRIVILEGES ON SCHEMA public TO nome_usuario;
 
-- Conceder permissões em tabela específica
GRANT SELECT, INSERT, UPDATE, DELETE ON nome_banco.tabela TO nome_usuario;
 
-- Conceder todas as permissões em tabelas
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO nome_usuario;
 
-- Exemplos
GRANT ALL PRIVILEGES ON DATABASE monitor_db TO developer;
GRANT SELECT, INSERT, UPDATE ON public.hosts TO developer;

Revoke (Revogar Permissões):

-- Revogar todas as permissões em um banco
REVOKE ALL PRIVILEGES ON DATABASE nome_banco FROM nome_usuario;
 
-- Revogar permissões específicas em um banco
REVOKE CONNECT, CREATE ON DATABASE nome_banco FROM nome_usuario;
 
-- Revogar permissões em esquema
REVOKE ALL PRIVILEGES ON SCHEMA public FROM nome_usuario;
 
-- Revogar permissões em tabela específica
REVOKE DELETE ON nome_banco.tabela FROM nome_usuario;
 
-- Exemplos
REVOKE DELETE ON public.hosts FROM developer;
REVOKE ALL PRIVILEGES ON DATABASE monitor_db FROM developer;

-- Revogar todas as permissões em um banco
REVOKE ALL PRIVILEGES ON DATABASE nome_banco FROM nome_usuario;
 
-- Revogar permissões específicas em um banco
REVOKE CONNECT, CREATE ON DATABASE nome_banco FROM nome_usuario;
 
-- Revogar permissões em esquema
REVOKE ALL PRIVILEGES ON SCHEMA public FROM nome_usuario;
 
-- Revogar permissões em tabela específica
REVOKE DELETE ON nome_banco.tabela FROM nome_usuario;
 
-- Exemplos
REVOKE DELETE ON public.hosts FROM developer;
REVOKE ALL PRIVILEGES ON DATABASE monitor_db FROM developer;

Verificar permissões:

-- Listar usuários
\du
 
-- Verificar permissões de um usuário
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'hosts';
 
-- Listar permissões em banco de dados
SELECT datname, datacl
FROM pg_database
WHERE datname = 'monitor_db';

-- Listar usuários
\du
 
-- Verificar permissões de um usuário
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'hosts';
 
-- Listar permissões em banco de dados
SELECT datname, datacl
FROM pg_database
WHERE datname = 'monitor_db';

SQLite

SQLite
Docker SQLite
client
- DB Browser for SQLite
- DBeaver
- Adminer (docker adminer)
- sqlitebrowser
- sqlite3 (CLI)
- VSCode
  - SQLite
  - SQlite Viewer

SQLite Docker:

/codes/database/sqlite/Dockerfile

FROM alpine:3.19

RUN apk --no-cache add sqlite

WORKDIR /db

# ENTRYPOINT ["/bin/sh"]

# CMD [ "sqlite3", "initial.db" ]

/codes/database/sqlite/Dockerfile

FROM alpine:3.19

RUN apk --no-cache add sqlite

WORKDIR /db

# ENTRYPOINT ["/bin/sh"]

# CMD [ "sqlite3", "initial.db" ]

$ docker build -t sqlite:1.0 .
 
$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit
 
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .quit
 
/db # exit

$ docker build -t sqlite:1.0 .
 
$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit
 
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .quit
 
/db # exit

sqlite3 (dot-commands)

.databases: List names and files of attached databases
.dump: Dump database in SQL text format
.help: Show help
.quit: Stop interpreting input stream
.schema: Show the CREATE statements
.show: Displays current settings for various parameters
.tables: List names of tables

Data Types:

NULL
INTEGER: signed integer (1, 2, 3, 4, 6, 8 bytes)
REAL: floating point value (8-byte IEEE floating point number)
TEXT: text string (UTF-8, UTF-16BE or UTF-16LE)
BLOB: blob of data

Create Database

$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .databases
main: /db/database.db r/w
 
sqlite> CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);
 
sqlite> .tables
hosts
 
sqlite> .schema hosts
CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   address TEXT NOT NULL
 );
 
sqlite> .quit
 
/db # exit

$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .databases
main: /db/database.db r/w
 
sqlite> CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);
 
sqlite> .tables
hosts
 
sqlite> .schema hosts
CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   address TEXT NOT NULL
 );
 
sqlite> .quit
 
/db # exit

Backup

$ sqlite3 ./dev.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
sqlite> .schema table_name
sqlite> .dump
sqlite> .dump table_name
sqlite> .output ./backup.sql
sqlite> .exit
 
$ sqlite3 dev.db .dump > dev.sql

$ sqlite3 ./dev.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
sqlite> .schema table_name
sqlite> .dump
sqlite> .dump table_name
sqlite> .output ./backup.sql
sqlite> .exit
 
$ sqlite3 dev.db .dump > dev.sql

Recovery

$ sqlite3 ./restore.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .read ./backup.sql
sqlite> .exit
 
$ sqlite3 restore.db < backup.sql

$ sqlite3 ./restore.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .read ./backup.sql
sqlite> .exit
 
$ sqlite3 restore.db < backup.sql

MongoDB Server

Server
- MongoDB (install)
- Docker Mongo
- MongoDB Atlas
Client
- MongoDB Compass
- DBeaver
- Adminer (docker adminer)
- mongo-express
- MongoDB Shell - mongosh (CLI)
- VSCode
  - MongoDB for VS Code
Guias
- bradtraversy/mongodb_cheat_sheet.md
- SQL to MongoDB Mapping Chart

Mongo Docker:

$ docker run --name mongo -d mongo:7.0
 
$ docker exec -it mongo mongosh
Current Mongosh Log ID: 65de1e4556e305af36af7b82
Connecting to:          mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.
 
test>exit

$ docker run --name mongo -d mongo:7.0
 
$ docker exec -it mongo mongosh
Current Mongosh Log ID: 65de1e4556e305af36af7b82
Connecting to:          mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.
 
test>exit

Volume:

$ docker run -d \
  --name mongo \
  -e MONGO_ROOT_USERNAME=root MONGO_ROOT_PASSWORD=secret \
  -v mongodata:/data/db \
  mongo:7.0

$ docker run -d \
  --name mongo \
  -e MONGO_ROOT_USERNAME=root MONGO_ROOT_PASSWORD=secret \
  -v mongodata:/data/db \
  mongo:7.0

Docker Compose (Mongo, Mongo Express):

$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network mongo-server_app-tier  Created                                                                                     0.1s
 ✔ Container mongo                Started                                                                                     0.7s
 ✔ Container mongo-express        Started                                                                                     0.1s
 
$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test>exit
 
$docker compose down
[+] Running 3/3
 ✔ Container mongo-express        Removed                                                                                     0.0s
 ✔ Container mongo                Removed                                                                                     0.5s
 ✔ Network mongo-server_app-tier  Removed                                                                                     0.2s

$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network mongo-server_app-tier  Created                                                                                     0.1s
 ✔ Container mongo                Started                                                                                     0.7s
 ✔ Container mongo-express        Started                                                                                     0.1s
 
$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test>exit
 
$docker compose down
[+] Running 3/3
 ✔ Container mongo-express        Removed                                                                                     0.0s
 ✔ Container mongo                Removed                                                                                     0.5s
 ✔ Network mongo-server_app-tier  Removed                                                                                     0.2s

mongosh

use: Set current database
show
- show databases, show dbs: Print a list of all available databases.
- show collections, show tables: Print a list of all collections for current database.
- show profile: Prints system.profile information.
- show users: Print a list of all users for current database.
- show roles: Print a list of all roles for current database.
- show logs: Print all logs.

Create Database (CRUD)

Create
- db.<collection>.insertOne()
- db.<collection>.insertMany()
- db.<collection>.bulkWrite()
Read
- db.<collection>.find()
- db.<collection>.aggregate()
Update
- db.<collection>.updateOne()
- db.<collection>.updateMany()
- db.<collection>.findOneAndUpdate()
Delete
- db.<collection>.deleteOne()
- db.<collection>.deleteMany()
- db.<collection>.findOneAndDelete()

$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test> show dbs
admin   100.00 KiB
config   12.00 KiB
local    72.00 KiB
 
test> use monitor_db
switched to db monitor_db
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
 
monitor_db> db.getCollectionNames()
[]
 
monitor_db> db.hosts.insertOne(
...   {
...     name: 'Google DNS',
...     address: '8.8.8.8'
...   }
... )
{
  acknowledged: true,
  insertedId: ObjectId('66de735b5e9174c5eec76a8c')
}
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
monitor_db   40.00 KiB
 
 
monitor_db> db.getCollectionNames()
[ 'hosts' ]
 
monitor_db> db.hosts.find()
[
  {
    _id: ObjectId('66de735b5e9174c5eec76a8c'),
    name: 'Google DNS',
    address: '8.8.8.8'
  }
]
 
monitor_db> exit

$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test> show dbs
admin   100.00 KiB
config   12.00 KiB
local    72.00 KiB
 
test> use monitor_db
switched to db monitor_db
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
 
monitor_db> db.getCollectionNames()
[]
 
monitor_db> db.hosts.insertOne(
...   {
...     name: 'Google DNS',
...     address: '8.8.8.8'
...   }
... )
{
  acknowledged: true,
  insertedId: ObjectId('66de735b5e9174c5eec76a8c')
}
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
monitor_db   40.00 KiB
 
 
monitor_db> db.getCollectionNames()
[ 'hosts' ]
 
monitor_db> db.hosts.find()
[
  {
    _id: ObjectId('66de735b5e9174c5eec76a8c'),
    name: 'Google DNS',
    address: '8.8.8.8'
  }
]
 
monitor_db> exit

Criar usuário:

// Criar usuário com senha
db.createUser({
  user: "nome_usuario",
  pwd: "senha",
  roles: [
    { role: "readWrite", db: "monitor_db" }
  ]
});
 
// Criar usuário administrador
db.createUser({
  user: "admin_user",
  pwd: passwordPrompt(),
  roles: [
    { role: "root", db: "admin" }
  ]
});
 
// Exemplo
db.createUser({
  user: "developer",
  pwd: passwordPrompt(),
  roles: [
    { role: "readWrite", db: "monitor_db" },
    { role: "read", db: "admin" }
  ]
});

// Criar usuário com senha
db.createUser({
  user: "nome_usuario",
  pwd: "senha",
  roles: [
    { role: "readWrite", db: "monitor_db" }
  ]
});
 
// Criar usuário administrador
db.createUser({
  user: "admin_user",
  pwd: passwordPrompt(),
  roles: [
    { role: "root", db: "admin" }
  ]
});
 
// Exemplo
db.createUser({
  user: "developer",
  pwd: passwordPrompt(),
  roles: [
    { role: "readWrite", db: "monitor_db" },
    { role: "read", db: "admin" }
  ]
});

Deletar usuário:

// Deletar usuário do banco atual
db.dropUser("nome_usuario");
 
// Exemplo
db.dropUser("developer");

// Deletar usuário do banco atual
db.dropUser("nome_usuario");
 
// Exemplo
db.dropUser("developer");

Grant (Conceder Permissões):

// Conceder permissões a usuário existente
db.grantRolesToUser("nome_usuario", [
  { role: "readWrite", db: "monitor_db" },
  { role: "dbAdmin", db: "monitor_db" }
]);
 
// Exemplo
db.grantRolesToUser("developer", [
  { role: "readWrite", db: "monitor_db" }
]);

// Conceder permissões a usuário existente
db.grantRolesToUser("nome_usuario", [
  { role: "readWrite", db: "monitor_db" },
  { role: "dbAdmin", db: "monitor_db" }
]);
 
// Exemplo
db.grantRolesToUser("developer", [
  { role: "readWrite", db: "monitor_db" }
]);

Revoke (Revogar Permissões):

// Revogar permissões de um usuário
db.revokeRolesFromUser("nome_usuario", [
  { role: "readWrite", db: "monitor_db" }
]);
 
// Exemplo
db.revokeRolesFromUser("developer", [
  { role: "dbAdmin", db: "monitor_db" }
]);

// Revogar permissões de um usuário
db.revokeRolesFromUser("nome_usuario", [
  { role: "readWrite", db: "monitor_db" }
]);
 
// Exemplo
db.revokeRolesFromUser("developer", [
  { role: "dbAdmin", db: "monitor_db" }
]);

Verificar permissões:

// Listar usuários do banco atual
show users
 
// ou
 
db.getUsers()
 
// Verificar permissões de um usuário específico
db.getUser("nome_usuario")
 
// Exemplo
db.getUser("developer")

// Listar usuários do banco atual
show users
 
// ou
 
db.getUsers()
 
// Verificar permissões de um usuário específico
db.getUser("nome_usuario")
 
// Exemplo
db.getUser("developer")

Roles comuns no MongoDB:

Role	Descrição
`read`	Permissão apenas leitura em um banco de dados
`readWrite`	Permissão de leitura e escrita em um banco de dados
`dbAdmin`	Permissões administrativas do banco (criar índices, etc)
`userAdmin`	Permissão para gerenciar usuários do banco
`dbOwner`	Combinação de `readWrite`, `dbAdmin` e `userAdmin`
`root`	Acesso total a todos os bancos (super usuário)

Databases

Database	Create Database	Use Database	Create Table	Insert table
MySQL	`CREATE DATABASE database_name;`	`USE database_name;`	`CREATE TABLE table_name (...);`	`INSERT INTO table_name ...;`
Postgres	`CREATE DATABASE database_name;`	`\c database_name`	`CREATE TABLE table_name (...);`	`INSERT INTO table_name ...;`
SQLite		`sqlite3 database_name.db`	`CREATE TABLE table_name (...);`	`INSERT INTO table_name ...;`
MongoDB	`use database_name;`	`use database_name;`		`db.collection_name.insertOne();`

SQL (CREATE TABLE)

Banco de Dados Comando SQL

MySQL

Banco de Dados	Comando SQL
MySQL	`CREATE TABLE hosts ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );` `CREATE TABLE hosts ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );`
PostgreSQL	`CREATE TABLE hosts ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );` `CREATE TABLE hosts ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );`
SQLite	`CREATE TABLE hosts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL );` `CREATE TABLE hosts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL );`

CREATE TABLE hosts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

CREATE TABLE hosts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

PostgreSQL

CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

SQLite

CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);

CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);

Usuários (CREATE USER, GRANT e REVOKE)

Operação	MySQL	PostgreSQL	MongoDB
Criar Usuário	`CREATE USER 'user'@'localhost' IDENTIFIED BY 'senha';` `CREATE USER 'user'@'localhost' IDENTIFIED BY 'senha';`	`CREATE USER user WITH PASSWORD 'senha';` `CREATE USER user WITH PASSWORD 'senha';`	`db.createUser({ user: "user", pwd: passwordPrompt(), roles: [...] });` `db.createUser({ user: "user", pwd: passwordPrompt(), roles: [...] });`
Deletar Usuário	`DROP USER 'user'@'localhost';` `DROP USER 'user'@'localhost';`	`DROP USER user;` `DROP USER user;`	`db.dropUser("user");` `db.dropUser("user");`
Grant (Banco)	`GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';` `GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';`	`GRANT ALL PRIVILEGES ON DATABASE database TO user;` `GRANT ALL PRIVILEGES ON DATABASE database TO user;`	`db.grantRolesToUser("user", [ { role: "readWrite", db: "database" } ]);` `db.grantRolesToUser("user", [ { role: "readWrite", db: "database" } ]);`
Grant (Tabela)	`GRANT SELECT, INSERT ON database.table TO 'user'@'localhost';` `GRANT SELECT, INSERT ON database.table TO 'user'@'localhost';`	`GRANT SELECT, INSERT ON database.table TO user;` `GRANT SELECT, INSERT ON database.table TO user;`	`// Não há grant por tabela // Use read/readWrite no db` `// Não há grant por tabela // Use read/readWrite no db`
Revoke (Banco)	`REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'localhost';` `REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'localhost';`	`REVOKE ALL PRIVILEGES ON DATABASE database FROM user;` `REVOKE ALL PRIVILEGES ON DATABASE database FROM user;`	`db.revokeRolesFromUser("user", [ { role: "readWrite", db: "database" } ]);` `db.revokeRolesFromUser("user", [ { role: "readWrite", db: "database" } ]);`
Revoke (Tabela)	`REVOKE SELECT, INSERT ON database.table FROM 'user'@'localhost';` `REVOKE SELECT, INSERT ON database.table FROM 'user'@'localhost';`	`REVOKE SELECT, INSERT ON database.table FROM user;` `REVOKE SELECT, INSERT ON database.table FROM user;`	`// Não há revoke por tabela` `// Não há revoke por tabela`
Aplicar Permissões	`FLUSH PRIVILEGES;` `FLUSH PRIVILEGES;`	`-- Automático -- (sem comando necessário)` `-- Automático -- (sem comando necessário)`	`// Automático // (sem comando necessário)` `// Automático // (sem comando necessário)`
Listar Usuários	`SELECT user, host FROM mysql.user;` `SELECT user, host FROM mysql.user;`	`\du` `\du`	`show users // ou db.getUsers()` `show users // ou db.getUsers()`

Tipos de Dados

Types	MySQL	Postgres	SQLite
Character	`CHAR(n)`, `VARCHAR(n)`, `TINYTEXT(n)`, `TEXT(n)`, `MEDIUMTEXT(n)`, `LONGTEXT(n)`, `BINARY(n)`, `VARBINARY(n)`	`CHARACTER (N)`, `CHAR(n)`, `CHARACTER VARYING (N)`, `VARCHAR(n)`, `TEXT`	`TEXT`
Numeric	`BIT`, `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`, `DECIMAL`, `FLOAT`, `DOUBLE`, `BOOLEAN` (`TINYINT(1)`)	`SMALLINT`, `INTEGER`, `BIGINT`, `DECIMAL`, `NUMERIC`, `REAL`, `DOUBLE`, `SMALLSERIAL`, `SERIAL`, `BIGSERIAL`,	`NUMERIC`, `INTEGER`, `REAL`
Date/Time	`DATE`, `DATETIME`, `TIMESTAMP`, `TIME`, `YEAR`	`DATE`, `INTERVAL` , `TIMESTAMP`, `TIME`
Large Object (LOB)	`TINYBLOB`, `BLOB(n)`, `MEDIUMBLOB`, `LONGTEXT`	`BYTEA`	`BLOB`
Especial	Espacial (`POINTS`, `LINES`), `JSON`	`MONEY`, `BOOLEAN`, `ENUM`, Espacial (`POINTS`, `LINES`), Network (`INET`, `CIDR`, `MACADDR`), `TSQUERY` , `UUID`, `XML`, `JSON`, `JSONB`, `ARRAY`, `TYPE`, `NUMRANGE`

Referências

SGBD
- MySQL
- Postgres
- MongoDB
- SQLite
Tipo de dados
- MySQL
- Postgres
- SQLite

Editar esta página