MySQLのバイナリーログからデータ更新過程を確認したい

CMSなどで同じ記事が何度も更新されたとして、DBにある最終形は別にして、記事が更新された過程を知りたいといことがまれにある。この過程をバイナリーログ(binlog)から復元する方法を RDS MySQL 固有の動きを中心にメモ。 環境は データベースは RDS 版 MySQL(5.6) クライアントは EC2 上の Amazon Linux バイナリーログの用途 バイナリーログはテーブルへの更新を連続的に記録するもので レプリケーション 差分バックアップ/リストア などで利用することが可能。 binlog の中を覗いてみる 次にメインの binlog を覗いてみる。 binlog を確認 MySQL に接続して show binary logs コマンドで確認する。 binlog をローカルに落とす binlog のファイル一覧を確認したところで、binlog ファイルをローカルに落として中身を確認する。 あとはファイル内のレプリケーション用SQLからお目当てのSQLを探し当てれば OK。 リカバリ目的などで生の binlog が欲しい時は MySQL5.6 から追加された –raw も渡すこと。バイナリーログも mysqlbinlog に直接食わせればテキスト形式に変換できる。 binlog 関連の設定 binlog retention period 通常の MySQL

AWS CLIを使ってマルチAZなElastiCache Redisのリードレプリカを作成する

ゴール AWS ElastiCache は RDS と同じくマルチ AZ に対応している。 Q: Does Amazon ElastiCache for Redis support Multi-AZ operation? Yes, with Amazon ElastiCache for Redis you can create a read replica in another AWS Availability Zone. Upon a failure of the primary node, we will provision a new primary node. In scenarios where the primary node cannot

AWS CLIを使ってElasciCache Redisのパラメーターを変更する

通常の Redis では、パラメーターは CONFIG コマンド経由で変更する。 http://redis.io/topics/config 一方で、マネージド・サービスである AWS ElasciCache Redis では、運用にインパクトを与える CONFIG のようなコマンドは利用できないようになっている。(redis.conf で rename-command CONFIG “”のように設定すれば良い) Restricted Commands In order to deliver a managed service experience, ElastiCache restricts access to certain cache engine-specific commands that require advanced privileges. … For cache clusters running Redis, the following commands are unavailable: bgrewriteaof bgsave config debug migrate

Redisでアクセスランキングを実装

ニュースサイトのサイドメニューでよく見かける「アクセスの多かった記事」のようなランキングを Redis のデータ型 Sorted Set で実装する方法をメモ。 東洋経済の例 Redis の Sorted Set を使ったアクセスランクの表現 Redis のデータ型 sorted set は文字通り順序付けられた集合。 key 単位で集合を定義でき、各メンバーはスコアを持ち、スコアによって集合内で順位付けられる。 メンバーを記事、スコアをアクセス数とみなして、アクセスランクを表現する。 日別ランキングであれば下図のようになる。 週別ランキングであれば下図のようになる。 スコアの大きい順(=アクセスの多い順)に並べればアクセスランキングの完成となる。 Sorted Set の操作 次にアクセスされた時の Sorted Set の操作を考える。 キーは YYYYMMDD で持ち、アクセスされるたびに、記事のスコアを1増やす。 スコアを明示的に指定してメンバー追加するときは ZADD を使って ZADD key score member というようにやるけれど、1ずつインクレメントするので ZINCRBY をつかう。 コマンドのシンタックスは ZINCRBY key increment member ZINCRBY myzset 1 “two” の例でもわかるように、メンバーが存在しない場合はスコアの初期値は 0 となる。

Redisで最新更新されたN件を実装

最近更新されたN件を表示させるには 最近更新したアイテムほど上位に表示 下位・ランク外のアイテムも、更新すれば最上位に表示 同じアイテムが短期間に複数更新されても重複表示しない N件を超えたアイテムの情報は不要なので破棄 といったことが必要になる。 このデータ構造を Redis のデータ型 Sorted Set で実装する方法をメモ。 Sorted Set を少し触ってみる Redis のデータ型 sorted set は文字通り順序付けられた集合で Z で始まるコマンドを使ってデータを操作する。 ZADD key score member のシンタックスで集合のメンバーを追加 計算量は O(log(N)) myzset 集合のメンバーを score でソート メンバー baz のスコアを3から1に変更 myzset 集合のメンバーを score のランキング順で再ソート 集合なのでメンバー baz は重複登録されず、スコアが 2 のメンバー bar よりも上位に表示されている。 Sorted Set で 最近のN件を実装 音楽再生サイトを運営しているとして、この Sorted Set を使って最近再生された曲N件を実装してみる。

