2012年11月11日日曜日

データベースが消せない!(ERROR 1010 (HY000): Error dropping database )

データベースを消そうとしたら、次のようなエラーが発生してしまい消せませんでした(; ・`д・´)
mysql> DROP DATABASE データベース名;
ERROR 1010 (HY000): Error dropping database (can't rmdir './データベース名', errno: 39)
仕方ないので強引に/var/lib/mysql/データベース名/のディレクトリを
全部mvで退避させてから実行してみると問題なくSHOW DATABASES一覧から消えました!ワーイヽ(゚∀゚)メ(゚∀゚)メ(゚∀゚)ノワーイ
こんなやり方でも消せるんですね(´・∀・`)ヘー

というか、エラー内容にcan't rmdirって書かれてるという事は、
MySQLもDROP TABLEを実行すると単純にディレクトリをごっそり消してるだけだったり・・・?o(゚Д゚ = ゚Д゚)o キョロキョロ


参考URL

リストアの高速化

データの復旧時やslaveの追加時のセットアップ用にdumpファイルをリストアする際に
なるべく時間をかけずにやれる方法です!m9( ゚Д゚) ドーン!

リストアの性能を上げるために次の項目を一時的に無効化します。
・一般クエリログの無効化
・バイナリーログの無効化
・Double Writeの無効化
・コミット時のディスクフラッシュの無効化

vi /etc/my.cnf
#ダブルライトバッファの無効化
skip_innodb_doublewrite
#slaveでバイナリーログを出力している場合には設定をコメントアウトして無効化にする
#log_slave_updates

# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
■一般ログの無効化
mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | ON                        |
| general_log_file | /data/mysql-log/mysql.log |
+------------------+---------------------------+
2 rows in set (0.02 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             1 |
+---------------+
1 row in set (0.01 sec)

#general_logを0にする(再起動するとmy.cnfの値に戻る)
mysql> SET GLOBAL general_log = 0;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)
■バイナリーログの無効化
mysql> SHOW GLOBAL VARIABLES LIKE '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)

#sql_log_binを0にする(再起動するとmy.cnfの値に戻る)
mysql> SET GLOBAL sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | OFF   |
+---------------------------------+-------+
3 rows in set (0.00 sec)
■コミットと同時にログファイルの内容をディスクへフラッシュを無効化
mysql> SELECT @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

#innodb_flush_log_at_trx_commitを2にする(再起動するとmy.cnfの値に戻る)
mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                2 |
+----------------------------------+
1 row in set (0.00 sec)
ダンプファイルが大きい場合には次のようなエラーが出る場合があります。
$ mysql -u root -p --default-character-set=utf8 データベース名 < /home/admin/データベース名.2012-11-04_18-00-01.sql
Enter password:
ERROR 2006 (HY000) at line 7944: MySQL server has gone away
その場合には、次の設定値を大きくしてから試してみてください。
#起動中のまま32MBに設定を変更する場合(再起動するとmy.cnfの値に戻る)
mysql> SET GLOBAL max_allowed_packe=32*1024*1024;

#my.cnfの設定で行う場合
#[mysqld]セクションのmax_allowed_packetのサイズを大きくする。
max_allowed_packet = 32M

リストアが終わったら各項目を元に戻すこと。
クラッシュ時にデータが破損してしまう可能性が高い設定になってしまっているため

他にも高速化の方法として、ログファイルサイズの調整、自動拡張の抑制、パーティショニング、
更新のサイズ、CSVストレージエンジンの活用などを行うと高速化することができるそうです。
下記参考URLから確認してみてください!(`・ω・´)ゞビシッ!!

参考URL

テーブルスペース初期化で大きいサイズ指定の際の注意点

テーブルスペースの初期化は大きめに取っておいた方が
自動拡張の回数が減るからI/Oが発生しにくくなってパフォーマンスが(・∀・)イイ!!

と、前の記事やどこかの記事でみたので試してみた際のφ(`д´)メモメモ...
■テーブルスペースを30Gで初期化した場合について

あまり大きいサイズを指定すると、
起動した際にテーブルスペースの初期化で時間がかかってしまって、
MySQLが起動に失敗したように見えるらしいです。

