MySQLのテーブル一覧を出してデータベースを棚卸しする方法｜SHOW TABLESでサイズ・行数・ストレージエンジンを確認する

この記事のポイント

・SHOW TABLES でデータベース内のテーブル名を一覧表示できる
・SHOW TABLE STATUS でサイズ・行数・ストレージエンジンを一括確認できる
・information_schema.TABLES のSQLで複数DBを横断・ソートして棚卸しできる
・mysql -e オプションでシェルスクリプトから非対話的に実行できる

mysql -u root -p

mysql> USE wordpress;
Database changed

mysql -u root -p wordpress

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.00 sec)

mysql> SHOW TABLES LIKE 'wp_post%';
+----------------------------------+
| Tables_in_wordpress (wp_post%)   |
+----------------------------------+
| wp_postmeta                      |
| wp_posts                         |
+----------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW FULL TABLES FROM wordpress;
+-----------------------+------------+
| Tables_in_wordpress   | Table_type |
+-----------------------+------------+
| wp_comments           | BASE TABLE |
| wp_posts              | BASE TABLE |
| wp_users              | BASE TABLE |
| v_recent_posts        | VIEW       |
+-----------------------+------------+
4 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS FROM wordpress\G
*************************** 1. row ***************************
           Name: wp_posts
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1482
 Avg_row_length: 338
    Data_length: 499712
Max_data_length: 0
   Index_length: 163840
      Data_free: 0
 Auto_increment: 3085
    Create_time: 2024-03-15 09:23:11
    Update_time: 2024-09-01 08:44:19
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> SELECT
    table_name,
    engine,
    table_rows AS approx_rows,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb
FROM information_schema.TABLES
WHERE table_schema = 'wordpress'
ORDER BY total_mb DESC;
+-----------------------+--------+-------------+----------+---------+
| table_name            | engine | approx_rows | total_mb | free_mb |
+-----------------------+--------+-------------+----------+---------+
| wp_posts              | InnoDB |        1482 |     0.63 |    0.00 |
| wp_options            | InnoDB |         523 |     0.59 |    0.00 |
| wp_postmeta           | InnoDB |        5817 |     0.55 |    0.00 |
| wp_comments           | InnoDB |         143 |     0.30 |    0.00 |
| wp_commentmeta        | InnoDB |         214 |     0.09 |    0.00 |
| wp_usermeta           | InnoDB |          82 |     0.08 |    0.00 |
+-----------------------+--------+-------------+----------+---------+
12 rows in set (0.00 sec)

mysql> OPTIMIZE TABLE wp_postmeta;

mysql> SELECT table_name, engine
FROM information_schema.TABLES
WHERE table_schema = 'wordpress'
AND engine = 'MyISAM';
Empty set (0.00 sec)

# MyISAMをInnoDBへ変換（テーブルサイズによっては時間がかかるため、本番は低負荷時に実行）
ALTER TABLE テーブル名 ENGINE=InnoDB;

# 基本形（パスワードをコマンド引数に渡すとhistoryに残るため注意）
mysql -u root -p'パスワード' wordpress -e "SHOW TABLES;"

# ~/.my.cnf を作成（所有者のみ読み取り可能にする）
cat > ~/.my.cnf << 'EOF'
[client]
user=root
password=秘密のパスワード
EOF
chmod 600 ~/.my.cnf

#!/bin/bash
# テーブル数を毎日記録するcron用スクリプトの例
DB_NAME="wordpress"
TABLE_COUNT=$(mysql "$DB_NAME" -e "SHOW TABLES;" | grep -c .)
echo "$(date '+%Y-%m-%d'): ${DB_NAME} テーブル数=${TABLE_COUNT}" >> /var/log/mysql-table-count.log

mysql> USE nonexist;
ERROR 1049 (42000): Unknown database 'nonexist'

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.00 sec)

mysql> SHOW TABLES;
ERROR 1044 (42000): Access denied for user 'app_user'@'localhost' to database 'mysql'

# app_user に wordpress DBのSELECT権限を付与する例
GRANT SELECT ON wordpress.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;