MYSQLの運用・管理ツール検証

Percona Toolkit

最新版のPercona Toolkitをダウンロード
http://www.percona.com/downloads/percona-toolkit/LATEST/

[root@HOME001 mysql]# wget http://www.percona.com/redir/downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz
--2013-01-31 14:03:47--  http://www.percona.com/redir/downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz
www.percona.com をDNSに問いあわせています... 74.121.199.234
www.percona.com|74.121.199.234|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: /downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz [続く]
--2013-01-31 14:03:47--  http://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-2.1.8.tar.gz
www.percona.com:80 への接続を再利用します。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1477253 (1.4M) [application/x-gzip]
`percona-toolkit-2.1.8.tar.gz' に保存中

100%[=======================================================================>] 1,477,253    441K/s 時間 3.3s

2013-01-31 14:03:51 (441 KB/s) - `percona-toolkit-2.1.8.tar.gz' へ保存完了 [1477253/1477253]

[root@HOME001 mysql]#

percona-toolkitのインストール

[root@HOME001 mysql]# tar -zxvf percona-toolkit-2.1.8.tar.gz
percona-toolkit-2.1.8/
percona-toolkit-2.1.8/MANIFEST
percona-toolkit-2.1.8/INSTALL
percona-toolkit-2.1.8/Changelog
percona-toolkit-2.1.8/docs/
percona-toolkit-2.1.8/docs/percona-toolkit.pod
percona-toolkit-2.1.8/Makefile.PL
percona-toolkit-2.1.8/README
省略...
percona-toolkit-2.1.8/bin/pt-slave-restart
percona-toolkit-2.1.8/bin/pt-query-digest
percona-toolkit-2.1.8/bin/pt-online-schema-change
percona-toolkit-2.1.8/bin/pt-fk-error-logger
percona-toolkit-2.1.8/bin/pt-pmp
percona-toolkit-2.1.8/bin/pt-stalk
percona-toolkit-2.1.8/bin/pt-show-grants
percona-toolkit-2.1.8/bin/pt-config-diff
percona-toolkit-2.1.8/COPYING
percona-toolkit-2.1.8/lib/
[root@HOME001 mysql]#

[root@HOME001 mysql]# cd percona-toolkit-2.1.8
[root@HOME001 percona-toolkit-2.1.8]#


[root@HOME001 percona-toolkit-2.1.8]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite DBD::mysql 3 not found.
Writing Makefile for percona-toolkit
[root@HOME001 percona-toolkit-2.1.8]#


[root@HOME001 percona-toolkit-2.1.8]# cpan -i DBD::mysql
CPAN: Storable loaded ok (v2.20)
Going to read '/root/.cpan/Metadata'
  Database was generated on Thu, 31 Jan 2013 05:07:28 GMT
Running install for module 'DBD::mysql'
CPAN: Data::Dumper loaded ok (v2.124)
'YAML' not installed, falling back to Data::Dumper and Storable to read prefs '/root/.cpan/prefs'
Running make for C/CA/CAPTTOFU/DBD-mysql-4.022.tar.gz
CPAN: Digest::SHA loaded ok (v5.47)
CPAN: Compress::Zlib loaded ok (v2.02)
Checksum for /root/.cpan/sources/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.022.tar.gz ok
CPAN: Archive::Tar loaded ok (v1.58)
DBD-mysql-4.022/

省略・・

Appending installation info to /usr/lib/perl5/perllocal.pod
  CAPTTOFU/DBD-mysql-4.022.tar.gz
  /usr/bin/make install  -- OK
Warning (usually harmless): 'YAML' not installed, will not store persistent state
[root@HOME001 percona-toolkit-2.1.8]#


[root@HOME001 percona-toolkit-2.1.8]# perl -MDBD::mysql -e 'print "$DBD::mysql::VERSION\n"'
4.022
[root@HOME001 percona-toolkit-2.1.8]#


[root@HOME001 percona-toolkit-2.1.8]# perl Makefile.PL
Writing Makefile for percona-toolkit
[root@HOME001 percona-toolkit-2.1.8]# 


[root@HOME001 percona-toolkit-2.1.8]# make
cp bin/pt-mysql-summary blib/script/pt-mysql-summary
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-mysql-summary
cp bin/pt-kill blib/script/pt-kill
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-kill
cp bin/pt-online-schema-change blib/script/pt-online-schema-change
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-online-schema-change
cp bin/pt-trend blib/script/pt-trend

省略...

Manifying blib/man1/pt-variable-advisor.1p
Manifying blib/man1/pt-index-usage.1p
Manifying blib/man1/pt-tcp-model.1p
Manifying blib/man1/pt-duplicate-key-checker.1p
Manifying blib/man1/pt-config-diff.1p
Manifying blib/man1/pt-stalk.1p
[root@HOME001 percona-toolkit-2.1.8]#


[root@HOME001 percona-toolkit-2.1.8]# make install
Installing /usr/local/share/man/man1/pt-tcp-model.1p
Installing /usr/local/share/man/man1/pt-mysql-summary.1p
Installing /usr/local/share/man/man1/pt-slave-delay.1p
Installing /usr/local/share/man/man1/pt-log-player.1p
Installing /usr/local/share/man/man1/pt-online-schema-change.1p
省略...
Installing /usr/local/bin/pt-ioprofile
Installing /usr/local/bin/pt-tcp-model
Installing /usr/local/bin/pt-table-sync
Installing /usr/local/bin/pt-find
Installing /usr/local/bin/pt-kill
Appending installation info to /usr/lib/perl5/perllocal.pod
[root@HOME001 percona-toolkit-2.1.8]#


[root@HOME001 bin]# which pt-query-digest
/usr/local/bin/pt-query-digest
[root@HOME001 bin]#

検証用のSlowログを意図的に作成してみる。
動的設定はGLOBALで設定しているので既存セッションには有効になっていないが。
既存設定を即時変更する場合は、globalオプションを付けなければOK。

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql>


mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

別セッションからは反映されている事を確認。

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql>

幾つかQueryを実行したので、こちらをpt-query-digestで確認。

SET timestamp=1359610105;
show variables like 'long%';
# Time: 130131 14:28:32
# User@Host: root[root] @ localhost []
# Query_time: 0.000441  Lock_time: 0.000108 Rows_sent: 39  Rows_examined: 39
SET timestamp=1359610112;
select * from test.TABLE001;
# Time: 130131 14:29:08
# User@Host: root[root] @ localhost []
# Query_time: 0.433720  Lock_time: 0.000131 Rows_sent: 0  Rows_examined: 1
SET timestamp=1359610148;
delete from test.TABLE001 where id = 39;
# Time: 130131 14:29:12
# User@Host: root[root] @ localhost []
# Query_time: 0.000011  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 1
SET timestamp=1359610152;
# administrator command: Quit;

Slowログのサンプルが集まったのでlong_query_timeの設定を戻す。

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> exit

Slowログをpt-query-digestにて解析してみる。

[root@HOME001 data]# /usr/local/bin/pt-query-digest /usr/local/mysql/data/home001-mysql-slow.log

# 380ms user time, 30ms system time, 13.71M rss, 19.34M vsz
# Current date: Thu Jan 31 14:33:10 2013
# Hostname: HOME001.localdomain
# Files: /usr/local/mysql/data/home001-mysql-slow.log
# Overall: 11 total, 7 unique, 0.06 QPS, 0.00x concurrency _______________
# Time range: 2013-01-31 14:26:02 to 14:29:12
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          901ms    11us   461ms    82ms   433ms   171ms   865us
# Lock time            1ms       0   166us   104us   152us    53us   125us
# Rows sent            122       0      39   11.09   38.53   16.89    0.99
# Rows examine         161       0      39   14.64   38.53   18.20    0.99
# Query size           377      27      63   34.27   40.45   10.82   26.08

# Profile
# Rank Query ID           Response time Calls R/Call Apdx V/M   Item
# ==== ================== ============= ===== ====== ==== ===== ==========
#    1 0x677853E0D6CB8C75  0.4610 51.2%     1 0.4610 1.00  0.00 UPDATE test.TABLE?
#    2 0x16CB61E3D2F803A3  0.4337 48.1%     1 0.4337 1.00  0.00 DELETE test.TABLE?
# MISC 0xMISC              0.0064  0.7%     9 0.0007   NS   0.0 <5 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x677853E0D6CB8C75 at byte 3035 _____
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0]*, V/M = 0.00
# Query_time sparkline: |     ^  |
# Time range: all events occurred at 2013-01-31 14:28:19
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          9       1
# Exec time     51   461ms   461ms   461ms   461ms   461ms       0   461ms
# Lock time     11   137us   137us   137us   137us   137us       0   137us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine  24      39      39      39      39      39       0      39
# Query size    16      63      63      63      63      63       0      63
# String:
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 'TABLE001'\G
#    SHOW CREATE TABLE `test`.`TABLE001`\G
update test.TABLE001 set comment = 'test percona-toolkit-2.1.8'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  comment = 'test percona-toolkit-2.1.8' from test.TABLE001 \G

# Query 2: 0 QPS, 0x concurrency, ID 0x16CB61E3D2F803A3 at byte 3653 _____
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0]*, V/M = 0.00
# Query_time sparkline: |     ^  |
# Time range: all events occurred at 2013-01-31 14:29:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          9       1
# Exec time     48   434ms   434ms   434ms   434ms   434ms       0   434ms
# Lock time     11   131us   131us   131us   131us   131us       0   131us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       1       1       1       1       1       0       1
# Query size    10      39      39      39      39      39       0      39
# String:
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 'TABLE001'\G
#    SHOW CREATE TABLE `test`.`TABLE001`\G
delete from test.TABLE001 where id = 39\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from  test.TABLE001 where id = 39\G
[root@HOME001 data]#

percona

参考サイト
pt-query-digest
tcpdumpとmk-query-digestでMySQLのクエリ解析
実録MySQLのチューニング 春の陣
Maatkitはtcpdumpを食べられる上,memcachedも咀嚼出来る

その他
mk-table-checksum – Perform an online replication consistency check,
maatkitでレプリケーションデータの整合性を確認
Tracking long running transactions in MySQL

関連情報
mysqldumpslow

[root@HOME001 data]# mysqldumpslow -t 3 -l /usr/local/mysql/data/home001-mysql-slow.log

Reading mysql slow query log from /usr/local/mysql/data/home001-mysql-slow.log
Count: 1  Time=0.46s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  update test.TABLE001 set comment = 'S'