PostgreSQL9.3をカラム指向ストレージ(cstore_fdw)に対応させる

分析向けデータベースを展開している CitusDB が PostgreSQL を列指向ストレージ対応させる foreign data wrapper(cstore_fdw) をオープンソース化したので、とりあえずインストールしてみた。 cstore_fdw の特徴 github の cstore_fdw に特徴がまとめられている。 http://citusdata.github.io/cstore_fdw/ 箇条書きすると Faster Analytics – Reduce analytics query disk and memory use by 10x Lower Storage – Compress data by 3x Easy Setup – Deploy as standard PostgreSQL extension Flexibility – Mix row- and column-based tables in the same DB

MySQLのauto_incrementについてメモ

MySQL には Oracle/PostgreSQL の sequence にあたるものがないので、自動採番するときはカラムを auto_increment にする。 http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html auto_increment の指定 初期値の指定 デフォルトでは 1 から採番される。 ALTER TABLE で AUTO_INCREMENT の初期値を指定する AUTO_INCREMENT カラムの最大値より小さい値を指定した時は無視されるので注意が必要。 初期値は CREATE TABLE 時に指定することもできる。 初期値、増加幅を指定 Oracle/PostgreSQL は create sequence test_seq start with 5 increment by 10;とすると、初期値が 5 で 10 ずつ増えるシーケンスを作成できる。 MySQL では初期値やステップはシステム変数でこれらが管理されていて、 start with に対応するのが auto_increment_increment, increment by に対応するのが auto_increment_offset となる auto_increment_offset >

memcachedのバージョンを調べる

$ memcached –help とか $ memcached -v とかやっても、memcached のバージョンが表示されなかったので。 ヘルプメッセージに1行目を確認 memcached プロトコルをしゃべる memcached プロトコルはシンプル telnet 経由でバージョンを調べるには次のようにする netcat(nc) を使うと次のようにできる

MySQLのLIKEが遅い

検索ボックスから入力すると WHERE column_name LIKE “%foo%” というような部分一致検索する機能があり、この処理が非常に遅い。 % で始まる LIKE はインデックスを使えないから。 データベースが MySQL 5.1(InnoDB) の場合に、これをはやくするアプローチを考えてみる MySQL の設定を見直す 設定がデフォルトのままだったら、チューニングする。 ベースとなる設定 http://blog.nomadscafe.jp/2012/10/mysql-mycnf-github.html SQL を見直す explainで実行計画を確認し LIKE 以外の箇所で改善点がないか見直す。 今回のケースでは、サブクエリーがフルスキャンしていたので、インデックスを追加して3000倍達成。 前方一致検索にする インデックスが使えるように、部分一致検索(LIKE “%foo%”)はやめて前方一致検索(LIKE “foo%”)にする 完全一致や前方一致で十分なのに、無意味に部分一致になっている箇所があれば完全/前方一致にする。 要件によってはどうしても部分一致が必要ということもあるので、ケースバイケース フルテキスト検索を利用 MySQL の Full-Text Search を利用する 残念なことに、この機能はストレージエンジン MyISAM に対して提供されてきた機能で、InnoDB は 5.6 以降でしか利用できない。 更に残念なことに、パーサーは欧米言語仕様なので、分かち書きをしてあげないとまともに使えない The FULLTEXT parser determines where words start and end by looking

MySQL:レプリケーションでエラーステートメントの実行を無視する

MySQL のステートメントベースレプリケーション(SBR)で、エラーの起きているステートメントの実行を飛ばすには、スレーブで以下の作業を行う AWS RDS ではストアドプロシージャ rds_skip_repl_error を呼ぶ 実行例 MASTER 側で実行すべき CREATE TABLE 文を SLAVE 側で実行し、そのあと MASTER でも実行してしまい、レプリケート時にエラーが発生したケースを考える。 (このようなケースは IF NOT EXISTS で CREATE TABLE するとか SLAVE は READ-ONLY にするとかで回避できるけど) CREATE TABLE の実行に失敗し、SLAVE_SQL_Running, Last_Error, Last_SQL_Error あたりからエラーが起きていることがわかる SQL_SLAVE_SKIP_COUNTER で問題のステートメントを 1 文だけスキップする。 レプリケーションエラーが起きていないことを確認 References High Performance MySQL, 3rd Edition, Replication Problems and Solutions(pp.495) http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#Appendix.MySQL.CommonDBATasks.SkipError