タグ: MySQL

MySQL Replication – Master コケ時の復旧方法

Masterがこけると、当然Slaveのデータも更新されない。

  1. Slaveにログインして 
    stop slave;

    で、SLAVE状態を止める。

  2. IPアドレスをMasterのものに付け替える。 
    vi /etc/sysconfig/network-scripts/ifcfg-eth0

IPADDR=***.***.***.***

    を書き換え。

    /etc/init.d/network restart

    で、ネットワークの再起動。

たぶんこれだけ。

State が Copying to tmp table

クエリが帰って来ないなーという時は、今何やってるのか見てみる。
mysql -u **** -p
Enter password:
でログインして

show processlist;

すると今現在稼働しているプロセスリストを表示してくれる。

+----------+------+-------------------+------+-------------+-------+----------------------------------------------------------------+------------------+
| Id       | User | Host              | db   | Command     | Time  | State                                                          | Info             |
+----------+------+-------------------+------+-------------+-------+----------------------------------------------------------------+------------------+
| 37853901 | repl | 10.10.10.10:56317 | NULL | Binlog Dump | 23241 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
| 37853903 | repl | 10.10.10.11:44864 | NULL | Binlog Dump | 23238 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
| 38092482 | root | localhost         | NULL | Query       |     0 | NULL                                                           | show processlist |
+----------+------+-------------------+------+-------------+-------+----------------------------------------------------------------+------------------+

こんななら問題なし？

ココに

+----------+------+-------------------+----------+---------+-------+----------------------+-----------------------------------------------+
| Id       | User | Host              | db       | Command | Time  | State                | Info                                          |
+----------+------+-------------------+----------+---------+-------+----------------------+-----------------------------------------------+
| 38081655 | root | 10.10.10.12:34775 | hogehoge | Query   |   728 | Copying to tmp table | SELECT `hoge` FROM table WHERE `hoge` = null  |
| 38081656 | root | 10.10.10.12:34776 | hogehoge | Sleep   |   728 |                      | NULL                                          |
+----------+------+-------------------+----------+---------+-------+----------------------+-----------------------------------------------+

って「Copying to tmp table」があったら、そりゃ遅い。メモリ上で処理できなくてディスクに書き出してるってことなので。
ちなみにTimeに728って書いてあるってことは既に12分08秒経過してる。

どうしようもないのでこのプロセスにはいなくなってもらうことにする。

kill 38081655

でいなくなってもらう。

テーブルを複製する

テーブルの構造変更やデータ変更する前にバックアップしておかないと、壊しちゃったとき大変。
なのでテーブルを複製しておく。

同じ構造のテーブルを作成する。

create table data_backup like data_original;

で、このテーブルに現在あるデータを全部流し込む。

insert into data_backup select * from data_original;

これでテーブル単位でのバックアップ完了。
作ったらバックアップで作業したり更新したりテストしたり出来る。

文字コードをすべてutf8に

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | utf8                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | utf8                       | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

このようになっていたりして予期しないところで化けちゃったりなんだったり。

vi /etc/my.cnf

に以下のものを追加します。

[mysqld]
character-set-server = utf8
default-character-set = utf8
init_connect='SET NAMES utf8'

[client]
default-character-set = utf8

変更したらMySQLを再起動。

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       | 
| character_set_connection | utf8                       | 
| character_set_database   | utf8                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | utf8                       | 
| character_set_server     | utf8                       | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci | 
| collation_database   | utf8_general_ci | 
| collation_server     | utf8_general_ci | 
+----------------------+-----------------+
3 rows in set (0.00 sec)

この形でOK。

初期ログインパスワード設定

みんなが書いていることだけどgrantを思い出せなかったので、自分でも書いておく。

はじめに書く呪文は

grant all privileges on *.* to username identified by 'password' with grant option;

です。
usernameはroot@localhostでもroot@111.222.333.444でも。
これで入れなかったらまず、

flush privileges;

でユーザテーブルをリフレッシュしてみること。
それでも入れなかったら原因を考えてみる。

Forcing close of thread … (未解決)

MySQLの落とすときに

120210 20:36:36 [Note] /usr/libexec/mysqld: Normal shutdown

120210 20:36:38 [Warning] /usr/libexec/mysqld: Forcing close of thread 554  user:''

・・・・・・スレッドの数が1ずつ減っていく

120210 20:36:38 [Warning] /usr/libexec/mysqld: Forcing close of thread 54  user: ''

