サーバいじくり雑記

2014年7月30日

MySQLレプリケーションスレーブをマスターに昇格させる

Filed under: Database — bompopo @ 6:00 PM

前回から少し時間が空いてしまいましたがいよいよレプリケーションスレーブをマスターに昇格させます。

※ この昇格の目的はマスターサーバからスレーブサーバへの機能の移転です。

マスター側の作業

昇格実行の前にDB更新を実行するアプリ等を停止させておきます。
WebサイトのDBであればhttpd自体を停止させたりします。ソフトが対応していればサブのDBへレプリケーションスレーブサーバを設定しておきます。

$ sudo service httpd stop

スレーブ側の作業

まずスレーブのIOスレッドを停止させその後スレーブ自体をストップさせます。その後設定をリセットします。

mysql > STOP SLAVE IO_THREAD;
mysql > STOP SLAVE ;
mysql > SET GLOBAL read_only = 0;
mysql > RESET SLAVE ALL;

my.cnfのレプリケーション設定を削除し、MySQLサーバを再起動します。

$ sudo service mysqld restart

昇格完了後の作業

これで先ほどまでスレーブだったMySQLは通常のDBとして更新可能な状態となりました。
サーバ移転のために設定したレプリケーション設定もまっさらな状態にクリアしてあります。
この時点で使っているソフト等を起動してもOKです。

$ sudo service httpd start

この先もレプリケーションを利用してバックアップ等行いたい場合は新たにレプリケーション設定をするとよいです。
完全なmysqldumpを行うにはDBをロックしなくてはならないのでレプリケーションスレーブでバックアップしたいところですな!

ということで一連のSSLなMySQLレプリケーションを試すは終了です。

広告

2014年7月20日

MySQL既存DBにレプリケーション設定をしてできるだけ無停止でスレーブを追加したい

Filed under: Database — bompopo @ 6:00 PM

さて、先日これに先立ってレプリケーション通信経路のSSL化を行ったわけですが、今回はいよいよレプリケーションの設定です。

以下こういう設定で進めます。他にもいくつか設定でてきますがそこは適宜

既存稼働中のDBを持つサーバ       = MasterServer
スレーブDBとなるサーバ           = SlaveServer
レプリケーション対象DB名         = rep_db
スレーブでDBを操作するユーザ名   = db_user
スレーブでDBを操作するパスワード = db_password
レプリケーションに使うユーザ名   = rep_user
レプリケーションに使うパスワード = rep_password

マスター側(MasterServer)の設定

レプリケーションするには権限が結構必要です。RELOADとかSUPER等は最初のmysqldump時に必要なので後で外してもOKだと思います。SSL接続に関しては先日の雑記を参照のこと。

mysql > GRANT RELOAD,SUPER,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO
            rep_user@SlaveServer IDENTIFIED BY "rep_password" REQUIRE SSL ;'
mysql > GRANT ALL ON rep_db.* TO rep_user@SlaveServer IDENTIFIED BY "rep_password" REQUIRE SSL ;

my.cnfに以下を記述します。

[mysqld]
# SSL接続用設定
ssl-ca=/usr/local/etc/ssl/ca-cert.pem
ssl-cert=/usr/local/etc/ssl/mysql/mysql-master-server-cert.pem
ssl-key=/usr/local/etc/ssl/mysql/mysql-master-server-key.pem
# レプリケーション用設定
server-id=1001
log-bin
expire_logs_days=10
binlog_do_db=rep_db

少々ダウンタイムが発生しますが、DBを再起動します。

$ sudo service mysql-server restart

レプリケーションマスターとしての動作をステータスで確認します。

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysqld-bin.000004
        Position: 2183397
    Binlog_Do_DB: rep_db
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

こんな感じでBINファイルやポジション、DB名が設定されていればOK。

スレーブ側(SlaveServer)の設定

レプリケーションDBとユーザを作成します。

mysql > CREATE DATABASE `rep_db` default character set utf8;
mysql > GRANT ALL ON rep_db.* TO db_user@localhost IDENTIFIED BY "db_password";

my.cnfに以下を設定します。

[mysqld]
server-id=1002
replicate-do-db=rep_db

DBを再起動します。

$ sudo service mysql-server restart

さぁレプリケーションスレーブの設定です。
まずは接続情報のみを設定します。

mysql > CHANGE MASTER TO
        MASTER_HOST='MasterServer',
        MASTER_USER='rep_user', 
        MASTER_PASSWORD='rep_password',
        MASTER_SSL      = 1,
        MASTER_SSL_CA   = '/usr/local/etc/ssl/ca-cert.pem';

