CentOS 6.5 x86_64でMySQLのマスター・スレーブを設定

タックス

MySQLのマスター・スレーブの設定をすることに恵まれましたので、手順をまとめてみました。

    今回挑戦したものは、MySQL 5.6を使用したマスター・スレーブの設定ですが、無停止でスレーブサーバを設置する方法など、現場で使えるレベルの手順をまとめることができましたので、ご紹介したいと思います。
    なお、SCLリポジトリにある5.5やMariaDBを使用する場合、若干設定ファイル等のPathが違いますが、基本的には設定方法は変わらないので、読み替えてください。

    ■ MySQLのマスター・スレーブの設定方法

  • mysql 5.6.xのインストール(マスター・スレーブ)

  • # yum -y install mysql56-server
    ※ wingリポジトリを設定している場合、5.6.xがインストールできます。
    ※ SCLリポジトリを使用することで、5.5.xやMariaDBもインストールできます。

  • /etc/my.cnfファイルの設定(マスター)

  • # vi /etc/my.cnf
    [mysqld]
    server-id=10
    log-bin=mysqld-bin
    binlog-ignore-db=test,performance_schema,information_schema

  • /etc/my.cnfファイルの設定(スレーブ)

  • # vi /etc/my.cnf
    [mysqld]
    server-id=11
    relay-log=mysqld-relay-bin
    read_only=1

  • サービスの起動(マスター・スレーブ)

  • # service mysqld start

  • MySQLのrootのパスワード設定(マスター・スレーブ)

  • # mysql -u root
    > grant all privileges on *.* to 'root'@'localhost' identified by 'パスワード';
    > flush privileges;

  • レプリケーション用ユーザ設定(マスター)

  • > grant replication slave on *.* to 'repuser'@'スレーブIPアドレス' identified by 'パスワード';

    ※ IPアドレス帯を設定する場合
    > grant replication slave on *.* to 'repuser'@'スレーブIPアドレス/ネットマスク' identified by 'パスワード';

    > flush privileges;

  • データベースを作成(マスター)

  • 適当にデータベースを作成しましょう。

  • ロックをかける(マスター)

  • > flush tables with read lock;
    ※ ロック後はダンプを取得するまでmysqlを抜けないでください。抜けるとロックが解除されます。

    > show master status\G

    File : バイナリログ名
    Position : ファイル内のオフセット。現在位置
    ※ 上記をメモする。

  • ダンプ取得と圧縮(マスター)

  • # mysqldump -u root -p --all-databases --lock-all-tables > /root/dump.sql

    ※ スキーマ別にバックアップする場合
    # mysqldump -u root -p スキーマ名 --lock-all-tables > dump_xxxx.sql

    # gzip dump.sql

  • ロックの解除(マスター)

  • > unlock tables;

  • ダンプをスレーブに転送(マスター)

  • # scp dump.sql.gz root@スレーブIPアドレス:/root
    ※ scpに限らずダンプファイルをスレーブに転送出来れば良い。

  • ダンプをリストア(スレーブ)

  • # gzip -d dump.sql.gz

    # mysql -uroot -p < /root/dump.sql

    ※ スキーマ別にバックアップする場合
    # mysql -uroot -p スキーマ名 < /root/dump.sql

  • マスター情報を登録(スレーブ)

  • > reset slave;

    > change master to master_host='マスターIPアドレス', master_user='repuser', master_password='パスワード', master_log_file='mysqld-bin.xxxxxx', master_log_pos=xxxx;

  • レプリケーションを開始(スレーブ)

  • > start slave;

  • レプリケーションを確認(スレーブ)

  • > show slave status\G

    以上が、マスター・スレープの基本的な設定になります。
    以下、障害対応や無停止でスレーブを追加する方法などをご紹介します。

    ■ マスターからスレーブをリストアしレプリケーションを復旧する方法。

  • 初期の処理(スレーブ)

  • # service mysqld stop

    # rm -rf /var/lib/mysql

    # service mysqld start

  • ロックをかける(マスター)

  • > flush tables with read lock;

    > show master status\G

  • ダンプ取得(マスター)

  • # mysqldump -u root -p --all-databases --lock-all-tables > /root/dump.sql

  • ロックの解除(マスター)

  • > unlock tables;

  • ダンプをスレーブに転送(マスター)

  • # scp /root/dump.sql root@スレーブIPアドレス:/root

  • ダンプをリストア(スレーブ)

  • # mysql -uroot < /root/dump.sql

    # mysqladmin flush-privileges -uroot

  • マスター情報を登録(スレーブ)

  • > change master to master_host='マスターIPアドレス', master_user='repuser', master_password='パスワード', master_log_file='mysqld-bin.xxxxxx', master_log_pos=xxxx;

  • レプリケーションを開始(スレーブ)

  • > start slave;

  • レプリケーションを確認(スレーブ)

  • > show slave status\G

    ■ 無停止でスレーブを追加する場合

    ※ 新規構築時、障害で復旧時にはこちらをおすすめします。

  • MySQLの自動起動を止める(マスター・スレーブ)

  • # chkconfig mysqld off

  • my.cnfの設定(スレーブ)

  • # vi /etc/my.cnf
    (スレーブ用設定をする)

  • MySQLの起動(スレーブ)

  • # service mysqld start

  • ダンプ取得(マスター)

  • # mysqldump -u root -p --all-databases --master-data --single-transaction > /root/dump.sql

    ※ スキーマ別にバックアップ。
    # mysqldump -u root -p スキーマ名 --master-data --single-transaction > dump.sql

    # scp /root/dump.sql root@スレーブIPアドレス:/root

  • マスター情報を登録(スレーブ)

  • > reset slave;

    > change master to master_host='マスターIPアドレス', master_user='repuser', master_password='パスワード';

  • ダンプをリストア(スレーブ)

  • # mysql -uroot < /root/dump.sql

    # mysqladmin flush-privileges -uroot


    ※ スキーマ別にリストア
    # mysql -uroot -p スキーマ名 < /root/dump.sql

  • レプリケーションを開始(スレーブ)

  • > start slave;

  • レプリケーションを確認(スレーブ)

  • > show slave status\G

    ■ スレーブをマスターに昇格

  • ロックをかける(マスター)

  • > flush tables with read lock;
    ※ ロック後はmysqlを抜けないでください。抜けるとロックが解除されます。

  • 確認(マスター)

  • > show status like 'Key_blocks_not_flushed';
    ※ 0であることを確認。

    > show engine innodb status\G
    ※ Log sequence numberとLog flushed up toの値に差異がないこと確認します。

  • MySQLを停止(マスター)

  • # service mysqld stop

  • スレーブ停止(スレーブ)

  • > show processlist;
    ※ Reconnecting after a failed master event readになっていることを確認します。

    > stop slave;
    > reset slave;

  • my.cnfの設定(スレーブ)

  • # vi /etc/my.cnf
    (スレーブ用設定をする)

    # service mysqld restart

  • read_onlyの状態を確認(スレーブ)

  • > select @@read_only;
    ※ 1になっている場合は、「set global read_only = 0;」を実行してください。

  • バイナリ ログの状態を確認(スレーブ)

  • > select @@log_bin;
    ※ 0になっている場合は、my.cnfを修正しmysqlを再起動してください。

  • リレーログの状態を確認(スレーブ)

  • > select @@relay_log;
    ※ NULLになっていない場合は、my.cnfを修正しmysqlを再起動してください。

  • masterをリセット(スレーブ)

  • > reset master;

  • masterになっていることを確認

  • > show master status\G

  • 元masterをスレーブに設定(マスター)

  • 手順はスレーブ構築時と同じ手順。

    ■ その他役立つ知識

  • スレーブの停止

  • 実行を停止すると、スレーブはマスターからのバイナリ ログの読み込みを止め (IO_THREAD)、未処理のリレー ログのイベント処理を停止します (SQL_THREAD)。 スレッド タイプを指定して、IO スレッド、または SQL スレッドのどちらかを別々に一時停止できます。

    > stop slave;

    > stop slave io_thread;

    > stop slave sql_thread;

  • バイナリ ログ内のイベント確認

  • > show binlog events;

  • my.cnfのinnodb_buffer_pool_sizeの設定

  • MySQL専用サーバの場合物理メモリーの50~80%の値であることが好ましいです。

  • my.cnfのkey_buffer_size

  • 一般的には、マシンのメモリー使用率 25 % の値であることが好ましいです。

  • my.cnfのtmpdir

  • スレーブサーバではメモリー上に置いてはいけません。

    ■ MySQLを再起動する方法

  • スレーブ側のMySQLを停止

  • # service mysqld stop

  • マスター側のMySQLを停止

  • # service mysqld stop

  • マスター側のMySQLを開始

  • # service mysqld start

  • スレーブ側のMySQLを開始

  • # service mysqld start

  • スレーブ側でステータス確認。

  • # mysql -uroot -p
    > show slave status\G

    ■ show slave status\Gで確認した際のエラーと対処方法。

    Slave_IO_Running: Yes
    Slave_SQL_Running: No

    このようにNoになっている場合は対処が必要になります。

  • スレーブから起動した場合。

  • Last_IO_Errno: 2003
    Last_IO_Error: error connecting to master 'repuser@~:3306' - retry-time: 60 retries: 86400

    回避方法
    一旦両方をSTOPし、マスターから起動すると正常にレプリケーションされます。

  • マスターにあってスレーブにないテーブルを更新した場合。

  • Last_SQL_Errno: 1146
    Last_SQL_Error: Error 'Table '~' doesn't exist' on query. Default database: 'meltomo'. Query: '~'

    回避方法
    マスターからバックアップを取得し、スレーブを再構築する必要があります。

  • スレーブにあって、マスターにないレコードを追加したなどの不整合がある場合。

  • ケース1
    Last_Errno: 1062
    Last_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: '~'. Query: '~'

    ケース2
    Last_SQL_Errno: 1049
    Last_SQL_Error: Error 'Unknown database '~'' on query. Default database: 'test'. Query: '~'

    回避方法
    stop slave; set global sql_slave_skip_counter = 1; start slave;
    を実行する。(クエリーを1つスキップする。)
    内容がアンマッチならマスターからスレーブを再構築が無難です。

  • マスターにあってスレーブのテーブルで、データ型が違うカラムがある場合。

  • Last_SQL_Errno: 1677
    Last_SQL_Error: Column 0 of table '~' cannot be converted from type 'int' to type 'bigint(20)'

    回避方法
    マスターからバックアップを取得し、スレーブを再構築する必要があります。

  • 初期構築時に1236エラーが出た場合。

  • Last_IO_Errno: 1236
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

    回避方法
    スレーブのマスター情報の設定にミスがある可能性があります。
    change masterコマンドを正しく設定してください。

    ■ 疑問点

    マスター・スレーブ両方のサーバを再起動する場合、やっぱり自動起動はオフにして、停止はスレーブ→マスターの順に行い、起動はマスター→スレーブの順で起動するのが正しいのでしょうか?
    今のところ、この手順で問題ありませんが、検索してもこういった情報を見つけることができませんでした。
    もし、お詳しい方がいらっしゃいましたらご教示頂けると助かります。

    以下、参考リンクです。

    Wikipedia(Linux)
    Wikipedia(CentOS)
    Wikipedia(MariaDB)
    Wikipedia(MySQL)
    MariaDB
    MySQL

関連記事


FC2Blog Rankingblogram投票ボタンとれまが人気ブログランキングブログランキング・にほんブログ村へ
くる天 人気ブログランキングにほんブログ村 IT技術ブログ CentOSへ

テーマ : Linux
ジャンル : コンピュータ

コメントの投稿

非公開コメント

プロフィール

WING☆

Author:WING☆


カレンダー
09 | 2017/10 | 11
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31 - - - -
最新記事
最新コメント
最新トラックバック
月別アーカイブ
カテゴリ
カウンター
お問い合わせ

名前:
メール:
件名:
本文:

WING☆からのお知らせ
  • CentOS 7設定集
  •    └wingリポジトリ(el7)

  • CentOS 6 x64設定集
  •    └wingリポジトリ(el6)

  • CentOS 5 x64設定集
  •    └wingリポジトリ(el5)



    My Yahoo!に追加

    FC2ブログランキング



    リンク
    ブロとも一覧
    Virtualization & Sever Maniax
    RSSリンクの表示
    QRコード
    QRコード
    ブロとも申請フォーム

    この人とブロともになる