120210 20:39:51  InnoDB: Starting shutdown...
120210 20:39:52  InnoDB: Shutdown completed; log sequence number 0 1206809921
120210 20:39:52 [Note] /usr/libexec/mysqld: Shutdown complete

ってカンジで

[Warning] /usr/libexec/mysqld: Forcing close of thread ***  user:''

がたくさんある。

なんじゃらほい？

MySQL の Too Many Connections を回避

show processlist

で確認すると

unauthenticated user

がいっぱい溜まってToo Many Connectionsになっちゃってる場合。

skip-name-resolve

を my.cnf の[mysqld]の中に書いてあげるといいらしい。

いざって時にはすでに mysql 自体がハングっちゃって「show processlist」すら出来ない場合もあるだろうから、
名前解決する必要がないってわかっている場合は事前に書いておいても問題ないかと思う。

その前に

max_connections
thread_cache
wait_time

辺りを見直すのも吉。

MySQL Replication

レプリケーションの作り方について。
MySQL レプリケーションの設定 – とみぞーノートさま
現場指向のレプリケーション詳説 – IRORI.ORGさま
と、参考になるところはいっぱいあるのだが、自分で書かないと覚えられないたちなので仕方がない。
MySQLのドキュメントだと5.1.1. レプリケーションのセットアップ方法とか12.6. 複製ステートメントとかの辺り。

要はMasterで発行されたSQLをSlaveでもそのまま実行するということ。
とりあえず作り方。

  1. まず、普通に2台作る。
  2. [MASTER] my.cnf にサーバーIDなどを書き足す 
    [mysqld]
log-bin=mysql-bin
server-id=168001012

    「log-bin」はバイナリログを取っておく、という指定。
    「server-id」は適当にSlaveとかぶらないものを指定する。
    required unique id between 1 and 2^32 – 1
    ということなので、1 から 4,294,967,295 の範囲内で。
    今回はサーバのIPアドレス、下3つを採用。

  3. [MASTER] ログイン 
    mysql -u root -p
  4. [MASTER] SlaveからMasterに接続する際のユーザーを作成する 
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.13' IDENTIFIED BY 'slave_password';

    「repl」はユーザー名。ほかの用途に使わないようなものを指定する。
    「192.168.1.13」はSlave側のアドレス。
    Slaveが複数ある場合には「192.168.1.0/255.255.255.0」とかのほうがいいかも。

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'slave_password';

    こんなカンジで。

  5. [MASTER] 書き込みをブロックする 
    mysql> FLUSH TABLES WITH READ LOCK;

    この状態だと読み込みはOKということになる。

  6. [MASTER] データのバックアップを取る
    mysqldumpが使える環境であれば、こちらの方が容量が少なくなるのでオススメ。
    –all-databases → すべてのデータベースをDumpする。
    –add-drop-database → それぞれのデータベースをCREATEする前に「DROP DATABASE …」文を書き加える。
    –add-drop-table → それぞれのテーブルをCREATEする前に「DROP TABLE …」文を書き加える。
    –add-drop-trigger → それぞれのトリガをCREATEする前に「DROP TRIGGER …」文を書き加える。
    ※ –add-drop-database 、 –add-drop-table 、 –add-drop-trigger をつけた場合、
    当然だけど既存の同名データベース・同名テーブル・同名トリガは削除される。
    -x → 「–lock-all-tables」と同義でDump中、全てのデータベースをロックする。
    –master-data=1 → 「CHANGE MASTER TO …」文でMasterへ接続するためのパラメータも書いてくれる。「–master-data=2」にすると、「CHANGE MASTER TO …」文をコメントアウトして書き残す。
    デフォルトは「1」なので「–master-data」だけで良い。

    mysqldump -u root -p --all-databases --add-drop-database --add-drop-table -x >> /tmp/sqldump_20120807.sql

    個別のデータベースのDUMPを出力する場合は以下のようにする
    –databases [db_name] → [db_name] で指定したデータベースのみDumpする。

    mysqldump -u root -p --databases db_name --add-drop-database --add-drop-table -x >> /tmp/sqldump_20120807_db_name_only.sql

    mysqldumpが使えない時は「/var/lib/mysql」配下をすべてtarで固める。

    tar -cpf /var/tmp/mysql.tar /var/lib/mysql
  7. [MASTER] Master側のステータスを確認しておく
    この値はあとでSlaveからMasterへ接続するときに使用する。

    mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000024 | 49005253 | test         | manual,mysql     |