一応設定されているか確認。他にももっと表示されますが省略しています。

mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                  Master_Host: MasterServer
                  Master_User: rep_user
                  Master_Port: 3306
              Replicate_Do_DB: rep_db
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /usr/local/etc/ssl/ca-cert.pem

さぁ、マスターからデータをまるっとダンプして取ってきましょう。
ここでは無停止を意識しているのでこの方法ですが、DBの利用の仕方、設定によっては以下のようなダンプの方法ではDBをちゃんとコピーできない場合があります。それってどんな時?っていうのはMySQLマニュアルmysqldumpの項目を読みましょう〜。

$ mysqldump -u rep_user --password='rep_password' --ssl-ca=/usr/local/etc/ssl/ca-cert.pem \
  -h MasterServer --single-transaction --quick -e --compress --master-data rep_db | \
  mysql -u rep_user --password='db_password' --compress rep_db

さて、実はログ名やポジションは先のダンプの–master-dataで付与され、それを読み込んだので勝手に設定されています。手動で設定するより簡単確実なのでできればこういう自動で設定する方法を取りたいですね。

一応ちゃんと設定されているか確認します。ログファイルとポジションが先のmysqldump取り込みにより勝手に入っているはずです。以下の出力も省略しています。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                  Master_Host: MasterServer
                  Master_User: rep_user
                  Master_Port: 3306
              Master_Log_File: mysqld-bin.000004
          Read_Master_Log_Pos: 2183397
              Replicate_Do_DB: rep_db

いよいよレプリケーションを開始します。

mysql> START SLAVE;

そしてステータス確認して、IOやSQLのRunningがYESでStateにエラーがなければ成功しているはずです。そしてマスターDBに更新が発生している場合ポジションがどんどん進んでいるはず!

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

以上、レプリケーション設定完了。突き詰めればチューニングの余地は十分ありますが最低限の設定は上記な感じですね。

次はスレーブをマスターに昇格、というか、入れ替えてご覧に入れます。

2014年7月17日

MySQLデータベースとの通信をSSL暗号化する

Filed under: ソフト, Database — bompopo @ 6:00 PM

管理してるサーバの移転でダウンタイムを最小化するためにレプリケーション機能を使いたくなったのです。しかし普通にレプリケーションした場合は生情報が通信経路に流れてしまうので、そこでSSL暗号化通信ですよ。

通信をSSL暗号化する設定

サーバ証明書だけでも自己署名してやればそれのみでSSL通信可能ですが、他の証明書作成にも使えるので自前CAは作っておきたいですね。使い回しましょう。

まずCAを作成します。

$ sudo mkdir -p /usr/local/etc/ssl/mysql/
$ sudo chown mysql:mysql /usr/local/etc/ssl/mysql/                 # ←ハマリポイントその1
$ cd /usr/local/etc/ssl/mysql/
$ sudo openssl genrsa -out ca-key.pem 2048
$ sudo chmod 400 ca-key.pem
$ sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem \
  -subj '/DC=example/DC=com/CN=CA'                                 # ←ハマリポイントその2
$ sudo chmod 444 ca-cert.pem
$ ls -l
-r--------   1 root   wheel  1667  xx xx xx:xx ca-cert.pem
-r--r--r--   1 root   wheel  1675  xx xx xx:xx ca-key.pem

サーバ証明書を作成し、CAで署名します。

$ cd /usr/local/etc/ssl/mysql/
$ sudo -u mysql openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem \
  -subj "/DC=example/DC=com/CN=MySQL\ Server"                      # ←ハマリポイントその2
$ sudo -u mysql openssl rsa -in server-key.pem -out server-key.pem # ←ハマリポイントその3
$ sudo chmod 444 server-key.pem
$ sudo openssl x509 -req -in server-req.pem -days 3650 -CA ../ca-cert.pem -CAkey ../ca-key.pem \
  -out server-cert.pem -set_serial 01
$ sudo chown mysql:mysql server-sert.pem
$ sudo chmod 444 server-sert.pem
$ ls -l
-r--r--r--  1 mysql  mysql  1318  xx xx xx:xx server-cert.pem
-r--r--r--  1 mysql  mysql  1679  xx xx xx:xx server-key.pem
-rw-r--r--  1 mysql  mysql  1074  xx xx xx:xx server-req.pem

CAとサーバ証明書の準備はできました。
次はMySQLサーバの設定に入ります。my.cnfに以下を記述。

[mysqld]
ssl-ca=/usr/local/etc/ssl/ca-cert.pem
ssl-cert=/usr/local/etc/ssl/mysql/server-cert.pem
ssl-key=/usr/local/etc/ssl/mysql/server-key.pem

