Skip to content

Instantly share code, notes, and snippets.

@be-hase

be-hase/gist:6350921

Last active December 21, 2015 18:59
Show Gist options
  • Save be-hase/6350921 to your computer and use it in GitHub Desktop.
Save be-hase/6350921 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL レプリケーション
Raw
gistfile1.md

###【master】mysqlのインストール

# yum install mysql-server

###【master】mysql関連のファイルが入っているか確認

# find / -name mysql
/var/lib/mysql
/usr/bin/mysql
/usr/share/mysql
/usr/lib64/perl5/vendor_perl/DBD/mysql
/usr/lib64/perl5/vendor_perl/auto/DBD/mysql
/usr/lib64/mysql

###【master】my.confの設定 今回はmysqlのみのサーバーなので、ひとまずlargeを使用しておく。

# cp /usr/share/mysql/my-large.cnf /etc/my.cnf

###【master】mysqlの起動

# service mysqld start

###【master】自動起動するようにする

# chkconfig mysqld on

###【master】rootのパスワードを設定

# mysqladmin -u root password *********

###【master】ログインしてみる

# mysql -u root -p

###【master】mysqlユーザーを作成する

GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '********' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO mysql@'%' IDENTIFIED BY '********' WITH GRANT OPTION;

###【master】匿名ユーザーとパスワードがないユーザーを削除する

use mysql;
delete from user where user='';
delete from user where password='';
flush privileges;

###【master】文字コードの確認 my.confを編集

[mysqld]
character-set-server = utf8

[mysql]
default-character-set = utf8

[mysqldump]
default-character-set = utf8

> 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)

確認OK。

###【master】レプリケーション用のユーザーを作成する 本来なら、%ではなく、IPなりを指定すること。

>  GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'password';

###【master】バイナリログの確認 my.cnfに以下の記述をする

[mysqld]
log-bin=mysql-bin

ログがでているか確認

# less /var/lib/mysql
略  
-rw-rw----  1 mysql mysql    27707 Aug 22 09:14 mysql-bin.000001
-rw-rw----  1 mysql mysql  1058967 Aug 22 09:14 mysql-bin.000002
-rw-rw----  1 mysql mysql     1136 Aug 22 09:26 mysql-bin.000003
-rw-rw----  1 mysql mysql      269 Aug 22 09:57 mysql-bin.000004
-rw-rw----  1 mysql mysql      107 Aug 22 09:57 mysql-bin.000005
-rw-rw----  1 mysql mysql       95 Aug 22 09:57 mysql-bin.index

ちゃんとmysql-binという名前で出ている。

###【master】リレーログの設定

[mysqld]
relay-log = relay-log

###【master】server-idの設定

server-id       = 1001

###【aws】マスターのAMIからレプリケーション用のサーバを作る。

management consoleからポチポチやればOK

###【repl】レプリケーションDBのserver idを変更する

server-id       = 1002

###【master】データベースを作成する

> create database hoge;

###【master】テーブルを作成する

CREATE  TABLE `comments` (   `id` INT NOT NULL AUTO_INCREMENT ,   `content` VARCHAR(150) NOT NULL DEFAULT '' ,   PRIMARY KEY (`id`) ) ENGINE = InnoDB;

###【master】適当にデータをinsertしておく

insert into comments (content) values ('hoge');
insert into comments (content) values ('bar');

###【master】マスタサーバのスナップショットを作成する

  • 読み込み専用にする
# echo "FLUSH TABLES WITH READ LOCK;" | mysql -u root -pパスワード
  • データベースファイルのあるディレクトリに移動
# cd /var/lib/mysql
  • データベースのディレクトリを丸ごと tar で固める
# tar cpf /var/tmp/db-master-snapshot.tar .
  • SHOW MASTER STATUS を実行し、バイナリログの Position 値を調べる。
# "SHOW MASTER STATUS\G" | mysql -u root -pパスワード >> /var/tmp/db-master-snapshot-show-master-status.txt

中身はこんな感じ

            File: mysql-bin.000009
        Position: 865
    Binlog_Do_DB: 
Binlog_Ignore_DB:
  • データベースのロックを解除する
# echo "UNLOCK TABLES;" | mysql -u root -pパスワード

###【master】【repl】/var/tmp/db-master-snapshot.tarをscpでmasterからreplに転送する

  • master
scp -i ~/.ssh/hsb_aws.pem [email protected]:/var/tmp/db-master-snapshot.tar .
  • repl