たとえば次の設定を行って起動してみると、
#
innodb_data_file_path = ibdata1:30G:autoextend

# /etc/init.d/mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]
起動できない!!どうしようどうしよう!って慌ててプロセスを見てみると、
# ps axu|grep -i [m]ysql
root     11810  0.1  0.0 106108  1496 pts/1    S    00:37   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql    12691  0.6  4.4 5057200 363608 pts/1  Sl   00:37   0:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data/mysql-log/mysql-error.log --open-files-limit=8192 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
プロセスが残っていて起動している事に・・・ついにMySQLの霊的な何かががががっ:(;゛゜'ω゜'):

ではなくて、MySQLさんがバックグラウンドで何か頑張っている模様ですガン( ゜д゜)ガレ
ただログインを試してみるけどエラーにはなる。
# mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ibdataを見てみると設定したサイズより小さいファイルが作られているけど、
時間が経過すると少しずつ大きくなっている。

# ls -alt /var/lib/mysql/|grep ibdata
-rw-rw---- 1 mysql mysql 1258291200 Nov 10 21:43 ibdata1
# ls -alt /var/lib/mysql/|grep ibdata
-rw-rw---- 1 mysql mysql 2306867200 Nov 10 21:47 ibdata1
# ls -alt /var/lib/mysql/|grep ibdata
-rw-rw---- 1 mysql mysql 2831155200 Nov 10 21:48 ibdata1
# ls -alt /var/lib/mysql/|grep ibdata
-rw-rw---- 1 mysql mysql 4194304000 Nov 10 21:52 ibdata1
# ls -alt /var/lib/mysql/|grep ibdata
-rw-rw---- 1 mysql mysql 5567471616 Nov 10 21:57 ibdata
このまま待っていると、設定したサイズになり完了して、ログインも行えるようになります。
ただ、待っている時間が・・・なんと約2時間ほどかかりました:(;゛゜'ω゜'):
長すぎて寝てしまうところでした(つд⊂)ゴシゴシ

その時のMySQLのログはこちらになります。
121110 20:33:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121110 20:33:33 [Note] Plugin 'FEDERATED' is disabled.
121110 20:33:33 [ERROR] Function 'rpl_semi_sync_master' already exists
121110 20:33:33 [Warning] Couldn't load plugin named 'rpl_semi_sync_master' with soname 'semisync_master.so'.
121110 20:33:33 InnoDB: The InnoDB memory heap is disabled
121110 20:33:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121110 20:33:33 InnoDB: Compressed tables use zlib 1.2.3
121110 20:33:33 InnoDB: Using Linux native AIO
121110 20:33:33 InnoDB: Initializing buffer pool, size = 4.0G
121110 20:33:34 InnoDB: Completed initialization of buffer pool
121110 20:33:34 InnoDB: highest supported file format is Barracuda.
"/data/mysql-log/mysql-error.log" [readonly] 509L, 33378C
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
121111  0:37:20  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
121111  0:40:04  InnoDB: Waiting for the background threads to start
121111  0:40:05 InnoDB: 1.1.8 started; log sequence number 0
121111  0:40:05 [Note] Semi-sync replication initialized for transactions.
121111  0:40:05 [Note] Semi-sync replication enabled on the master.
121111  0:40:06 [Warning] 'user' entry 'root@db03' ignored in --skip-name-resolve mode.
121111  0:40:06 [Warning] 'user' entry '@db03' ignored in --skip-name-resolve mode.
121111  0:40:06 [Warning] 'proxies_priv' entry '@ root@db03' ignored in --skip-name-resolve mode.
121111  0:40:06 [Note] Event Scheduler: Loaded 0 events
121111  0:40:06 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.22-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi
起動時にエラーが起きないようにするには、起動スクリプトのSTARTUPTIMEOUTの値を変更すれば良いかも?
view /etc/init.d/mysqld

