MYSQLレプリケーションレビュー
 MYSQLでレプリケーション設定してある環境にてスレーブを追加する方法の再確認。

基本設定レビュー
MYSQL Replication Review
メンテナンス時の動作・挙動レビュー
MYSQL Replicationスレーブ停止と再開

環境
——————————
├─マスターDB(HOME001)
├─スレーブDB(HOME002)
└─スレーブDB (colinux) 新規追加

[root@HOME002 ~]# mysql -e "select @@version" -u root -p
Enter password:
+------------+
| @@version  |
+------------+
| 5.5.29-log |
+------------+
[root@HOME002 ~]#

スレーブDB(colinux)にインストールされていたMYSQLは古いバージョンだったので、
削除してHOME002からMYSQLフォルダー全部(MYSQLバイナリー、データ)を新しいスレーブにコピー。
my.cnfも既存スレーブからコピーしてserver-id だけ変更して利用。
コピー時は一時的にHOME002のMYSQLサービスを停止しましたが、STOP SLAVEしてバックアップ取得しても良い。

新規スレーブにコピーしたフォルダーの権限をMYSQLユーザーに所有権変更

[root@colinux mysql]# chown -R mysql:mysql mysql-5.5.29-linux2.6-i686/

既存スレーブのserver-id on my.cnf

[root@HOME002 ~]# hostname
HOME002.localdomain
[root@HOME002 ~]#  cat /etc/my.cnf | grep server-id | egrep -v ^#
server-id       = 2
[root@HOME002 ~]#

新規スレーブのserver-id on my.cnfを変更して既存スレーブと衝突しないよう調整

[root@colinux data]# hostname
colinux
[root@colinux data]# cat /etc/my.cnf | grep server-id | egrep -v ^#
server-id       = 3
[root@colinux data]#

マスターDBに新しいスレーブが接続出来るようにアカウントの追加

mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@'colinux' IDENTIFIED BY 'パスワード';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

新規追加したスレーブにてMYSQLのサービスを起動してエラーが無いことを確認

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 1100
               Relay_Log_File: relay-bin.000019
                Relay_Log_Pos: 1246
        Relay_Master_Log_File: mysql-bin.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1100
              Relay_Log_Space: 1396
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql>

上記設定が完了したら、実際にマスターDBにデータを入れて基本動作を確認してみる。

マスターDBにてデータ追加
add slave

既存スレーブ
slave1

新規追加スレーブ
slave2

マスターと各スレーブが同期されエラーログが記録されて無い事を確認して終了。

「留意」
マスターはマルチスレッド処理だが、スレーブ側処理はシングルスレッドなのでマスターが
64bitやメモリー増設して処理が高速化するとスレーブ側は遅延する可能性がある。

MySQL/レプリケーション

スレーブへのレプリケーションのタイムラグを解消
innodb_flush_log_at_trx_commit=2


FIOによるディスクパフォーマンス確認
HDDも以前と比較して選択肢が増えて、必要に応じてSATA,SAS,SSD,Fusion-IO等を選択し、
用件、コスト、パフォーマンス、可用性に応じて全体的なバランスからシステムを選択する必要がある。
ディスク、CPU、メモリー、ネットワークのパフォーマンスを必要に応じて確認する必要があるが、
今回はI/Oの確認の為にディスクベンチマークをFIOで行う方法の再確認。

fio
FIOの取得

[root@HOME001 tools]# wget http://brick.kernel.dk/snaps/fio-2.0.12.1.tar.bz2
--2012-12-30 15:57:22--  http://brick.kernel.dk/snaps/fio-2.0.12.1.tar.bz2
brick.kernel.dk をDNSに問いあわせています... 87.104.106.3
brick.kernel.dk|87.104.106.3|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 244365 (239K) [application/x-bzip]
`fio-2.0.12.1.tar.bz2' に保存中

100%[==============================================>] 244,365      209K/s 時間 1.1s

2012-12-30 15:57:25 (209 KB/s) - `fio-2.0.12.1.tar.bz2' へ保存完了 [244365/244365]

[root@HOME001 tools]#

FIOの展開

[root@HOME001 tools]# tar -jxf fio-2.0.12.1.tar.bz2
[root@HOME001 tools]# ls -l
合計 704
drwxrwxr-x. 11 root  root    4096 12月 19 03:55 2012 fio-2.0.12.1
-rw-r--r--.  1 root  root  244365 12月 19 03:56 2012 fio-2.0.12.1.tar.bz2
drwxrwxrwx.  6 mysql users   4096 12月 30 13:10 2012 htop-0.8.3
-rw-r--r--.  1 root  root  428061  6月 23 23:05 2009 htop-0.8.3.tar.gz
drwxr-xr-x.  5 root  root    4096 12月 29 07:21 2012 iotop-0.4.4
-rw-r--r--.  1 root  root   30334 10月 31 06:01 2011 iotop-0.4.4.tar.gz
[root@HOME001 tools]#

