MySQL

Rest Term::Tech Note

このページでは主にMySQL関連のメモを書いています。

公式リファレンス
MySQL公式リファレンスマニュアル

パラメータチューニング

MySQLのバッファには以下の2種類のタイプがある。パラメータチューニングの際にはこの2つのタイプの違いを意識しないといけない。

• グローバルバッファ (mysqld内部で1つだけ確保される)
• スレッドバッファ (スレッド毎に確保される)

スレッドバッファに多くのメモリを割り当てすぎるとコネクションが増えた途端すぐにメモリ不足に。物理メモリ以上のサイズを割り当ててしまうと、スワップが発生して逆にパフォーマンスが落ちる。

その他

MySQL5.5ではデフォルトストレージエンジンはInnoDBとなっている。

データベースのダンプ

データベースサーバのホスト移行時など、頻繁に使う訳ではないのでオプションなどはすぐ忘れてしまうのでメモ。

$ mysqldump -u root -p --single-transaction --flush-logs --opt --databases {データベース名 (スペースで区切って複数指定可能)} --tables {テーブル単位でダンプしたい場合はテーブル名も指定} > {出力ファイル名}

オプションメモ

• single-transaction : テーブルをロックしないでバックアップ。InnoDBでは常に指定すること。
• master-data : レプリケーション用データをダンプする。CHANGE MASTER TO ~ のクエリがダンプに追加される。
• routines : Storead Procedure/Function を作成するための CREATE PROCEDURE および CREATE FUNCTION 文が含まれる。プロシージャや関数がある場合はこのオプションを付けるのを忘れずに。

レプリケーション

MySQLのレプリケーション関連のメモ。

• 環境
  CentOS 5.9 (x86_64)
  MySQL 5.5.x, InnoDB

• レプリケーションの流れ

  マスタ側の更新系のクエリが、マスタのバイナリログに記録される。
  ↓
  スレーブのI/OスレッドがマスタのBinlog Dumpスレッドに接続し、
  マスタのBinlog Dumpスレッドはバイナリログの内容を送信する。
  ↓
  スレーブのI/Oスレッドは、受け取ったマスタのバイナリログをリレーログに保存。
  ↓
  スレーブのSQLスレッドがリレーログからクエリを読み取って実行。

• レプリケーションユーザの作成
  レプリケーション専用ユーザなので権限は REPLICATION SLAVE のみ与えておけばよい。 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'password';

• レプリケーション設定
  • マスター側

    ## /etc/my.cnf
    server-id = 1  ## サーバID
    log-bin = mysql-bin  ## バイナリログ名
    relay-log = relay-log  ## リレーログ名
    expire_logs_days = 30  ## ログの保存期間
    binlog_format = 0  ## バイナリログのフォーマット (0:MIXED, 1:, 2:)

    また、レプリケーションにできる限りの耐用性と一貫性を期待する場合はマスターの設定ファイルに sync_binlog=1 と innodb_flush_log_at_trx_commit=1 を設定しておく。
  • スレーブ側

    server-id = 2

    フェイルオーバーをサポートするにはスレーブ側でもバイナリログを書き出しておく必要がある。その際は設定ファイルに log-slave-updates を設定する。

• マスターのスナップショットを取得
  データの取得には mysqldump コマンドを利用する。--master-data を指定するとダンプファイルにレプリケーション位置を指定するSQLも出力される。また、InnoDBの場合は --single-transaction も指定しておこう。 $ mysqldump -uユーザ名 -pパスワード データベース名 --master-data --single-transaction > dump.sql ## バイナリログ名と位置を確認 $ head -50 dump.sql | grep -i 'change master' CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000021', MASTER_LOG_POS=47397;

• スレーブにマスターデータをロード
  スレーブにデータベースを作成してからダンプしたマスターデータをロードする。 mysql> create database データベース名 $ mysql -uユーザ名 -pパスワード データベース名 < dump.sql

• レプリケーションの開始 ## マスター情報を設定 mysql> CHANGE MASTER TO -> MASTER_HOST='マスターのホスト名', -> MASTER_USER='レプリケーションユーザー名', -> MASTER_PASSWORD='レプリケーションユーザーのパスワード', -> MASTER_LOG_FILE='バイナリログファイル名', -> MASTER_LOG_POS=レプリケーション位置; ## レプリケーションを開始 mysql> START SLAVE; ## レプリケーションの確認 mysql> SHOW SLAVE STATUS; ... 省略 ## 以下2項目が Yes になっていればレプリケーションは正常に動作している Slave_IO_Running: Yes Slave_SQL_Running: Yes

• その他
  リレーログはバイナリログと同じ形式なので、mysqlbinlog で読み取ることができる。リレーログは必要がなくなると（そのイベントがすべて実行されたら）、SQLスレッドによって自動的に削除される。

レプリケーションのセットアップ方法
レプリケーション FAQ
mysqldump — データベースバックアッププログラム

alter table

• メモ
  1800万レコードの外部キー制約のあるテーブルのカラムの型変更(int -> bitint)に約10分程度かかった。
  • 環境:
    MySQL 5.6.22
    Intel Xeon E5-2630L v2 2.40GHz / 64GB RAM

alter table `sampledb`.`log` drop foreign key `fk_log_request1`; alter table `sampledb`.`request` change column `request_id` `request_id` bigint unsigned not null auto_increment; alter table `sampledb`.`log` change column `request_id` `request_id` bigint unsigned not null; alter table `sampledb`.`log` add constraint `fk_log_request1` foreign key (`request_id`) references `sampledb`.`request` (`request_id`) on delete no action on update no action;

MariaDBについて

CentOS 7.x以降はデフォルトのデータベースサーバはMySQLではなくMariaDBになっている。