MySQLBackUp

更新日2009-07-29 (水) 12:47:25

レプリケーションを利用したバックアップ

マスターでの更新情報をバイナリログとしてスレーブに転送、これをSQLに変換しスレーブで実行しデータ同期を行うようだ。バイナリログは逐次スレーブ側に転送される?

BackUpをとるには同じDBを作製しておく必要がある。
しかし、レプリケーションするポイントが一番初めからだと必要ないかも。
DBさえあればテーブル作製、フィールド更新などはレプリケーションしてくれる。

これさえできればマスタ:スレーブがn:1で一台のBackupサーバに複数のマスターのレプリケーションが取れる

マスター

/etc/my.cnf

[mysqld]

## REPLICATION MASTER SETTING

## user = mysql
server-id = 1
log-bin
character-set-server = utf8

設定内容
SLAVE IP :10.1.4.142
User :repl

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'10.1.4.142' IDENTIFIED BY '*****';

現在のバイナリログの状態を確認

マスタのバイナリログのどの時点からレプリケートを開始すればよいかスレーブにはわからないからないので、場所を決定するに必要となる。この場所がFileとPosition。

(Master側でDBをLock) <=変更が多くないときは不要かもでも安全のため

mysql> FLUSH TABLES WITH READ LOCK;

logファイルと、logポジションを表示

 mysql> SHOW MASTER STATUS;   <=(1)
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000024 |       98 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

DBをBackUpしてスレーブ側にコピーしてからUnLock

mysql> UNLOCK TABLES;

スレーブ

/etc/my.cnf

[mysqld]

## REPLICATION SLAVE SETTING

server-id = 2
character-set-server = utf8
read-only   <=アプリケーションからの変更禁止

以下のCHANGE MASTER TO と STOP/START SLAVE;はDBを追加するごとに必要

一度CHANGE MASTER TO は実行すればOK

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='10.99.99.202', 
    -> MASTER_PORT=3306,   <=''は不要
    -> MASTER_USER='repl', 
    -> MASTER_PASSWORD='*******', 
    -> MASTER_LOG_FILE='mysql-bin.000024', <=MASTER側で表示したSTATUSの結果を記入
    -> MASTER_LOG_POS=98;  <=MASTER側で表示したSTATUSの結果を記入。''は不要

Query OK, 0 rows affected (0.04 sec)
/etc/my.cnf に設定してもOKのようだがMASTER_LOG_FILEやMASTER_LOG_POSは逐次変更するのかな?
[mysqld]
server-id=2
master-host=マスターサーバのアドレス
master-user=repl
master-password=パスワード
master-port=3306

スレーブの起動

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

START SLAVEでマスター側に自分がスレーブであることを知らせるパケットが流れるようだ。これ以降マスタのDBが変更されるごとに、スレーブが更新される。
また、MASTER_LOG_FILEやMASTER_LOG_POSが過去の場所を示すと更新時そのポイントから実行されるので、その場所から以降発行されたSQLが実行される。

たとえば

MASTER側
ID     DATA
1      aaaa
2      bbbb
3      cccc  <=この場所が指定ポイントA
4      dddd
5      eeee  <=SALVE START後挿入B

SLAVE側
ポイントAをCHANGE MASTER TO のMASTER_LOG_FILEとMASTER_LOG_POSで指定れSTART  
SLAVEが実行された場合は
ID     DATA 
4      dddd
5      eeee  
となる. (ID3も含まれるかも?)

スレーブの状態の表示

mysql> show slave status\G;

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.99.99.202
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000024
        Read_Master_Log_Pos: 98
              Relay_Log_File: localhost-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000024
           Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         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: 98
            Relay_Log_Space: 235
            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
1 row in set (0.00 sec)