SSL接続するMySQLのユーザを作ります。

$ mysql -u root --password='password' -h localhost -e \
  'GRANT ALL ON db_name.* TO ssluser@hostname IDENTIFIED BY "sslpassword" REQUIRE SSL ; FLUSH PRIVILEGES;'

ポイントは”REQUIRE SSL”の部分です。これでこのユーザは接続時にSSLが必須となります。

mysqldを再起動し、SSLが有効化されていることを確認します。

$ service mysql-server restart
$ mysql -u root --password='password' -h localhost -e \
  "show global variables like '%ssl%';"
+---------------+---------------------------------------------------------------+
| Variable_name | Value                                                         |
+---------------+---------------------------------------------------------------+
| have_openssl  | YES                                                           |
| have_ssl      | YES                                                           |
| ssl_ca        | /usr/local/etc/ssl/ca-cert.pem                        |
| ssl_capath    |                                                               |
| ssl_cert      | /usr/local/etc/ssl/mysql/mysql-master-server-cert.pem |
| ssl_cipher    |                                                               |
| ssl_key       | /usr/local/etc/ssl/mysql/mysql-master-server-key.pem  |
+---------------+---------------------------------------------------------------+

注目はhave_opensslとhave_sslの部分。ここがYESになっているとSSL通信が可能です。

実際にSSL接続して暗号化されているか確認します。
mysqlコマンドで接続する際には通常時と違い–ssl-ca引数でCA証明書ファイルを指定します。

$ mysql -u ssluser --password='sslpassword' -h hostname --ssl-ca=/usr/local/etc/ssl/mysql/ca-cert.pem -e \
 "SHOW STATUS LIKE 'Ssl_cipher';"
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

暗号化モードを表す文字列がValueに入っています。暗号化されていない場合はValueには文字列が入っていません。

さて、一連の手順を説明したところでハマリポイントの説明をします。

ハマリポイントその1

MySQLサーバが使用するサーバ証明書を配置するディレクトリの所有権はmysqldを実行するユーザでなくてはなりません。
こんな感じでエラーが出てSSLが有効になりません。

SSL error: Unable to get certificate from '/usr/local/etc/ssl/mysql/server-cert.pem'
[Warning] Failed to setup SSL
[Warning] SSL error: Unable to get certificate
ハマリポイントその2

CAとサーバ証明書は別々のCNを指定しましょう。これは証明書を検証する際にその中でユニークである必要があるのです。

ハマリポイントその3

