技術の犬小屋

Webプログラミングを餌に生きる犬のメモ帳

Posts in the MySQL category

データベースのバックアップについて、あまり意識したことが無かったので、とりあえずメモしておく。
 

ダンプファイルとトランザクションログ

MySQLのデータを完全にバックアップするには、ダンプファイルとトランザクションログが必要である。
以下より、それぞれのファイルの役割について説明する。
 

ダンプファイル

ダンプファイルとは、メモリやレジスタ、ファイル、ディスクなどのある瞬間の中身を丸ごと記録したファイルである。
 
MySQLでは、mysqldumpコマンドを使って、データベースを丸ごとエクスポートすることが出来る。
mysqldumpコマンドは以下の様な書式で記述する。
 
DBのダンプファイルを作成するには、以下のようにする。

mysqldump -u ユーザ名 -p DB名 > 出力先ファイル名

 
ダンプファイルからデータを復元するには、以下のようにする。

mysql -u ユーザ名 -p DB名 < ダンプファイル名

 
通常、ダンプファイルはバッチ処理で定期的に作成する。
 

トランザクションログ

トランザクションログ(データベースログやバイナリログとも呼ばれる)とは、クラッシュやハードウェア故障があったとしてもデータベース管理システムのACID特性を保障するための操作履歴を指す。
定期的にバッチ処理でダンプファイルを作成していても、いざデータベースへの障害が起こった場合、ダンプファイルの作成から時間が経過していればしているほど、データの巻き戻りが多く発生してしまうが、トランザクションログを追いかけることで直近で発行されたSQLを調べることが出来、巻き戻りを無くすことが出来る。
 
MySQLでは、MySQLの設定ファイルであるmy.cnfファイルに以下の設定を追加することでトランザクションログを保存することが出来る。

log-bin=mysql-bin

 
追加で以下を書いておくと、直近の7日分のみを保存することが出来る。
トランザクションログが肥大化すると、かなり容量を食うので書いておくことを推奨する。

expire_logs_days=7

 
 
以上
 
 
参考
ダンプファイルとは 〔 .dmpファイル 〕 〔 dmpファイル 〕 – 意味/解説/説明/定義 : IT用語辞典
トランザクションログ – Wikipedia
mysql binlogからDB復旧 – Qiita
MySQLのdump(ダンプ)でデータをバックアップ/復元する MySQL基礎の基礎 – 久保清隆のブログ

MySQL Workbench 6.0を使っている。Amazon EC2で作成したインスタンスにMySQLを設置し,そこにMySQL Workbenchで接続を試みたところ,失敗し,4時間ほど持っていかれたので,接続方法と接続が失敗するときの解決方法をメモしておく。
 

MySQL Workbench 6.0でSSHを経由してDBに接続する

今回はMySQL Workbenchを使って,SSHを経由してサーバのDBに接続する方法について説明する。
 
MySQL Workbench 6.0を起動すると,以下のような画面が表示される。DBに接続するには,まずは新しい接続設定を作成する必要がある。
 
mysqlworkbench_new_connection
 
上記画像の赤枠で囲った「+」ボタンから新しい接続設定を作成する画面に移行することができる。
 
mysqlworkbench_setup_new_connection
 
以下より,設定項目について説明する。
 

Connection Name

接続設定に付ける名前なので,適当に入力する。
 

Connection Method

今回は,Standard TCP/IP over SSHを選択する。
 

SSH Hostname

ホスト名とポート番号を指定する。書式としては以下のようになる。
 
ホスト名:ポート番号
 
具体例としては,以下のようになる。
 
54.238.127.117:22
 

SSH Username

SSH接続で使用するユーザ名を入力する。Amazon Linuxの場合は,デフォルトのec2-userを指定する。
 

SSH Password

SSH接続で使用するユーザのパスワードを入力する。
 

SSH Key File

SSH接続に使用する秘密鍵を指定する。Amazon Linuxの場合は,インスタンス生成時に作成した秘密鍵(pemファイル)を指定する。
 
※SSH Key Fileの指定には,日本語が含まれるフォルダ・ディレクトリ・ファイル名を指定することはできない。指定したとしても,接続の際にエラーとなる。
 

MySQL Hostname