ERROR:
No query specified
項目名                 内容 
Master_Host            マスタのホスト名。 
Master_User            マスタへの接続に使用するユーザ名。 
Master_Port            マスタのポート番号。 
Connect_retry          マスタと接続できなかった場合に、スレーブが再接続を試みるまでの待機秒数。 
Master_Log_File        スレーブのI/Oスレッドが現在処理中のマスタのバイナリログファイル名。 
Read_Master_Log_Pos    I/Oスレッドが読み込んだマスタのバイナリログの位置。 
Relay_Log_File         スレーブのSQLスレッドが現在処理中のスレーブのリレーログファイル名。 
Relay_Log_Pos SQL      スレッドが実行完了したスレーブのリレーログの位置。 
Relay_Master_Log_File  SQLスレッドが最後に実行したクエリが記録されていたマスタのバイナリログファイル名。 
Slave_IO_Running I/O   スレッドが稼働中かどうか。 
Slave_SQL_Running SQL  スレッドが稼働中かどうか。 
Replicate_do_db        レプリケートするように指定されているデータベース名。 
Replicate_ignore_db    レプリケートしないように指定されているデータベース名。 
Last_errno             最後に実行したクエリのエラー番号。『0』ならば成功。 
Last_error             最後に実行したクエリのエラーメッセージなど。空文字はエラーがないことを示す。 
Skip_counter           最後にSQL_SLAVE_SKIP_COUNTERを使用したときの値。使用していなければ『0』になる。 
Exec_master_log_pos    SQLスレッドが最後に実行したクエリの、マスタのバイナリログでの位置。 
Relay_log_space        存在するリレーログファイルのサイズ。単位はバイト。 

スレーブの停止

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

SLAVEが停止しないときmultiでMySQLを起動しているときはシングルのところで一度STOPさせるとうまくいくようだ

CHANGE MASTER TO を実行すると以下のファイルがSLAVEにできるので、その情報でその後再起動は問題なく動作する。

# less /var/lib/mysql/master.info
14
mysql-bin.000024
98
10.99.99.202
repl
******
3306
60
0
# less /var/lib/mysql/relay-log.info
./localhost-relay-bin.000002
235
mysql-bin.000024
98

その他の指定

レプリケーションを作製するDBやテーブルの指定
Slaveのmy.cnfに指定

[mysqld]

replicate-do-db対象となるデータベース名
replicate-ignore-db対象外となるデータベース名
replicate-do-table対象となるテーブル名指定方法:データベース名.テーブル名
replicate-ignore-table対象外となるテーブル名指定方法:データベース名.テーブル名

複数あるときは
replicate-do-db = db1
replicate-do-db = db2

のように併記する。

メンテナンス

サーバ側

MySQLはデータベース単位でバイナリログに記録する/しないの指定ができ、その内容がBinlog_do_db/Binlog_ignore_dbの欄に表示されます。特に指定していない場合は全てのデータベースがバイナリログの対象

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000024
        Position: 3683
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
項目名            内容 
File              使用中のバイナリログのファイル名。 
Position          使用中のバイナリログの位置情報。 
Binlog_do_db      バイナリログに記録するように指定されているデータベース名。 
Binlog_ignore_db  バイナリログに記録しないように指定されているデータベース名。 

バイナリログファイルも以下のように確認できるが増えるので適当に削除する。このときはスレーブのステータスのRelay_Log_File (スレーブのSQLスレッドが現在処理中のスレーブのリレーログファイル名)を指定しそれ以前を削除。

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     14329 |
| mysql-bin.000002 |       445 |
| mysql-bin.000003 |      3725 |
| mysql-bin.000004 |       656 |
(略)
| mysql-bin.000022 |       253 |
| mysql-bin.000023 |       117 |
| mysql-bin.000024 |      3683 |
+------------------+-----------+
24 rows in set (0.01 sec)

削除

mysql> PURGE MASTER LOGS TO 'mysql-bin.000023';
Query OK, 0 rows affected (0.06 sec)
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000023 |       117 |
| mysql-bin.000024 |      3683 |
+------------------+-----------+
2 rows in set (0.01 sec)

動作確認

