Obtener información de una base de datos con mysqlshow

Publicado por Luciano el 17 de agosto de 2008 en Bases de Datos, MySQL · Sin comentarios

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 |
+--------------------+

  • 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 |
+---------------------------------------+

  • 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.

  • 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.

  • 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 | |
+----------------------------+--------------+-----------------+------+-----+---------+-------+------------+---------+

  • 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 | |
+--------------+-------------+-----------------+------+-----+---------+-------+------------+---------+

  • 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 | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

  • 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 | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

  • 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 | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Vía: The Geek Stuff

Etiquetas: ,

Anterior Blanquear el password del root Siguiente El destino de los logs en MySQL 5.1
Recomendados para tí
Logo-mysql

junio 2, 2008

Exportar/Importar una base de datos MySQL

Logo-mysql

junio 26, 2008

Crear usuario y asignar permisos en MySQL

mayo 6, 2008

Cambiar INSERT por UPDATE para claves duplicadas

Manual-SQL-en-español

junio 11, 2008

Usar SQL para generar SQL

Deje su comentario

Por favor, ingrese su nombre

Por favor, ingrese un correo-e válido

Por favor, ingrese su mensaje

Etiquetas populares

Linux, Java, MySQL, Ubuntu, Programación, Windows, Bases de Datos, C++, Sistemas Operativos, SQL, Netbeans, UNIX, bash, IDE, Python, shell script, Consola, Debian, Redes, Humor, Comandos, Shell, PHP, Google, Eclipse, USB, Ruby, Wordpress, Código, Oracle

luauf.com 2012

WordPress