Databases

Postgresql

Debugging commands

# login using the postgres user
psql -U postgres
psql -U postgres -p $PG_PORT -h $PG_HOST

# display commands help
postgres-# \?

# display user roles
postgres=# \du
postgres=# SELECT usename AS role_name FROM pg_catalog.pg_user;

# show Postgres configuration file named pg_hba.conf 
postgres=# show hba_file;
# reload the configuration file while Postgres is running
postgres=# SELECT pg_reload_conf();

# list databases
postgres=# \l+
# connect to a database
postgres=# \c database_name
# list schemas
postgres-# \dn+
# list current schema
postgres=# SHOW search_path;
# set a schema
postgres=# SET search_path TO target_schema;
# display tables:
postgres=# \dt
# if want to see schema tables
postgres=# \dt+
# display columns of a table
postgres=# \d+ table_name
# select first row from a table
postgres=# SELECT * FROM table_name LIMIT 1;
# count the records in a table
postgres=# SELECT COUNT(*) FROM table_name;
# select last row from a table (row count -1)
postgres=# SELECT * FROM table_name OFFSET row_number ROWS;

# Stop
pg_ctl stop

# Start
pg_ctl start

MySQL

Take dump

• Take a dump of dbname connecting to hostname: mysqldump -h hostname -P 3306 --hex-blob --single-transaction -u root dbname > /path-to-dump-$(date +"%Y-%m-%d").sql

Errors management

• Access denied for user 'root' (using password: NO)

mysql -u root
# ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
# use mysql
INSERT INTO mysql.user(user,ssl_cipher,x509_issuer,x509_subject,authentication_string,host) VALUES ('root','','','','','%');
SELECT user,authentication_string,plugin,host FROM mysql.user;
SELECT `User`, `Grant_priv` FROM `mysql`.`user` WHERE `User` = 'root';
UPDATE `mysql`.`user` SET `Grant_priv` = 'Y' WHERE `User` = 'root';
FLUSH PRIVILEGES;
# replace dbname with the target database
GRANT ALL PRIVILEGES ON `dbname`.* TO root;
GRANT ALL PRIVILEGES ON `dbname`.* TO 'root'@'%';

• enable utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding) on tablename and column: ALTER TABLE tablename MODIFY column VARCHAR(384) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;