必要なライブラリーを追加

[root@HOME001 fio-2.0.12.1]# yum install libaio-devel
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: ftp.iij.ad.jp
 * extras: ftp.iij.ad.jp
 * updates: ftp.iij.ad.jp
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package libaio-devel.i686 0:0.3.107-10.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================
 Package                            Arch                       Version                                 Repository                  Size
========================================================================================================================================
Installing:
 libaio-devel                       i686                       0.3.107-10.el6                          base                        13 k

Transaction Summary
========================================================================================================================================
Install       1 Package(s)

Total download size: 13 k
Installed size: 19 k
Is this ok [y/N]: y
Downloading Packages:
libaio-devel-0.3.107-10.el6.i686.rpm                                                                             |  13 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : libaio-devel-0.3.107-10.el6.i686                                                                                     1/1
  Verifying  : libaio-devel-0.3.107-10.el6.i686                                                                                     1/1
Installed:
  libaio-devel.i686 0:0.3.107-10.el6
Complete!
[root@HOME001 fio-2.0.12.1]#

libaio

MAKEとインストール

[root@HOME001 fio-2.0.12.1]# make
    CC gettime.o
    CC fio.o
    CC ioengines.o
    CC init.o
    CC stat.o
    CC log.o
    CC time.o
    CC filesetup.o
    CC eta.o
    CC verify.o
省略.......
    CC engines/rdma.o
    CC profiles/tiobench.o
    CC engines/fusion-aw.o
    CC engines/falloc.o
    CC engines/e4defrag.o
    CC fio
[root@HOME001 fio-2.0.12.1]#

[root@HOME001 fio-2.0.12.1]# make install
install -m 755 -d /usr/local/bin
install fio fio_generate_plots /usr/local/bin
install -m 755 -d /usr/local/man/man1
install -m 644 fio.1 /usr/local/man/man1
install -m 644 fio_generate_plots.1 /usr/local/man/man1
[root@HOME001 fio-2.0.12.1]#

fio

実際にFIOでI/Oテストを行ってみる。
fio –directory=/tmp/ –direct=1 –rw=write –bs=4k –size=512M –numjobs=2 –runtime=100 –group_reporting –name=seq-write

[root@HOME001 fio-2.0.12.1]# fio --directory=/tmp/ --direct=1 --rw=write --bs=4k --size=512M --numjobs=2 --runtime=100 --group_reporting --name=seq-write
seq-write: (g=0): rw=write, bs=4K-4K/4K-4K/4K-4K, ioengine=sync, iodepth=1
seq-write: (g=0): rw=write, bs=4K-4K/4K-4K/4K-4K, ioengine=sync, iodepth=1
fio-2.0.12.1
Starting 2 processes
seq-write: Laying out IO file(s) (1 file(s) / 512MB)
seq-write: Laying out IO file(s) (1 file(s) / 512MB)
Jobs: 2 (f=2): [WW] [100.0% done] [0K/11584K/0K /s] [0 /2896 /0  iops] [eta 00m:00s]
seq-write: (groupid=0, jobs=2): err= 0: pid=10250: Sun Dec 30 16:16:04 2012
  write: io=1024.0MB, bw=11369KB/s, iops=2842 , runt= 92231msec
    clat (usec): min=223 , max=202116 , avg=693.03, stdev=5958.70
     lat (usec): min=225 , max=202118 , avg=695.77, stdev=5958.71
    clat percentiles (usec):
     |  1.00th=[  266],  5.00th=[  274], 10.00th=[  294], 20.00th=[  306],
     | 30.00th=[  310], 40.00th=[  314], 50.00th=[  318], 60.00th=[  326],
     | 70.00th=[  350], 80.00th=[  358], 90.00th=[  362], 95.00th=[  378],
     | 99.00th=[  892], 99.50th=[ 1112], 99.90th=[99840], 99.95th=[100864],
     | 99.99th=[100864]
    bw (KB/s)  : min= 3760, max= 7072, per=49.97%, avg=5680.87, stdev=554.02
    lat (usec) : 250=0.12%, 500=96.95%, 750=1.30%, 1000=0.84%
    lat (msec) : 2=0.39%, 4=0.02%, 10=0.01%, 20=0.01%, 50=0.01%
    lat (msec) : 100=0.04%, 250=0.32%
  cpu          : usr=1.09%, sys=14.74%, ctx=267814, majf=0, minf=0
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=0/w=0/d=262144, short=r=0/w=0/d=0

Run status group 0 (all jobs):
  WRITE: io=1024.0MB, aggrb=11369KB/s, minb=11369KB/s, maxb=11369KB/s, mint=92231msec, maxt=92231msec