scp -i ~/.ssh/hsb_aws.pem db-master-snapshot.tar [email protected]:/var/tmp

###【repl】マスターのスナップショットを元にスレーブを作成する

  • mysqlサーバ停止
# service mysqld stop
  • データベースがあるディレクトリへ移動
# cd /var/lib/mysql/
  • データベースファイルを全て削除
# rm -rf *
  • マスタサーバのスナップショットをデータベースディレクトリに展開
# tar -xpf /var/tmp/db-master-snapshot.tar -C /var/lib/mysql/
  • mysqlサーバを起動
# service mysqld start

###【repl】スレーブを開始する

  • CHANGE MASTER TO でマスターサーバに接続する設定をする。この時に、マスターサーバのスナップショットを作成した時に記録した File: mysql-bin.000009 と Position: 865 を指定する。
  • START SLAVE でスレーブを開始する。
CHANGE MASTER TO
MASTER_HOST='ec2-54-250-45-78.ap-northeast-1.compute.amazonaws.com',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=865;

START SLAVE;

###【repl】slave statusの確認

SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ec2-54-250-45-78.ap-northeast-1.compute.amazonaws.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 549
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1001

Slave_IO_Running: と Slave_SQL_Running: が両方とも YES になっていると、スレーブは問題なく動作していることになる。

  • Slave_IO_Running: No の場合
    • I/O スレッドに問題が発生している。
    • マスターサーバに接続できない（接続ユーザー名が間違っているなど）や、ディスクに空きがないなど。
  • Slave_SQL_Running: No の場合
    • SQL スレッドに問題が発生している。
    • クライアントがスレーブサーバに更新系の SQL を発行したなど。

###【master】【repl】実際にマスターでSQLを実行してみる。

  • master
 select * from comments;
+----+---------+
| id | content |
+----+---------+
|  1 | hoge    |
|  2 | bar     |
|  3 | aaaa    |
|  4 | bbbbbbb |
+----+---------+
  • repl
select * from comments;
+----+---------+
| id | content |
+----+---------+
|  1 | hoge    |
|  2 | bar     |
|  3 | aaaa    |
|  4 | bbbbbbb |
+----+---------+
  • master
insert into comments (content) values ('cc');
select * from comments;
+----+---------+
| id | content |
+----+---------+
|  1 | hoge    |
|  2 | bar     |
|  3 | aaaa    |
|  4 | bbbbbbb |
|  5 | cc      |
+----+---------+
  • repl
select * from comments;
+----+---------+
| id | content |
+----+---------+
|  1 | hoge    |
|  2 | bar     |
|  3 | aaaa    |
|  4 | bbbbbbb |
|  5 | cc      |
+----+---------+

ちゃんとレプリケーションにも反映されていることがわかる。

###【repl】リレーログが生成されていることを確認してみる

# ls /var/lib/mysql/
hoge                   master.info       mysql-bin.000006  performance_schema
ibdata1                mysql             mysql-bin.000007  relay-log.000002
ib_logfile0            mysql-bin.000001  mysql-bin.000008  relay-log.000003
ib_logfile1            mysql-bin.000002  mysql-bin.000009  relay-log.index
ip-10-132-14-100.err   mysql-bin.000003  mysql-bin.000010  relay-log.info
ip-10-132-30-25.err    mysql-bin.000004  mysql-bin.index   test
ip-10-160-119-190.err  mysql-bin.000005  mysql.sock

ちゃんとrelay-logが生成されていることがわかる。 ちなみにリレーログは自動的に削除されるらしい。

###【master】バイナリログを削除してみる

マスターではどんどんとバイナリログが貯まる。
なので、定期的に削除する必要ある。
(設定で1週間だけ保持するみたいなのがあったから、それを設定すれば特に必要ないのかも)

rmで削除するのではなく、PURGE MASTER LOGS 構文で削除(パージ)する。

まずは、SHOW MASTER STATUS; でバイナリログのファイル名を確認する。

show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 |      804 |              |                  |
+------------------+----------+--------------+------------------+

次にPURGE MASTER LOGS でパージする。

purge master logs to 'mysql-bin.000010';

lsしてみる

hoge         ip-10-132-30-25.err    mysql-bin.000010    test
ib_logfile0  ip-10-134-145-242.err  mysql-bin.index
ib_logfile1  ip-10-160-119-190.err  mysql.sock
ibdata1      mysql                  performance_schema

たしかに、9,8…が削除されていることがわかる。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment