MySQL – ゆーも

【MySQL】データベースとテーブルの使用容量（サイズ）確認方法

データベース毎の使用容量

SELECT
	table_schema
	, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'size(MB)'
FROM
	information_schema.tables
GROUP BY
	table_schema;

結果

+--------------------+----------+
| table_schema       | size(MB) |
+--------------------+----------+
| db_a               |     77.0 |
| db_b               |     17.3 |
| information_schema |      0.0 |
+--------------------+----------+

テーブル毎の使用容量

# 調べたいデータベースに切り替え
USE db_a

SELECT
	table_name
	, FLOOR((data_length + index_length) / 1024 / 1024) AS 'all(MB)'
	, FLOOR((data_length) / 1024 / 1024) AS 'data(MB)'
	, FLOOR((index_length) / 1024 / 1024) AS 'index(MB)'
	, table_rows AS 'tbl rows'
	, avg_row_length AS 'row avg(B)'
FROM
	information_schema.tables 
WHERE
	table_schema = DATABASE()
ORDER BY
	(data_length + index_length) DESC;

結果

+------------+---------+----------+-----------+----------+------------+
| table_name | all(MB) | data(MB) | index(MB) | tbl rows | row avg(B) |
+------------+---------+----------+-----------+----------+------------+
| table_a    |      16 |        8 |         8 |    15791 |        532 |
| table_b    |       0 |        0 |         0 |      780 |        588 |
+------------+---------+----------+-----------+----------+------------+

all(MB)　テーブル全体のサイズ
data(MB)　データファイルのサイズ
index(MB) インデックスファイルのサイズ
tbl rows レコード件数
row avg(B) 平均レコードサイズ

MySQLでテーブルを指定してDUMP

テーブルのDUMPをすべく、mysqldumpコマンドを実行したところ下記のエラーが発生。

mysqldump -u DB_USER -p DB_NAME DB_TABLE_01 DB_TABLE_02 > mysql_dump.sql

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect

どうやら、CPI の共用レンタルサーバー「シェアードプラン」では、ホストを指定して実行しないとダメらしい。

ということで、ローカルホストの指定を追加して実行したら無事成功。

mysqldump -h 127.0.0.1 -u DB_USER -p DB_NAME DB_TABLE_01 DB_TABLE_02 > mysql_dump.sql

MySQLのWarning（Code 1592）Unsafe statement written to the binary log…BINLOG_FORMAT = STATEMENTの回避方法

MySQLにて、カテゴリなどの階層構造のデータの持ち方を、書籍「SQL アンチパターン」を参考に「経路列挙型」で保持してみたところ、UPDATE にて下記の Warning が発生。
Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

ブログ用にちょっと書き換えましたが、UPDATE 文の内容はだいたい下記のような内容。

UPDATE category SET 
  category_c_path = (
    SELECT temp_cd.category_c_path FROM (
      SELECT category_c_path FROM category WHERE category_i_id = 1
    ) as temp_cd
  ) || LAST_INSERT_ID() || "/"
WHERE category_i_id = LAST_INSERT_ID();

自分自身のテーブルからデータを取得して UPDATE をかけてるので、マスタとスレーブでデータが異なってしまう場合があるよ、ということらしい。

調べてみたところ、BINLOG_FORMAT を MIXED にすれば、危険な関数を使用した場合には ROW（実際に変更された行データを記録）モードになり、それ以外は STATEMENT（実際に実行されたSQLを記録）モードになるとのこと。

危険な関数は使用してないけど、今回のケースでも適用できるのかな？と疑問に思いつつ試してみる。

ということでまずは、SET SESSION で現在の接続だけを MIXED に変更してテスト。

SET SESSION binlog_format = 'MIXED';

# 設定が MIXED に変わっていることを確認
SHOW SESSION VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

# 一応、グローバルは STATEMENT のままなのを確認
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

問題の SQL を実行してみて Warning が発生しないことを確認。
問題がなければ、グローバルの設定も変更。

SET GLOBAL binlog_format = 'MIXED';

あとは、再起動後も設定が保持されるように、my.cnf を編集。

vim /etc/my.cnf

[mysqld]
・・・
# レプリケーションフォーマット
binlog_format = MIXED

これにて設定完了。
my.cnf への記述が間違ってないか心配なら、再起動して確認をしておいた方が無難かな。

service mysqld restart

SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

「SQLアンチパターン」はテーブル設計の仕方など、かなり参考になる良書なのですが、今回のような環境によって Warning が発生するケースがある場合は、注釈あたりで MIXED にするといいよって書いてあると、より親切だな～と思いました。

参考サイト
MySQL :: MySQL 5.1 リファレンスマニュアル :: 5.1.2.1 レプリケーションフォーマットのセッティング

MySQL – BINLOG_FORMAT 関連のエラーログ！ – mk-mode BLOG