Count: 1  Time=0.43s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  delete from test.TABLE001 where id = N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select @@version_comment limit N

[root@HOME001 data]#

MYSQLのソースコードを眺めてみました。
システムインフラを中心に仕事してきたので、CやC++は大学、卒業後のCとLIPSでの開発以来なので、
殆ど初心者ですが少しずつ眺める程度に勉強しておこうと思います。

ソースをダウンロードしてコアファイルのある、
以下のフォルダーを確認してみました。

[root@HOME001 sql]# pwd
/home/mysql/mysql-5.5.29/sql
[root@HOME001 sql]# 

cc

mysqld.ccだけでも約8000行あり、他にもソースは多々あります。
必要に応じて基本動作のイメージを掴む程度で。

[root@HOME001 sql]# cat mysqld.cc | egrep -n -A5 -i "/\*" | egrep -A5 -i "PAGE"
157:#endif /* HAVE_SOLARIS_LARGE_PAGES */
158-
159-#ifdef _AIX41
160-int initgroups(const char *,unsigned int);
161-#endif
162-
--
3232-#ifdef HAVE_LARGE_PAGES
3233:  /* Initialize large page size */
3234-  if (opt_large_pages && (opt_large_page_size= my_get_large_page_size()))
3235-  {
3236-      DBUG_PRINT("info", ("Large page set, large_page_size = %d",
3237-                 opt_large_page_size));
3238-      my_use_large_pages= 1;
--
3244:    /*
3245-       Either not configured to use large pages or Linux haven't
3246-       been compiled with large page support
3247-    */
3248-  }
3249:#endif /* HAVE_LARGE_PAGES */
3250-#ifdef HAVE_SOLARIS_LARGE_PAGES
3251:#define LARGE_PAGESIZE (4*1024*1024)  /* 4MB */
3252:#define SUPER_LARGE_PAGESIZE (256*1024*1024)  /* 256MB */
3253-  if (opt_large_pages)
3254-  {
3255:  /*
3256-    tell the kernel that we want to use 4/256MB page for heap storage
3257-    and also for the stack. We use 4 MByte as default and if the
3258-    super-large-page is set we increase it to 256 MByte. 256 MByte
3259-    is for server installations with GBytes of RAM memory where
3260-    the MySQL Server will have page caches and other memory regions
--
3299:#endif /* HAVE_SOLARIS_LARGE_PAGES */
3300-
3301:  /* connections and databases needs lots of files */
3302-  {
3303-    uint files, wanted_files, max_open_files;
3304-
--
6595-  {"Tc_log_max_pages_used",    (char*) &tc_log_max_pages_used,  SHOW_LONG},
6596-  {"Tc_log_page_size",         (char*) &tc_log_page_size,       SHOW_LONG},
--
6625:/** Compares two options' names, treats - and _ the same */
6626-static int option_cmp(my_option *a, my_option *b)
6627-{
6628-  const char *sa= a->name;
--
7869-  { &key_PAGE_cond, "PAGE::cond", 0},
7870-  { &key_COND_active, "TC_LOG_MMAP::COND_active", 0},
7871-  { &key_COND_pool, "TC_LOG_MMAP::COND_pool", 0},
7872:#endif /* HAVE_MMAP */
7873-  { &key_BINLOG_COND_prep_xids, "MYSQL_BIN_LOG::COND_prep_xids", 0},
7874-  { &key_BINLOG_update_cond, "MYSQL_BIN_LOG::update_cond", 0},
[root@HOME001 sql]#


MYSQL5.5から利用出来る以下のOptionを検証してみました。

--dump-slave --apply-slave-statements --include-master-host-port

前回はマスターで取得したデータはマスターのバイナリーログでリカバリー、
スレーブで取得したバックアップはスレーブのバイナリーログでリカバリーするように
–master-data=2オプションを付与してバックアップしてリカバリーを検証。
MYSQLDUMPを利用したオンラインバックアップと差分ログ適用

MYSQL5.5から追加されたdump-slaveオプションを利用すると、
追加のスレーブ作成が簡単に出来ます。(STOP SLAVE ~ START SLAVEコマンドも含まれます。)

先ずはSlaveデータベースにてレプリケーション対象のDBをバックアップします。

[root@HOME002 backup]# mysqldump --single-transaction --flush-logs --dump-slave --apply-slave-statements --include-master-host-port --databases test > DUMP_AT_SLAVE.sql -u root -p
Enter password:
[root@HOME002 backup]# ls -l
合計 12
-rw-r--r-- 1 root root 4357  1月 27 16:18 DUMP_AT_SLAVE.sql
[root@HOME002 backup]#


ダンプファイルは以下のようにスレーブのレプリケーションを停止して、
対象DBを再作成してマスターのログPOSITIONに合わせてからデータリストア後に
レプリケーションを再開しています。

[root@HOME002 backup]# cat DUMP_AT_SLAVE.sql
-- MySQL dump 10.13  Distrib 5.5.29, for linux2.6 (i686)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.5.29-log

   省略.....

--
-- stop slave statement to make a recovery dump)
--

STOP SLAVE;

--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--

CHANGE MASTER TO MASTER_HOST='HOME001', MASTER_PORT='3306', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=107;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

--
-- Table structure for table `TABLE001`
--


   省略.....


--
-- start slave statement to make a recovery dump)
--

START SLAVE;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- Dump completed on 2013-01-27 16:18:36
[root@HOME002 backup]#

上記のダンプファイルのCHANGE MASTERの部分に以下のスレーブユーザー設定を追記

MASTER_USER = 'slave_user',MASTER_PASSWORD = 'slave_password',

そして、新規Slaveの/etc/my.cnfにホストIDなどを追加しておけば、
直ぐにSlaveを追加する事が出来ます。

また、マスターのログとPOSITIONを参照しているのでマスターのDBが破損した場合も
リストアしてマスターのPOSITIONを確認した上でバイナリーログのリストアが可能です。

参考までにオプションを変更した場合のバックアップの違いは以下の通りです。

[root@HOME002 backup]# mysqldump --single-transaction --flush-logs --dump-slave --apply-slave-statements --include-master-host-port --databases test > DUMP_AT_SLAVE.sql -u root -p
Enter password:
[root@HOME002 backup]# mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DUMP_WITH_MASTER.sql -u root -p
Enter password:
[root@HOME002 backup]#

[root@HOME002 backup]# ls -l
合計 24
-rw-r--r-- 1 root root 4617  1月 27 17:37 DUMP_AT_SLAVE.sql
-rw-r--r-- 1 root root 4409  1月 27 17:39 DUMP_WITH_MASTER.sql
[root@HOME002 backup]# diff DUMP_AT_SLAVE.sql DUMP_WITH_MASTER.sql
19c19
< -- stop slave statement to make a recovery dump)
---
> -- Position to start replication or point-in-time recovery from
22,28c22
< STOP SLAVE;
<
< --
< -- Position to start replication or point-in-time recovery from (the master of this slave)
< --
<
< CHANGE MASTER TO MASTER_HOST='HOME001', MASTER_PORT='3306', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=126                          2;
---
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=107;
63,68d56
<
< --
< -- start slave statement to make a recovery dump)
< --
<
< START SLAVE;
79c67
< -- Dump completed on 2013-01-27 17:37:42
---
> -- Dump completed on 2013-01-27 17:39:13
[root@HOME002 backup]#

diff

参考サイト
MySQLコミュニティ騒然!MySQL 5.5.4が与えるインパクト。
MySQL 5.5新機能徹底解説
MYSQLDUMPを利用したオンラインバックアップと差分ログ適用



MYSQLDUMPを利用する時のOPTION確認とDUMPした後にバイナリーログを適用して、
最新のデータをリカバリーする方法の再確認。

以下のようなOPTIONでDB全体の整合性とバイナリーログのロールフォワードに必要なデータをダンプする。

–master-dataオプションと対象ロック

mysqldump --lock-all-tables --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p

–lock-all-tablesと–single-transactionは同時に利用する事が出来ません。HELPを読むと–lock-all-tablesを利用すると
自動的に–single-transactionはOFFにされるようです。–single-transactionはトランザクションをサポートする
InnoDBにのみ利用可能なのでMyISAMなどには有効では無いようです。同時にオプションを指定すると以下のようなエラーになる。
また–master-dataオプションを指定している場合は自動的に–lock-all-tablesがONになるようです。
暗黙的にONになるので運用者が明確にしておく為に意図的に指定しておいても良いかもしれません。
但し、–single-transactionが指定されていると–master-dataを指定していても–lock-all-tablesはONにならない様です。

[root@HOME002 data]# mysqldump --single-transaction --lock-all-tables --flush-logs --master-data=2 --databases test > /home/mysql/MYSQL_dump20130125.sql -u root -p
mysqldump: You can't use --single-transaction and --lock-all-tables at the same time.
[root@HOME002 data]#

こちらのバックアップオプションで特定データベースもしくはDB全体をバックアップ
InnoDBやMYISAMを含む全てのデータベースをDumpする場合
(–lock-all-tablesはあえて残してあります)
例)

 mysqldump --lock-all-tables --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
 mysqldump --lock-all-tables --flush-logs --master-data=2 --all-databases >  ALL_DB_DUMP.sql -u root -p

InnoDBのみ利用していて整合性の取れたデータをDumpする場合
例)

 mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
 mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >  ALL_DB_DUMP.sql -u root -p

—————————————————
以下のHELP抜粋
—————————————————
-x, –lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns –single-transaction and
–lock-tables off.

–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off –lock-tables.

–master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
–lock-all-tables on, unless –single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don’t
forget to read about –single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns –lock-tables
off.

-F, –flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
–databases= or –all-databases), the logs will be
flushed for each database dumped. The exception is when
using –lock-all-tables or –master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use –lock-all-tables or –master-data with
–flush-logs.

-A, –all-databases Dump all the databases. This will be same as –databases
with all databases selected.

–dump-date Put a dump date to the end of the output.
(Defaults to on; use –skip-dump-date to disable.)

-q, –quick Don’t buffer query, dump directly to stdout.
(Defaults to on; use –skip-quick to disable.)
—————————————————


MYISAMを含むデータもあるのでFULLをあえて–lock-all-tablesで取得して、
InnoDBしか含まないtestデータベースを–single-transactionで取得してます。

※本番環境では状況によって使い分けして下さい。