MASTER STATUSのPosition と SLAVE STATUSのRead_Master_Log_PosおよびSTATUSのExec_master_log_pos が等しいこと

  • SHOW MASTER STATUSのPosition
  • SHOW SLAVE STATUSのRead_Master_Log_Pos
  • SHOW SLAVE STATUSのExec_master_log_pos

の値がすべて同じ場合はOK

動作確認2

スレーブ

mysql> SHOW PROCESSLIST;
+----+-------------+-----------+-------+---------+------------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time       |  State                                                                | Info             |
+----+-------------+-----------+-------+---------+------------+-----------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL  | Connect |       7708 | Waiting for master to send event                                      | NULL             |
|  2 | system user |           | NULL  | Connect | 4284212285 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  4 | root        | localhost | test2 | Query   |          0 | NULL                                                                  | SHOW PROCESSLIST |
+----+-------------+-----------+-------+---------+------------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.01 sec)

Id 1と2のようなスレッドが二つ動作していること

マスタ

mysql> SHOW PROCESSLIST;
+----+------+------------------+-------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host             | db    | Command     | Time | State                                                          | Info             |
+----+------+------------------+-------+-------------+------+----------------------------------------------------------------+------------------+
| 21 | root | localhost        | test2 | Query       |    0 | NULL                                                           | SHOW PROCESSLIST |
| 23 | repl | 10.1.4.142:56560 | NULL  | Binlog Dump |  666 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+----+------+------------------+-------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

PostgreSQLBackup

PostgreSQLのレプリケーション(pgpool-II)

概念図

            ---------------      -----------------------
            |  pgpool-II  |      | System DB           |
Client ====>|             |<====>|   (local PostgreSQL)|
            |             |      |                     | 
            ---------------      -----------------------
             |     |     |
             |     |     |
            DB     DB    DB   <=SLAVE PostgreSQL

http://pgfoundry.org/projects/pgpool/ から pgpool-II-x.x.x.tar.gzをマスタマシンにDownload.

http://pgpool.projects.postgresql.org/tutorial-ja.html#start-shutdownを参考に作業を行った

インストールにはlibpq ライブラリが必要になる。これはpostgresql-develに入っているので先にインストール。

# apt-get install postgresql-devel

pgpool-IIのインストール

$ tar zxvf pgpool-II-2.1.tgz
$ cd pgpool-II-2.1
$ ./configure
$ make
$ su
# make install

設定はpgpool.confとpool_hba.confを設定

# cd /usr/local/etc
# cp pgpool.conf.sample pgpool.conf
# cp pool_hba.conf.sample pool_hba.conf

pgpool-IIは動作上ローカル上のPostgreSQLをSystemDBとして使用するそのため、system DB infoのポートもローカル上のPostgreSQLのポート番号を変更したときは変更しておく必要がある(1)

pgpool.conf

# Replication mode
##replication_mode = false
replication_mode = true
 
# system DB info
system_db_hostname = 'localhost's
##system_db_port = 5432
system_db_port = 5433     <==ここを注意(1)
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''

# backend_hostname, backend_port, backend_weight
# here are examples
# サーバ1
#backend_hostname0 = 'host1'
backend_hostname0 = 'localhost'
#backend_port0 = 5432
backend_port0 = 5433
#backend_weight0 = 1
backend_weight0 = 1  <=参照時(select)の重み付け
#backend_data_directory0 = '/data'
backend_data_directory0 = ''

# サーバ2
#backend_hostname1 = 'host2'
#backend_port1 = 5433
#backend_weight1 = 1
#backend_data_directory1 = '/data1'
backend_hostname1 = '10.1.6.136'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ''

動作確認

$ /usr/local/bin/pgpool -n -d > /tmp/pgpool.log 2>&1 &
 -c: clears query cache. enable_query_cache must be on
 -n: don't run in daemon mode. does not detatch control tty
 -d: debug mode. lots of debug information will be printed
 stop: stop pgpool
 -h: print this help

緊急停止

$ pgpool -m fast stop

PostgreSQLの起動

マスタ側のデータベース作製

# su postgres
$ initdb -E EUC_JP --no-locale -D /var/lib/pgsql/data_m1

