MySQL テーブルスペース肥大化対応

現在、運用・保守しているシステムの本番環境のメンテナンスを実施していると、「innodb_file_per_table」が設定されていないことを発見。メンテナンスついでに通常のibdataファイルを複数表領域に変更する手順。

MySQL接続

view plainprint?

1. # mysql -u root -p -h 127.0.0.1  

# mysql -u root -p -h 127.0.0.1

更新系ロック実行
更新系の処理をロックする(参照系はロックされない)。

view plainprint?

1. mysql> FLUSH TABLES WITH READ LOCK;  

mysql> FLUSH TABLES WITH READ LOCK;

ログからテーブルスペースへ書き込み中の可能性もあるので、「Log sequence number」と「Log flushed up to」の値が同じであることを確認。

view plainprint?

1. mysql> SHOW INNODB STATUS\G  
2. ～中略～  
3. ---  
4. LOG  
5. ---  
6. Log sequence number 642420560012  
7. Log flushed up to   642420560012  
8. Last checkpoint at  642420560012  
9. 0 pending log writes, 0 pending chkp writes  
10. 86747663 log i/o's done, 0.08 log i/o's/second  
11. ～中略～  

mysql> SHOW INNODB STATUS\G
～中略～
---
LOG
---
Log sequence number 642420560012
Log flushed up to   642420560012
Last checkpoint at  642420560012
0 pending log writes, 0 pending chkp writes
86747663 log i/o's done, 0.08 log i/o's/second
～中略～

「SHOW STATUS」を実行して、「Key_blocks_not_flushed」が「0」であることを確認。

view plainprint?

1. mysql> SHOW STATUS LIKE '%Key_blocks_not_flushed%';  
2. +------------------------+-------+  
3. | Variable_name          | Value |  
4. +------------------------+-------+  
5. | Key_blocks_not_flushed | 0     |  
6. +------------------------+-------+  
7. 1 row in set (0.01 sec)  

mysql> SHOW STATUS LIKE '%Key_blocks_not_flushed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

ダンプ
全てのデータベースをダンプする。

view plainprint?

1. # mysqldump -u root -p -h 127.0.0.1 --all-database --master-data=2 --default-character-set=binary --single-transaction --flush-logs --quick | gzip --best -c > ./all_database.sql.gz  

# mysqldump -u root -p -h 127.0.0.1 --all-database --master-data=2 --default-character-set=binary --single-transaction --flush-logs --quick | gzip --best -c > ./all_database.sql.gz

「--all-database」
⇒ 全てのデータベースのからデータをダンプするように設定。
「--master-data=2」
⇒ スレーブのセットアップ用のバイナリーログのファイル名と開始位置を出力するように設定。
「--default-character-set=binary」
⇒ UTF-8のデータベースからのダンプ文字化け回避の為に設定。
「--single-transaction」
⇒ テーブルへの参照＆更新をブロックすることなく、mysqldumpコマンド開始時点のスナップショットを取るように設定。(MyISAMなどでは利用できない)
「--flush-logs」
⇒ バイナリーログのローテーションを行うように設定。
「--quick」
⇒ クエリをバッファせず、stdouに直接ダンプするように設定。

更新系ロック解除

view plainprint?

1. mysql> UNLOCK TABLES;  

mysql> UNLOCK TABLES;

my.cnf変更
my.cnfの設定を変更する。

view plainprint?

1. [mysqld]  
2. innodb_data_file_path=ibdata1:10M:autoextend  
3. innodb_autoextend_increment=64  
4. innodb_file_per_table  

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_autoextend_increment=64
innodb_file_per_table

「innodb_data_file_path = ibdata1:10M:autoextend」
⇒ デフォルト値を設定。
「innodb_autoextend_increment=64」
⇒ 拡張時に64MBの領域を確保するように設定。
「innodb_file_per_table」
⇒ テーブルスペースをテーブル単位で作成するように設定。

MySQL接続

view plainprint?

1. # mysql -u root -p -h 127.0.0.1  

# mysql -u root -p -h 127.0.0.1

アクセス状況確認
他からのアクセスがないことを確認する。

view plainprint?

1. mysql> SHOW PROCESSLIST;  

mysql> SHOW PROCESSLIST;

スレーブ停止

view plainprint?

1. mysql> SLAVE STOP;  

mysql> SLAVE STOP;

MySQL停止

view plainprint?

1. # /etc/init.d/mysqld stop  

# /etc/init.d/mysqld stop

既存データ退避

view plainprint?

1. # mkdir /home/mysql/tmp/data/  
2. # mv /home/mysql/data/ib_logfile0 /home/mysql/tmp/data/ib_logfile0  
3. # mv /home/mysql/data/ib_logfile1 /home/mysql/tmp/data/ib_logfile1  
4. # mv /home/mysql/data/ibdata1 /home/mysql/tmp/data/ibdata1  

# mkdir /home/mysql/tmp/data/
# mv /home/mysql/data/ib_logfile0 /home/mysql/tmp/data/ib_logfile0
# mv /home/mysql/data/ib_logfile1 /home/mysql/tmp/data/ib_logfile1
# mv /home/mysql/data/ibdata1 /home/mysql/tmp/data/ibdata1