+------------------+----------+--------------+------------------+
  8. [MASTER] バックアップをMasterからSlaveにコピー 
    scp -Cqp /tmp/mysqldump_20120807.sql root@192.168.1.13:/tmp/mysqldump_20120807.sql
    scp -Cqp /var/tmp/mysql.tar root@192.168.1.13:/var/tmp/mysql.tar
  9. [MASTER] 読み込みロックを解除する 
    mysql> UNLOCK TABLES;
  10. [MASTER] 終わったらMySQLからログアウトする。
  11. [SLAVE] my.cnf にサーバーIDなどを書き足す 
    [mysqld]
log-bin=mysql-bin
server-id=168001013

    「log-bin」はSlaveのみであれば特に必要ないが、
    binary logging – not required for slaves, but recommended
    という記述があるので、一応つけておく。
    Masterがコケた時、このSlaveがMasterになる可能性があるなら付けておく。
    「server-id」はMasterとかぶらないものを指定する。

  12. [SLAVE] コピーされたデータのバックアップを展開する 
    mysql -u root -p < /tmp/sqldump_20120807.sql
    cd /var/lib/mysql
rm -fr *
tar xpf /var/tmp/mysql.tar
  13. [SLAVE] ログインし、Masterへ接続するためのパラメータを設定する
    mysqldumpで「–master-data」を1に設定した場合は上記で読み込ませた時に同時に実行されているはず。

    mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.12', 
                        MASTER_USER='repl', 
                        MASTER_PASSWORD='password', 
                        MASTER_LOG_FILE='mysql-bin.000024', 
                        MASTER_LOG_POS=49005253;

    いきおい余って MASTER_LOG_POS の数値を「’」で囲まないように。
    文字列として認識されてハマります。
    実際に小1時間ハマりました。。。

  14. [SLAVE] Slaveを起動する 
    mysql> START SLAVE;
  15. [SLAVE] Slaveの状態を確認する 
    mysql> SHOW SLAVE STATUS;

    Slave_IO_Running と、 Slave_SQL_Running が両方ともYesになっていることを確認。
    Seconds_Behind_Master が0であれば、マスターに対する遅延も発生していない、ということになる、はず。

ここまでで Master <-> Slave の構築はできたかな？と。

MySQL Query Log

まず、ログファイル作成。

touch /var/log/mysql/query.log
chmod 660 /var/log/mysql/query.log
chown mysql:mysql /var/log/mysql/query.log

次にmy.cnfの修正。

[mysqld]
log = /var/log/mysql/query.log

ここまでできたらMySQLの再起動。

service mysql restart

以上です。

Levenshtein distance

元ネタはココ。

CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
    DECLARE s1_char CHAR;
    -- max strlen=255
    DECLARE cv0, cv1 VARBINARY(256);
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
    IF s1 = s2 THEN
      RETURN 0;
    ELSEIF s1_len = 0 THEN
      RETURN s2_len;
    ELSEIF s2_len = 0 THEN
      RETURN s1_len;
    ELSE
      WHILE j  c_temp THEN SET c = c_temp; END IF;
            SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
            IF c &gt; c_temp THEN
              SET c = c_temp;
            END IF;
            SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
        END WHILE;
        SET cv1 = cv0, i = i + 1;
      END WHILE;
    END IF;
    RETURN c;
  END;

この関数を該当DB上に作成して、

SELECT place, levenshtein(place, 'スターバックス') AS distance FROM main_data ORDER BY distance ASC LIMIT 10;

みたいなクエリでアクセスするとレーベンシュタイン距離が返ってくる。

WP-DBManager

DBにはMySQLが使われているわけですが、
WP-DBManagerはそのDBのdumpを定期的に書き出してくれる。
「Terminalが使えないならphpMyAdminかAdminerを使えばいいじゃない」と普通は思うんだけど、それすらもままならないクライアントに運用してもらう場合とか入用になるかも。

ただし、上に「DBのdumpを定期的に書き出してくれる」と書いたように、wordpressのテーブルを判別して書き出しているわけではない。
レンタルサーバなどでwordpress用のテーブルには接頭辞つけて…とか関係なしに全テーブルのdumpを吐きだすっぽい。

初心者でもバックアップからの復元ぐらいやってほしいと思って入れてみたのだけど、テーブル名が全部羅列されている画面だったり、普通にクエリ発行できたりと、じつは初心者向けではないような。
「Terminal開くのめんどくせ」っていう人向けのツールだった。

MySQL

MySQL、SQLite3、groonga系のサーバの人についてはまとめようと思ったら、groonga系のコンテンツがなかったｗ

SQLiteのコンテンツもなかったｗ