# Set timeouts here so they can be overridden from /etc/sysconfig/mysqld
STARTTIMEOUT=120
STOPTIMEOUT=60
MYOPTIONS=
(`・ω・´)ゞビシッ!!

テーブルスペース肥大化の対処方法(ibdata*)

前回テーブルスペースについて調べてみた後に、
運用中のサーバーの設定をやファイルを確認してみたら・・・innodb_file_per_tableが使われていない!!!:(;゙゚'ω゚'):

という事なので、早速設定の方を試してみましたワッショイヽ(゚∀゚)メ(゚∀゚)メ(゚∀゚)ノワッショイ

■手順1.現状のibdata1のサイズの確認をしてみる
# ls -alt /var/lib/mysql/|grep ib
-rw-rw----  1 mysql mysql 277899902976 11月 10 16:16 2012 ibdata1
-rw-rw----  1 mysql mysql      5242880  4月 16 15:06 2012 ib_logfile0
-rw-rw----  1 mysql mysql      5242880  4月  6 12:05 2012 ib_logfile1

277899902976・・・(;゜ Д゜) …!?
277,899,902,976byteっていくつでしたっけ?もちろん277MBですよね?(;゚ Д゚) …!?

チガイマスネ。277GBですね・・・orz

とりあえず、my.cnfにinnodb_file_per_tableだけを追加してやってみた結果。
#設定前のサイズ
# ls -alt /var/lib/mysql/|grep ib
-rw-rw----  1 mysql mysql 277899902976 Nov 10 21:08 ibdata1
-rw-rw----  1 mysql mysql      5242880 Apr 16  2012 ib_logfile0
-rw-rw----  1 mysql mysql      5242880 Apr  6  2012 ib_logfile1
設定直後には/var/lib/mysq/データベース名/に*.ibdファイルが作られていないが、
ALTER TABLE テーブル名 ENGINE InnoDB;

のコマンドを実行すると、ibdファイルが作成される。
# ls -alt /var/lib/mysql/データベース名/|grep ibd
-rw-rw----  1 mysql mysql     163840 Nov 10 20:19 user.ibd
こちらから引用です。
ALTER TABLE 実行中もテーブルからREADが出来るからだ。
テーブルが壊れた場合にはREPAIR TABLEコマンドを使うし、最適化したい場合にはOPTIMIZE TABLEコマンドを使うのだが、
これらのコマンドはWRITEだけでなくREADもブロックしてしまう。従って、メンテナンス中にWRITEは出来なくてもREADだけは可能にしたい、
というような場合には、まずはALTER TABLEを試して見るといいだろう。

ALTER TABLEは操作が正常に完了するまでは元のテーブルに対して一切の変更を加えない。
たとえコマンド実行中にマシンがクラッシュしても、テンポラリテーブルの残骸が残るだけで、元のテーブルは元通りである。 そういう意味では、ALTER TABLEはとても安全な操作であるとも言える。
innodb_file_per_table設定後に作られたこのファイルは
ALTERコマンドを利用するとデフラグを行ってくれるため、
定期的に実行することで*.ibdのサイズは小さくなる。(ibdata1のサイズは変わらない)
# ls -alt /var/lib/mysql/
total 271977488
-rw-rw----  1 mysql mysql 277899902976 Nov 10 22:14 ibdata1
なので、元々あったibdataのサイズは変わらない・・・ガ━━(;゚Д゚)━━ン!!

そこで、ibdata1のサイズを小さくしたい場合には、
ibdata1のサイズを変更した場合には元々あったibdataやib_logfile0を一度消す必要がある。

消すという事は、全データを消すことになるので、
復元する為にデータベースをダンプし、リストアを行う必要があるみたいです。

単純に、my.cnfの設定で、値を変えた場合のエラーは次のような内容でした。
#デフォルトの設定(自動拡張有効で、初期化で10MBの領域を確保する)
innodb_data_file_path = ibdata1:10M:autoextend
↓
#自動拡張有効で、初期化で30Gの領域を確保する
#innodb_data_file_path = ibdata1:30G:autoextend

#エラー内容
121110 21:03:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121110 21:03:24 [Note] Plugin 'FEDERATED' is disabled.
121110 21:03:24 [ERROR] Function 'rpl_semi_sync_master' already exists
121110 21:03:24 [Warning] Couldn't load plugin named 'rpl_semi_sync_master' with soname 'semisync_master.so'.
121110 21:03:24 InnoDB: The InnoDB memory heap is disabled
121110 21:03:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121110 21:03:24 InnoDB: Compressed tables use zlib 1.2.3
121110 21:03:24 InnoDB: Using Linux native AIO
121110 21:03:24 InnoDB: Initializing buffer pool, size = 4.0G
121110 21:03:24 InnoDB: Completed initialization of buffer pool
InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 1966080 pages, max 0 (relevant if non-zero) pages!
121110 21:03:24 InnoDB: Could not open or create data files.
121110 21:03:24 InnoDB: If you tried to add new data files, and it failed here,
121110 21:03:24 InnoDB: you should now edit innodb_data_file_path in my.cnf back
121110 21:03:24 InnoDB: to what it was, and remove the new ibdata files InnoDB created
121110 21:03:24 InnoDB: in this failed attempt. InnoDB only wrote those files full of
121110 21:03:24 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
121110 21:03:24 InnoDB: remove old data files which contain your precious data!
121110 21:03:24 [ERROR] Plugin 'InnoDB' init function returned error.
121110 21:03:24 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
121110 21:03:24 [Note] Semi-sync replication initialized for transactions.
121110 21:03:24 [Note] Semi-sync replication enabled on the master.
121110 21:03:24 [ERROR] Unknown/unsupported storage engine: InnoDB
121110 21:03:24 [ERROR] Aborting

121110 21:03:24 [Note] unregister_replicator OK
121110 21:03:24 [Note] /usr/libexec/mysqld: Shutdown complete

121110 21:03:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

それでは、本題のibdataをリセットしてみます(゚д゚)(。_。)(゚д゚)(。_。) ウンウン

今回は、レプリケーションを既に行っているmaster,slave環境で、
ibdataをリセットしたかったので、レプリケーションの設定を再度やり直す方法にしました。

■手順1.念のためmasterの更新系の処理をロックする(参照系はロックされない)
# FLUSH TABLES WITH READ LOCK;
■手順2.masterのロックとテーブルスペースへの反映が正常に完了しているか確認
こちらから引用。
Log sequence numberは、ログバッファへの更新が行われたトータルのバイト数、
Log flushed up toはWALへの書き込みが行われたバイト数、
Last checkpoint atは最後にチェックポイントが行われたバイト数
innodb_flush_log_at_trx_commit=1ならば、Log sequence numberとLog flushed up toは非常に近い値になる。
ログからテーブルスペースへ書き込み中の可能性もあるので確認する
SHOW ENGINE INNODB STATUSの"Log sequence number"と"Log flushed up to"の値が同じであることを確認する。
#5.5より前のバージョンはSHOW INNODB STATUS\G
SHOW ENGINE INNODB STATUS\G
---
LOG
---
Log sequence number 694320234293
Log flushed up to   694320234293
Last checkpoint at  694320234293
0 pending log writes, 0 pending chkp writes
11390094 log i/o's done, 0.00 log i/o's/second
mysql> show status LIKE '%Key_blocks_not_flushed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
+------------------------+-------+
1 row in set (0.00 sec)
値が一致しない場合、または念のために、
ログファイル内のデータを全てテーブルスペースへ反映させる方法でシャットダウンしておいた方が良いかも?
#
mysql> SELECT @@global.innodb_fast_shutdown;
+-------------------------------+
| @@global.innodb_fast_shutdown |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

#ログファイル内のデータを全てテーブルスペースへ反映させてからシャットダウンする設定に変更
mysql> SET GLOBAL innodb_fast_shutdown=0;

#停止
/etc/init.d/mysqld stop

#起動
/etc/init.d/mysqld start
■手順3.masterサーバーのデータをダンプする
--all-database 全てのデータベースのからデータをdumpする --master-data=2をつけておく。
slaveのセットアップで利用できるバイナリーログのファイル名と開始位置を出力する。 --default-character-set=binary
--single-transaction
InnoDBを利用している場合、テーブルへの参照&更新をブロックすることなく、
mysqldumpコマンド開始時点のスナップショットを取る。(MyISAMなどでは利用できない)
--flush-logs
バイナリーログのローテーションを行う
--quick
#全てのデータベースをダンプする場合
# mysqldump -u root -p --all-database --master-data=2 --default-character-set=binary --single-transaction --flush-logs --quick | gzip --best -c > /data/tmp/all_database_20121111.sql.gz
■手順4.データベースの削除 ダンプでフルバックアップを取ってあるので、DROP DATABASEで全部消しておいた方が良いかも。
消す場合には、mysqlがデフォルトで作成するデータベースは残しておく。
(information_schema、performance、mysql、test)

DROP DATABASEをせずに、手順9のibdata*,ib_logfile*をmvまたはrmでした場合に、
全データは無くなるが、データベースのディレクトリが残ってしまい、リストアがうまく行かない場合がある。 各データベースのdatabaseのディレクトリが残ってしまっている。(*.ibdが残っていた)

そのせいかリストアした際に、次のようなエラーが出てしまった。
ERROR 1005 (HY000) at line 39: Can't create table 'データベース名.access_log' (errno: 1)
テーブルがあるせいなのかと思い、SHOW TABLEで確認してみるけど、
全て消えてしまって無くなっていました。

SHOW DATABASESでは名前があったのでDROP DATABASEを試してみるけど、 次のようなエラーが発生で消せませんでした。
mysql> DROP DATABASE データベース名;
ERROR 1010 (HY000): Error dropping database (can't rmdir './データベース名', errno: 39)
仕方ないので強引に/var/lib/mysql/データベース名/のディレクトリを
全部mvで退避させてから実行してみると問題なくリストアが進みました!(`・ω・´)シャキーン ■手順5.masterの更新系のロック解除とMySQLの停止
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
■手順6.slave側のテーブルスペースへの反映の確認
#5.5より前のバージョンはSHOW INNODB STATUS\G
SHOW ENGINE INNODB STATUS\G
---
LOG
---
Log sequence number 672811663077
Log flushed up to   672811663077
Last checkpoint at  672811663077
0 pending log writes, 0 pending chkp writes
9667 log i/o's done, 0.00 log i/o's/second

