MySQLをSSLでレプリケーション

2005/05/13変更

Vine3.1の環境で、OpenSSLを利用してMySQL4.1のレプリケーションをしてみたときの記録です。自分の覚書きのつもりで書いておきます。ただし、まだ1回しか経験がありませんので、違っているところがあるかもしれません。

なお、MySQL4.0ではSSLを使ったレプリケーションはできませんので、これを行うには必ずMySQL4.1にする必要があります。

以下のメモではMySQLがインストールされていない状態から書いているので、 すでにMySQLがインストール済みならアン・インストールしておいてください。 この場合、データのバックアップをしておくなど、事前処置をお願いします。


MySQL4.1をSSL対応にしてインストール

ここでは、MySQL本家のSRPMをリビルドして、SSL対応に変更してからインストールするまでを書きます。

レプリケーション・マスターとレプリケーション・スレーブがどちらもVine3.1というつもりで書いていますが、Redhatでも同様にしてレプリケーション可能のようです。

  1. 一般ユーザーでログインします。
  2. MySQL本家から「Downloads/MySQL 4.1」の下のほうにある「Source RPM」をダウンロードします。
    ホーム・ディレクトリの ~/rpm/SRPMS 以下にダウンロードしてください。
    これを書いている時点では MySQL-4.1.11-0.src.rpm が最新バージョンでしたので、これにそって書きます。
  3. ダウンロードしたファイルをインストールします。これで ~/rpm/SPECS/mysql-4.1.11.spec ができるはずです。
    $ cd ~/rpm/SRPMS
    $ rpm -ivh MySQL-4.1.11-0.src.rpm
  4. SPECファイルを書き換えます。
    $ cd ~/rpm/SPECS
    $ emacs mysql-4.1.11.spec
    # 261行目あたりの以下を変更
    # ここではついでにデフォルト文字コードをUJISにしています
    ./configure \
    	$* \
    	--with-charset=ujis \
    	--with-vio --with-openssl=/usr \
    	--with-openssl-includes=/usr/include \
    	--with-openssl-libs=/usr/lib \
    	--enable-assembler \
    	--enable-local-infile \
    	--with-mysqld-user=%{mysqld_user} \
    	--with-unix-socket-path=/var/lib/mysql/mysql.sock \
    	--prefix=/ \
    	--with-extra-charsets=complex \
    	--exec-prefix=%{_exec_prefix} \
    	--libexecdir=%{_sbindir} \
    	--libdir=%{_libdir} \
    	--sysconfdir=%{_sysconfdir} \
    	--datadir=%{_datadir} \
    	--localstatedir=/var/lib/mysql \
    	--infodir=%{_infodir} \
    	--includedir=%{_includedir} \
    	--mandir=%{_mandir} \
    	--enable-thread-safe-client \
    	--with-readline ;
    	# Add this for more debugging support
    	# --with-debug
    	# Add this for MyISAM RAID support:
    	# --with-raid
    
    # 325行目あたりの以下を変更 BuildMySQL "--enable-shared \ --without-openssl \ ← この行を削除する --with-berkeley-db \ --with-innodb \ --with-ndbcluster \ --with-raid \ --with-archive \ --with-csv-storage-engine \ --with-example-storage-engine \ --with-embedded-server \ --with-comment=\"MySQL Community Edition - Max (GPL)\" \ --with-server-suffix='-Max'"
    # 377行目あたりの以下を変更 %ifarch i386 --with-mysqld-ldflags='-all-static' \ ← この行を削除する --with-client-ldflags='-all-static' \ ← この行を削除する $USE_OTHER_LIBC_DIR \ %endif --with-comment=\"MySQL Community Edition - Standard (GPL)\" \ --with-server-suffix='%{server_suffix}' \ --without-embedded-server \ --without-berkeley-db \ --with-innodb" ← 「\」を「"」に変更 --without-vio \ ← この行を削除する --without-openssl" ← この行を削除する nm --numeric-sort sql/mysqld > sql/mysqld.sym
  5. リビルドします。
    $ rpm -ba mysql-4.1.11.spec
  6. 出来上がったrpmをインストールします。以下は例です。これ以外にもNDB用のモジュールなども出来ていますので必要ならインストールします。
    $ cd ~/rpm/RPMS/i386
    $ su
    # rpm -Uvh MySQL-server-4.1.11-0.i386.rpm
    # rpm -Uvh MySQL-Max-4.1.11-0.i386.rpm
    # rpm -Uvh MySQL-client-4.1.11-0.i386.rpm
    # rpm -Uvh MySQL-shared-4.1.11-0.i386.rpm
    # rpm -Uvh MySQL-devel-4.1.11-0.i386.rpm
  7. SSL対応になっているか確認します。
    $ mysql -u root -p
    mysql> show variables like '%ssl%';
    この時、have_opensslが「YES」になっていればOKです。