SSH接続先のサーバにMySQLが設置されている場合は,127.0.0.1,もしくはlocalhostを指定する。どちらの指定方法もサーバ上から見たローカルを指定している。
 

MySQL Server Port

MySQLで使用するポート番号を指定する。デフォルトでは,MySQLは3306番ポートを使用するので,3306と指定する。
 

Username

MySQL内部で使用されるユーザ名を指定する。デフォルトでは,rootという名前のユーザが作成されているので,特に問題無ければrootを指定する。
 

Password

MySQL内部で使用されるユーザに設定されているパスワードを入力する。Usernameにrootを選択した場合,デフォルトではrootユーザにパスワードは設定されていないので空欄のままでよい。
 
これで,必要な設定項目の入力が完了したので,接続設定をテストするために画面下部の「Test Connection」ボタンを押して,接続のテストを行う。
以下のような画面が表示されれば,接続が成功している。
 
Connection parameters are correct
 

接続が失敗するとき

接続が上手くいかないときは,以下の3点を確認する。

  1. 秘密鍵のフォルダ・ディレクトリ・ファイル名に日本語が含まれていないか
  2. 秘密鍵にppkファイルなどを指定していないか
  3. ファイアウォールの設定で22番,及び3306番ポートへの接続を許可しているか

以下より,それぞれの点について説明する。
 

秘密鍵のフォルダ・ディレクトリ・ファイル名に日本語が含まれていないか

MySQL Workbenchの接続設定で使用した,SSH Key Fileには,日本語を含むフォルダ・ディレクトリ・ファイル名を指定することができない。もし,指定した場合は接続の際にエラーとなる。
 

秘密鍵にppkファイルなどを指定していないか

ppkファイルは,putty専用の形式に変換された秘密鍵なので,MySQLWorkbenchでは使用することができない。ppkファイルの生成元になった秘密鍵があるはずなので,そちらを使用する。
 

ファイアウォールの設定で22番,及び3306番ポートへの接続を許可しているか

ファイアウォールの設定で22番ポート,及び3306番ポートの使用を許可しなければ,DBに接続することができない。Amazon EC2であれば,AWSマネージメントコンソールから,Security Groupsに対して,22 (SSH),及び3306 (MYSQL)を許可する必要がある。
 
 
以上
 
 
参考
自分で行った検証の結果

仕事でAmazon EC2を使っている。WordPressを設置したインスタンスのIP(Public IP/Elastic IP)の変更を行った際にWordPressの動作がおかしくなった。今回はその状態になったときの対処方法をメモしておく。
 

割り当てているドメイン・固定IPを変更する際に発生する問題

WordPressを設置しているサーバの固定IPを変更したところ,WordPressページのレイアウトが崩れたり,管理画面が開けなくなる現象に遭遇した。
 

原因

WordPressをセットアップしたときに,WordPressで使用しているDBにドメイン・IPアドレスの情報が記録されており,この情報と現在のドメイン・IPアドレスの情報が一致していないと,WordPressの動作がおかしくなる。
 

対処方法

WordPressで使用しているDBにログインし,ドメイン・IPアドレスの情報が格納されたレコードのフィールドを適宜変更する。
 
まずは,DBにログインする。MySQLを使用している場合,以下のようなコマンドでログインすることができる。
 

    sudo mysql -u root -p

 
WordPressで使用しているDBを選択する。
 

mysql> use wordpressdb

 
現在のドメイン・IPアドレスの設定値を確認する。
 

mysql> select * from wp_options where option_name = 'siteurl';

 

mysql> select * from wp_options where option_name = 'home';

 
上記,2つのレコードのフィールドが現在のドメイン・IPアドレスと異なっており,変更が必要なことが確認できると思う。
 
続いて,対象フィールドを現在のドメイン・IPアドレスに変更する。
 

mysql> update wp_options set option_value = '現在のドメイン・IPアドレス' where option_name = 'siteurl';

 

mysql> update wp_options set option_value = '現在のドメイン・IPアドレス' where option_name = 'home';

 
これで変更の適用が完了した。WordPressのページを表示すると,表示が正常になっていることが確認できる。
 
 
以上
 
 
参考
EC2でWordpressの管理画面が開かなくなった: 継続は力なり?

SQLインジェクションについて,改めて勉強したので忘れないようにメモしておく。
 

