MySQLのインスタンスを複数設けず、スレーブとしたセッションのみをリードオンリーにする

はじめに

レプリケーション前提の実装を行うとき、All-in-One環境で開発を行うときなどはMySQLのインスタンスを複数設けず、同じMySQLにマスターとスレーブを設定する。なんてケースがあると思います。

実際はちゃんとスレーブを設けるべきですが、スレーブを設けることで、余計な管理コストが若干入ってしまうため、めんどくさがってしまう人もいるでしょう。

しかし、その手間を省くことで以下のようなことがおきたりするものです。

・あ～～～スレーブに書きにいってるｗｗｗｗｗｗｗｗｗｗｗｗ。readonly指定がなかったら即死だったｗｗｗｗｗｗ
・私の神クラスは更新と参照のみを自動的に判断してくれるのだがバグってちゃんと動いてなかったわｗｗｗｗガハハ

どんなに意識が高くても人間はミスする生物なのですから、手を抜くべきではないのです。

では普通はこのようなときにどうするのか

普通はmysql_multiか、mysqlを手動で複数起動（実際はmultiと一緒ではあるが……）をする感じでしょう。
DBサーバがちゃんと複数あれば一番いいのですけどね。

手動でmysql複数立ち上げるのも、手順的には指で数えられるくらいのステップで可能です。
ただここで最もネックなのは、初期では、マスターの現在の状態をダンプして復元する手間と、my.cnfを書く（編集する）必要がある。
という感じでしょうか。

初期ではデータ量が少なくネックでもないですし、慣れていればやることも少ないはずですが、ココらへんが全く触ったことがない人では、これだけで時間を無駄に浪費しかねません（いや正直これくらい覚えてほしいのだけれど……）
※docker？コンテナ？vagrant？しらんな？(＾ω＾　)

ではどうするか

スレーブに設定されたセッションのみ READ ONLY にすればいいじゃない！
というのが今回の話です。前置きなげえな。
ただしここまで読んで頂いた方には大変申し訳無いですが、レプリケーション遅延の再現までの話はしませんので、そういうのが聞きたかったひとはブラウザバックしてください。（これもそのうちできるかやってみたいですね）
ここでは、スレーブとしているセッションに対し更新系クエリを飛ばしたときに「エラーにしてあげる」という話のみします。

そんなことは可能なのか？

可能です。
InnoDB ご存知の SET TRANSACTION を使用します。
https://dev.mysql.com/doc/refman/5.6/ja/set-transaction.html

SET TRANSACTION ではアクセスモードとセッション単位かグローバルかの指定がMySQL 5.6.5 の時点で可能になっています。

では早速やってみましょう。

使用している MySQL は 5.7.19
PHP は 7.2.3 です。

適当にテーブルを用意しますね。

create_table.sql

CREATE TABLE account (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    profile VARCHAR(255) NOT NULL DEFAULT "",
    last_login_time DATETIME NOT NULL DEFAULT "1000-01-01 00:00:00",
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
INSERT INTO account (name) VALUES 
("akeno misaki"    ),
("mashiro munetani"),
("shima tateishi"  ),
("mei irizaki"     ),
("kouko nosa"      ),
("shiretoko rin"   );

即席でかいたPHPのテストコードは以下です

test.php

<?php
$config_master = (object)[
    "hostname"   => "localhost",
    "database"   => "xxxx",
    "username"   => "xxxx",
    "password"   => "xxxx",
    "is_readonly"=> false,
];

$config_slave = clone $config_master;
$config_slave->is_readonly = true;

class MySQL {

    public function connect($config) {
        try {
            $this->pdo = new PDO(
                "mysql:host={$config->hostname};dbname={$config->database}",
                $config->username,
                $config->password);

            if ($config->is_readonly === true) {
                if ( $this->query("SET SESSION TRANSACTION READ ONLY") === false ) {
                    return false;
                }
            }

        } catch (PDOException $e) {
            echo "connection failed: " . $e->getMessage() . "\n";
            $this->pdo = null;
            return false;
        }
        return true;
    }

    public function query($sql) {
        if ($this->pdo == null)
            return false;
        return $this->pdo->query($sql);
    }

    public function last_error() {
        if ($this->pdo == null)
            return "";
        $str = "";
        foreach ($this->pdo->errorInfo() as $v) {
            $str .= "$v,";
        }
        return $str . "\n";
    }

    // --------------------------------
    private $pdo = null;
}

echo "mysql master -------------------------------------\n";
$mysql_m = new MySQL;
if($mysql_m->connect($config_master) === false) die("err");
if (($res = $mysql_m->query("SELECT id, name FROM account")) !== false) {
    foreach ( $res as $row ) {
        echo sprintf("id: %04d name %20s\n", $row["id"], $row["name"]);
    }
}
if ($mysql_m->query("INSERT INTO account (name) VALUES ('sango sugimoto')") === false){
    die($mysql_m->last_error());
}

echo "mysql slave --------------------------------------\n";
$mysql_s = new MySQL;
if($mysql_s->connect($config_slave) === false) die("err");
if (($res = $mysql_s->query("SELECT id, name FROM account")) !== false) {
    foreach ( $res as $row ) {
        echo sprintf("id: %04d name %20s\n", $row["id"], $row["name"]);
    }
}
if ($mysql_s->query("INSERT INTO account (name) VALUES ('sango sugimoto')") === false){
    die($mysql_s->last_error());
}

では実行しましょう

php mysqltest.php

mysql master -------------------------------------
id: 0001 name akeno misaki
id: 0002 name mashiro munetani
id: 0003 name shima tateishi
id: 0004 name mei irizaki
id: 0005 name kouko nosa
id: 0006 name shiretoko rin
mysql slave --------------------------------------
id: 0001 name akeno misaki
id: 0002 name mashiro munetani
id: 0003 name shima tateishi
id: 0004 name mei irizaki
id: 0005 name kouko nosa
id: 0006 name shiretoko rin
id: 0007 name sango sugimoto
25006,1792,Cannot execute statement in a READ ONLY transaction.,

やったぜ。

この内容は、正直バッドノウハウです。
この方式をとってなにか起きても保証できませんのでよろしくです＼(^o^)／