MySQL起動

view plainprint?

1. # /etc/init.d/mysqld start  

# /etc/init.d/mysqld start

リストア実行

view plainprint?

1. # mysql -u root -p -h 127.0.0.1 --default-character-set=utf8 < ./all_database.sql  

# mysql -u root -p -h 127.0.0.1 --default-character-set=utf8 < ./all_database.sql

スレーブ設定
ダンプファイルからマスタの情報を取得。

view plainprint?

1. # head -100 ./all_database.sql  
2. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000454', MASTER_LOG_POS=106;  

# head -100 ./all_database.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000454', MASTER_LOG_POS=106;

上記のコマンドで出力されたLOG_FILEとLOG_POSの値を、以下の「CHANGE MASTER TO」の「MASTER_LOG_FILE」、「MASTER_LOG_POS」の各値に設定する。

view plainprint?

1. mysql> CHANGE MASTER TO  
2.     -> MASTER_HOST = 'masterサーバーのIPアドレス',  
3.     -> MASTER_PORT = 3306,  
4.     -> MASTER_USER = 'slaveユーザーのアカウント名',  
5.     -> MASTER_PASSWORD = 'slaveユーザーのパスワード',  
6.     -> MASTER_LOG_FILE = 'mysql-bin.000454',  
7.     -> MASTER_LOG_POS = 106,  
8.     -> MASTER_CONNECT_RETRY = 10;  

mysql> CHANGE MASTER TO
    -> MASTER_HOST = 'masterサーバーのIPアドレス',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'slaveユーザーのアカウント名',
    -> MASTER_PASSWORD = 'slaveユーザーのパスワード',
    -> MASTER_LOG_FILE = 'mysql-bin.000454',
    -> MASTER_LOG_POS = 106,
    -> MASTER_CONNECT_RETRY = 10;

スレーブ開始

view plainprint?

1. mysql> SLAVE START;  

mysql> SLAVE START;

スレーブ状況を確認。

view plainprint?

1. mysql> SHOW SLAVE STATUS\G;  
2. *************************** 1. row ***************************  
3.                Slave_IO_State: Waiting for master to send event  
4.                   Master_Host: masterサーバーのIPアドレス  
5.                   Master_User: slaveユーザーのアカウント名  
6.                   Master_Port: 3306  
7.                 Connect_Retry: 10  
8.               Master_Log_File: mysql-bin.000454  
9.           Read_Master_Log_Pos: 202859032  
10.                Relay_Log_File: mysql-relay-bin.000006  
11.                 Relay_Log_Pos: 160873379  
12.         Relay_Master_Log_File: mysql-bin.000454  
13.              Slave_IO_Running: Yes  
14.             Slave_SQL_Running: Yes  
15.               Replicate_Do_DB:  
16.           Replicate_Ignore_DB: test  
17.            Replicate_Do_Table:  
18.        Replicate_Ignore_Table:  
19.       Replicate_Wild_Do_Table:  
20.   Replicate_Wild_Ignore_Table:  
21.                    Last_Errno: 0  
22.                    Last_Error:  
23.                  Skip_Counter: 0  
24.           Exec_Master_Log_Pos: 202859032  
25.               Relay_Log_Space: 160873534  
26.               Until_Condition: None  
27.                Until_Log_File:  
28.                 Until_Log_Pos: 0  
29.            Master_SSL_Allowed: No  
30.            Master_SSL_CA_File:  
31.            Master_SSL_CA_Path:  
32.               Master_SSL_Cert:  
33.             Master_SSL_Cipher:  
34.                Master_SSL_Key:  
35.         Seconds_Behind_Master: 0  
36. Master_SSL_Verify_Server_Cert: No  
37.                 Last_IO_Errno: 0  
38.                 Last_IO_Error:  
39.                Last_SQL_Errno: 0  
40.                Last_SQL_Error:  
41. 1 row in set (0.00 sec)  
42.   
43. ERROR:  
44. No query specified  

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: masterサーバーのIPアドレス
                  Master_User: slaveユーザーのアカウント名
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000454
          Read_Master_Log_Pos: 202859032
               Relay_Log_File: mysql-relay-bin.000006
                Relay_Log_Pos: 160873379
        Relay_Master_Log_File: mysql-bin.000454
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: test
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 202859032
              Relay_Log_Space: 160873534
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

※また、本ページを作成するのに以下のサイトを参考にしました。
mysqlのibdataが原因でディスクフルになったときに復旧方法
MySQLのibdataファイルを複数表領域に変更する
MySQLの「innodb_buffer_pool_size」と「innodb_log_file_size」の設定
MySQLのInnoDBデータファイル「ibdata1」の最適化
ibdata1 のサイズを減らす手順
拡張され続ける InnoDB のデータファイルのサイズを小さくする方法
MySQLのバックアップと"FLUSH TABLES WITH READ LOCK"についてAdd Star
現場指向のレプリケーション詳説
MySQL innodbでのibdata1肥大問題
InnoDBのログとテーブルスペースの関係
InnoDBデータファイルのサイズを変更するには