mysql> show status LIKE '%Key_blocks_not_flushed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
+------------------------+-------+
1 row in set (0.05 sec)
masterと同様に値が一致しない場合には、手順2の方法でSET GLOBAL innodb_fast_shutdown=0;を利用する。
■手順7.slaveの停止
slaveの停止
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
■手順8.master側のmy.cnfの設定を変更
# vi /etc/my.cnf

#デフォルト値
innodb_data_file_path = ibdata1:10M:autoextend
#拡張時に64MBの領域を確保する
innodb_autoextend_increment=64
#テーブルスペースをテーブル単位で作成する
innodb_file_per_table
■手順9.ibdata*,ib_logfile*を退避させる
# mv /var/lib/mysql/ibdata* /tmp/
# mv /var/lib/mysql/ib_logfile* /tmp/
※innodb_log_group_home_dirを設定している場合には、ib_logfileはその場所にあります。
デフォルトではdatadirで設定した場所になります(datadirのデフォルト設定は/var/lib/mysql/)
■手順10.MySQLを起動させる
# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
■手順11.ibdataが初期化された確認する
# ls -alt /var/lib/mysql/
total 2286160
drwxr-xr-x 11 mysql mysql      12288 Nov 11 12:43 .
-rw-rw----  1 mysql mysql  268435456 Nov 11 12:43 ib_logfile0
-rw-rw----  1 mysql mysql   77594624 Nov 11 12:43 ibdata1
-rw-rw----  1 mysql mysql  268435456 Nov 11 12:43 ib_logfile1
77594624ということは、77,594,624byte。77MBになった(゚∀゚)キタコレ!!
無事に277GBが77MBのダイエットに成功!!これはダイエット本として発売するしかっ(ΦωΦ)フフフ…
■手順12.各テーブルごとに作られる.ibdファイルが作られているか確認する
# ls -alt /var/lib/mysql/データベース名/|grep ibd
total 1988
drwxr-xr-x 11 mysql mysql 12288 Nov 11 12:43 ..
この時点では.ibdファイルは作られていないΣ(゚Д゚ υ) アリャ ※ibdataなどを初期化したのでデータが全部消えてしまっている。 ■手順13.先程ダンプしたファイルをリストアする。
#先程ダンプしたファイルの解凍
# gunzip /data/tmp/all_database_20121111.sql.gz