なお、できあがったrpmはマスター・サーバ、スレーブ・サーバともインストールする必要があります。 デストリビュージョンが同じならスレーブ側はリビルドの必要がありません。ftpなどでrpmパッケージをコピーしてスレーブ側もインストールします。


マスター・サーバーの準備と設定

  1. サーバのキーを生成します。ここでは/etc/mysqlというディレクトリを作成し、この下にキーを保存することとします。
    以下の作業をrootで行います
    DIR=/etc/mysql
    PRIV=$DIR/private
    mkdir $DIR $PRIV $DIR/newcerts
    cp /usr/share/ssl/openssl.cnf $DIR
    replace ./demoCA $DIR -- $DIR/openssl.cnf
    touch $DIR/index.txt
    echo "01" > $DIR/serial
    
    # CAの生成
    openssl req -new -x509 -keyout $PRIV/cakey.pem \
    -out $DIR/cacert.pem -config $DIR/openssl.cnf
    Generating a 1024 bit RSA private key
    ......................++++++
    ...........++++++
    writing new private key to '/etc/mysql/private/cakey.pem'
    Enter PEM pass phrase:パスフレーズの入力
    Verifying - Enter PEM pass phrase:パスフレーズの入力
    -----
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter '.', the field will be left blank.
    -----
    Country Name (2 letter code) [AU]:JP
    State or Province Name (full name) [Some-State]:Mie
    Locality Name (eg, city) []:Kuwana
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:tsuttayo.sytes.net
    Organizational Unit Name (eg, section) []:
    Common Name (eg, YOUR name) []:
    Email Address []:
    
    
    # サーバ・キーの生成
    openssl req -new -keyout $DIR/server-key.pem \
    -out $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf
    Generating a 1024 bit RSA private key
    ......++++++
    ..........++++++
    writing new private key to '/etc/mysql/server-key.pem'
    Enter PEM pass phrase:パスフレーズの入力
    Verifying - Enter PEM pass phrase:パスフレーズの入力
    -----
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter '.', the field will be left blank.
    -----
    Country Name (2 letter code) [AU]:JP
    State or Province Name (full name) [Some-State]:Mie
    Locality Name (eg, city) []:Kuwana
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:tsuttayo.sytes.net
    Organizational Unit Name (eg, section) []:
    Common Name (eg, YOUR name) []:
    Email Address []:
    
    Please enter the following 'extra' attributes
    to be sent with your certificate request
    A challenge password []:
    An optional company name []:
    
    # パスフレーズの除去
    openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
    Enter pass phrase for /etc/mysql/server-key.pem:パスフレーズの入力
    writing RSA key
    
    # キーへ署名
    openssl x509 -in $DIR/server-req.pem -out $DIR/server-cert.pem \
    -req -signkey $DIR/server-key.pem -days 3650
    Signature ok
    subject=/C=JP/ST=Mie/L=Kuwana/O=tsuttayo.sytes.net
    Getting Private key
  2. my.cnfのヒナ方をコピーします。すでにmy.cnfが存在しているならこの必要はないと思います。
    # cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
  3. my.cnfを編集します。
    # emacs /etc/my.cnf

    [client]
    # デフォルトの文字セット
    default-character-set=ujis
     
    [mysqld]
    # 以下の行があるかを確認する。なければ追加しておく。
    log-bin
     
    # 以下の行があるかを確認。なければ追加しておく。
    # なお「1」は間違えやすいので適当に変えるほうがいいでしょう。
    server-id = 1
     
    # MySQLクライアント・サーバ間のパケットサイズ
    # この設定値はマスター、スレーブとも同じにしておく必要があります。
    max_allowed_packet=16M
     
    # マスターサーバのSSL関係の設定
    master-ssl       = 1
    master-ssl-ca    = /etc/mysql/cacert.pem
    master-ssl-cert  = /etc/mysql/server-cert.pem
    master-ssl-key   = /etc/mysql/server-key.pem
     
    # デフォルトの文字セット
    default-character-set=ujis
     
    [mysqldump]
    # デフォルトの文字セット
    default-character-set=ujis
     
    [mysql]
    # デフォルトの文字セット
    default-character-set=ujis

    my.cnfを変更したら、MySQLサーバを再起動して設定値を有効にします。
    # /etc/init.d/mysql restart
    # exit
  4. レプリケーション用のMySQLユーザーを作成します。
    以下ではslave_userをユーザー名、'ABCD'をパスワードにしていますので、ご自身で適当に値を変えてください。
    また、ここではSSLでのレプリケーションだけを許すようにしています。この設定ではSSL以外でレプリケーションしようとしても出来なくなります。
    $ mysql -u root -p
    mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@'%' IDENTIFIED BY 'ABCD' REQUIRE SSL;

