43 lines
2.2 KiB
Plaintext
43 lines
2.2 KiB
Plaintext
|
|
Cosas de mysql:
|
|
mysql -u root
|
|
CREATE DATABASE IF NOT EXISTS testdb /*DEFAULT CHARACTER SET utf8mb4*/;
|
|
DROP DATABASE testdb;
|
|
SHOW DATABASES;
|
|
SHOW TABLES FROM database;
|
|
SHOW COLUMNS FROM table.database;
|
|
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "testdb";
|
|
CREATE USER 'userdb'@'localhost' IDENTIFIED BY 'password';
|
|
DROP USER 'userdb'@'localhost';
|
|
UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE USER='user' AND Host="host";
|
|
UPDATE mysql.user SET Password='cadena_larga_como_la_que_se_ve_en_el_select_de_abajo' WHERE USER='user' AND Host="host";
|
|
SELECT user,password,host FROM mysql.user;
|
|
SHOW GRANTS FOR 'Usuario'@'localhost';
|
|
SHOW processlist;
|
|
SHOW status like 'Threads%';
|
|
--- Threads_cached. Es el número de procesos cacheados actualmente.
|
|
--- Threads_connected. Número de conexiones activas actualmente.
|
|
--- Threads_create. Las conexiones que se han creado hasta el momento.
|
|
--- Threads_running. Las que se están ejecutando actualmente.
|
|
|
|
|
|
Cosas de postgresql:
|
|
Ver las bbdd:
|
|
SELECT pg_database.datname FROM pg_database;
|
|
Conectarse a una bbdd:
|
|
\c database_name;
|
|
Ver tablas de una bbdd:
|
|
\dt;
|
|
Consultar el tamaño de las bases de datos:
|
|
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
|
|
Consultar el Top10 de las tablas de una base de datos que más espacio consumen:
|
|
SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
|
|
Consultar el tamaño de todos los objetos:
|
|
SELECT relname AS objectname, relkind AS objecttype, reltuples AS «#entries», pg_size_pretty(relpages::bigint*8*1024) AS size FROM pg_class ORDER BY relpages DESC;
|
|
Esta última consulta muestra 4 campos:
|
|
objectname: el nombre del objeto.
|
|
objecttype: r = tabla, i = índice, S = secuencia, v = vista, c = tipo compuesto, t = tabla TOAST.
|
|
#entries: el número de entradas en el objeto (p.ej. filas)
|
|
size: el tamaño del objeto.
|
|
|