鍵をRSAモードに変換しないとMySQLは読み込んでくれません。(実際はヘッダとフッターの一部にRSAという文字列を加えるだけの変換なのですが(笑)

おまけ:クライアント証明書認証もしたい

上記に加えてクライアント側にクライアント証明書のセットアップとサーバ側の接続ユーザをSSL条件ではなくX509条件にしておきます。

クライアント証明書の作り方はサーバ証明書と一緒なので記述はしません。
DBのユーザ作成では”REQUIRE X509″を指定します。

$ mysql -u root --password='password' -h localhost -e \
'GRANT ALL ON db_name.* TO ssluser@hostname IDENTIFIED BY "sslpassword" REQUIRE X509 ; FLUSH PRIVILEGES;'

接続する際には–ssl-caに加えて–ssl-certと–ssl-keyを使ってクライアント証明書を指定します。

$ mysql -u ssluser --password='sslpassword' -h hostname --ssl-ca=/usr/local/etc/ssl/ca-cert.pem
--ssl-cert=/usr/local/etc/ssl/mysql/client-cert.pem \
--ssl-key=/usr/local/etc/ssl/mysql/client-key.pem

2014年6月24日

データの削除は非推奨

Filed under: Database, 開発技術 — bompopo @ 9:28 PM

データの削除は非推奨

ふむふむ。確かにわからんでもない。DBの容量が問題にならなくなった今だからできるようになった手法ではありますが。あとはDBのインデックス的な問題とかが残りますが、それは力技でなんとかできる方法がすぐ思いつくのでもっと捻ればなんとかできそうな気がする。

2013年12月19日

パスワードが漏洩しないウェブアプリの作り方 〜 ソルトつきハッシュで満足する前に考えるべきこと

Filed under: セキュリティ, Database, 開発技術, Web — bompopo @ 10:22 PM

パスワードが漏洩しないウェブアプリの作り方 〜 ソルトつきハッシュで満足する前に考えるべきこと

おお、なるほど!!これはいい方法だ!!ここまで考えが至ってなかったな〜。というかストアドプロシージャとか存在を忘れていたよ(笑

ハッシュ化をアプリ側でやるとなるとソルトを読み出しできるようにするか、ハッシュ値をストアドに渡して内部でソルトを加えたのをさらにハッシュ化(ある意味ストレッチ?)する感じ?後者の方がいい感じに見えるけど、ソルト前のハッシュ値がSQLログに残る(同じ問題…)のでSQLログが読めちゃう権限取られるとヒントを増やしちゃう感じ?

どうすんのがベターじゃろか。

2013年10月6日

PostgreSQL の TOAST の仕組みと効果

Filed under: ソフト, Database, 開発技術 — bompopo @ 5:20 PM

PostgreSQL の TOAST の仕組みと効果

効果ありとされる同じASCII文字の繰り返しのみのデータなんてありえねぇことでして・・、ランダムな文字列で効果が無いとしたらそれはまだ実用レベルに達していないってことだよね。

使おうと思ってたのなぁ。

使おうとする機能が実際には期待したものではないってのは結構あるので実装前に評価はやっぱ必要だあぁ。

【5分でわかる最新キーワード解説】 NoSQLとRDBを両立する「NewSQL」って何だ?

Filed under: ソフト, Database — bompopo @ 11:41 AM

【5分でわかる最新キーワード解説】 NoSQLとRDBを両立する「NewSQL」って何だ?

実際使ってないし使ったのシステム実際に見たことがないのでなんとも言えぬ。今度色々調べてみよう。

2013年8月17日

PostgreSQL 9.3 新機能の概要

Filed under: ソフト, Database — bompopo @ 8:53 PM

PostgreSQL 9.3 新機能の概要

マテリライズドビュー面白いな。複数のビューを一気に作りデータ一貫性を保証することはできるのかな。あと地味にパラレルバックアップは嬉しい。数十ギガ単位のフルバックアップは時間かかってたですよ…。その後の圧縮の方が時間とCPU食いますがw

2013年6月8日

PostgreSQLをインストール・設定する

Filed under: ソフト, Database, FreeBSD — bompopo @ 10:40 PM

日本語環境用の初期設定をどうしたらスマートにできるかってので躓いてしまった。initdbの引数で指定できるのを今回知ったですよ。今まで普通に作った後で設定ファイルいじってたw

まずはインストール。

$ portmaster databases/postgresql92-server databases/postgresql92-client databases/postgresql92-contrib

「/etc/login.conf」に以下を追記。日本語環境用に少し手を加えています。

postgres:\
        :lang=ja_JP.UTF-8:\
        :setenv=LOCALE=ja_JP.UTF-8:\
        :tc=default:

「/etc/rc.conf」に以下を追記。同じく日本語環境用に少し手を加えています。

postgresql_enable="YES"
postgresql_data="/usr/local/pgsql/data"
postgresql_flags="-w -s -m fast"
postgresql_initdb_flags="--encoding=utf-8 --locale=ja_JP.UTF-8 --text-search-config=japanese"
postgresql_class="postgres"

「postgresql.conf」の以下をIPv4でLISTENするように変更

listen_addresses = 'XXX.XXX.XXX.XXX
port = 5432

初期化してからデーモンを起動します。

$ service postgresql initdb
$ service postgresql start

2012年12月18日

RDBMSでも大規模データをあきらめないためには 第1回 大規模データではRDBMSのどこがボトルネックになるのか?

Filed under: ソフト, Database — bompopo @ 2:48 PM

RDBMSでも大規模データをあきらめないためには 第1回 大規模データではRDBMSのどこがボトルネックになるのか?

Oracleはシルバーやっとこ取ったって初心者の頃、Linux触れるんでしょ?じゃーそれで動いてるapacheやtomcat、Oracleの設定チューニングもお願いね!と丸投げされた時のことを思い出す。

オメーら丸投げするやつらは、「運転免許証持ってるの?じゃー車バリバリ改造チューンしてくれよ」って言われるとどう思うのか。

と嫌な思い出が頭をよぎったのですが、ボトルネック見つけ出すのは正直ツライ。実運用的な負荷をかけ各種ステータス値とにらめっこして…でもRDBMSはある程度までチューンしたらどの値もいっぱいいっぱいで…でもまだ性能要件満たしてなくてOS周りも含めて色々なパラメータ調節してバランスとってまた負荷かけて…と地道で時間のかかる作業。でもリーダーは(というかその仕事場には)チューニングしたことある人おらんかったので、そんなに時間もくれなくて。みたいな!
どこぞのやってみた系HPの値をそのままコピペして終わる作業じゃねーんだよ〜。ってのをDBのチューニング作業では実感できます。

ということでこの連載にはちょっと期待!

Older Posts »