#リストア
# mysql -u root --default-character-set=utf8 < /data/tmp/all_database_20121111.sql
■手順14.再度*.ibdファイルがあるか確認してみる
# ls -alt /var/lib/mysql/データベース名/|grep ibd
-rw-rw----  1 mysql mysql 3317694464 Nov 11 13:14 *****.ibd
-rw-rw----  1 mysql mysql  234881024 Nov 11 13:14 *****.ibd
-rw-rw----  1 mysql mysql   58720256 Nov 11 13:07 *****.ibd
-rw-rw----  1 mysql mysql     196608 Nov 11 13:06 *****.ibd

キタ――(゚∀゚)――!!
これで、無事にibdataのリセットに成功しました(`・ω・´)シャキーン
後は、定期的にALTER TABLEを行うことでデフラグを解消してくれて、
ibdファイルのサイズは小さくしてくれます。
■手順15.slave側のibdataのリセット。
手順6でslaveの停止をしているので、手順7以降を同様にslaveでも行う。

■手順16.レプリケーションの開始設定
slaveのibdataなどを削除したため、
再度レプリケーションの設定が必要となります。
手順12で利用したダンプファイルからmasterの情報を利用して、 slaveの設定を行います。
#
head -100 /data/tmp/all_database_20121111.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=107;
上記のコマンドで出力されたLOG_FILEとLOG_POSの値を、
CHANGE MASTER TOのMASTER_LOG_FILE、MASTER_LOG_POSの各値に設定する。
#レプリケーションの設定
mysql> CHANGE MASTER TO
    -> MASTER_HOST='masterサーバーのIPアドレス',
    -> MASTER_USER='slaveユーザーのアカウント名',
    -> MASTER_PASSWORD='slaveユーザーのパスワード',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000028',
    -> MASTER_LOG_POS=107,
    -> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)

#同期開始
mysql> SLAVE START;
以上(`・ω・´)ゞビシッ!!
ibdataにあったものが個々のテーブルの*.ibdファイルに分散され、ibdataは小さくなりましたが、
運用を続けているとibdataはやっぱり肥大化してしまうみたいです。
(ibdataファイルにはデータは入らなくなったけど、各*.ibdのメタデータを保存してるため)
ただ、今回の設定をしておくことで、その肥大化の速度を緩やかにすることが出来る!
と言ったことなのかな?
設定の注意点としては、
innodb_file_per_tableを設定してもibdataは必要なので消してはいけません!
innodb_log_group_home_dirを設定してはいけません!!
ド━━━━m9(゚∀゚)━━━━ン!!