[root@HOME001 backup]# dumpdate=`date '+%F'`
[root@HOME001 backup]# dumpfile=MYSQL_DUMP_$dumpdate
[root@HOME001 backup]# dumpfile_dir=/home/mysql/backup/
[root@HOME001 backup]# mysqldump --lock-all-tables --flush-logs --master-data=2 --all-databases >  $dumpfile_dir$dumpfile\_ALL.sql -u root -p
Enter password:
[root@HOME001 backup]# 
[root@HOME001 backup]# mysqldump --single-transaction --flush-logs --master-data=2 --databases test > $dumpfile_dir$dumpfile\_test.sql -u root -p
Enter password:
[root@HOME001 backup]# ls -l /home/mysql/backup/MYSQL_DUMP_*                                                          
-rw-r--r--. 1 root root 516386  1月 26 11:00 2013 /home/mysql/backup/MYSQL_DUMP_2013-01-26_ALL.sql
-rw-r--r--. 1 root root   4000  1月 26 11:03 2013 /home/mysql/backup/MYSQL_DUMP_2013-01-26_test.sql
[root@HOME001 backup]#


FULLバックアップを取得したので先ずはデータの確認して、
データを追記した後に擬似的な操作ミスとしてTruncateしてデータを空にしてみる。

mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 25 | LOG20120113          | mysql-bin.000034 Take Full Backup             |
| 24 | LOG20120111--01      | after mysql-bin.000034                        |
| 23 | LOG20120111--01      | after mysql-bin.000034                        |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.62 sec)

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.13 sec)

mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 35 | Recreate Replication | after dump 20120126                           |
| 34 | Recreate Replication | after dump 20120126                           |
| 33 | Recreate Replication | after dump 20120126                           |
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>


mysql> truncate table TABLE001;
Query OK, 0 rows affected (0.40 sec)

mysql> select * from TABLE001 order by id desc limit 0,10;
Empty set (0.00 sec)

mysql>

confirmation1


上記でバックアップしたダンプファイルの中身を確認してLOGのポジションを確認。

[root@HOME001 backup]# cat MYSQL_DUMP_2013-01-26_test.sql | egrep -i master
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;
[root@HOME001 backup]#


擬似オペミス直前まで戻す為にどのバイナリーログまで戻したら良いか確認。

最後のバックアップから全てのデータを戻したい場合は、
バックアップのログポジションを確認した後に確認したポジション
以降の全てのログを戻せば良い。

[root@HOME001 data]# mysqlbinlog --no-defaults mysql-bin.000007 | egrep -i -B5 truncate
#130126 11:26:50 server id 1  end_log_pos 929   Xid = 366
COMMIT/*!*/;
# at 929
#130126 11:41:11 server id 1  end_log_pos 1015  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1359168071/*!*/;
truncate table TABLE001
[root@HOME001 data]#

ロールフォワードする為には以下のログの中身を適用する必要がある。
今回の検証で確認したケースでのStartとEndのポジション

mysql-bin.000007
 –start-position=107
 –stop-position=929

バイナリーログから戻すべきデータを再作成する為のスクリプトを作成する。
OracleでいうArchiveログを適用するRecoveryモード時に適用するログをマニュアル作成する感じ?
Positionは上記で確認したPositionを設定。

[root@HOME001 data]# mysqlbinlog --no-defaults --start-position=107 --stop-position=929 mysql-bin.000007 > /home/mysql/backup/recovery.sql
[root@HOME001 data]#

障害発生前まで戻す為の材料が揃ったのでリカバリー開始

[root@HOME001 backup]# ls -l
合計 524
drwxr-xr-x. 6 root root   4096  1月 24 14:45 2013 2013-01-24_14-44-57
drwxr-xr-x. 6 root root   4096  1月 24 15:23 2013 2013-01-24_15-23-31
-rw-r--r--. 1 root root 516386  1月 26 11:00 2013 MYSQL_DUMP_2013-01-26_ALL.sql
-rw-r--r--. 1 root root   4000  1月 26 11:03 2013 MYSQL_DUMP_2013-01-26_test.sql
-rw-r--r--. 1 root root   2713  1月 26 12:17 2013 recovery.sql
[root@HOME001 backup]#

[root@HOME001 backup]# mysql -u root -p test < MYSQL_DUMP_2013-01-26_test.sql
Enter password:
&#91;root@HOME001 backup&#93;# mysql -u root -p test < recovery.sql
Enter password:
&#91;root@HOME001 backup&#93;#
&#91;/SHELL&#93;

<strong>リカバリー完了したのでデータを確認して完了。</strong>
[SHELL]
mysql> use test
Database changed
mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 35 | Recreate Replication | after dump 20120126                           |
| 34 | Recreate Replication | after dump 20120126                           |
| 33 | Recreate Replication | after dump 20120126                           |
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>

after

スレーブでmysqldumpを取得時にバイナリーログPOSITIONを記録する場合の留意:

--master-data

オプションは、バックアップを取得したサーバーでのポジションになるので
スレーブでバックアップ取得していてマスターDBが破損した場合でも、
スレーブのバイナリーログから対象のデータをリカバリーする必要があります。
もしマスター側のバイナリーログから必要なDDL、DMLを追加で取得する場合は、
調査と対応に時間がかなりかかる事が想定されます。
MYSQL5.5以降であれば、スレーブで取得する場合は

--dump-slave

を使用する事で、
MasterデータベースのバイナリーログのPOSITIONを記録してくれるので、
マスターのログを利用してのリカバリーが可能です。
但し、マスターが全損する場合もあるので用途によって運用でカバーする必要があります。

検証結果

mysqldump

参考サイト
How to obtain a correct dump using mysqldump and single-transaction when DDL is used at the same time?
mysqldump –single-transaction に –flush-logs をつけてはいけない
mysqldump の option 復習
7.12. mysqldump — データベースバックアッププログラム
mysqlbinlog — バイナリログファイルを処理するためのユーティリティ


MYSQLオンラインバックアップ

基本的にWeb系のサービスではオンラインバックアップが基本だと思うので、
改めてMYSQLのオンラインバックアップの選択肢を確認。

ハードウエアやOSの機能を利用
 LVMやストレージが提供しているSnapShot機能を利用する。
 自分で簡単なスクリプトを作成すれば瞬時にオンラインで
 MYSQLバックアップをする事が可能。

ソフトウエアの機能を利用したバックアップ
mysqldump
mMySQL Enterprise Backup
InnoDB Hot Backup
Xtra Backup

Xtra Backupの動作確認
32bit版はi686 64bit版はx86_64
http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/
http://www.percona.com/software/percona-xtrabackup

OS確認

[root@HOME001 mysql]# uname -m
i686
[root@HOME001 mysql]#

ダウンロード

[root@HOME001 mysql]# wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
--2013-01-24 13:31:20--  http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
www.percona.com をDNSに問いあわせています... 74.121.199.234
www.percona.com|74.121.199.234|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: /downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz [続く]
--2013-01-24 13:31:21--  http://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.5/binary/Linux/i686/percona-xtrabackup-2.0.5-499.tar.gz
www.percona.com:80 への接続を再利用します。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 22328480 (21M) [application/x-gzip]
`percona-xtrabackup-2.0.5-499.tar.gz' に保存中

100%[==========================================================>] 22,328,480  1.40M/s 時間 15s

2013-01-24 13:31:36 (1.45 MB/s) - `percona-xtrabackup-2.0.5-499.tar.gz' へ保存完了 [22328480/22328480]

[root@HOME001 mysql]#

展開
MYSQLのホームディレクトリーのbinに展開したファイルをコピーしないとエラーが出たのでコピーしてます。

[root@HOME001 mysql]# tar zxvf percona-xtrabackup-2.0.5-499.tar.gz
percona-xtrabackup-2.0.5/
percona-xtrabackup-2.0.5/bin/
percona-xtrabackup-2.0.5/bin/xtrabackup_55
percona-xtrabackup-2.0.5/bin/xtrabackup_51
percona-xtrabackup-2.0.5/bin/innobackupex
percona-xtrabackup-2.0.5/bin/xtrabackup
省略…
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/bug723097.sql
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/ib_stream_common.sh
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/incremental_sample-db/incremental_sample-schema.sql
percona-xtrabackup-2.0.5/share/percona-xtrabackup-test/inc/common.sh
[root@HOME001 mysql]#


[root@HOME001 percona-xtrabackup-2.0.5]# pwd
/home/mysql/percona-xtrabackup-2.0.5
[root@HOME001 percona-xtrabackup-2.0.5]# ls -l
合計 8
drwxr-xr-x. 2 root root 4096  1月 18 05:37 2013 bin
drwxr-xr-x. 4 root root 4096  1月 18 05:37 2013 share
[root@HOME001 percona-xtrabackup-2.0.5]# ls -l bin/
合計 32272
-rwxr-xr-x. 1 root root   106390  1月 18 05:37 2013 innobackupex
lrwxrwxrwx. 1 root root       12  1月 24 13:34 2013 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x. 1 root root  2031819  1月 18 05:37 2013 xbstream
-rwxr-xr-x. 1 root root  9809963  1月 18 05:34 2013 xtrabackup
-rwxr-xr-x. 1 root root  8375751  1月 18 05:37 2013 xtrabackup_51
-rwxr-xr-x. 1 root root 12713916  1月 18 05:29 2013 xtrabackup_55
[root@HOME001 percona-xtrabackup-2.0.5]#cd bin
[root@HOME001 bin]# cp -rp * /usr/local/mysql/bin/
[root@HOME001 bin]# cd /usr/local/mysql/bin/
[root@HOME001 bin]# mkdir /home/mysql/backup

tar

InnoDBのオンラインバックアップ実行
パスワードは変数を入れてます。

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass /home/mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130124 14:44:51  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:44:51  innobackupex-1.5.1: Connected to database with mysql child process (pid=4736)
130124 14:44:57  innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex-1.5.1
           prints "completed OK!".

innobackupex-1.5.1: Using mysql  Ver 14.14 Distrib 5.5.29, for linux2.6 (i686) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

innobackupex-1.5.1: Created backup directory /home/mysql/backup/2013-01-24_14-44-57
130124 14:44:57  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:44:57  innobackupex-1.5.1: Connected to database with mysql child process (pid=4763)
130124 14:44:59  innobackupex-1.5.1: Connection to database server closed

130124 14:44:59  innobackupex-1.5.1: Starting ibbackup with command: xtrabackup_55  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/backup/2013-01-24_14-44-57
innobackupex-1.5.1: Waiting for ibbackup (pid=4771) to suspend
innobackupex-1.5.1: Suspend file '/home/mysql/backup/2013-01-24_14-44-57/xtrabackup_suspended'