マスタ側のデータベース起動

$ pg_ctl -D /var/lib/pgsql/data_m1 -l logfile start
postmasterは起動中です

確認

$ ps ax
17280 pts/0    S      0:00 /usr/bin/postmaster -D /var/lib/pgsql/data_m1
17282 pts/0    S      0:00 postgres: writer process
17283 pts/0    S      0:00 postgres: stats buffer process
17284 pts/0    S      0:00 postgres: stats collector process

スレーブ側でデータベース作製

# su postgres
$ initdb -E EUC_JP --no-locale -D /var/lib/pgsql/data_s1

スレーブ側でデータベース起動

$ pg_ctl -D /var/lib/pgsql/data_s1 -l logfile start
postmasterは起動中です。
$ psql -l
            データベース一覧
   名前    |  所有者  | エンコーディング
-----------+----------+------------------
 postgres  | postgres | EUC_JP
 template0 | postgres | EUC_JP
 template1 | postgres | EUC_JP

pgbench

pgbenchでチェックを行うのでインストールするが特に必要はない

# apt-get install postgresql-contrib
(略)

準備中...                   ########################################### [100%]
   1:perl-DBD-Pg            ########################################### [ 25%]
   2:postgresql-server      ########################################### [ 50%]
   3:postgresql             ########################################### [ 75%]
   4:postgresql-contrib     ########################################### [100%]
完了

でパッケージがアップグレードされるようだ

pgpool-II動作確認

$ /usr/local/bin/pgpool 
$ createdb -p 9999 bench_replication
createdb: データベースpostgresに接続できませんでした: サーバとの接続が想定外にクローズされました
        おそらく要求の処理前または処理中にサーバが異常終了
        したことを意味しています。

pgpool起動後すぐ実行すると上のようなエラーが出る場合がある。そのときはもう一度実行してみる

$ /usr/bin/pgbench -i -p 9999 bench_replication
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit  index "branches_pkey" for table "branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "tellers_pkey" for table "tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "accounts_pkey" for table "accounts"
vacuum...done.

PostgreSQLがリモートアクセスを許可するにはpg_hba.confだけでなくpostgresql.confの設定も必要なのでスレーブ側でも行う。またここでportも指定できるので複数DBを立ち上げるときはここでport指定をする。

/var/lib/pgsql/data_s1/postgresql.conf

#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all

listen_addresses = '*'

# Portの指定
#port = 5432                            # (change requires restart)
port = 5433

listen_addresses ='localhost, 10.99.99.0'のように指定すればできるようだが、うまく動作しなかった。結局「*」だけが有効のようだ?

注意

以下のエラーで動作しなかった場合PostgreSQL7.4以降であればprotocol version は3なので問題なく動作する。しかしPostgreSQL側のpg_hba.conf で

local   all         all                               trust

が許可されていない場合起こりうる。

2008-10-21 14:57:25 LOG:   pid 10344: pool_do_auth: maybe protocol version mismatch (current version 3)

実際のSQL発行

$ psql -c "insert into t_test values(2,'bbb')" test -p 9999

これでマスタ、スレーブ両方のDBに書き込まれる

ただし、スレーブ側にもDBがないと以下のようなエラーが出る

psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

よってスレーブ側がDownしているときはDBへの接続を失敗するようだ。

このときは、マスターがpgpoolのDBとして動作しているときのポートにアクセスすると スレーブは無視されるので接続できる

/usr/local/etc/pgpool.confの

# system DB info
system_db_hostname = 'localhost'
system_db_port = 5433 <==ここのポート
$ psql -p 5433 (DB名)

でOK

またINSERT, UPDATE, DELETE した行数がすべて一致しなかった場合に、トランザクションがアボートして以下のようなエラーが出る

delete from t_data;
ERROR:  pgpool detected difference of the number of update tuples
HINT:  check data consistency between master and other db node

このときはスレーブ側のレコード数を合わせるとOKなようだ。


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2009-07-29 (水) 12:47:25 (3425d)