参考URL

2012年11月10日土曜日

テーブルスペースとログファイルについて(ibdata*、ib_logfile*)

いつもmysqlの中身を見た時に巨大化しているファイルを良く見かけていましたが、
( ´_ゝ`)フーンっていうぐらいにしか思っていなかったので
ibdata*とib_logfile*について調べてみました(ΦωΦ)フフフ…

簡単な説明をすると、
ibdata*は共有テーブルスペース(全データを管理します。)
ib_logfile*は、ログファイルらしいです。

データは直接テーブルスペースに更新がされるのではなく、
一旦ログファイルに更新内容が書き込まれる。
その後に、テーブルスペースに反映される流れになっているらしい。
テーブルスペースの更新はコストが高いのですぐに反映するのではなく、
ログファイルに書いておくことで、書き込みの性能をあげてるらしい(´・∀・`)ヘー

そのため、InnoDBのデータは、テーブルスペースとログを合わせて完全な情報となるらしい。

↓実際のファイルはこいつらですド━━━━m9(゚∀゚)━━━━ン!!
# ls -alt /var/lib/mysql
合計 14871500
-rw-rw----  1 mysql mysql    5242880 11月  5 01:49 2012 ib_logfile0
-rw-rw----  1 mysql mysql    5242880 11月  5 01:49 2012 ib_logfile1
-rw-rw----  1 mysql mysql 9590276096 11月  5 01:49 2012 ibdata1
それでは、まずはibdataの詳細について!(`・ω・´)シャキーン

ibdataはデフォルトの設定では、全データを1つのファイルで管理している?ので、
運用を続けていくうちにどんどん肥大化していきます。

その原因として、デフォルトの設定のibdataファイルの自動拡張設定があります。
自動拡張の設定は、innodb_data_file_pathでautoextendが記述されていると拡張されてしまう。
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
この設定があると、最大値が無いためHDD100%まで肥大化してします・・・:(;゙゚'ω゚'):

また一度大きくなったibdataのテーブルスペースを小さくする手段としては、
ダンプ後にibdata*のファイルを削除後にリストアするしか方法がないとのことですガ━━(;゜Д゜)━━ン!!

自動拡張が原因なので、自動拡張の上限をつければ良いかと思いきや、
今度は上限に達した場合に、テーブルへの更新が出来なくなるという現象になるらしい・・・orz
HDDの100%になっても出来ませんけどね・・・その際にはMySQLにすらログインできなくなる(´;ω;`)ウッ…

自動拡張無しの設定
[mysqld]
innodb_data_file_path=ibdata1:300G
自動拡張を無くした状態で、テーブルスペースを大きくしたい場合には、
[mysqld]
innodb_data_file_path=ibdata1:300G;/data2/ibdata2:700G
のように記述していかなければいけない。
(ibdata1とibdata2というファイルが作られる?)

そこで、この問題を回避するための手段として、innodb_file_per_tableを設定する方法がある(゚∀゚)キタコレ!!
ただし注意点として次のようなことがあるそうです。
こちらのサイトから引用です
innodb_file_per_tableの設定をしておくと、テーブルごとにテーブルスペースが作成される
拡張子は.ibdである。.ibdファイルは要求に応じて自動拡張するのだが、テーブルを削除すると対応する.ibdファイルも削除されるのである。
つまり、一度テーブルスペースが大きくなってしまってからでもテーブルを削除すればファイルシステムの空き領域を増やすことが可能になるのである。
これは運用上とてもメリットがあるだろう。
テーブルごとにテーブルスペースを作成する場合でも、共有テーブルスペース(ibdata1)が必要になる点に注意しよう。
ここには、各テーブルのディクショナリ情報や ロールバックセグメントなどが格納される。一度に大量のアップデートを行った場合などには大きなロールバックセグメントが必要になる。
その際に共有テーブルスペースが自動拡張しないよう、サイズを決めうちにしておくといいだろう。

ただし、この運用で気をつけないといけないのは共有テーブルスペースを削除してはならない!!ということである。
確かに共有テーブルスペースにテーブルのデータは入っていないが、各.ibdのメタデータを格納しているので、
共有テーブルスペースがないとInnoDBが.ibdファイルを見つけられないのである。
また、他のサーバへ単体の.ibdファイルだけを持っていっても使えない。(無理矢理使う方法はあるけど一般的にはオススメしない。)
つまり、.ibdファイルと共有テーブルスペースはセットなのである。

innodb_file_per_tableを設定しても、ibdataは消しちゃダメ!絶対!ァカンァカン(´゚д゚`)ノ゙ノ゙ってことですね!
あとは、innodb_data_home_dirを明示的に指定する場合には、
innodb_file_per_tableオプションは利用してはいけないらしい。

■データファイルの初期化について
利用するディスク容量が決まっている場合には、最初にデータファイルを初期化しておくと良い。
データファイルは自動拡張しますが、拡張するタイミングでI/Oが発生するため、
最初に領域を確保し自動拡張しないように設定した方が高速になる。
#2TB用の設定
innodb_data_file_path=ibdata1:2000G
データファイルのサイズが大きい場合には初期化の時間にかかってしまうので、
セットアップを早く終わらせた場合には、次のように記述する。
#自動拡張を有効にして、最大値が500G、拡張子時に確保するサイズは64MBになる。
innodb_data_file_path=ibdata1:1G:autoextend:max:500G
innodb_autoextend_increment=64
maxを設定しない場合には、肥大化に気をつけてHDDのサイズを超えないようにする。

実際のテーブルスペースの肥大化の対象方法いついてはこちらの記事に書いてあります(`・ω・´)ゞビシッ!!