xtrabackup_55 version 2.0.5 for Percona Server 5.5.16 Linux (i686) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 8388608
>> log scanned up to (1656933)
[01] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_14-44-57/ibdata1
[01]        ...done

130124 14:45:03  innobackupex-1.5.1: Continuing after ibbackup has suspended
130124 14:45:03  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 14:45:03  innobackupex-1.5.1: Connected to database with mysql child process (pid=4785)
>> log scanned up to (1656933)
130124 14:45:05  innobackupex-1.5.1: Starting to lock all tables...
>> log scanned up to (1656933)
>> log scanned up to (1656933)
130124 14:45:15  innobackupex-1.5.1: All tables locked and flushed to disk

130124 14:45:15  innobackupex-1.5.1: Starting to backup non-InnoDB tables and files
innobackupex-1.5.1: in subdirectories of '/usr/local/mysql/data'
innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/performance_schema/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/DB_REPLICATION/db.opt'
innobackupex-1.5.1: Backing up file '/usr/local/mysql/data/test/TABLE001.frm'
innobackupex-1.5.1: Backing up files '/usr/local/mysql/data/mysql/*.{frm,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
130124 14:45:15  innobackupex-1.5.1: Finished backing up non-InnoDB tables and files

130124 14:45:15  innobackupex-1.5.1: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1656933'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1656933)

xtrabackup: Transaction log of lsn (1656933) to (1656933) was copied.
130124 14:45:18  innobackupex-1.5.1: All tables unlocked
130124 14:45:18  innobackupex-1.5.1: Connection to database server closed

innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_14-44-57'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107
130124 14:45:18  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#


[root@HOME001 bin]# ls -l /home/mysql/backup/
合計 4
drwxr-xr-x. 6 root root 4096  1月 24 14:45 2013 2013-01-24_14-44-57
[root@HOME001 bin]#

innobackup

並列処理を指定する事も可能

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --parallel=4 /home/mysql/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130124 15:23:25  innobackupex-1.5.1: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130124 15:23:25  innobackupex-1.5.1: Connected to database with mysql child process (pid=5010)
130124 15:23:31  innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex-1.5.1
           prints "completed OK!".
省略・・・
xtrabackup: Starting 4 threads for parallel data files transfer
[03] Copying ./ibdata1 to /home/mysql/backup/2013-01-24_15-23-31/ibdata1
[03]        ...done
省略・・・
innobackupex-1.5.1: Backup created in directory '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000050', position 107
130124 15:23:52  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#

リストアを試してみる
リストア時にフルバックアップで取得したデータをリストアすると、
データフォルダーが空では無いとエラーになる。
バイナリーログは差分リカバリーで必要になるので残しておく。
Oracleでいうテーブルスペースのオンライバックアップをリストアして、
ArchiveログやRedoを適用してロールフォワードするような感じ。

[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|       28 |
+----------+
[root@HOME001 bin]# mysql -u root -p$b_pass -e "truncate table test.TABLE001;"
[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|        0 |
+----------+
[root@HOME001 bin]#
[root@HOME001 bin]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@HOME001 bin]# mv /usr/local/mysql/data  /home/mysql/backup/
[root@HOME001 bin]# mkdir /usr/local/mysql/data
[root@HOME001 bin]# chown -R mysql:mysql /usr/local/mysql/data

restore1

コピーバックしてリストア
注意:レプリケーション環境ではきちんと必要なファイル等を理解した上でリカバリーして下さい。

[root@HOME001 bin]# ./innobackupex-1.5.1 --user root --password $b_pass --copy-back /home/mysql/backup/2013-01-24_15-23-31

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex-1.5.1
           prints "completed OK!".

innobackupex-1.5.1: Starting to copy files in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/performance_schema'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/file_summary_by_event_name.frm' to '/usr/l         ocal/mysql/data/performance_schema/file_summary_by_event_name.frm'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/performance_schema/rwlock_instances.frm' to '/usr/local/mysql         /data/performance_schema/rwlock_instances.frm'
....省略
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition.MYI' to '/usr/local/mysql/data/mys         ql/time_zone_transition.MYI'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/mysql/time_zone_transition_type.frm' to '/usr/local/mysql/dat         a/mysql/time_zone_transition_type.frm'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/DB_REPLICATION'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/DB_REPLICATION/db.opt' to '/usr/local/mysql/data/DB_REPLICATI         ON/db.opt'
innobackupex-1.5.1: Creating directory '/usr/local/mysql/data/test'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/test/TABLE001.frm' to '/usr/local/mysql/data/test/TABLE001.frm'

innobackupex-1.5.1: Starting to copy InnoDB system tablespace
innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original InnoDB data directory '/usr/local/mysql/data'
innobackupex-1.5.1: Copying '/home/mysql/backup/2013-01-24_15-23-31/ibdata1' to '/usr/local/mysql/data/ibdata1'

innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/home/mysql/backup/2013-01-24_15-23-31'
innobackupex-1.5.1: back to original InnoDB log directory '/usr/local/mysql/data'
innobackupex-1.5.1: Finished copying back files.
130124 15:38:03  innobackupex-1.5.1: completed OK!
[root@HOME001 bin]#

サービス再起動とデータの確認

[root@HOME001 bin]# /etc/init.d/mysql start
Starting MySQL....                                         [  OK  ]
[root@HOME001 bin]# mysql -u root -p$b_pass -e "select count(*) from test.TABLE001;"
+----------+
| count(*) |
+----------+
|       28 |
+----------+
[root@HOME001 bin]#

リカバリーログの内容(ログファイル再作成、ロールフォワードの履歴)

130124 15:40:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
130124 15:40:42 [Note] Plugin ‘FEDERATED’ is disabled.
130124 15:40:42 InnoDB: The InnoDB memory heap is disabled
130124 15:40:42 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
130124 15:40:42 InnoDB: Compressed tables use zlib 1.2.3
130124 15:40:42 InnoDB: Using Linux native AIO
130124 15:40:42 InnoDB: Initializing buffer pool, size = 32.0M
130124 15:40:42 InnoDB: Completed initialization of buffer pool
130124 15:40:42 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:42 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 8 MB
InnoDB: Database physically writes the file full: wait…
130124 15:40:43 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130124 15:40:43 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Last MySQL binlog file position 0 3917, file name ./mysql-bin.000035
130124 15:40:43 InnoDB: Waiting for the background threads to start
130124 15:40:44 InnoDB: 1.1.8 started; log sequence number 1657356
130124 15:40:44 [Note] Semi-sync replication initialized for transactions.
130124 15:40:44 [Note] Semi-sync replication enabled on the master.
130124 15:40:44 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
130124 15:40:44 [Note] – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
130124 15:40:44 [Note] Server socket created on IP: ‘0.0.0.0’.
130124 15:40:44 [Note] Event Scheduler: Loaded 0 events
130124 15:40:44 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.29-log’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Community Server (GPL)
130124 15:40:46 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000050, 194)
130124 15:40:46 [Note] Stop semi-sync binlog_dump to slave (server_id: 2)

restore3

その他LVM のスナップショット機能を利用したバックアップに関しては後日検証
LVMの現状確認

[root@HOME001 ~]# fdisk -l /dev/sda
ディスク /dev/sda: 60.0 GB, 60011642880 バイト
ヘッド 255, セクタ 63, シリンダ 7296
Units = シリンダ数 of 16065 * 512 = 8225280 バイト
セクタサイズ (論理 / 物理): 512 バイト / 512 バイト
I/O size (minimum/optimal): 512 bytes / 512 bytes
ディスク識別子: 0x1ecfb89b

デバイス ブート      始点        終点     ブロック   Id  システム
/dev/sda1   *           1          64      512000   83  Linux
パーティション 1 は、シリンダ境界で終わっていません。
/dev/sda2              64        7296    58091520   8e  Linux LVM
[root@HOME001 ~]#


[root@HOME001 ~]# pvscan
  PV /dev/sda2   VG vg_home001   lvm2 [55.40 GiB / 0    free]
  Total: 1 [55.40 GiB] / in use: 1 [55.40 GiB] / in no VG: 0 [0   ]
[root@HOME001 ~]# 

[root@HOME001 ~]# vgscan
  Reading all physical volumes.  This may take a while...
  Found volume group "vg_home001" using metadata type lvm2
[root@HOME001 ~]# 

[root@HOME001 ~]# lvscan
  ACTIVE            '/dev/vg_home001/lv_root' [46.86 GiB] inherit
  ACTIVE            '/dev/vg_home001/lv_home' [4.57 GiB] inherit
  ACTIVE            '/dev/vg_home001/lv_swap' [3.97 GiB] inherit
[root@HOME001 ~]# 

[root@HOME001 ~]# vgs
  VG         #PV #LV #SN Attr   VSize  VFree
  vg_home001   1   3   0 wz--n- 55.40g    0
[root@HOME001 ~]# 

[root@HOME001 ~]# lvs
  LV      VG         Attr     LSize  Pool Origin Data%  Move Log Copy%  Convert
  lv_home vg_home001 -wi-ao--  4.57g
  lv_root vg_home001 -wi-ao-- 46.86g
  lv_swap vg_home001 -wi-ao--  3.97g
[root@HOME001 ~]# 

[root@HOME001 ~]# df
Filesystem           1K-ブロック    使用   使用可 使用% マウント位置
/dev/mapper/vg_home001-lv_root      48360312   2618204  43285532   6% /
tmpfs                                 969472         0    969472   0% /dev/shm
/dev/sda1                             495844     36136    434108   8% /boot
/dev/mapper/vg_home001-lv_home       4721020    441796   4039404  10% /home
tmpfs                                  65536         0     65536   0% /usr/local/tmp
[root@HOME001 ~]# 

[root@HOME001 ~]# swapon -s
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       4161528 0       -1
[root@HOME001 ~]#


もしボリューム名を変更したら起動、fstabの変更も忘れずに
[root@HOME001 ~]# cat /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/mapper/vg_home001-lv_root
#          initrd /initrd-[generic-]version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title CentOS (2.6.32-279.el6.i686)
        root (hd0,0)
        kernel /vmlinuz-2.6.32-279.el6.i686 ro root=/dev/mapper/vg_home001-lv_root rd_NO_LUKS rd_LVM_LV=vg_home001/lv_swap rd_NO_MD rd_LVM_LV=vg_home001/lv_root crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=jp106 LANG=ja_JP.UTF-8 rd_NO_DM rhgb quiet
        initrd /initramfs-2.6.32-279.el6.i686.img
[root@HOME001 ~]#

参考

MySQLバックアップ頂上決戦!! LVMスナップショット vs InnoDB Hot Backup
Percona XtraBackup
Percona XtraBackupの基本的な使い方
XtraBackupを使ってMySQLをバックアップしよう
MySQLバックアップツール比較 XtraBackup / mysqldump / Mydumper
MySQL – XtraBackupを使ったバックアップ&リストアについて
レプリケーション作成を簡単にする mysql40dump という mysqldump の wrapper を作った話
keepalived + MySQL-MHA + xtrabackupで自動フェイルオーバーと手動フェイルバック(その1 keepalived編)
Percona XtraBackupの抽出と圧縮の並列処理
LVM を導入する
LVM のパーティションを管理する
LVM のスナップショット機能を使ってみる
How to setup a slave for replication in 6 simple steps with Xtrabackup


MYSQL5.6も近い将来リリースされるので再度基本レビューしておく。

確認したMYSQLバージョン

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.5.29-log |
+------------+
1 row in set (0.00 sec)

MYSQLファイルパスについて
basedir MYSQLインストールディレクトリー
datadir データディレクトリー(mysql_install_dbスクリプト実行時に–datadir=/dataなどで指定可能)
/etc/my.cnfにて変更可能

	[root@HOME001 mysql]# cat /etc/my.cnf | grep innodb
	#innodb_data_home_dir = /usr/local/mysql/data
	#innodb_data_file_path = ibdata1:10M:autoextend
	#innodb_log_group_home_dir = /usr/local/mysql/data
	innodb_buffer_pool_size = 32M
	innodb_additional_mem_pool_size = 2M
	innodb_log_file_size =   8M
	innodb_log_buffer_size = 8M
	innodb_flush_log_at_trx_commit = 1
	innodb_lock_wait_timeout = 50
	[root@HOME001 mysql]#
mysql> show variables like '%dir%';
+-----------------------------------------+-------------------------------------------------------+
| Variable_name                           | Value                                                 |
+-----------------------------------------+-------------------------------------------------------+
| basedir                                 | /usr/local/mysql                                      |
| binlog_direct_non_transactional_updates | OFF                                                   |
| character_sets_dir                      | /usr/local/mysql-5.5.29-linux2.6-i686/share/charsets/ |
| datadir                                 | /usr/local/mysql/data/                                |
| innodb_data_home_dir                    |                                                       |
| innodb_log_group_home_dir               | ./                                                    |
| innodb_max_dirty_pages_pct              | 75                                                    |
| lc_messages_dir                         | /usr/local/mysql-5.5.29-linux2.6-i686/share/          |
| plugin_dir                              | /usr/local/mysql/lib/plugin/                          |
| slave_load_tmpdir                       | /tmp                                                  |
| tmpdir                                  | /tmp                                                  |
+-----------------------------------------+-------------------------------------------------------+
11 rows in set (0.00 sec)

mysql>

TCP/IP PORT・Socket接続について。
複数インスタンスを立ち上げる場合はPort、Socket、Datadirが被らないようにする必要あり。

mysql> show variables like '%socket%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| socket        | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> show variables like '%port%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_support_xa   | ON    |
| large_files_support | ON    |
| port                | 3306  |
| report_host         |       |
| report_password     |       |
| report_port         | 3306  |
| report_user         |       |
+---------------------+-------+
7 rows in set (0.01 sec)

mysql>

コネクションに関しては、Default値がバージョンによっても異なるので、
確認して不足している場合は適宜値を変更する必要があります。
Java EE, RubyなどのようにコネクションPoolする場合とPHPなどのように
都度接続する場合など調整方法も変更する必要があると思います。
MSSQLなどを利用している場合も同様に.Netやサーバー側のPool設定
またはLoad Balancerなどの調整なども環境によっては必要になると思います。

mysql> show variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| max_connections          | 151             |
| max_user_connections     | 0               |
+--------------------------+-----------------+
4 rows in set (0.00 sec)

mysql>

既定のストレージエンジンについて
CREATE TABLE文実行時にENGINE=MyISAMなどで指定する事も可能。

mysql> show variables like '%storage%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

mysql>

ログファイルについて。
log_binに関しては、リカバリーやレプリケーションで必須なので設定。

mysql> show variables like '%log%';
+-----------------------------------------+-----------------------------------------------+
| Variable_name                           | Value                                         |
+-----------------------------------------+-----------------------------------------------+
| back_log                                | 50                                            |
| binlog_cache_size                       | 32768                                         |
| binlog_direct_non_transactional_updates | OFF                                           |
| binlog_format                           | MIXED                                         |
| binlog_stmt_cache_size                  | 32768                                         |
| expire_logs_days                        | 7                                             |
| general_log                             | OFF                                           |
| general_log_file                        | /usr/local/mysql/data/HOME001.log             |
| innodb_flush_log_at_trx_commit          | 1                                             |
| innodb_locks_unsafe_for_binlog          | OFF                                           |
| innodb_log_buffer_size                  | 8388608                                       |
| innodb_log_file_size                    | 8388608                                       |
| innodb_log_files_in_group               | 2                                             |
| innodb_log_group_home_dir               | ./                                            |
| innodb_mirrored_log_groups              | 1                                             |
| log                                     | OFF                                           |
| log_bin                                 | ON                                            |
| log_bin_trust_function_creators         | OFF                                           |
| log_error                               | /usr/local/mysql/data/HOME001.localdomain.err |
| log_output                              | FILE                                          |
| log_queries_not_using_indexes           | OFF                                           |
| log_slave_updates                       | OFF                                           |
| log_slow_queries                        | OFF                                           |
| log_warnings                            | 1                                             |
| max_binlog_cache_size                   | 18446744073709547520                          |
| max_binlog_size                         | 1073741824                                    |
| max_binlog_stmt_cache_size              | 18446744073709547520                          |
| max_relay_log_size                      | 0                                             |
| relay_log                               |                                               |
| relay_log_index                         |                                               |
| relay_log_info_file                     | relay-log.info                                |
| relay_log_purge                         | ON                                            |
| relay_log_recovery                      | OFF                                           |
| relay_log_space_limit                   | 0                                             |
| slow_query_log                          | OFF                                           |
| slow_query_log_file                     | /usr/local/mysql/data/HOME001-slow.log        |
| sql_log_bin                             | ON                                            |
| sql_log_off                             | OFF                                           |
| sync_binlog                             | 0                                             |
| sync_relay_log                          | 0                                             |
| sync_relay_log_info                     | 0                                             |
+-----------------------------------------+-----------------------------------------------+
41 rows in set (0.00 sec)

mysql>

mysql> show variables like '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | MIXED                |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

mysql>

遅いQueryを早期発見してDBを安定稼動させ続ける為に必要です。
設定を有効にしておきましょう。
オンラインでも設定可能ですが再起動に備えて/etc/my.cnfに設定入れておくと良い。

mysql> show variables like '%slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| log_slow_queries    | OFF                                    |
| slow_launch_time    | 2                                      |
| slow_query_log      | OFF                                    |
| slow_query_log_file | /usr/local/mysql/data/HOME001-slow.log |
+---------------------+----------------------------------------+
4 rows in set (0.00 sec)

mysql> SET GLOBAL log_slow_queries = 1;
Query OK, 0 rows affected, 1 warning (0.39 sec)

mysql> show variables like '%slow%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| log_slow_queries    | ON                                     |
| slow_launch_time    | 2                                      |
| slow_query_log      | ON                                     |
| slow_query_log_file | /usr/local/mysql/data/HOME001-slow.log |
+---------------------+----------------------------------------+
4 rows in set (0.00 sec)

mysql>

設定は反映されるかWarningに以下のメッセージが出るので、
以降は”SET GLOBAL slow_query_log = 1;”コマンドで有効に設定する。

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

SLOWログの閾値を設定する。
Default10秒なので1秒に変更するが再起動が必要。
規定値として1秒に設定しておいて、I/O負荷状況や必要に応じて
”log_slow_queries”でON・OFFを切り替えるのが良いかと思う。

mysql> show variables like '%long%';
+---------------------------------------------------+-----------+
| Variable_name                                     | Value     |
+---------------------------------------------------+-----------+
| long_query_time                                   | 10.000000 |
| max_long_data_size                                | 1048576   |
| performance_schema_events_waits_history_long_size | 10000     |
+---------------------------------------------------+-----------+
3 rows in set (0.00 sec)

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long%';
+---------------------------------------------------+-----------+
| Variable_name                                     | Value     |
+---------------------------------------------------+-----------+
| long_query_time                                   | 10.000000 |
| max_long_data_size                                | 1048576   |
| performance_schema_events_waits_history_long_size | 10000     |
+---------------------------------------------------+-----------+
3 rows in set (0.00 sec)

mysql>

SLOWログの設定を設定ファイルに入れて再起動。
long_query_timeはマイクロ秒単位で設定可能(例:long_query_time=0.5)

[root@HOME001 mysql]# vi /etc/my.cnf
[root@HOME001 mysql]# cat /etc/my.cnf | egrep -i "slow|long"
# In this file, you can use all long options that a program supports.
slow_query_log=ON
slow_query_log_file=home001-mysql-slow.log
long_query_time=1
[root@HOME001 mysql]#


mysql> show variables like '%slow%';
+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| log_slow_queries    | ON                     |
| slow_launch_time    | 2                      |
| slow_query_log      | ON                     |
| slow_query_log_file | home001-mysql-slow.log |
+---------------------+------------------------+
4 rows in set (0.00 sec)

mysql>

mysql> show variables like '%long%';
+---------------------------------------------------+----------+
| Variable_name                                     | Value    |
+---------------------------------------------------+----------+
| long_query_time                                   | 1.000000 |
| max_long_data_size                                | 1048576  |
| performance_schema_events_waits_history_long_size | 10000    |
+---------------------------------------------------+----------+
3 rows in set (0.00 sec)

mysql> 

Fusion-IOなどの高性能のディスクも出てきましたが、
メモリーの方が高速である事は変わりないのでパフォーマンスに影響ある
innodb_buffer_pool_sizeを適切に設定しInnoDB内のデータ領域を
メモリー内に保持する必要があります。
サーバーを1つのMYSQLインスタンス専用として利用している場合は、
物理メモリーサイズの60%~80%程度を割り当てて様子を見て調整すれば良いようです。
Linuxの古いカーネルなどを利用している場合は、InnoDBログサイズなどが大きく
ファイルシステムのキャッシュから圧迫される事でSwapが頻発してしまう可能性があるそうです。
残りのメモリーは、OSやアプリケーション接続スレッドなどに利用される。
※ 参考:unmap_mysql_logs https://github.com/yoshinorim/unmap_mysql_logs
※ Swapの確認に関しては下に追記しておきます。

mysql> show variables like '%buffer_pool%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| innodb_buffer_pool_instances | 1        |
| innodb_buffer_pool_size      | 33554432 |
+------------------------------+----------+
2 rows in set (0.01 sec)

mysql>

MySQL innodb_flush_method = O_DIRECTに関してネットで確認してみると
検証されている方も沢山いて非常に参考になります。
全ての環境でパフォーマンスが上がる訳では無さそうなので検証してしてみる必要あり。

MySQL innodb_flush_method = O_DIRECTを設定するとダイレクトI/OというOSの機能が有効になります。
ダイレクトI/Oを利用するとInnoDBのメモリー領域とディスク間のデータのやりとりに(InnoDB Buffer<->Disk)、
OSのファイルシステムキャッシュを利用しないI/O方式になるとの事。
※ オーバーヘット増:Disk -> OS Page Cache -> InnoDB Buffer
※ オーバーヘット減:Disk -> InnoDB Buffer
※ メモリーが多い環境で有効との事。

mysql> show variables like '%flush%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| flush                          | OFF   |
| flush_time                     | 0     |
| innodb_adaptive_flushing       | ON    |
| innodb_flush_log_at_trx_commit | 1     |
| innodb_flush_method            |       |
+--------------------------------+-------+
5 rows in set (0.00 sec)

mysql>

innodb_data_file_pathパラメーターに関しては、
データファイルの初期サイズやディレクトリーを指定する為に利用。
データファイルの拡張が頻繁に発生する環境では予めファイルサイズを大きめに取って、
ユーザーがサイトを利用している間にファイル拡張が発生して
パフォーマンスが落ちないようにしておく方が良いかと思います。

mysql> show variables like '%innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

innodb_autoextend_incrementがautoextendで拡張する拡張単位を指定します。
Defaultでは8MB単位になっているので必要に応じて変更。

mysql> show variables like '%innodb_autoextend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 8     |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_autoextend_increment = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%innodb_autoextend%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 10    |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql>

再起動で変更が消えてしまうので、my.cnfに設定を追記しておくこと。

[root@HOME001 mysql]# vi /etc/my.cnf
[root@HOME001 mysql]# cat /etc/my.cnf | grep innodb_autoextend_increment
innodb_autoextend_increment = 10
[root@HOME001 mysql]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@HOME001 mysql]#

innodb_log_file_sizeはInnoDBのログファイルサイズを指定出来ます。
OracleのREDOログやMS SQLのT-Logと同じなのでクラッシュリカバリーでも利用されます。
大きいとログスイッチの頻度やチェックポイント処理を下げる事が出来ますが、
多くのトランザクションログが一つのファイルに含まれて障害発生時にログの破損やリカバリー処理に
時間がかかり困る場合もあるので、パフォーマンスとリカバリーの観点から適切なサイズを選択する必要があります。

innodb_log_file_size
小さい: リカバリー高速だが更新処理が遅くなる。
大きい: 高速だがリカバリーが遅い。ログファイルの破損の注意。

mysql> show variables like '%innodb_log%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_buffer_size    | 8388608 |
| innodb_log_file_size      | 8388608 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+---------------------------+---------+
4 rows in set (0.00 sec)

mysql>


安定性からMYSQLコンパイル済みのバイナリーを利用してますが、
コンパイル済みMYSQLのConfigオプションを確認したい場合は以下のファイルを確認。


[root@HOME001 docs]# tail -n 100 /usr/local/mysql/docs/INFO_BIN
===== Information about the build process: =====
Build was run at 2012-12-10 07:35:23 on host 'loki02'

Build was done on  Linux-2.6.9-89.ELsmp using i686
Build was done using cmake 2.8.5

===== Compiler flags used (from the 'sql/' subdirectory): =====
# compile C with /usr/local/gcc-4.3.4/bin/gcc
# compile CXX with /usr/local/gcc-4.3.4/bin/gcc
C_FLAGS =  -fPIC -Wall -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/sql -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/regex -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/zlib -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/sql    -DHAVE_YASSL -DYASSL_PURE_C -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED
C_DEFINES = -DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER
CXX_FLAGS = -fno-exceptions  -fPIC -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/sql -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/regex -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/zlib -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/sql    -DHAVE_YASSL -DYASSL_PURE_C -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED
CXX_DEFINES = -DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER

Pointer size: 4

===== Feature flags used: =====
-- Cache values
CMAKE_BUILD_TYPE:STRING=RelWithDebInfo
CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql
COMMUNITY_BUILD:BOOL=ON
ENABLED_PROFILING:BOOL=ON
ENABLE_DEBUG_SYNC:BOOL=ON
ENABLE_GCOV:BOOL=OFF
FEATURE_SET:STRING=community
INSTALL_LAYOUT:STRING=STANDALONE
MYSQL_DATADIR:PATH=/usr/local/mysql/data
MYSQL_MAINTAINER_MODE:BOOL=OFF
WITH_ARCHIVE_STORAGE_ENGINE:BOOL=ON
WITH_BLACKHOLE_STORAGE_ENGINE:BOOL=ON
WITH_DEBUG:BOOL=OFF
WITH_EMBEDDED_SERVER:BOOL=ON
WITH_EXAMPLE_STORAGE_ENGINE:BOOL=OFF
WITH_EXTRA_CHARSETS:STRING=all
WITH_FEDERATED_STORAGE_ENGINE:BOOL=ON
WITH_INNOBASE_STORAGE_ENGINE:BOOL=ON
WITH_LIBEDIT:BOOL=OFF
WITH_LIBWRAP:BOOL=OFF
WITH_PARTITION_STORAGE_ENGINE:BOOL=ON
WITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON
WITH_PIC:BOOL=ON
WITH_READLINE:BOOL=ON
WITH_SSL:STRING=bundled
WITH_UNIT_TESTS:BOOL=ON
WITH_VALGRIND:BOOL=OFF
WITH_ZLIB:STRING=bundled

===== EOF =====
[root@HOME001 docs]#


参考:
MySQL 5.1のスロークエリログ
MySQL 5.1のmysqldumpslowで快速チューニング
MySQL innodb_flush_method = O_DIRECTの検討
非同期I/Oの謎
初期化パラメータ filesystemio_options の値によってI/Oに関するシステムコールがどのように変化するか
Oracleの同期IOと非同期IOについて

その他パフォーマンス関連調整項目:
MySQL InnoDBストレージエンジンのチューニング(前編)
MySQL InnoDBストレージエンジンのチューニング(後編)

mysql> show variables like '%innodb_io_capacity%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_io_capacity | 200   |
+--------------------+-------+
1 row in set (0.00 sec)

mysql>


mysql> show variables like '%innodb_doublewrite';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%_io_threads';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql>

Swapに関しての確認メモ
以下のようなコマンドで確認する事が出来る。

[root@HOME001 ~]#  /sbin/swapon -s
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       4161528 0       -1
[root@HOME001 ~]#

[root@HOME001 ~]# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       4161528 0       -1
[root@HOME001 ~]#


[root@HOME001 ~]#  cat /proc/meminfo
MemTotal:        1938948 kB
MemFree:         1679616 kB
Buffers:           10584 kB
Cached:           149116 kB
SwapCached:            0 kB
Active:            55040 kB
Inactive:         140456 kB
Active(anon):      36056 kB
Inactive(anon):        4 kB
Active(file):      18984 kB
Inactive(file):   140452 kB
Unevictable:           0 kB
Mlocked:               0 kB
HighTotal:       1189716 kB
HighFree:         995844 kB
LowTotal:         749232 kB
LowFree:          683772 kB
SwapTotal:       4161528 kB
SwapFree:        4161528 kB
Dirty:                 8 kB
Writeback:             0 kB
AnonPages:         35816 kB
Mapped:            13712 kB
Shmem:               264 kB
Slab:              37544 kB
SReclaimable:       7596 kB
SUnreclaim:        29948 kB
KernelStack:        1056 kB
PageTables:         1688 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     5131000 kB
Committed_AS:     341716 kB
VmallocTotal:     122880 kB
VmallocUsed:       13716 kB
VmallocChunk:      90828 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:       10232 kB
DirectMap2M:      897024 kB
[root@HOME001 ~]#




[root@HOME001 ~]# free -kt
             total       used       free     shared    buffers     cached
Mem:       1938948     259580    1679368          0      10836     149104
-/+ buffers/cache:      99640    1839308
Swap:      4161528          0    4161528
Total:     6100476     259580    5840896
[root@HOME001 ~]#


[root@HOME001 ~]# free -mt
             total       used       free     shared    buffers     cached
Mem:          1893        253       1640          0         10        145
-/+ buffers/cache:         97       1796
Swap:         4063          0       4063
Total:        5957        253       5704
[root@HOME001 ~]#


[root@HOME001 ~]# df -h /dev/mapper/vg_home001-lv_swap
Filesystem            Size  Used Avail Use% マウント位置
-                     940M  260K  940M   1% /dev
[root@HOME001 ~]# 

[root@HOME001 ~]# vmstat 10 -S M
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0   1639     10    145    0    0    21    10   27   64  0  0 98  1  0
 0  0      0   1639     10    145    0    0     0     0   10   41  0  0 100  0  0




NFS機能確認について
Linuxカーネルが古かったり、ネットワークカードが古いとPCI-ExpressやGB Etherのスループット
を出し切れないとの事でNFSマウントを利用して検証してみようと思いましたが、
なかなかDiscardや再送が発生する処理を出し切れてないので検証出来てません。
とりあえずNFSのレビューだけ取り合えず行いました。

メモ:
LinuxのカーネルがRHEL6などでないと、ネットワーク処理(送受信)が1コアでしか処理できず、
処理しているCPUコアが100%になってしまい処理が遅延してしまうとの事。
新しいLinuxカーネルではTx/Rx Multiqueueが改善されていて、複数のCPUコアを使用してネットワーク
送受信を処理出来るそうです。Linuxカーネルのバージョンによっては、パケットロスが発生するとの事。
CentOSであれば5.7、6以降を利用する方がいいとの事。
net.core.somaxconn(net.core.somaxconnはTCPソケットが受け付けた接続要求を格納するキュー)
などがディフォルトで128なので設定変更して暫定対応する方法も検討しても良いかもしれません。

NFSサービス用基本アプリ確認

[root@HOME001 nfs_dir]# /sbin/chkconfig --list rpcbind
rpcbind         0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root@HOME001 nfs_dir]# /sbin/chkconfig --list nfslock
nfslock         0:off   1:off   2:off   3:on    4:on    5:on    6:off
[root@HOME001 nfs_dir]# /sbin/chkconfig --list nfs
nfs             0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root@HOME001 nfs_dir]#


[root@HOME001 nfs_dir]# service rpcbind status
rpcbind (pid  1174) を実行中...
[root@HOME001 nfs_dir]#

[root@HOME001 nfs_dir]# service nfslock status
rpc.statd (pid  1192) を実行中...
[root@HOME001 nfs_dir]#

[root@HOME001 nfs_dir]# service nfs status
rpc.svcgssd は停止しています
rpc.mountd (pid 2565) を実行中...
nfsd (pid 2630 2629 2628 2627 2626 2625 2624 2623) を実行中...
rpc.rquotad (pid 2561) を実行中...
[root@HOME001 nfs_dir]#

NFS設定ファイル確認しサービスの有効化

[root@HOME001 nfs_dir]# cat /etc/hosts.allow | grep ALL
ALL : 192.168.11.0/255.255.255.0
[root@HOME001 nfs_dir]# cat /etc/hosts.deny | grep ALL
ALL:ALL
[root@HOME001 nfs_dir]#


[root@HOME001 nfs_dir]# vi /etc/exports
[root@HOME001 home]# cat /etc/exports
/home/nfs_dir 192.168.11.0/255.255.255.0(rw,sync,no_root_squash)
[root@HOME001 nfs_dir]# exportfs -ra
[root@HOME001 nfs_dir]# showmount -e
Export list for HOME001.localdomain:
/home/nfs_dir 192.168.11.0/255.255.255.0
[root@HOME001 nfs_dir]# exportfs -v
/home/nfs_dir   192.168.11.0/255.255.255.0(rw,wdelay,no_root_squash,no_subtree_check)
[root@HOME001 nfs_dir]#
[root@HOME001 home]# cat /proc/fs/nfs/exports
# Version 1.2
# Path Client(Flags) # IPs
/home/nfs_dir   192.168.11.0/255.255.255.0(rw,no_root_squash,sync,wdelay,no_subtree_check,uuid=549fba92:c5d34bdc:ad460fad:a3888c8a)
[root@HOME001 home]#

NFSクライアント側でのマウント

[root@HOME002 home]# mkdir nfs_home001
[root@HOME002 ~]# mount -t nfs -o rw home001:/home/nfs_dir /home/nfs_home001
[root@HOME002 ~]# df -h
Filesystem          サイズ  使用  残り 使用% マウント位置
/dev/hda2             9.7G  4.6G  4.7G  50% /
/dev/hda1             251M   19M  220M   8% /boot
tmpfs                 125M     0  125M   0% /dev/shm
home001:/home/nfs_dir 4.6G  382M  4.0G   9% /home/nfs_home001
[root@HOME002 ~]#


[root@HOME002 nfs_home001]# hostname
HOME002.localdomain
[root@HOME002 nfs_home001]# pwd
/home/nfs_home001
[root@HOME002 nfs_home001]# touch test_nfs | echo "test for nfs" > test_nfs
[root@HOME002 nfs_home001]# ls -lh
合計 4.0K
-rw-r--r-- 1 root root 13  1月 18 11:46 test_nfs
[root@HOME002 nfs_home001]#

もし再起動しれも継続的に利用する場合はfstabに追記

[root@HOME002 nfs_home001]# cat /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
sysfs                   /sys                    sysfs   defaults        0 0
LABEL=SWAP-hda3         swap                    swap    defaults        0 0
home001:/home/nfs_dir   /home/nfs_home001       nfs     defaults        0 0
[root@HOME002 nfs_home001]#

nfsmount

その他パフォーマンス確認
64MのファイルをNFSマウント上に作成中にnuttcpで0.1秒毎に確認して再送が発生の有無確認。
クライアントを増やして検証してみる。

dd if=/dev/zero of=/home/nfs_home001/nfs_check_file bs=64K count=1024
[root@HOME002 tools]# ./nuttcp  -v -i0.1 192.168.11.10
nuttcp-t: v7.1.6: socket
nuttcp-t: buflen=65536, nstream=1, port=5101 tcp -> 192.168.11.10
nuttcp-t: time limit = 10.00 seconds
nuttcp-t: connect to 192.168.11.10 with mss=1448, RTT=1.683 ms
nuttcp-t: send window size = 16384, receive window size = 87380
nuttcp-t: available send window = 12288, available receive window = 65535
nuttcp-r: v7.1.6: socket
nuttcp-r: buflen=65536, nstream=1, port=5101 tcp
nuttcp-r: interval reporting every 0.10 seconds
nuttcp-r: accept from 192.168.11.11
nuttcp-r: send window size = 22120, receive window size = 87380
nuttcp-r: available send window = 16590, available receive window = 65535
    0.0625 MB /   0.10 sec =    5.2407 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4874 Mbps     0 retrans
    0.1875 MB /   0.10 sec =   15.7282 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4862 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4856 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4861 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4850 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4862 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4858 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4858 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4858 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4859 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4856 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4859 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2428 Mbps     0 retrans
    0.1875 MB /   0.10 sec =   15.7293 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4851 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4862 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4859 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4854 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4861 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2427 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2429 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2430 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2428 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2427 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2428 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2431 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4854 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4861 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4860 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4859 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4823 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4881 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4857 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4856 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4857 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4837 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4861 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4840 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4858 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4865 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4851 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4861 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4862 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4863 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4860 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4836 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4860 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4864 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4861 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4848 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4839 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4862 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2427 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2430 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2428 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2428 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2429 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2430 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2429 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2425 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2431 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2430 Mbps     0 retrans
    0.0625 MB /   0.10 sec =    5.2424 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4860 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4854 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4866 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
    0.1250 MB /   0.10 sec =   10.4860 Mbps     0 retrans
    0.0000 MB /   0.10 sec =    0.0000 Mbps     0 retrans
nuttcp-t: 7.5625 MB in 10.13 real seconds = 764.50 KB/sec = 6.2628 Mbps
nuttcp-t: retrans = 0
nuttcp-t: 121 I/O calls, msec/call = 85.72, calls/sec = 11.95
nuttcp-t: 0.0user 5.9sys 0:10real 58% 0i+0d 0maxrss 0+5pf 0+417csw

nuttcp-r: 7.5625 MB in 10.13 real seconds = 764.43 KB/sec = 6.2622 Mbps
nuttcp-r: 5566 I/O calls, msec/call = 1.86, calls/sec = 549.43
nuttcp-r: 0.0user 0.1sys 0:10real 1% 0i+0d 324maxrss 0+21pf 5658+47csw
[root@HOME002 tools]#

nfstest

参考:
ネットワーク診断 ( nuttcp )
NFS(Network File System)の概要
NFS Serverとして動かす
net.core.somaxconnについて調べてみた


MYSQLにおけるバイナリーログ切り替え、操作、内容確認、リカバリーに関しての取りまとめ。

ログスイッチ
オラクルでオンラインバックアップを利用していた時には、バックアップの最後でREDOログスイッチを行ってArchiveログに書き込んで、
バックアップ中の処理も含めて確実にリストア出来る状況にしていましたが、MYSQLでもバイバリーログを同様にSwitchして同様に処理を
ログに書き込む事が出来ます。MYSQLDUMPのオプションでも(–flush-logs)を利用する事が出来ます。

ログFLUSHとSwitch

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.49 sec)

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.48 sec)

mysql>

flash-log

ログの中身を確認
2009年にもmysqlbinlogでログ操作にて一度検証した内容を改めて確認。

バイナリーログなのでCATでそのまま確認するのは難しいので以下のmysqlbinlogコマンドにて確認。


[root@HOME001 data]# mysqlbinlog --no-defaults mysql-bin.000034 | grep TABLE001
insert into TABLE001(title,comment) values('LOG20120111','after mysql-bin.000034')
insert into TABLE001(title,comment) values('LOG20120111','after mysql-bin.000034')
insert into TABLE001(title,comment) values('LOG20120111','after mysql-bin.000034')
[root@HOME001 data]#

mysqlbinlog

特定の時間以降のSQLコマンドを抜粋
リストアに利用可能:

mysqlbinlog --no-defaults --start-datetime="2013-01-13 15:19:00" mysql-bin.000034 > /tmp/today_recover.sql
[root@HOME001 data]# mysqlbinlog --no-defaults --start-datetime="2013-01-13 15:19:00" mysql-bin.000034
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130113 14:16:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.29-log created 130113 14:16:55
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
R0PyUA8BAAAAZwAAAGsAAAABAAQANS41LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 1179
#130113 15:19:02 server id 1  end_log_pos 1247  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1358057942/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1247
#130113 15:19:02 server id 1  end_log_pos 1275  Intvar
SET INSERT_ID=23/*!*/;
# at 1275
#130113 15:19:02 server id 1  end_log_pos 1424  Query   thread_id=4     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1358057942/*!*/;
insert into TABLE001(title,comment) values('LOG20120111--01','after mysql-bin.000034')
/*!*/;
# at 1424
#130113 15:19:02 server id 1  end_log_pos 1451  Xid = 33
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@HOME001 data]#

特定のPOSITION以降のSQLコマンドを抜粋
リストアに利用可能:

mysqlbinlog --no-defaults --start-position=1247 mysql-bin.000034 > /tmp/today_recover.sql
[root@HOME001 data]# mysqlbinlog --no-defaults --start-position=1247 mysql-bin.000034
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130113 14:16:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.29-log created 130113 14:16:55
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
R0PyUA8BAAAAZwAAAGsAAAABAAQANS41LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 1247
#130113 15:19:02 server id 1  end_log_pos 1275  Intvar
SET INSERT_ID=23/*!*/;
# at 1275
#130113 15:19:02 server id 1  end_log_pos 1424  Query   thread_id=4     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1358057942/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into TABLE001(title,comment) values('LOG20120111--01','after mysql-bin.000034')
/*!*/;
# at 1424
#130113 15:19:02 server id 1  end_log_pos 1451  Xid = 33
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@HOME001 data]# 

バックアップ取得とリカバリー

データを追加後にデータベースのバックアップしバイナリーログを適用する。

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 Take Full Backup');
Query OK, 1 row affected (0.43 sec)

バックアップ取得

[root@HOME001 data]# mysqldump -u root -p --single-transaction --flush-logs test > mysqldump_`date +%Y%m%d%H%M`.sql  Enter password:
[root@HOME001 data]#

dump

FULLバックアップ後にデータを3件追加

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 After  Full Backup');
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 After  Full Backup');
Query OK, 1 row affected (0.32 sec)

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 After  Full Backup');
Query OK, 1 row affected (0.39 sec)

mysql>

リストア

[root@HOME001 data]# mysql -u root -p test < mysqldump_201301131536.sql
&#91;/SQL&#93;

<strong>データ確認</strong>
[SQL]
mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | タイトル 20121224     | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
|  3 | Title 20121231        | This will go to two nodes                 |
|  4 | Title 20121231        | This will go to two nodes 2               |
|  5 | Semi-Sync20120110     | Install Plugin                            |
|  6 | Semi-Sync20120110     | Install semi-sync Plugin                  |
|  7 | Semi-Sync20120110     | Install semi-sync Plugin                  |
|  8 | Semi-Sync20120110     | Install semi-sync Plugin                  |
|  9 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 10 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 11 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 12 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 13 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 14 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 15 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 16 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 17 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 18 | Semi-Sync20120111     | Install semi-sync Plugin                  |
| 19 | LOG20120111           | after mysql-bin.000034                    |
| 20 | LOG20120111           | after mysql-bin.000034                    |
| 21 | LOG20120111           | after mysql-bin.000034                    |
| 22 | LOG20120111           | after mysql-bin.000034                    |
| 23 | LOG20120111--01       | after mysql-bin.000034                    |
| 24 | LOG20120111--01       | after mysql-bin.000034                    |
| 25 | LOG20120113           | mysql-bin.000034 Take Full Backup         |
+----+-----------------------+-------------------------------------------+
25 rows in set (0.00 sec)

mysql>

バイナリーログから最後のFULLバックアップからFULLリストア直前までのSQLコマンドを戻す。


[root@HOME001 data]# mysqlbinlog –no-defaults –stop-datetime=”2013-01-13 15:45:00″ mysql-bin.000035 > recovery.sql
[root@HOME001 data]# mysql -u root -p test < recovery.sql Enter password: [root@HOME001 data]# [/SHELL] データがリカバリーされている事を確認済み
final

留意:
複数バイナリーログをまとめてリストア可能

mysqlbinlog --no-defaults mysql-bin.000003 mysql-bin.000004 > recovery.sql

mysqlbinlogコマンドは以下のように最後にROLLBACKを追記するので、
トランザクションが複数のバイナリーログにまたがる場合は上記のように纏めて
実行しないとトランザクションの不整合が発生する。

DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@HOME001 data]#

参考

How to Read MySQL Binary Log Files (BinLog) with mysqlbinlog

4.9.3.1. リカバリの時刻指定
mysqlbinlog — バイナリログファイルを処理するためのユーティリティ

その他
MySQLバックアップ頂上決戦!! LVMスナップショット vs InnoDB Hot Backup


expire_logs_dayによるログの自動削除

MYSQLのバイナリーログファイルがディスク容量を圧縮してきているので、
バックアップのタイミングと調整してログの定期削除を実施する設定を投入。

設定前

[root@HOME001 data]# ls
DB_REPLICATION           mysql             mysql-bin.000006  mysql-bin.000012  mysql-bin.000018
HOME001.localdomain.err  mysql-bin.000001  mysql-bin.000007  mysql-bin.000013  mysql-bin.000019
HOME001.localdomain.pid  mysql-bin.000002  mysql-bin.000008  mysql-bin.000014  mysql-bin.000020
ib_logfile0              mysql-bin.000003  mysql-bin.000009  mysql-bin.000015  mysql-bin.000021
ib_logfile1              mysql-bin.000004  mysql-bin.000010  mysql-bin.000016  mysql-bin.000022
ibdata1                  mysql-bin.000005  mysql-bin.000011  mysql-bin.000017  mysql-bin.000023
[root@HOME001 data]# 

MYSQLの設定

mysql> SHOW GLOBAL VARIABLES like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> 

my.cnfに設定入れて再起動

[root@HOME001 data]# cat /etc/my.cnf | grep expire_logs_days
expire_logs_days = 7
[root@HOME001 data]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@HOME001 data]# 

設定投入後
my.cnfに設定を入れて再起動すると既に設定が反映されている。

[root@HOME001 data]# ls
DB_REPLICATION           ibdata1           mysql-bin.000027  mysql-bin.000032
HOME001.localdomain.err  mysql             mysql-bin.000028  mysql-bin.index
HOME001.localdomain.pid  mysql-bin.000024  mysql-bin.000029  performance_schema
ib_logfile0              mysql-bin.000025  mysql-bin.000030  test
ib_logfile1              mysql-bin.000026  mysql-bin.000031
[root@HOME001 data]#
mysql> SHOW GLOBAL VARIABLES like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

log-expire

その他、SQLコマンドでマニュアル削除する方法。(PURGE)

削除前

[root@HOME002 data]# ls -l mysql-bin.*
合計 35136
-rw-rw---- 1 mysql mysql      126  1月 24 16:50 mysql-bin.000001
-rw-rw---- 1 mysql mysql      126  1月 25 14:32 mysql-bin.000002
-rw-rw---- 1 mysql mysql      126  1月 25 15:24 mysql-bin.000003
-rw-rw---- 1 mysql mysql      126  1月 25 15:28 mysql-bin.000004
-rw-rw---- 1 mysql mysql      150  1月 25 18:15 mysql-bin.000005
-rw-rw---- 1 mysql mysql      150  1月 25 18:16 mysql-bin.000006
-rw-rw---- 1 mysql mysql      150  1月 25 18:17 mysql-bin.000007
-rw-rw---- 1 mysql mysql      126  1月 25 18:46 mysql-bin.000008
-rw-rw---- 1 mysql mysql      126  1月 26 18:24 mysql-bin.000009
-rw-rw---- 1 mysql mysql      221  1月 27 12:15 mysql-bin.000010
-rw-rw---- 1 mysql mysql      221  1月 27 16:11 mysql-bin.000011
-rw-rw---- 1 mysql mysql      150  1月 27 16:13 mysql-bin.000012
-rw-rw---- 1 mysql mysql      221  1月 27 16:18 mysql-bin.000013
-rw-rw---- 1 mysql mysql      221  1月 27 17:37 mysql-bin.000014
-rw-rw---- 1 mysql mysql      150  1月 27 17:39 mysql-bin.000015
-rw-rw---- 1 mysql mysql      126  1月 27 17:57 mysql-bin.000016
-rw-rw---- 1 mysql mysql      107  1月 29 12:54 mysql-bin.000017
-rw-rw---- 1 mysql mysql      323  1月 29 12:54 mysql-bin.index
[root@HOME002 data]#

削除コマンド実行
既にバックアップ対象データが取られていれば、古いバイナリーログは不要。


mysql> PURGE MASTER LOGS TO 'mysql-bin.000008';
Query OK, 0 rows affected (0.07 sec)

mysql>

PURGE

コマンド実行後
指定したバイナリーログ以前のファイルが削除されている事を確認。

[root@HOME002 data]# ls -l mysql-bin.*
-rw-rw---- 1 mysql mysql 126  1月 25 18:46 mysql-bin.000008
-rw-rw---- 1 mysql mysql 126  1月 26 18:24 mysql-bin.000009
-rw-rw---- 1 mysql mysql 221  1月 27 12:15 mysql-bin.000010
-rw-rw---- 1 mysql mysql 221  1月 27 16:11 mysql-bin.000011
-rw-rw---- 1 mysql mysql 150  1月 27 16:13 mysql-bin.000012
-rw-rw---- 1 mysql mysql 221  1月 27 16:18 mysql-bin.000013
-rw-rw---- 1 mysql mysql 221  1月 27 17:37 mysql-bin.000014
-rw-rw---- 1 mysql mysql 150  1月 27 17:39 mysql-bin.000015
-rw-rw---- 1 mysql mysql 126  1月 27 17:57 mysql-bin.000016
-rw-rw---- 1 mysql mysql 107  1月 29 12:54 mysql-bin.000017
-rw-rw---- 1 mysql mysql 190  1月 29 13:27 mysql-bin.index
[root@HOME002 data]#


MYSQLを利用してsemi-synchronous-replicationしていても人的ミスでデータが無くなってしまう事もあるので、
バックアップは必ず定期的に計画的な運用の一環として行っておく。

レプリケーションしている場合は、負荷の高いマスターDBでダンプを取得しないでも運用・バックアップ専用スレーブで
バックアップ取得すれば利用者にも迷惑かけずにデータ保護する事が出来る。

——————————————
1:スレーブDBにてダンプ取得 2:マスターに取得したダンプをコピー 3:マスターにて意図的にテーブルTruncate
4:スレーブのデータも消えている事を確認 5:マスターDBにデータをリストア 6:スレーブにてDBが復旧している事を確認。
——————————————

スレーブで取得したデータをマスターでもリストアするので一応”master-data=2”を付けてバックアップ
master-data=2オプションはバックアップ時点のバイナリーログの位置情報をダンプファイル
に記録するオプションで、バイナリーログによるロールフォワードに利用されます。

[root@HOME002 mysql]# mysqldump --single-transaction --flush-logs --master-data=2 --databases test > MYSQL_dump20130111.sql -u root -p
Enter password:
[root@HOME002 mysql]#

こちらが”master-data=1”でダンプした場合。” CHANGE MASTER TO MASTER_LOG_FILE=, MASTER_LOG_POS=”がコメントアウトされて無い。

[root@HOME002 mysql]# mysqldump --single-transaction --flush-logs --master-data=1 --databases test > MYSQL_dump20130111.sql -u root -p
Enter password:
[root@HOME002 mysql]#

diff

ダンプデータをスレーブからマスターに転送

[root@HOME002 mysql]# scp MYSQL_dump20130111.sql root@home001:/home/mysql/
root@home001's password:
MYSQL_dump20130111.sql           100% 3211     3.1KB/s   00:
[root@HOME002 mysql]# 

マスターDBにてデータを削除(仮ミスオペ)し、リストア


mysql> truncate table TABLE001;
Query OK, 0 rows affected (0.40 sec)

mysql> select * from TABLE001;
Empty set (0.00 sec)

mysql> exit
Bye
[root@HOME001 mysql]# mysql -u root -p test < MYSQL_dump20130111.sql; Enter password: [root@HOME001 mysql]# [/SHELL] マスターとスレーブにてデータが復旧されている事を確認
restore