Disk stats (read/write):
    dm-0: ios=0/262084, merge=0/0, ticks=0/243053, in_queue=243054, util=100.00%, aggrios=0/262205, aggrmerge=0/80, aggrticks=0/197656, aggrin_queue=197235, aggrutil=99.97%
  sda: ios=0/262205, merge=0/80, ticks=0/197656, in_queue=197235, util=99.97%
[root@HOME001 fio-2.0.12.1]#

fioでベンチマーク中にiotopでパフォーマンス確認
fio-test

ファイルが残っているのでマニュアルで削除する。

[root@HOME001 fio-2.0.12.1]# ls -lh /tmp/
合計 1.1G
srwxrwxrwx. 1 mysql mysql    0 12月 30 15:44 2012 mysql.sock
-rw-r--r--. 1 root  root  512M 12月 30 16:16 2012 seq-write.1.0
-rw-r--r--. 1 root  root  512M 12月 30 16:16 2012 seq-write.2.0
-rw-------. 1 root  root   254 12月 30 16:02 2012 yum_save_tx-2012-12-30-16-02d9sxpX.yumtx
[root@HOME001 fio-2.0.12.1]#

その他:参考値簡易測定
何もインストールしないで通常のDDコマンドでも簡易的な確認可能

[root@HOME001 tmp]# time dd if=/dev/zero of=tempfile_1M bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 35.9152 s, 29.9 MB/s

real    0m36.251s
user    0m0.006s
sys     0m2.802s
[root@HOME001 tmp]# time dd if=/dev/zero of=tempfile_1024M bs=1024M count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 38.0579 s, 28.2 MB/s

real    0m38.178s
user    0m0.000s
sys     0m3.910s
[root@HOME001 tmp]# ls -lh tempfile_*
-rw-r--r--. 1 root root 1.0G 12月 30 16:57 2012 tempfile_1024M
-rw-r--r--. 1 root root 1.0G 12月 30 16:57 2012 tempfile_1M
[root@HOME001 tmp]#

dd

参考
fioを用いたディスクIOのパフォーマンス測定
クラウドを加速させるSSD技術(前編)
書き込み速度ベンチマーク
容量指定のダミーファイルを作成したい


Windowsでは名前解決したアドレスはローカルキャッシュに残っているが、
Linuxではローカルキャッシュに残って無いので場合によっては、
名前解決の処理やネットワークの負荷を軽減する為にLinuxでも利用した方が良い場合がある。
但し、便利な反面ローカルにキャッシュが残っている事を認識してないと問題解決に
時間がかかる場合があるので、運用者は常に認識しておいた方が良いかと。

DNSMASQのインストール

[root@HOME001 htop-0.8.3]# yum install dnsmasq
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: ftp.iij.ad.jp
 * extras: ftp.iij.ad.jp
 * updates: ftp.iij.ad.jp
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package dnsmasq.i686 0:2.48-6.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                                  Arch                                  Version                                     Repository                             Size
========================================================================================================================================================================
Installing:
 dnsmasq                                  i686                                  2.48-6.el6                                  base                                  144 k

Transaction Summary
========================================================================================================================================================================
Install       1 Package(s)

Total download size: 144 k
Installed size: 281 k
Is this ok [y/N]: y
Downloading Packages:
dnsmasq-2.48-6.el6.i686.rpm                                                                                                                      | 144 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : dnsmasq-2.48-6.el6.i686                                                                                                                              1/1
  Verifying  : dnsmasq-2.48-6.el6.i686                                                                                                                              1/1

Installed:
  dnsmasq.i686 0:2.48-6.el6

Complete!
[root@HOME001 htop-0.8.3]# /sbin/chkconfig --list dnsmasq
dnsmasq         0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@HOME001 htop-0.8.3]#

自動起動する場合は、chkconfigにて自動起動設定して下さい。

ローカルからDNSを利用出来るように設定ファイルの編集
/etc/dnsmasq.conf

 [root@HOME001 htop-0.8.3]# cat /etc/dnsmasq.conf | grep "127.0.0.1" | egrep -i -v ^#
 listen-address=127.0.0.1
 [root@HOME001 htop-0.8.3]#

1

/etc/resolv.conf

 [root@HOME001 htop-0.8.3]# cat /etc/resolv.conf | grep "127.0.0.1" | egrep -i -v ^#
 nameserver 127.0.0.1
 [root@HOME001 htop-0.8.3]#

resolv

サービスの再起動

[root@HOME001 htop-0.8.3]# /etc/init.d/dnsmasq restart
Shutting down dnsmasq:                                     [  OK  ]
Starting dnsmasq:                                          [  OK  ]
[root@HOME001 htop-0.8.3]# 

