Obtener información de una base de datos con mysqlshow
Autor: Luciano | Categoría: Bases de Datos, MySQL
En esta publicación, veremos el uso de la aplicación de línea de comandos mysqlshow para ver las bases de datos, tablas, columnas e información de índices de una base de datos MySQL mediante nueve ejemplos.
Los parámetros comunes, para cada uno de los ejemplos, serán:
- -h <host>: localhost, ip o nombre de dominio del servidor.
- -u <user>: nombre de usuario
- -p <password>: contraseña.
Para el caso de la contraseña, puedes ingresar la contraseña inmediatamente después del -p (muy util para cuando utilizamos mysqlshow dentro de un script) o bien, solamente -p, donde se nos solicitará luego la contraseña.
En nuestros ejemplo, no especificaremos el host, suponemos que ejecutamos mysqlshow en el mismo servidor. No ingresaremos la constraseña luego del parámetro -p, sino que esperaremos a que nos la solicite.
- Mostrar las bases de datos disponibles
mysqlshow -uroot -p
Enter password: ****
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
Enter password: ****
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
- Mostrar todas las tablas de una base de datos
mysqlshow -uroot -p information_schema
Enter password: ****
Database: information_schema
+---------------------------------------+
| Tables |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
Enter password: ****
Database: information_schema
+---------------------------------------+
| Tables |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
- Mostrar todas las tablas, con el número de columnas, de una base de datos
mysqlshow -v -uroot -p information_schema
Enter password: ****
Database: information_schema
+---------------------------------------+----------+
| Tables | Columns |
+---------------------------------------+----------+
| CHARACTER_SETS | 4 |
| COLLATIONS | 6 |
| COLLATION_CHARACTER_SET_APPLICABILITY | 2 |
| COLUMNS | 19 |
| COLUMN_PRIVILEGES | 7 |
| KEY_COLUMN_USAGE | 12 |
| PROFILING | 18 |
| ROUTINES | 20 |
| SCHEMATA | 5 |
| SCHEMA_PRIVILEGES | 5 |
| STATISTICS | 15 |
| TABLES | 21 |
| TABLE_CONSTRAINTS | 6 |
| TABLE_PRIVILEGES | 6 |
| TRIGGERS | 19 |
| USER_PRIVILEGES | 4 |
| VIEWS | 8 |
+---------------------------------------+----------+
17 rows in set.
Enter password: ****
Database: information_schema
+---------------------------------------+----------+
| Tables | Columns |
+---------------------------------------+----------+
| CHARACTER_SETS | 4 |
| COLLATIONS | 6 |
| COLLATION_CHARACTER_SET_APPLICABILITY | 2 |
| COLUMNS | 19 |
| COLUMN_PRIVILEGES | 7 |
| KEY_COLUMN_USAGE | 12 |
| PROFILING | 18 |
| ROUTINES | 20 |
| SCHEMATA | 5 |
| SCHEMA_PRIVILEGES | 5 |
| STATISTICS | 15 |
| TABLES | 21 |
| TABLE_CONSTRAINTS | 6 |
| TABLE_PRIVILEGES | 6 |
| TRIGGERS | 19 |
| USER_PRIVILEGES | 4 |
| VIEWS | 8 |
+---------------------------------------+----------+
17 rows in set.
- Mostrar todas las tablas, con el número de columnas y filas, de una base de datos
mysqlshow -vv -uroot -p information_schema
Enter password: ****
Database: information_schema
+---------------------------------------+----------+------------+
| Tables | Columns | Total Rows |
+---------------------------------------+----------+------------+
| CHARACTER_SETS | 4 | 36 |
| COLLATIONS | 6 | 126 |
| COLLATION_CHARACTER_SET_APPLICABILITY | 2 | 126 |
| COLUMNS | 19 | 775 |
| COLUMN_PRIVILEGES | 7 | 0 |
| KEY_COLUMN_USAGE | 12 | 147 |
| PROFILING | 18 | 0 |
| ROUTINES | 20 | 0 |
| SCHEMATA | 5 | 4 |
| SCHEMA_PRIVILEGES | 5 | 0 |
| STATISTICS | 15 | 270 |
| TABLES | 21 | 101 |
| TABLE_CONSTRAINTS | 6 | 102 |
| TABLE_PRIVILEGES | 6 | 0 |
| TRIGGERS | 19 | 0 |
| USER_PRIVILEGES | 4 | 75 |
| VIEWS | 8 | 3 |
+---------------------------------------+----------+------------+
17 rows in set.
Enter password: ****
Database: information_schema
+---------------------------------------+----------+------------+
| Tables | Columns | Total Rows |
+---------------------------------------+----------+------------+
| CHARACTER_SETS | 4 | 36 |
| COLLATIONS | 6 | 126 |
| COLLATION_CHARACTER_SET_APPLICABILITY | 2 | 126 |
| COLUMNS | 19 | 775 |
| COLUMN_PRIVILEGES | 7 | 0 |
| KEY_COLUMN_USAGE | 12 | 147 |
| PROFILING | 18 | 0 |
| ROUTINES | 20 | 0 |
| SCHEMATA | 5 | 4 |
| SCHEMA_PRIVILEGES | 5 | 0 |
| STATISTICS | 15 | 270 |
| TABLES | 21 | 101 |
| TABLE_CONSTRAINTS | 6 | 102 |
| TABLE_PRIVILEGES | 6 | 0 |
| TRIGGERS | 19 | 0 |
| USER_PRIVILEGES | 4 | 75 |
| VIEWS | 8 | 3 |
+---------------------------------------+----------+------------+
17 rows in set.
- Mostrar todas las columnas de una tabla
mysqlshow -uroot -p information_schema triggers
Enter password: ****
Database: information_schema Table: triggers
+----------------------------+--------------+-----------------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------------------------+--------------+-----------------+------+-----+---------+-------+------------+---------+
| TRIGGER_CATALOG | varchar(512) | utf8_general_ci | YES | | | | select | |
| TRIGGER_SCHEMA | varchar(64) | utf8_general_ci | NO | | | | select | |
| TRIGGER_NAME | varchar(64) | utf8_general_ci | NO | | | | select | |
| EVENT_MANIPULATION | varchar(6) | utf8_general_ci | NO | | | | select | |
| EVENT_OBJECT_CATALOG | varchar(512) | utf8_general_ci | YES | | | | select | |
| EVENT_OBJECT_SCHEMA | varchar(64) | utf8_general_ci | NO | | | | select | |
| EVENT_OBJECT_TABLE | varchar(64) | utf8_general_ci | NO | | | | select | |
| ACTION_ORDER | bigint(4) | | NO | | 0 | | select | |
| ACTION_CONDITION | longtext | utf8_general_ci | YES | | | | select | |
| ACTION_STATEMENT | longtext | utf8_general_ci | NO | | | | select | |
| ACTION_ORIENTATION | varchar(9) | utf8_general_ci | NO | | | | select | |
| ACTION_TIMING | varchar(6) | utf8_general_ci | NO | | | | select | |
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | utf8_general_ci | YES | | | | select | |
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | utf8_general_ci | YES | | | | select | |
| ACTION_REFERENCE_OLD_ROW | varchar(3) | utf8_general_ci | NO | | | | select | |
| ACTION_REFERENCE_NEW_ROW | varchar(3) | utf8_general_ci | NO | | | | select | |
| CREATED | datetime | | YES | | | | select | |
| SQL_MODE | longtext | utf8_general_ci | NO | | | | select | |
| DEFINER | longtext | utf8_general_ci | NO | | | | select | |
+----------------------------+--------------+-----------------+------+-----+---------+-------+------------+---------+
Enter password: ****
Database: information_schema Table: triggers
+----------------------------+--------------+-----------------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------------------------+--------------+-----------------+------+-----+---------+-------+------------+---------+
| TRIGGER_CATALOG | varchar(512) | utf8_general_ci | YES | | | | select | |
| TRIGGER_SCHEMA | varchar(64) | utf8_general_ci | NO | | | | select | |
| TRIGGER_NAME | varchar(64) | utf8_general_ci | NO | | | | select | |
| EVENT_MANIPULATION | varchar(6) | utf8_general_ci | NO | | | | select | |
| EVENT_OBJECT_CATALOG | varchar(512) | utf8_general_ci | YES | | | | select | |
| EVENT_OBJECT_SCHEMA | varchar(64) | utf8_general_ci | NO | | | | select | |
| EVENT_OBJECT_TABLE | varchar(64) | utf8_general_ci | NO | | | | select | |
| ACTION_ORDER | bigint(4) | | NO | | 0 | | select | |
| ACTION_CONDITION | longtext | utf8_general_ci | YES | | | | select | |
| ACTION_STATEMENT | longtext | utf8_general_ci | NO | | | | select | |
| ACTION_ORIENTATION | varchar(9) | utf8_general_ci | NO | | | | select | |
| ACTION_TIMING | varchar(6) | utf8_general_ci | NO | | | | select | |
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | utf8_general_ci | YES | | | | select | |
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | utf8_general_ci | YES | | | | select | |
| ACTION_REFERENCE_OLD_ROW | varchar(3) | utf8_general_ci | NO | | | | select | |
| ACTION_REFERENCE_NEW_ROW | varchar(3) | utf8_general_ci | NO | | | | select | |
| CREATED | datetime | | YES | | | | select | |
| SQL_MODE | longtext | utf8_general_ci | NO | | | | select | |
| DEFINER | longtext | utf8_general_ci | NO | | | | select | |
+----------------------------+--------------+-----------------+------+-----+---------+-------+------------+---------+
- Mostrar el detalle de una columna específica de una tabla
mysqlshow -uroot -p information_schema triggers trigger_name
Enter password: ****
Database: information_schema Table: triggers Wildcard: trigger_name
+--------------+-------------+-----------------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------+-------------+-----------------+------+-----+---------+-------+------------+---------+
| TRIGGER_NAME | varchar(64) | utf8_general_ci | NO | | | | select | |
+--------------+-------------+-----------------+------+-----+---------+-------+------------+---------+
Enter password: ****
Database: information_schema Table: triggers Wildcard: trigger_name
+--------------+-------------+-----------------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------+-------------+-----------------+------+-----+---------+-------+------------+---------+
| TRIGGER_NAME | varchar(64) | utf8_general_ci | NO | | | | select | |
+--------------+-------------+-----------------+------+-----+---------+-------+------------+---------+
- Mostrar toda la metadata de una tabla
mysqlshow -i -uroot -p information_schema triggers
Enter password: ****
Database: information_schema Wildcard: triggers
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| TRIGGERS | MyISAM | 0 | Dynamic | | 0 | 0 | 281474976710655 | 1024 | 0 | | 2008-08-17 18:25:01 | 2008-08-17 18:25:02 | | utf8_general_ci | | max_rows=3108 | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
Enter password: ****
Database: information_schema Wildcard: triggers
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| TRIGGERS | MyISAM | 0 | Dynamic | | 0 | 0 | 281474976710655 | 1024 | 0 | | 2008-08-17 18:25:01 | 2008-08-17 18:25:02 | | utf8_general_ci | | max_rows=3108 | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
- Mostrar los índices y las columnas de una tabla
mysqlshow -k -uroot -p mysql host
Enter password: ****
Database: mysql Table: host
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Host | char(60) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| Db | char(64) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| Select_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Insert_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Update_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Delete_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Drop_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Grant_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| References_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Index_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Alter_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_tmp_table_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Lock_tables_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_view_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Show_view_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_routine_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Alter_routine_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Execute_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| host | 0 | PRIMARY | 1 | Host | A | | | | | BTREE | |
| host | 0 | PRIMARY | 2 | Db | A | 0 | | | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Enter password: ****
Database: mysql Table: host
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Host | char(60) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| Db | char(64) | utf8_bin | NO | PRI | | | select,insert,update,references | |
| Select_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Insert_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Update_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Delete_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Drop_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Grant_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| References_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Index_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Alter_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_tmp_table_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Lock_tables_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_view_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Show_view_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Create_routine_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Alter_routine_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
| Execute_priv | enum('N','Y') | utf8_general_ci | NO | | N | | select,insert,update,references | |
+-----------------------+---------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| host | 0 | PRIMARY | 1 | Host | A | | | | | BTREE | |
| host | 0 | PRIMARY | 2 | Db | A | 0 | | | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- Mostrar los índices pero no las columna de una tabla
mysqlshow -k -uroot -p mysql host columna_que_no_existe
Enter password: ****
Database: mysql Table: host Wildcard: columna_que_no_existe
+-------+------+-----------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| host | 0 | PRIMARY | 1 | Host | A | | | | | BTREE | |
| host | 0 | PRIMARY | 2 | Db | A | 0 | | | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Enter password: ****
Database: mysql Table: host Wildcard: columna_que_no_existe
+-------+------+-----------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| host | 0 | PRIMARY | 1 | Host | A | | | | | BTREE | |
| host | 0 | PRIMARY | 2 | Db | A | 0 | | | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Vía: The Geek Stuff
Suscripción:
Suscribete al feed aquí
Búsqueda personalizada
Licencia de uso
Usuarios
Comentarios: