MySQLでDB情報とかテーブル情報とかユーザ情報とかを取得するコマンド
ふと使おうとしたときに忘れてしまうコマンドたち。
データベース名の取得
mysql> SHOW DATABASES;
実行結果
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | hogehoge | | test | +--------------------+ 4 rows in set (0.00 sec)
テーブル名の取得
mysql> SHOW TABLES;
実行結果
mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | KEY_COLUMN_USAGE | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | STATISTICS | | TABLES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +---------------------------------------+ 16 rows in set (0.00 sec)
テーブル構造の取得
mysql> DESCRIBE テーブル名;
実行結果
mysql> DESCRIBE COLUMNS; +--------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(21) | NO | | 0 | | | COLUMN_DEFAULT | varchar(64) | YES | | NULL | | | IS_NULLABLE | varchar(3) | NO | | | | | DATA_TYPE | varchar(64) | NO | | | | | CHARACTER_MAXIMUM_LENGTH | bigint(21) | YES | | NULL | | | CHARACTER_OCTET_LENGTH | bigint(21) | YES | | NULL | | | NUMERIC_PRECISION | bigint(21) | YES | | NULL | | | NUMERIC_SCALE | bigint(21) | YES | | NULL | | | CHARACTER_SET_NAME | varchar(64) | YES | | NULL | | | COLLATION_NAME | varchar(64) | YES | | NULL | | | COLUMN_TYPE | longtext | NO | | | | | COLUMN_KEY | varchar(3) | NO | | | | | EXTRA | varchar(20) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(255) | NO | | | | +--------------------------+--------------+------+-----+---------+-------+ 19 rows in set (0.01 sec)
ユーザ権限の取得
mysql> SHOW GRANTS FOR ユーザ名@ホスト名;
実行結果
mysql> SHOW GRANTS FOR root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)
ユーザ情報とか
ユーザ情報は「mysql.user」テーブルに入っている。ユーザ名とパスワードを取得する場合を例として記述する。
mysql> SELECT User, Password FROM mysql.user
テーブルの中身は以下
mysql> DESCRIBE mysql.user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | | | | x509_issuer | blob | NO | | | | | x509_subject | blob | NO | | | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | +-----------------------+-----------------------------------+------+-----+---------+-------+ 37 rows in set (0.00 sec)