dnsmasq

ローカルで名前解決が出来るか確認

[root@HOME001 htop-0.8.3]# dig @127.0.0.1 kakaku.com

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> @127.0.0.1 kakaku.com
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 37234
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;kakaku.com.                    IN      A

;; ANSWER SECTION:
kakaku.com.             411     IN      A       210.129.151.129

;; Query time: 9 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Sun Dec 30 13:53:28 2012
;; MSG SIZE  rcvd: 44

&#91;root@HOME001 htop-0.8.3&#93;# 
&#91;/SHELL&#93;

<a href="http://variable.jp/2012/12/30/linux%e3%81%ab%e3%81%8a%e3%81%91%e3%82%8bdns-cache/dig/" rel="attachment wp-att-2450"><img src="http://variable.jp/wp-content/uploads/2012/12/dig.jpg" alt="dig" width="624" height="351" /></a>

<a href="http://variable.jp/2012/12/30/linux%e3%81%ab%e3%81%8a%e3%81%91%e3%82%8bdns-cache/dig-grep/" rel="attachment wp-att-2452"><img src="http://variable.jp/wp-content/uploads/2012/12/dig-grep.jpg" alt="dig-grep" width="442" height="80" /></a>

<strong>ローカルキャッシュのリフレッシュ</strong>
[SHELL]
[root@HOME001 htop-0.8.3]# /etc/init.d/dnsmasq force-reload
Shutting down dnsmasq:                                     [  OK  ]
Starting dnsmasq:                                          [  OK  ]
[root@HOME001 htop-0.8.3]#

force-reload

参考

Dnsmasq

いろいろなキャッシュ:dnsmasq, cache proxy

dnsmasqで簡易DNSサーバ


Linuxシステムにてディスクボトルネックを確認する為のコマンド

VMSTAT

[root@HOME001 ~]# vmstat
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 1663492  10488 167444    0    0    62    19   31   58  1  0 97  2  0
[root@HOME001 ~]#

IOSTAT