マスター・サーバのデータを抽出(MyISAMのみの場合)

ここではMyISAMテーブルだけでできたデータベースをレプリケーションする場合のデータ抽出について書きます。 InnoDBを使ったデータベースでは以下の「マスター・サーバのデータを抽出(InnoDBが含まれる場合)」を参考にしましょう。

  1. MySQLのデータベースが保存されているディレクトリを調べます。
    $ echo 'show variables' | mysql -u root -p | grep datadir
    datadir /var/lib/mysql/
    上記のように表示されると思います。このディレクトリをひかえておきます。
  2. キャッシュされている書き込みをフラッシュし、データベースへの書き込みを禁止します。
    $ mysql -u root -p
    mysql> FLUSH TABLES WITH READ LOCK;
  3. マスター・サーバーのログ名とオフセット値を調べます。
    mysql> SHOW MASTER STATUS;
    以下のように表示されるので、FileとPositionを正確に控えてください。
    +-----------------+----------+--------------+------------------+
    | File			  | Position | Binlog_do_db | Binlog_ignore_db |
    +-----------------+----------+--------------+------------------+
    | server1-bin.065 | 4		 |				|				   |
    +-----------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    上記例では「server1-bin.065」「4」をひかえておくことになります。
    この後、mysqlコマンドからぬけないようにしてください。ロックしたままの状態で、別コンソールを起動して以下の操作してください。
  4. 現在のデータベースの状態をtarを使って圧縮保存しておきます。ここではdatadirが/var/lib/mysqlとして書いてありますので、ご自身の環境に変えてください。

    すべてのデータベースをレプリケーションするのなら、次のようにして先ほど調べたディレクトリをすべて保存しておきます。
    $ su -
    # cd /var/lib
    # tar cpzf db.tar.gz mysql

    一部のデータベースをレプリケーションするのなら、次のようにして先ほど調べたディレクトリの中の1つのディレクトリを保存しておきます。DATABASE_NAMEはレプリケーションしたいデータベース名です。
    $ su -
    # cd /var/lib
    # tar cpzf db.tar.gz mysql/DATABASE_NAME

  5. マスター・サーバのロックを解除します。さきほど、mysqlコマンドを入力したままの状態で放置してあるコンソールに切り替えて、次のようにします。
    mysql> UNLOCK TABLES;
    これで、マスター・サーバ側は通常どおり使えます。データ更新をしても問題ありません。

マスター・サーバのデータを抽出(InnoDBが含まれる場合1)

色々な方法があるようですが、以下はその中の一つの例として書きます。詳しくはMySQLのマニュアルを参照してください。

マニュアルによると、この方法ではInnoDBとMySQLが混在していてもうまくいくらしいです。ただし、とても時間がかかります。

  1. マスター・サーバ側の書き込みを禁止しておきます。
    $ mysql -u root -p
    mysql> FLUSH TABLES WITH READ LOCK;
    この後、mysqlコマンドからぬけないようにしてください。ロックしたままの状態で、別コンソールを起動して以下の操作してください。
  2. マスター・サーバ側で以下のようにしてデータを抽出します。
    $ mysqldump -uroot -p --master-data -A > master.data ←全データベースの場合
    $ mysqldump -uroot -p --master-data --databases DATABASE1 DATABASE2> master.data ←指定データベースのみ
  3. マスター・サーバのロックを解除します。さきほど、mysqlコマンドを入力したままの状態で放置してあるコンソールに切り替えて、次のようにします。
    mysql> UNLOCK TABLES;
    これで、マスター・サーバ側は通常どおり使えます。データ更新をしても問題ありません。

マスター・サーバのデータを抽出(InnoDBが含まれる場合2)

この方法でもInnoDBとMySQLが混在していてもうまくいくようです。 また、この方式はデータ抽出の時間も短いです。