SQLインジェクションとは

データベースと連動したWebサイトで,データベースへの問い合わせや操作を行うプログラムにパラメータとしてSQL文の断片を与えることにより,データベースを改ざんしたり不正に情報を入手する攻撃。また,そのような攻撃を許してしまうプログラムの脆弱性のこと。
 

具体例

例えば,ユーザによって入力されたユーザID,パスワードをそれぞれ {$userId} , {$passwd} に代入し,上記SQLにてその存在が確認できればログインできるWEBページがあるとする。
 
そのWEBページで使われているSQLは以下のようになっている。
 

SELECT * FROM ユーザマスタ WHERE ユーザID = '{$userId}' AND パスワード = '{$passwd}'

 
悪意のあるユーザがユーザID「dog」としてパスワードを入力せずにログインを試みるようなパラメータを入力した際には,以下のようなSQLとなる。
 

SELECT * FROM ユーザマスタ WHERE ユーザID = 'dog' AND パスワード = '' OR 'A' = 'A'

 
$passwd に対して ‘ OR ‘A’ = ‘A’ というパラメータを渡している。このように OR で A は A である,というような条件式を追加することで不正にログインすることが出来てしまう。
 

SQLインジェクション脆弱性への対策方法

SQLインジェクション脆弱性への対策としては,プレースホルダを使用する。ここで,パラメータ部分を示す記号「?」のことをプレースホルダと呼び,そこへ実際の値を割り当てることを「バインドする」と呼ぶ。
 
プレースホルダには,以下の2つがある。

  • 静的プレースホルダ
  • 動的プレースホルダ

 
以下,これらについて説明する。
 

静的プレースホルダ

静的プレースホルダは,JIS/ISO の規格では「準備された文(Prepared Statement)」と規定されている。これは,プレースホルダのままのSQL文をデータベースエンジン側にあらかじめ送信して,実行前に,SQL文の構文解析などの準備をしておく方式である。SQL実行の段階で,実際のパラメータの値をデータベースエンジン側に送信し,データベースエンジン側がバインド処理を行う。つまり,SQL文の構文がバインド前に確定する。このことから,セキュリティの観点で,静的プレースホルダは最も安全である。静的プレースホルダでは,SQLを準備する段階で SQL文の構文が確定し,後からSQL構文が変化することがないため,パラメータの値がリテラルの外にはみ出す現象は起きない。その結果として,SQLインジェクションの脆弱性が生じることはない。
 

動的プレースホルダ

動的プレースホルダとは,プレースホルダを利用するものの,パラメータのバインド処理をデータベースエンジン側で行うのではなく,アプリケーション側のライブラリ内で実行する方式である。動的プレースホルダは静的プレースホルダとは異なり,バインド処理を実現するライブラリによっては,SQL構文を変化させるようなSQLインジェクションを許してしまう,脆弱な実装のものが存在する可能性を否定できない。
よって,SQLインジェクション脆弱性対策には,なるべく静的プレースホルダを使用することを推奨する。
 

プログラミング言語とデータベースの組み合わせによって使用されるプレースホルダの種類が分かれる

プログラミング言語とデータベースの組み合わせで,デフォルトで使用されるプレースホルダが,静的プレースホルダか,動的プレースホルダか,に分かれる。
 
以下にプログラミング言語とデータベースの組み合わせでどちらのプレースホルダが使われるかを示す。
 

Java + Oracle

項目 内容
プレースホルダの実装 静的のみ
デフォルトで使用されるプレースホルダ 静的

 

Java + MySQL

項目 内容
※静的プレースホルダを使用するには,DB接続時にuseServerPrepStmts=trueというパラメータを指定する必要がある。
プレースホルダの実装 静的または動的
デフォルトで使用されるプレースホルダ 動的

 

PHP + MySQL

項目 内容
※静的プレースホルダを使用するには,DB接続時にPDO::setAttribute(ATTR_EMULATE_PREPARES, false)というパラメータを指定する必要がある。
プレースホルダの実装 静的または動的
デフォルトで使用されるプレースホルダ 動的

 

PHP + PostgreSQL

項目 内容
プレースホルダの実装 静的のみ
デフォルトで使用されるプレースホルダ 静的

 

Perl + MySQL