[root@HOME001 ~]# iostat
Linux 2.6.32-279.el6.i686 (HOME001.localdomain)         2012年12月29日  _i686_  (1 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.66    0.00    0.54    1.83    0.00   96.98

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               2.70       142.88        44.06     284638      87784
dm-0              7.92       129.00        44.04     256994      87728
dm-1              0.16         1.29         0.00       2576          0
dm-2              0.06         0.46         0.01        922         24

[root@HOME001 ~]# 

[root@HOME001 iotop-0.4.4]# iostat -d -x
Linux 2.6.32-279.el6.i686 (HOME001.localdomain)         2012年12月29日  _i686_  (1 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.67     3.11    1.39    0.39    88.43    27.72    65.42     0.21  115.80  11.72   2.08
dm-0              0.00     0.00    1.57    3.42    80.07    27.33    21.51     1.78  356.10   3.90   1.95
dm-1              0.00     0.00    0.10    0.00     0.77     0.00     8.00     0.00    5.24   2.16   0.02
dm-2              0.00     0.00    0.05    0.05     0.37     0.38     7.93     0.00   16.82  12.62   0.12

[root@HOME001 iotop-0.4.4]#

[root@HOME001 iotop-0.4.4]# iostat -d -x dm-0 15 10
Linux 2.6.32-279.el6.i686 (HOME001.localdomain)         2012年12月29日  _i686_  (1 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
dm-0              0.00     0.00    1.53    3.32    77.73    26.54    21.51     1.73  356.00   3.92   1.90

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
dm-0              0.00     0.00    0.00    0.20     0.00     1.60     8.00     0.02  112.33 112.33   2.25

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

iostat

iostat-part

Iotop
http://guichaz.free.fr/iotop/

Linux has always been able to show how much I/O was going on (the bi and bo columns of the vmstat 1 command).
Iotop is a Python program with a top like UI used to show of behalf of which process is the I/O going on.
It requires Python ≥ 2.5 (or Python ≥ 2.4 with the ctypes module) and a Linux kernel ≥ 2.6.20 with the
TASK_DELAY_ACCT CONFIG_TASKSTATS, TASK_IO_ACCOUNTING and CONFIG_VM_EVENT_COUNTERS options on.

必要なカーネルとPythonのバージョン確認

[root@HOME001 tools]# uname -r
2.6.32-279.el6.i686
[root@HOME001 tools]# python -V
Python 2.6.6
[root@HOME001 tools]#

uname_python

ダウンロードとインストール

[root@HOME001 tools]# wget http://guichaz.free.fr/iotop/files/iotop-0.4.4.tar.gz
--2012-12-29 07:12:15--  http://guichaz.free.fr/iotop/files/iotop-0.4.4.tar.gz
guichaz.free.fr をDNSに問いあわせています... 212.27.63.130
guichaz.free.fr|212.27.63.130|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 30334 (30K) [application/x-gzip]
`iotop-0.4.4.tar.gz' に保存中

100%[=============================================================================>] 30,334      21.8K/s 時間 1.4s

2012-12-29 07:12:17 (21.8 KB/s) - `iotop-0.4.4.tar.gz' へ保存完了 [30334/30334]

[root@HOME001 tools]# tar zxvf iotop-0.4.4.tar.gz
iotop-0.4.4/
iotop-0.4.4/COPYING
iotop-0.4.4/PKG-INFO
iotop-0.4.4/NEWS
iotop-0.4.4/iotop/
iotop-0.4.4/iotop/__init__.py
iotop-0.4.4/iotop/netlink.py
iotop-0.4.4/iotop/ioprio.py
iotop-0.4.4/iotop/version.py
iotop-0.4.4/iotop/data.py
iotop-0.4.4/iotop/ui.py
iotop-0.4.4/iotop/genetlink.py
iotop-0.4.4/iotop/vmstat.py
iotop-0.4.4/.gitignore
iotop-0.4.4/iotop.py
iotop-0.4.4/ChangeLog
iotop-0.4.4/setup.py
iotop-0.4.4/README
iotop-0.4.4/iotop.1
iotop-0.4.4/setup.cfg
iotop-0.4.4/THANKS
iotop-0.4.4/bin/
iotop-0.4.4/bin/iotop
[root@HOME001 tools]#

[root@HOME001 iotop-0.4.4]# ./setup.py install
running install
running build
running build_py

install

iotopの実行

[root@HOME001 iotop-0.4.4]# /usr/bin/iotop
Total DISK READ:       0.00 B/s | Total DISK WRITE:       0.00 B/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    5 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/0]
    6 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [watchdog/0]
    7 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [events/0]
    8 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [cgroup]
    9 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [khelper]
   10 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [netns]
[root@HOME001 iotop-0.4.4]#


[root@HOME001 iotop-0.4.4]# iotop -bto --iter=1
07:27:50 Total DISK READ:       0.00 B/s | Total DISK WRITE:       0.00 B/s
    TIME  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN      IO    COMMAND
[root@HOME001 iotop-0.4.4]#

iotop

iotop_optopm

以下パッケージでも入手可能との事
sudo aptitude install iotop
sudo yum install iotop
sudo emerge iotop
sudo pacman -Sy iotop

参考
http://guichaz.free.fr/iotop/

Using iotop to check I/O and swap

htopでパフォーマンス確認


DNSクライアントにて指定するDNS設定と挙動について(/etc/resolv.conf)
1/3のDNSのうち一台でも稼動していれば、2~3秒程度で名前解決が出来る。
DNSの指定してない場合は、18秒程度で名前解決出来ないエラーが返ってくる。

[root@HOME001 usr]# man resolv.conf
RESOLV.CONF(5)             Linux Programmer’s Manual            RESOLV.CONF(5)

NAME
       resolv.conf - resolver configuration file

SYNOPSIS
       /etc/resolv.conf

nameserver Name server IP address
Internet address (in dot notation) of a name server that the resolver should
query. Up to MAXNS (currently 3, see ) name servers may be listed,
one per keyword. If there are multiple servers, the resolver library queries
them in the order listed. If no nameserver entries are present, the default is
to use the name server on the local machine. (The algorithm used is to try a
name server, and if the query times out, try the next, until out of name
servers, then repeat trying all the name servers until a maximum number of
retries are made.)

options
Options allows certain internal resolver variables to be modified.
The syntax is options option …
where option is one of the following:
debug sets RES_DEBUG in _res.options.

ndots:n
sets a threshold for the number of dots which must appear in a name given
to res_query(3) (see resolver(3)) before an initial absolute query will
be made. The default for n is 1, meaning that if there are any dots in a
name, the name will be tried first as an absolute name before any search
list elements are appended to it. The maximum value for this option is
silently capped to 15.

timeout:n
sets the amount of time the resolver will wait for a response from a
remote name server before retrying the query via a different name server.
Measured in seconds, the default is RES_TIMEOUT (currently 5, see
). The maximum value for this option is silently capped to 30.

attempts:n
sets the number of times the resolver will send a query to its name
servers before giving up and returning an error to the calling applica-
tion. The default is RES_DFLRETRY (currently 2, see ). The
maximum value for this option is silently capped to 5.

rotate sets RES_ROTATE in _res.options, which causes round robin selection of
nameservers from among those listed. This has the effect of spreading
the query load among all listed servers, rather than having all clients
try the first listed server first every time.

man

■通常時の名前解決にかかる時間

[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 43654
;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;yahoo.co.jp.                   IN      A

;; ANSWER SECTION:
yahoo.co.jp.            138     IN      A       124.83.187.140
yahoo.co.jp.            138     IN      A       203.216.243.240

;; Query time: 8 msec
;; SERVER: 8.8.8.8#53(8.8.8.8)
;; WHEN: Fri Dec 28 11:18:20 2012
;; MSG SIZE  rcvd: 61


real    0m0.019s
user    0m0.005s
sys     0m0.005s
&#91;/SHELL&#93;

<strong>■DNSの最初のエントリーにダミーDNSを設定</strong>
→ 約1秒で2nd DNSにて名前解決を実行する。
[SHELL]
[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 9092
;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;yahoo.co.jp.                   IN      A

;; ANSWER SECTION:
yahoo.co.jp.            80      IN      A       124.83.187.140
yahoo.co.jp.            80      IN      A       203.216.243.240

;; Query time: 10 msec
;; SERVER: 8.8.8.8#53(8.8.8.8)
;; WHEN: Fri Dec 28 11:19:18 2012
;; MSG SIZE  rcvd: 61


real    0m1.021s
user    0m0.005s
sys     0m0.004s
&#91;/SHELL&#93;

<strong>■DNSの最初と2番目のエントリーにダミーDNSを設定</strong>
→ 約2秒で3rd DNSにて名前解決を実行する。
[SHELL]
[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 63958
;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;yahoo.co.jp.                   IN      A

;; ANSWER SECTION:
yahoo.co.jp.            14      IN      A       124.83.187.140
yahoo.co.jp.            14      IN      A       203.216.243.240

;; Query time: 9 msec
;; SERVER: 8.8.8.8#53(8.8.8.8)
;; WHEN: Fri Dec 28 11:20:24 2012
;; MSG SIZE  rcvd: 61


real    0m2.021s
user    0m0.005s
sys     0m0.007s
&#91;/SHELL&#93; 


<strong>■3つ共に利用出来ないDNSを設定した場合</strong>
→ 約20秒に名前解決出来ないエラーを返す。
[SHELL]
[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; connection timed out; no servers could be reached

real    0m21.014s
user    0m0.008s
sys     0m0.005s

■2つ利用出来ないDNSを設定した場合
それ以外のDNSの指定無し。
→ 約18秒に名前解決出来ないエラーを返す。

[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; connection timed out; no servers could be reached

real    0m18.012s
user    0m0.005s
sys     0m0.005s

■1つ利用出来ないDNSを設定した場合
それ以外のDNSの指定無し。
→ 約15秒に名前解決出来ないエラーを返す。

[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; connection timed out; no servers could be reached

real    0m15.013s
user    0m0.008s
sys     0m0.004s

■resolv.cnfにDNSを追加してない場合
→ 約18秒に名前解決出来ないエラーを返す。

[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; connection timed out; no servers could be reached

real    0m18.014s
user    0m0.006s
sys     0m0.006s

■最後(3番目)のDNSのみ有効なDNSを設定した場合
→ 約2秒後に正常に稼動しているDNSサーバーで名前解決を行う


[root@HOME001 ~]# time dig yahoo.co.jp

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.10.rc1.el6 <<>> yahoo.co.jp
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 56911 ;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 0 ;; QUESTION SECTION: ;yahoo.co.jp. IN A ;; ANSWER SECTION: yahoo.co.jp. 157 IN A 203.216.243.240 yahoo.co.jp. 157 IN A 124.83.187.140 ;; Query time: 8 msec ;; SERVER: 8.8.8.8#53(8.8.8.8) ;; WHEN: Fri Dec 28 11:43:19 2012 ;; MSG SIZE rcvd: 61 real 0m2.019s user 0m0.004s sys 0m0.005s [/SHELL] 1つでも有効なDNSの設定があれば名前解決は2~3秒で完了
192.168.1.1と192.168.1.2はダミーDNS
last_entry

その他、OPTIONを指定した場合の挙動メモ
OPTIONSの指定のみでDNSを指定しない場合のタイムアウト
other

OPTIONSの指定して有効なDNSを指定しない場合のタイムアウト
other2

OPTIONSの指定して有効なDNSを2番目に指定した場合
other3

参考
http://www.opensource.apple.com/source/libresolv/libresolv-25.0.2/resolv.h
http://research.microsoft.com/en-us/um/redmond/projects/invisible/include/net/dns/resolv.h.htm


スレーブのハード、ソフトメンテナンス後のレプリケーション再開について

スレーブ停止中のMYSQLレプリケーション動作確認
マスターの状態(スレーブ停止中)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| TABLE001       |
+----------------+
1 row in set (0.00 sec)

mysql> desc TABLE001;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| comment | varchar(45)      | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.35 sec)

mysql> select * from TABLE001;
+----+-------------------------------------------+
| id | comment                                   |
+----+-------------------------------------------+
|  1 | This is replication test data001@20121221 |
+----+-------------------------------------------+
1 row in set (0.01 sec)

mysql>

default

列追加(スレーブ停止中)

mysql> ALTER TABLE TABLE001 ADD `title` varchar(20) DEFAULT NULL after id;
Query OK, 1 row affected (0.69 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc TABLE001;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title   | varchar(20)      | YES  |     | NULL    |                |
| comment | varchar(45)      | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

インデックス追加(スレーブ停止中)

mysql> ALTER TABLE TABLE001 ADD INDEX IDX_TABLE001_id(`id`);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

データ追加(スレーブ停止中)

mysql> select * from TABLE001;
+----+-------+-------------------------------------------+
| id | title | comment                                   |
+----+-------+-------------------------------------------+
|  1 | NULL  | This is replication test data001@20121221 |
+----+-------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into TABLE001(title,comment) values('タイトル 20121225','This is replication test when slave down');
Query OK, 1 row affected (0.44 sec)

mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | NULL                  | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>

データ更新(スレーブ停止中)

mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | NULL                  | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> update TABLE001 set title='タイトル 20121224' where id = 1;
Query OK, 1 row affected (0.42 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | タイトル 20121224     | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>

データ削除(スレーブ停止中)

mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | タイトル 20121224     | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> insert into TABLE001(title,comment) values('Title 20121225','This is replication test when slave down');
Query OK, 1 row affected (0.44 sec)

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 20121225        | This is replication test when slave down  |
+----+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> delete from TABLE001 where id = 3;
Query OK, 1 row affected (0.46 sec)

mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | タイトル 20121224     | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>

マスターのポジション確認

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |     1347 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

スレーブ起動し停止中の変更が反映されているか確認。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 1347
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 1493
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1347
              Relay_Log_Space: 1789
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql>

スレーブ側にデータが反映されている事を確認。

mysql> show variables like 'hostname';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| hostname      | HOME002.localdomain |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | タイトル 20121224     | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>

after


MYSQLレプリケーション動作レビュー
MYSQL5.5.29 GAをダウンロードして使用。

マスター  server-id = 1
スレーブ  server-id = 2

マスターDBを設定してレプリケーション用ユーザー作成

mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@'192.168.11.0/255.255.255.0' IDENTIFIED BY 'slave_password';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+------------+----------------------------+-------------------------------------------+
| user       | host                       | password                                  |
+------------+----------------------------+-------------------------------------------+
| root       | localhost                  | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| root       | 127.0.0.1                  | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| slave_user | 192.168.11.0/255.255.255.0 | *54EBB56A6317324D4A720D14C4ED56CA21D115F2 |
| root       | home001                    | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
+------------+----------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      291 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> exit
Bye


MYSQLを停止して初期データ取得
他にスレーブがいる場合は他のスレーブから取得
取得後に新規スレーブへデータ転送して転送元のMYSQLを再開

[root@HOME001 /]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@HOME001 /]# 


[root@HOME001 mysql]# tar -cvf mysql_data.tar data/
data/
data/mysql-bin.000001
data/performance_schema/
data/performance_schema/events_waits_summary_global_by_event_name.frm
data/performance_schema/rwlock_instances.frm
data/performance_schema/setup_consume


[root@HOME001 mysql]# scp mysql_data.tar mysql@home002:/home/mysql/
mysql@home002's password:
mysql_data.tar                                              100%   36MB   1.1MB/s   00:34
[root@HOME001 mysql]# /etc/


[root@HOME001 mysql]# /etc/init.d/mysql start
Starting MySQL..                                           [  OK  ]
[root@HOME001 mysql]#

ポジション確認
master_status
データ転送
mysql_scp


転送したデータを展開して、データとログディレクトリーに展開。
my.cnfに関しては、必要に応じて適宜変更。
マスターでデータ取得時に確認したステータスにスレーブを設定して開始。

[root@HOME002 mysql]# tar xvf mysql_data.tar
data/
data/mysql-bin.000001
data/performance_schema/
data/performance_schema/events_waits_summary_global_by_event_name.frm
data/performance_schema/rwlock_instances.frm
data/performance_schema/setup_consumers.frm
data/performance_schema/events_waits_history_long.frm
data/performance_schema/performance_timers.frm

[root@HOME002 mysql]# ls -l
合計 212980
drwxrwxr-x 5 mysql mysql      4096 12月 22 16:29 data
-rw-r--r-- 1 mysql mysql 180009180 12月 22 14:14 mysql-5.5.29-linux2.6-i686.tar.gz
-rw-r--r-- 1 mysql mysql  37836800 12月 22 16:33 mysql_data.tar
[root@HOME002 mysql]# rm -rf /usr/local/mysql/data
[root@HOME002 mysql]# mv data /usr/local/mysql
[root@HOME002 mysql]# 

mysql> show slave status \G
Empty set (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST = 'HOME001',
    -> master_port=3306,
    -> MASTER_USER = 'slave_user',
    -> MASTER_PASSWORD = 'slave_password',
    -> MASTER_LOG_FILE = 'mysql-bin.000004',
    -> MASTER_LOG_POS = 291;
Query OK, 0 rows affected (0.26 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1281
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 1427
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1281
              Relay_Log_Space: 2210
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
mysql>


mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  3 | system user |           | NULL | Connect | 1324 | Waiting for master to send event                                            | NULL             |
|  4 | system user |           | NULL | Connect |  323 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 12 | root        | localhost | NULL | Query   |    0 | NULL                                                                        | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql>

start_replication


エラーが無ければレプリケーションの設定変更完了
同期の動作確認を行う。

■Create table@マスターDB

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `TABLE001` (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> `comment` varchar(45) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.44 sec)

mysql>

■Slaveでのレプリケーション確認

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1521
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 1667
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1521
              Relay_Log_Space: 2450
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| TABLE001       |
+----------------+
1 row in set (0.00 sec)

mysql>

データINSERT検証
replication_status_insert

データINSERT検証 其の2
replication_status_insert2

データDELETE検証
replication_status_delete

TABLE MODIFY検証

mysql> alter table TABLE001 MODIFY COLUMN comment varchar(100);
Query OK, 32 rows affected (0.67 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql>

MYSQL-modify

過去の検証:
MYSQLレプリケーション (sqlコマンドにてslave設定)

MYSQL SLAVE STATUS確認(report-host)

その他留意事項
sync_binlogはクラッシュした場合などに重要な設定だが、パフォーマンスが大きく劣化するようなので、
5.6まではI/Oが遅いシステムなどで利用する場合は留意しておく必要がありそうです。

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

mysql> SET GLOBAL sync_binlog = 1;
Query OK, 0 rows affected (0.00 sec)

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

mysql>

MySQLのDRBD構成におけるネットワーク遅延の影響について
MySQL 5.6におけるsync_binlog=1の改善について
バイナリ ログ


来年から再びシステムスキル、英語スキル、マネージメントスキルを磨いてもう少し成長して
さらにこの先15~20年活躍する為に、先ずは検証環境を構築しておこうと思いCentOSをダウンロード。

CentOS
https://www.centos.org/
Download -> Asia
http://www.centos.org/modules/tinycontent/index.php?id=32
Japan
http://ftp.riken.jp/Linux/centos/6/isos/i386/
http://ftp.riken.jp/Linux/centos/6/isos/i386/CentOS-6.3-i386-bin-DVD1.iso
http://ftp.riken.jp/Linux/centos/6/isos/i386/CentOS-6.3-i386-bin-DVD2.iso

http://ftp.riken.jp/Linux/centos/6/isos/i386/sha1sum.txt
541f98e36a7034ab3b470ddf5e2232df3829983e CentOS-6.3-i386-bin-DVD1.iso
44d9e8652af683b844138ea2ea03e6772c18a613 CentOS-6.3-i386-bin-DVD2.iso
c596411085110dbb67fb030e667ae054afb413c8 CentOS-6.3-i386-minimal.iso
51dcbf68ddc0fc2907ecbba055bf041dc8ae7ca9 CentOS-6.3-i386-netinstall.iso
7cb1ea7eb35e4ee7112d4cd6b56528562cabc65a CentOS-6.3-i386-LiveCD.iso
f34c3f9c6880bffe6344aa6a0381275689f0bf02 CentOS-6.3-i386-LiveDVD.iso
0a4b92455c3838e5a9c1488882fe4322c3793c8c CentOS-6.3-i386-bin-DVD1to2.torrent
94605b80c6c9139a0d18d3db5a66f52c6bc04828 CentOS-6.3-i386-LiveCD.torrent
57eccb30f99903ca1d27923b0eceaff21a65c1c7 CentOS-6.3-i386-LiveDVD.torrent

ダウンロードしたイメージの整合性確認

D:\CentOS6.3>sha1sum CentOS-6.3-i386-bin-DVD1.iso
541f98e36a7034ab3b470ddf5e2232df3829983e *CentOS-6.3-i386-bin-DVD1.iso

D:\CentOS6.3>sha1sum CentOS-6.3-i386-bin-DVD2.iso
44d9e8652af683b844138ea2ea03e6772c18a613 *CentOS-6.3-i386-bin-DVD2.iso

D:\CentOS6.3>

sha1sum

あとはDVDにイメージを焼いて、DVDから起動してインストールしておく。