欠点は、マスター・サーバをシャットダウンする必要があります。 また、一部のデータベースのみをレプリケーションすることはできません。全データベースがレプリケーションの対象となります。

  1. マスター・サーバ側の書き込みを禁止しておきます。
    $ mysql -u root -p
    mysql> FLUSH TABLES WITH READ LOCK;
    この後、mysqlコマンドからぬけないようにしてください。ロックしたままの状態で、別コンソールを起動して以下の操作してください。
  2. マスター・サーバーのログ名とオフセット値を調べます。
    mysql> SHOW MASTER STATUS;
    以下のように表示されるので、FileとPositionを正確に控えてください。
    +-----------------+----------+--------------+------------------+
    | File			  | Position | Binlog_do_db | Binlog_ignore_db |
    +-----------------+----------+--------------+------------------+
    | server1-bin.065 | 4		 |				|				   |
    +-----------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    上記例では「server1-bin.065」「4」をひかえておくことになります。
  3. この後、MySQLサーバをシャットダウンします。別のターミナルを起動して以下のようにします。
    # /etc/init.d/mysql stop
  4. 現在のデータベースの状態をtarを使って圧縮保存しておきます。ここではdatadirが/var/lib/mysqlとして書いてありますので、ご自身の環境に変えてください。
    # SVR=サーバ名
    # cd /var/lib
    # tar cpzf master.tar.gz mysql/ib_logfile* mysql/ibdata* $SVR-bin.*
  5. マスター・サーバを起動しておきます。
    # /etc/init.d/mysql start
    これで、マスター・サーバ側は通常どおり使えます。データ更新をしても問題ありません。