項目 内容
※静的プレースホルダを使用するには,DB接続時にmysql_server_prepare=1というパラメータを指定する必要がある。
プレースホルダの実装 静的または動的
デフォルトで使用されるプレースホルダ 動的

 

ASP.NET + SQL Server

項目 内容
プレースホルダの実装 静的のみ
デフォルトで使用されるプレースホルダ 静的

 

まとめ

  • SQLインジェクション脆弱性への対策にはプレースホルダを使う
  • 動的プレースホルダよりも,なるべく静的プレースホルダを使うことが望ましい

 
 
以上
 
 
参考
SQLインジェクション
安全なSQLの呼び出し方
PDOでATTR_EMULATE_PREPARESを適切に設定してないとSQLインジェクションの原因になるかも(MySQL編)
プリペアド・ステートメント(プレースホルダ)入門
PDOにおける一応の安全宣言と残る問題点 | 徳丸浩の日記
SQLインジェクションのまとめ – No Programming, No Life

久々にAmazon EC2のAmazon LinuxにMySQLをセットアップしようとしたところ、手順を忘れかけていたのでメモを残しておく。
 

MySQLのセットアップ手順

Amazon LinuxへMySQLをセットアップするには、以下の手順に沿って行う。

  1. yumコマンドでMySQL Client(+MySQL Shared)をインストールする
  2. yumコマンドでMySQL Serverをインストールする
  3. chkconfigコマンドで起動設定を行う
  4. MySQLを起動する
  5. MySQLにログインし、rootのパスワードを変更する

 

MySQL Client(+MySQL Shared)をインストールする

以下のコマンドを実行し、MySQL ClientとMySQL Sharedをインストールする。

sudo yum install mysql

 
これにより、MySQL-client-communityとMySQL-shared-communityがインストールされる。MySQL Clientには/usr/bin/mysqlなどのMySQLクライアント用のコマンドが含まれる。MySQL Sharedには/usr/lib64/libmysqlclient.so/usr/lib64/libmysqlclient_r.soといった共有ライブラリが含まれる。
 
※共有ライブラリをインストールすることで、過去のMySQLクライアントライブラリへ動的リンクするものがあったとしても、依存関係を壊すことなく、アップグレードすることができる。
 

MySQL Serverをインストールする

以下のコマンドを実行し、MySQL Serverをインストールする。

sudo yum install mysql-server

 
これにより、MySQL-server-communityがインストールされる。MySQL Serverにはmysqlのsysinitスクリプト、my.cnf設定ファイルなど、MySQLサーバに必要なプログラムが含まれる。mysqldもこれに含まれる。
 

chkconfigコマンドで起動設定を行う

以下のコマンドを実行し、MySQLの起動設定を行う。

sudo chkconfig mysqld on

 
chkconfigコマンドで起動設定が成功しているかを確認することができる。

chkconfig

 
以下のように表示されれば設定が成功している。

mysqld           0:off   1:off   2:on    3:on    4:on    5:on    6:off

 

MySQLを起動する

以下のコマンドを実行し、MySQLを起動する。

sudo /etc/init.d/mysqld start

 

MySQLにログインし、rootのパスワードを変更する

以下のコマンドを実行し、MySQLにroot権限でログインする。

sudo mysql -u root

 
デフォルトで作成されているデータベースを確認する。

show databases;

 
MySQL自体のユーザー情報が管理されている「mysql」データベースを使用する。

use mysql;

 
「mysql」データベースにデフォルトで作成されているテーブルを確認する。

show tables;

 
「user」テーブルにて、ユーザのユーザ名とパスワードが管理されているので、rootの情報が管理されているレコードを対象にして、パスワードに変更を加える。以下のyourpasswordの部分にrootのパスワードを入力する。

update user set password=password ('yourpassword') where user='root';

 
flushコマンドで内部キャッシュを削除する。

flush privileges;

 
一旦、ログアウトする。

quit;

 
再度、rootでログインし、パスワードの設定が上手くいっているかを確認する。

mysql -u root -p

 
これでMySQLのセットアップは一通り完了した。
 
 
以上
 
 
参考
Amazon EC2 にてAmazon Linux AMIにMySQLを入れる – Ken’s Blog
MySQL の公式 RPM を使おう | Carpe Diem