参考URL

2012年11月4日日曜日

ウェブ監視(特定のURLの監視方法)

zabbixにはウェブ監視と呼ばれるものがあります。
指定したURLに対して定期的にアクセスを行い、そのレスポンスや速度を計測してくれます(・∀・)イイ!!

早速、設定の方を行ってみたいと思います。
■手順1.上部メニューの「設定」→「ウェブ」をクリックすると、ウェブ監視一覧が表示されます。

■手順2.右上にホストで、ウェブ監視を行いたいホストをプルダウンで選び、「シナリオの作成」を押すと、
シナリオの新規作成画面が表示されます。

■手順3.シナリオの設定画面で次のように入力する。
アプリケーション:WebMonitoring
名前:web01 server
認証:Basic認証(Basic認証を利用しているページにアクセスする場合には指定する)
ユーザ:(Basic認証を利用している場合にはIDを入力する)
パスワード:(Basic認証を利用している場合にはパスワードを入力する)
更新間隔(秒):30
エージェント:その他を選択して「zabbix_web_monitoring」を入力する
ステータス;有効
変数:{test}=test
【アプリケーション】
アイテムのグループ化用の名前になります。
【認証、ユーザ、パスワード】
認証ページなどを監視する場合に必要となる認証方法、ユーザ、パスワードになります。
【エージェント】
zabbixサーバーが監視用にアクセスする際のユーザーエージェントになります。
google analyticsなどで解析を行っている場合に、ウェブ監視のアクセスという事を
分かりやすくする為に、一応ユーザーエージェントを一般的なもの以外にしてあります。
ただ、google analyticsとかapacheのログならならzabbix serverのIPアドレスを除外リストに入れておけば問題ないかな?:(;゙゚'ω゚'):
【ステータス】
監視を行うかの状態になります。
【変数】
zabbixサーバーがアクセスを行う際に送信するデータの変数の登録。(変数の登録のみで送信データの登録ではない)
複数URLの監視を行う際や、送信データを共通で利用したい場合に設定しておくと管理が楽になる。
送信データがベタ書きでかまわない場合には、特に設定の必要なし。 たとえば、ページにアクセスする際にid値として10000を送りたい場合には、
ここで{id}の変数の値を{id}=1000と登録しておき、ステップの登録画面でid={id}として設定することで
送信することが出来ます。
{変数名}=値の記述方法になります。複数の場合には改行区切りになります。

■手順4.ステップの追加を押すと、ステップの登録画面が表示されるので、各項目を入力して、追加を押します。


名前:index.php
URL:http://test.com/index.php
POST:未記入
タイムアウト:15
要求文字列:未記入
ステータスコード:200
【名前】
アクセスするページの識別名。
【URL】
アクセスするページのURL
【POST】
アクセスする際にPOSTする値
【タイムアウト】
レスポンスを待つ時間
要求文字列は、レスポンス結果に特定の文字列が含まれているかどうかをチェックするための、判定文字列の登録。設定しない場合にはチェック無し。
【ステータスコード】
レスポンス結果の期待するステータスコードの設定。
複数ある場合にはカンマ区切りで入力する(200,201 or 200,201,200-211 )
※監視したいページが複数ある場合には、数分だけステップを追加してください。

POST送信したい場合には、POSTの項目に
id={id}&password={password}のように登録ができる。

GET送信したい場合には、URLの欄にhttp://test.com/index.php?id={id}&password={password}のように登録することでGET送信が可能です。 ※シナリオの登録画面の変数で{変数名}の変数が登録されている場合には変数が使えます。
登録していない場合にはベタ書きで設定すればその値が送信されます。
■手順5.ステップの登録が完了したら、シナリオの設定画面の保存を押して反映させる。

以上になります(`・ω・´)ゞビシッ!!

確認方法は、「監視データ」→「ウェブ」の一覧から先程設定した情報を追加されていると思います。