スレーブ・サーバの準備

  1. スレーブ・サーバにMySQL4.1をインストールします。これはマスター・サーバと同じ方法でインストールするのみです。
  2. スレーブ・サーバのキーを生成します。ここでは/etc/mysqlというディレクトリを作成し、この下にキーを保存することとします。
    以下の作業をrootで行います
    DIR=/etc/mysql
    PRIV=$DIR/private
    mkdir $DIR $PRIV $DIR/newcerts
    cp /usr/share/ssl/openssl.cnf $DIR
    replace ./demoCA $DIR -- $DIR/openssl.cnf
    touch $DIR/index.txt
    echo "01" > $DIR/serial
    
    # CAの生成
    openssl req -new -x509 -keyout $PRIV/cakey.pem \
    -out $DIR/cacert.pem -config $DIR/openssl.cnf
    Generating a 1024 bit RSA private key
    ......................++++++
    ...........++++++
    writing new private key to '/etc/mysql/private/cakey.pem'
    Enter PEM pass phrase:パスフレーズの入力
    Verifying - Enter PEM pass phrase:パスフレーズの入力
    -----
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter '.', the field will be left blank.
    -----
    Country Name (2 letter code) [AU]:JP
    State or Province Name (full name) [Some-State]:Mie
    Locality Name (eg, city) []:Kuwana
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:tsuttayo.sytes.net
    Organizational Unit Name (eg, section) []:
    Common Name (eg, YOUR name) []:
    Email Address []:
    
    # クライアント・キーの生成
    openssl req -new -keyout $DIR/client-key.pem \
    -out $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf
    Generating a 1024 bit RSA private key
    ......++++++
    ..........++++++
    writing new private key to '/etc/mysql/client-key.pem'
    Enter PEM pass phrase:パスフレーズの入力
    Verifying - Enter PEM pass phrase:パスフレーズの入力
    -----
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter '.', the field will be left blank.
    -----
    Country Name (2 letter code) [AU]:JP
    State or Province Name (full name) [Some-State]:Mie
    Locality Name (eg, city) []:Kuwana
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:tsuttayo.sytes.net
    Organizational Unit Name (eg, section) []:
    Common Name (eg, YOUR name) []:
    Email Address []:
    
    Please enter the following 'extra' attributes
    to be sent with your certificate request
    A challenge password []:
    An optional company name []:
    
    # パスフレーズの除去
    openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
    
    # キーへ署名
    openssl x509 -in $DIR/client-req.pem -out $DIR/client-cert.pem \
    -req -signkey $DIR/client-key.pem -days 3650
    Signature ok
    subject=/C=JP/ST=Mie/L=Kuwana/O=tsuttayo.sytes.net
    Getting Private key
  3. MySQLサーバをシャットダウンしておきます。
    # /etc/init.d/mysql stop
  4. 念のため、master.infoを削除しておきます。
    # rm -f /var/lib/mysql/master.info
  5. my.cnfのヒナ方をコピーします。すでにmy.cnfが存在しているならこの必要はないと思います。
    # cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
  6. my.cnfを編集します。
    # emacs /etc/my.cnf

    [client]
    # デフォルトの文字セット
    default-character-set=ujis
     
    [mysqld]
    # 以下の行があるかを確認する。なければ追加しておく。
    log-bin
     
    # 以下の行があるかを確認。なければ追加しておく。
    # マスター・サーバとは値を変えます。
    server-id = 100
     
    # レプリケーションしたいデータベース名。
    # 全データベースをレプリケーションするなら指定の必要はありません。
    replicate-do-db=DATABASE_NAME
     
    # MySQLクライアント・サーバ間のパケットサイズ
    # この設定値はマスター、スレーブとも同じにしておく必要があります。
    max_allowed_packet=16M
     
    # デフォルトの文字セット
    default-character-set=ujis
     
    [mysqldump]
    # デフォルトの文字セット
    default-character-set=ujis
     
    [mysql]
    # デフォルトの文字セット
    default-character-set=ujis

  7. 「マスター・サーバのデータを抽出(MyISAMのみの場合)」でマスター・サーバのデータを抽出した場合、スレーブ・サーバ側で解凍します。
    ftpなどでマスター・サーバからデータをコピーした後、以下のようにします。
    # cd /var/lib
    # tar zxvf db.tar.gz
    MySQLサーバを起動します。
    # /etc/init.d/mysql start
    以下のようにしてマスター・サーバを指定します。MASTER_LOG_FILEとMASTER_LOG_POSはマスター・サーバ側で「SHOW MASTER STATUS;」として調べた値です。
    mysql> STOP SLAVE;
    mysql> CHANGE MASTER TO
    mysql> MASTER_HOST='マスター・サーバのホスト名',
    mysql> MASTER_USER='slave_user',
    mysql> MASTER_PASSWORD='マスター・サーバで作成したMySQLパスワード',
    mysql> MASTER_LOG_FILE=''ログ名(Fileの値)',
    mysql> MASTER_LOG_POS=(Positionの値),
    mysql> MASTER_SSL = 1,
    mysql> MASTER_SSL_CERT = '/etc/mysql/client-cert.pem',
    mysql> MASTER_SSL_KEY = '/etc/mysql/client-key.pem';
  8. 「マスター・サーバのデータを抽出(InnoDBが含まれる場合1)」でマスター・サーバのデータを保存した場合、 ftpなどでマスター・サーバからデータをコピーした後、次のようにします。
    MySQLサーバを起動します。
    # /etc/init.d/mysql start
    # exit
    $ mysql -u root -p < master.data
    レプリケーション動作を開始させます。
    以下のようにしてマスター・サーバを指定します。
    mysql> STOP SLAVE;
    mysql> CHANGE MASTER TO
    mysql> MASTER_HOST='マスター・サーバのホスト名',
    mysql> MASTER_USER='slave_user',
    mysql> MASTER_PASSWORD='マスター・サーバで作成したMySQLパスワード',
    mysql> MASTER_SSL = 1,
    mysql> MASTER_SSL_CERT = '/etc/mysql/client-cert.pem',
    mysql> MASTER_SSL_KEY = '/etc/mysql/client-key.pem';

    なお、MySQLマニュアルではこれでいけるように書いてありますが、うちではうまくいきませんでした。
    $ head -n40 master.data
    このようにして、CHANGE MASTER TOの行を見てください。
    この値を見て、以下のようにします。
    mysql> CHANGE MASTER TO
    mysql> MASTER_LOG_FILE=''ログ名(Fileの値)',
    mysql> MASTER_LOG_POS=(Positionの値);

  9. 「マスター・サーバのデータを抽出(InnoDBが含まれる場合2)」でマスター・サーバのデータを保存した場合、 ftpなどでマスター・サーバからデータをコピーした後、次のようにします。
    # cd /var/lib
    # tar zxvf master.tar.gz
    # cd /var/lib/mysql
    # MASTER=マスターサーバ名
    # SLAVE=スレーブサーバ名
    # for sv in $MASTER-*; do
    > mv $sv `echo $sv | sed s/$MASTER/$SLAVE/`
    > done
  10. スレーブの設定がちゃんとできているか、確認します。
    mysql> SHOW SLAVE STATUS\G
    特に「Master_Host」「Master_User」「Master_Log_File」「Read_Master_Log_Pos」がちゃんと設定されているかを確認します。
    うまく設定されていないなら、「CHANGE MASTER TO」というSQL文で設定してください。
  11. スレーブ・スレッドを開始します。
    mysql> START SLAVE;
    mysql> exit

参考にした資料

Official MySQL Japanese Manual

Copyright©2001-2019 釣ったよ! All Right Reserved.    sg@tsuttayo.jpn.org