Bases de dades / Databases

Índex / Index

General




W3-mSQL PHP3 (es) (ch) JDBC

Info GUI
 
miniSQL    
MySQL
 
PostgreSQL  

SQL

  • Comparison of relational database management systems
  • Firebird
  • MariaDB (fork from MySQL) (SkySQL) (wp)
  • MySQL
  • PostgreSQL
  • SQLite
  • commands:



    MySQL
    sqlite
    PostgreSQL
    InfluxDB (CLI)
    admin user


    root

    postgres

    default database




    postgres
    CLI (from shell)

    user
    create a user

    • su - postgre
    • createuser --createdb my_user

    delete a user

    dropuser
    database
    create a database

    createdb --username my_user my_db
    delete a database

    dropdb
    statistics



    pg_stat_activity





    pg_cancel_backend

    backup database
    backup one database

    sqlite3 sqlite.db .dump >toto.sql pg_dump [-h <host>] [-p <port>] [-t <table>] my_db [| gzip] >my_db.sql[.gz]

    restore one database


    1. (create users)
    2. createdb [-T template0] my_db
    3. psql [--set ON_ERROR_STOP=on] [--single-transaction] my_db <my_db.sql
    4. or: cat my_db.sql.gz | gunzip | psql my_db

    backup one database (custom dump format)


    pg_dump -Fc my_db >my_db.sql

    restore one database (cuustom dump format)


    pg_restore -d my_db my_db.sql
    backup cluster
    backup all databases in cluster


    pg_dumpall >my_cluster.sql
    backup cluster structure only


    pg_dumpall --globals-only >my_cluster.sql
    restore cluster


    psql -f my_cluster.sql postgres cat my_cluster.sql.gz | gunzip | psql postgres

    connection
    connection to server mysql -u my_user -p sqlite3 toto.db
    • psql --username my_user [-h <host>] [-p <port>] my_db
    • su postgres -c "/usr/pgsql-9.5/bin/psql"
    influx -precision rfc3339
    commands
    (once connection has been established)
    general
    help

    .help
    \?

    info


    \conninfo

    database
    show databases
    show databases;
    \l

    use database
    use toto_db
    \c toto_db

    show tables
    show tables; .tables
    .schema
    \dt

    schema of a table
    desc table_name; .schema table_name
    \d table_name


    users


    \du
    SHOW USERS

    processes
    show processlist;




    max connections
    SHOW VARIABLES LIKE 'max_connections';




    SQL

    InfluxQL

    database create database CREATE DATABASE toto_db;

    CREATE DATABASE mydb


    list of databases


    SHOW DATABASES


    use a database


    USE mydb


    insert a value


    INSERT <measurement>[,<tag-key>=<tag-value>...] <field-key>=<field-value>[,<field2-key>=<field2-value>...] [unix-nano-timestamp]


    retrieve values


    SELECT ... FROM ... [WHERE ...] [LIMIT ...]
  • SQL Language (wp)
    • MySQL 5.7 Reference Manual
    • SQL Tutorial (Zentut)
    • Creation of a database
      • CREATE DATABASE toto_db;
    • Deletion of a database
      • DROP DATABASE toto_db;
    • Creation of a user
      • CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
      • At the same time of database creation:
        • CREATE DATABASE IF NOT EXISTS mydatabase_db;
          GRANT ALL ON mydatabase_db.* TO 'mydatabase_user'@'%' IDENTIFIED BY '...';
          GRANT ALL ON mydatabase_db.* TO 'mydatabase_user'@'localhost' IDENTIFIED BY '...'; FLUSH PRIVILEGES;
    • Esborra una fila en una taula.
    • Afegeix una columna (camp) a una taula (ALTER, NOT NULL):
      • ALTER TABLE toto_table ADD COLUMN toto_field varchar(10) NOT NULL
    • Esborra totes les files d'una taula:
      • TRUNCATE TABLE toto_table;
    • Esborra una taula de la base de dades:
      • DROP TABLE toto_table;
    • Afegeix un element a una taula
      • INSERT INTO nom_taula(columna1, columna2)
        VALUES ('first value_1', 'second_value_1'),
               ('first_value_2','second_value_2');
    • Mostra el contingut d'una taula
      • SELECT * FROM toto_table;
    • Subqueries
      • Correlated subquery (wp)
      • CREATE VIEW toto1_view AS
            SELECT id AS toto1ID
                FROM toto1
                WHERE tempsCreacio < CURRENT_TIMESTAMP - interval '20' minute;

        SELECT *
            FROM toto2 NATURAL JOIN toto1_view
            WHERE toto2.id = toto1ID;

      • DELETE toto2
            FROM toto2 INNER JOIN toto1_view
            WHERE toto2.id = toto1ID;
      • DROP VIEW toto1_view;

NoSQL (document-oriented)

Replicació / Replication

Cerca / Search

Disseny / Design

MongoDB

MySQL / MariaDB

Oracle

PostgreSQL

  • Installation
    • Mageia
      • urpmi postgresql9.4-server
      • systemctl enable postgresql.service
      • systemctl start postgresql.service
    • CentOS
      • YUM installation
        • yum install https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm
        • yum install postgresql94-server postgresql94-contrib
        • /usr/pgsql-9.4/bin/postgresql94-setup initdb
        • systemctl enable postgresql-9.4.service
        • systemctl start postgresql-9.4.service
        • systemctl status postgresql-9.4.service
      • version from CentOS original repository (9.2):
        • yum install postgresql-server
        • postgresql-setup initdb
        • systemctl enable postgresql.service
        • systemctl start postgresql.service

    • repo
      version
      bin dir
      config dir
      database dir
      service
      CentOS
      original repo
      9.2
      /bin/
      /var/lib/pgsql/data/
      /var/lib/pgsql/data/base/ systemctl start postgresql.service
      postgresql.org
      9.4
      /usr/pgsql-9.4/bin/
      /var/lib/pgsql/9.4/data/ /var/lib/pgsql/9.4/data/base/ systemctl start postgresql-9.4.service
      Mageia
      distribution
      9.4
      /usr/bin/
      /var/lib/pgsql/data/
      /var/lib/pgsql/data/base/ systemctl start postgresql.service
      Ubuntu
      distribution
      9.3

      /etc/postgresql/9.3/main/
      /var/lib/postgresql/9.3/main/base/ sudo service postgresql start
    • Get running version:
      • # pgsql_version=$(su postgres -c "psql -c 'SELECT version()'" | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')
      • This version will not work inside a root crontab: "sudo: sorry, you must have a tty to run sudo"
        • $ psql_version=$(sudo -u postgres psql postgres -c 'SELECT version()' | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')
  • Setup
    • authentication
      • What is the Default Password for PostgreSQL?
      • Authentication Methods
      • /var/lib/pgsql/[<version>]/data/pg_hba.conf
        • password check (sent in md5):
          • host    all             all             127.0.0.1/32            md5
        • no password check:
          • host    all             all             127.0.0.1/32            trust
      • Fabric
        • from fabric.contrib.files import sed

          def postgresql_install():
              ...
              # spaces are important
              sed('/var/lib/pgsql/data/pg_hba.conf',
                  'host    all             all             127.0.0.1/32            ident',
                  'host    all             all             127.0.0.1/32            md5',
                  use_sudo=True )
      • Django (GIS)
        • DATABASES = {
              'default': {
                  'ENGINE': 'django.contrib.gis.db.backends.postgis',
                  'NAME': '...',
                  'USER': '...',
                  'PASSWORD': open(os.path.join(BASE_DIR, 'db_p.txt'),'r').read().strip(),
                  'HOST': '127.0.0.1',
                  'PORT': '',                      # Set to empty string for default.
              }
          }
    • accessible from other computers (e.g.: 172.31.0.0/16)
      • /var/lib/pgsql/data/postgresql.conf
        • listen_addresses = '*'
      • /var/lib/pgsql/data/pg_hba.conf
        • host    all             all             172.31.0.0/16           md5
      • systemctl restart postgresql.service
      • open firewall: TCP 5432
    • create a superuser named 'my_su'
      • sudo su - postgres
      • createuser --superuser my_su
      • exit
    • create a user named 'geo_user', which can create databases
      • bash
        • Mageia
          • option 1: password specified interactively
            • createuser --username postgres --createdb geo_user
          • option 2: specified password
            • psql --username postgres --command="CREATE USER geo_user WITH PASSWORD 'xxxx' CREATEDB;"
        • CentOS
          • as, by default, access is "ident", running commands as master_user (postgres) will have superuser access
          • sudo su - postgres
          • option 1: password specified interactively
            • createuser --createdb geo_user
          • option 2: specified password
            • psql --command="CREATE USER geo_user WITH PASSWORD 'xxxx' CREATEDB;"
          • exit
      • Fabric
        • env.db_user = 'geo_user'
          env.postgresql_master_user = 'postgres'
          put('db_p.txt','/tmp')
          sudo('psql --command "CREATE USER %(db_user)s WITH PASSWORD \'$(cat /tmp/db_p.txt)\' CREATEDB;"' % env, user='%(postgresql_master_user)s' % env)
    • create a database as user geo_user
      • optionally, to avoid having to specify a password, create the file:
        • echo '127.0.0.1:5432:postgres:geo_user:xxxx' > ~/.pgpass
        • chmod 600 ~/.pgpass
      • createdb -h 127.0.0.1 --username=geo_user geodjango

    • cli tools
      psql
      create a superuser
      createuser --superuser my_su
      create a user that can create databases
      createuser [-h db_host] --username master_user --createdb my_user psql [-h db_host] --username master_user --command="CREATE USER my_user WITH PASSWORD 'xxxx' CREATEDB;"
      create a database
      createdb [-h db_host] --username=my_user my_database psql [-h db_host] --username my_user --command="CREATE DATABASE my_user;"
      NOTES:
      • usually, master_user is postgres
      • in previous examples, my_user was geo_user
      • if not specified, db_host is 127.0.0.1
    • check the connectivity to the database
      • optionally, to avoid having to specify a password, modify the file:
        • echo '127.0.0.1:5432:geodjango:geo_user:xxxx' >> ~/.pgpass
      • psql -h 127.0.0.1 --username=geo_user geodjango
    • remove a user
      • sudo su - postgres
      • dropuser geo_user
      • exit
    • completely remove database server data:
      • sudo rm -rf /var/lib/pgsql/data
      • then you can run "postgresql-setup initdb" again: it will create and populate /var/lib/pgsql/data
    • 9.26. System Administration Functions
      • list of active connections:
        • SELECT * FROM pg_stat_activity;
  • PostGIS
    • GeoDjango
    • Installation
      • Mageia
        • Mageia 7
          • urpmi postgis
        • On Mageia 6, server is version 9.6, but package postgis (2.2) has been compiled with version  9.5
          • ERROR:  incompatible library "/usr/lib64/postgresql/postgis-2.2.so": version mismatch
            DETAIL:  Server is version 9.6, library is version 9.5.
          • Installer Ekylibre v2 sous Mageia 6
          • Solució / Solution
        • Mageia <6
          • urpmi postgis
      • CentOS
        • yum install postgis
      • Only client
        • when server is at e.g. AWS RDS, and we need to run manage.py migrate
        • CentOS
          • yum install geos gdal
    • Setup
      • Installing PostGIS (GeoDjango)
      • add postgis extension to the database (used user must have root privileges)
        • psql --username postgres geodjango --command "CREATE EXTENSION postgis;"
      • Problemes / Problems
        • ERROR:  could not load library "/usr/lib64/postgresql/postgis-2.5.so": /usr/lib64/postgresql/postgis-2.5.so: undefined symbol: SearchSysCache3
          • migration from an older version
    • Get version
      • \c my_database;
        SELECT postgis_full_version();
    • Upgrade
  • Backup
    • SQL Dump
      • Install
        • CentOS
          • sudo yum install postgresql96-contrib
          • sudo yum install holland-pgdump
      • Usage
        • pg_dump
        • pg_dumpall
          • $ sudo su - postgres -c "/usr/pgsql-${pgsql_version}/bin/pg_dumpall | gzip > /tmp/db_dump_$(date '+%Y%m%d_%H%M').sql.gz"
          • # su postgres -c "/usr/pgsql-${pgsql_version}/bin/pg_dumpall | gzip > /tmp/db_dump_$(date '+%Y%m%d_%H%M').sql.gz"
          • version-aware:
            • # get version of running postgresql
              #pgsql_version=$(sudo -u postgres psql postgres -c 'SELECT version()' | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')
              pgsql_version=$(su postgres -c "psql -c 'SELECT version()'" | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')
              pg_dumpall_bin="/usr/pgsql-${pgsql_version}/bin/pg_dumpall"

              # create dump file
              su postgres -c "${pg_dumpall_bin} | gzip > $dump_path"
        • pgsql (recover)
          • /usr/pgsql-${pgsql_version}/bin/psql -f my_cluster.sql postgres
          • # su postgres -c "cat /abs/path/to/my_cluster.sql.gz | gunzip | /usr/pgsql-${pgsql_version}/bin/psql postgres"
        • pg_recover
  • Upgrade
    • Upgrade PostGIS
    • Upgrading a PostgreSQL Cluster (9.4)
      1. pg_dumpall
      2. pg_upgrade (pg_migrator)
        • PostgreSQL upgrade on CentOS
        • 9.2 -> 9.4
          1. install new postgresql version
            • Linux downloads (Red Hat family)
              • sudo yum install https://download.postgresql.org/pub/repos/yum/...
            • sudo yum install postgresql94-server
            • /usr/pgsql-9.4/bin/postgresql94-setup initdb
          2. install new pg_upgrade
            • sudo yum install postgresql94-contrib
          3. check upgrade
            • sudo su postgres
            • /usr/pgsql-9.4/bin/pg_upgrade --check -b /usr/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.4/data
            • Problemes / Problems
          4. stop old service
            • sudo systemctl stop postgresql.service
          5. run upgrade
            • sudo su postgres
            • /usr/pgsql-9.4/bin/pg_upgrade -b /usr/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.4/data
          6. copy config files
            • cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/9.4/data/postgresql.conf
            • cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/9.4/data/pg_hba.conf
          7. start new service
            • sudo systemctl start postgresql-9.4.service
            • sudo systemctl enable postgresql-9.4.service
          8. analyze new cluster
            • ./analyze_new_cluster.sh
      3. Replication

SQLite

Redis

  • Instal·lació / Installation
    • Mageia
      • urpmi redis
  • Setup
    • sudo systemctl start redis.service

InfluxDB

  • Influx Data
  • Instal·lació / Installation
    • Downloads
      • rpm
        • wget https://dl.influxdata.com/influxdb/releases/influxdb-1.7.9.x86_64.rpm
        • Mageia
          • urpmi influxdb-1.7.9.x86_64.rpm
    • Start
      • sudo systemctl start influxdb.service
    • Docker
      • Official repository:  influxdata / influxdata-docker
      • docker pull influxdb
      • How To Install InfluxDB Telegraf and Grafana on Docker
        1. Installing InfluxDB 1.7.x on Docker
          • Create new system user:
            • sudo useradd -rs /bin/false influxdb
            • user_group=$(cat /etc/passwd | awk -F: '/^influxdb/ {print $3":"$4}')
            • echo ${user_group}
          • Create /etc/influxdb/influxdb.conf (owned by root.root)
            • sudo mkdir -p /etc/influxdb
            • docker run --rm influxdb influxd config | sudo tee /etc/influxdb/influxdb.conf > /dev/null
            • ls -l /etc/influxdb/influxdb.conf
          • Create dir /var/lib/influxdb (owned by influxdb.influxdb)
            • sudo mkdir -p /var/lib/influxdb
            • sudo chown influxdb:influxdb -R /var/lib/influxdb
          • (optional) Create /etc/influxdb/scripts/influxdb-init.iql (owned by root.root)
            • sudo mkdir -p /etc/influxdb/scripts
            • cd /etc/influxdb/scripts
            • sudo nano influxdb-init.iql
              • CREATE DATABASE weather;
                CREATE RETENTION POLICY one_week ON weather DURATION 168h REPLICATION 1 DEFAULT;
          • (optional) Init database with created init file and create a user (--rm option must be specified before --user) (--user option is needed because files in /var/lib/influxdb must be owned by influxdb.influxdb):
            • docker run --rm --user ${user_group} -e INFLUXDB_HTTP_AUTH_ENABLED=true \
                       -e INFLUXDB_ADMIN_USER=admin \
                       -e INFLUXDB_ADMIN_PASSWORD=admin123 \
                       -v /var/lib/influxdb:/var/lib/influxdb \
                       -v /etc/influxdb/scripts:/docker-entrypoint-initdb.d \
                       influxdb /init-influxdb.sh
            • ls -l /var/lib/influxdb
          • Check that no other process is using port 8086:
            • sudo netstat -tulpn | grep 8086
          • Check that no firewall is blocking port 8086
          • Start influxdb service with the right user:
            • docker run -d -p 8086:8086 --user ${user_group} --name=influxdb \
              -v /etc/influxdb/influxdb.conf:/etc/influxdb/influxdb.conf \
              -v /var/lib/influxdb:/var/lib/influxdb \
              influxdb \
              -config /etc/influxdb/influxdb.conf
          • Check that influx is working:
            • sudo netstat -tulpn | grep 8086
            • curl -G http://localhost:8086/query --data-urlencode "q=SHOW DATABASES"
        2. Enabling authentication on InfluxDB for Docker
          • List containers
            • docker container ls
          • Execute bash in container and connect to database
            • docker exec -it influxdb /bin/bash
              • influx
                • SHOW DATABASES
                • SHOW USERS
                • if you did not create an admin user, do it now:
                  • CREATE USER admin WITH PASSWORD 'admin123' WITH ALL PRIVILEGES
                  • SHOW USERS
          • Modify conf file to enable http authentication (credentials will be needed):
            • sudo nano /etc/influxdb/influxdb.conf
              • [http]
                  enabled = true
                  bind-address = ":8086"
                  auth-enabled = true
          • Restart container:
            • docker restart influxdb
          • Connect to database using credentials (bash)
            • docker exec -it influxdb /bin/bash
              • influx -username admin -password admin123
                • SHOW DATABASES
          • Connect to database using credentials (curl)
            • curl -G -u admin:admin123 http://localhost:8086/query --data-urlencode "q=SHOW DATABASES"
        3. Installing Telegraf on Docker
          • Create new user
            • sudo useradd -rs /bin/false telegraf
            • telegraf_user_group=$(getent passwd | awk -F: '/^influxdb/ {print $3":"$4}')
          • Create config dir
            • sudo mkdir -p /etc/telegraf
          • Create config file
            • docker run --rm telegraf telegraf config | sudo tee /etc/telegraf/telegraf.conf > /dev/null
          • Set permissions
            • sudo chown telegraf:telegraf /etc/telegraf/*
          • Modify telegram config file
            • sudo nano /etc/telegraf/telegraf.conf
              • [[outputs.influxdb]]
                  ## HTTP Basic Auth
                  username = "admin"
                  password = "admin123"
          • Run telegraf (same network as influxdb container; /proc points to host)
            • docker run -d --user ${telegraf_user_group} --name=telegraf \
                    --net=container:influxdb \
                    -e HOST_PROC=/host/proc \
                    -v /proc:/host/proc:ro \
                    -v /etc/telegraf/telegraf.conf:/etc/telegraf/telegraf.conf:ro \
                    telegraf
          • Check logs
            • docker container logs -f --since 10m telegraf
          • Check data in influxdb
            • docker exec -it influxdb influx -username admin -password admin123
              • SHOW DATABASES
              • USE telegraf
              • SELECT * FROM cpu WHERE time < now() - 1m
        4. Visualizing Telegraf metrics in Grafana
          • Run grafana
            • docker run -d --name=grafana -p 3000:3000 grafana/grafana
          • Check that grafana is running on port 3000:
            • netstat -tulpn | grep 3000
          • Connect to web interface
            • http://localhost:3000/
              • admin/admin
              • Add data source
                • URL: ("IPv4Address" from docker network inspect bridge | grep influxdb -A 5)
                • Basic Auth: enable
                • Basic Auth Details
                  • User: admin
                  • Password: admin123
                • InfluxDB Details
                  • Database: telegraf
                  • User: admin
                  • Password admin123
                  • HTTP Method: GET
              • + -> Create -> Import
          • ...
      • docker
        • docker run -d --name container-influxdb influxdb
      • docker compose (automatically create a database "iot")
        • script init-influxdb.sh will be executed using the following environment variables:
          • INFLUXDB_HTTP_AUTH_ENABLED
          • INFLUXDB_META_DIR
          • INFLUXDB_ADMIN_USER
          • INFLUXDB_ADMIN_PASSWORD (INFLUXDB_INIT_PWD)
          • INFLUXDB_USER
          • INFLUXDB_USER_PASSWORD
          • INFLUXDB_READ_USER
          • INFLUXDB_READ_USER_PASSWORD
          • INFLUXDB_DB (PRE_CREATE_DB)
        • docker-compose.yml
          • version: '3'

            services:
              influxdb:
                image: influxdb
                container_name: container-influxdb
                restart: always
                environment:
                 -
            INFLUXDB_USER=telegraf      - INFLUXDB_USER_PASSWORD=telegraf
                 - INFLUXDB_DB=sensors
                ports:
                 - "8083:8083"
                 - "8086:8086"
                volumes:
                 - ~/docker/rpi-influxdb/data:/data
        • docker-compose up
  • Structure:
    • time series
      • point: <measurement>[,<tag-key>=<tag-value>...] <field-key>=<field-value>[,<field2-key>=<field2-value>...] [unix-nano-timestamp]
        • Line protocol syntax
        • measurement is like a table
        • examples:
          • temperature,machine=unit42,type=assembly external=25,internal=37 1434067467000000000
          • ...
  • Connect to database
    • influx -precision rfc3339
    • server is running on a docker container:
      • docker exec -it container-influxdb influx -precision rfc3339
    • List databases:
      • SHOW DATABASES
    • Create data:
      • CREATE DATABASE home_db
      • USE home_db
      • INSERT temperature,machine=unit42,type=assembly external=25,internal=37
    • Retrieve data:
      • SELECT * FROM temperature
      • SELECT mean("external") FROM "temperature" WHERE ("machine" = 'unit42') AND time >= now() - 30m GROUP BY time(2s) fill(null)

Telegraf

  • Telegraf
  • Instal·lació / Installation
    • Download
      • rpm
        • wget https://dl.influxdata.com/telegraf/releases/telegraf-1.13.1-1.x86_64.rpm
        • Mageia
          • urpmi telegraf-1.13.1-1.x86_64.rpm
  • Configuració / Setup
    • creeu un fitxer amb la configuració per defecte / create a file with default configuration:
      • telegraf config >telegraf.conf
    • o bé feu servir / or use /etc/telegraf/telegraf.conf
    • modifiqueu-lo, per exemple per a configurar / modify it, e.g. to setup MQTT Mosquitto -> InfluxDB
      • [[inputs.mqtt_consumer]]
          servers = ["tcp://raspberry_pi_ip:1883"]
          topics = [
            "sensors"
          ]
          data_format = "influx"
      • [[outputs.influxdb]]
          urls = ["http://raspberry_pi_ip:8086"]
          database = "sensors"
          skip_database_creation = true
          username = "telegraf"
          password = "telegraf"

http://www.francescpinyol.cat/bd.html
Primera versió: / First version:
Darrera modificació: 16 de febrer de 2020 / Last update: 16th February 2020

Valid HTML 4.01!

Cap a casa / Back home