MySQLレプリケーションをVagrantで試してみる。


## Vagrant 準備
```ruby:Vagrantfile
# -*- mode: ruby -*-
# vi: set ft=ruby :

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "ubuntu/trusty64"
  config.vm.box_url = "https://cloud-images.ubuntu.com/vagrant/trusty/current/trusty-server-cloudimg-amd64-vagrant-disk1.box"

  config.vm.define :ubuntu_1 do |ubuntu_1|
    ubuntu_1.vm.network :private_network, ip: "192.168.33.10"
  end

  config.vm.define :ubuntu_2 do |ubuntu_2|
    ubuntu_2.vm.network :private_network, ip: "192.168.33.11"
  end
end
```

## 起動
```
$ vagrant up
$ vagrant status
Current machine states:

ubuntu_1                  running (virtualbox)
ubuntu_2                  running (virtualbox)

This environment represents multiple VMs. The VMs are all listed
above with their current state. For more information about a specific
VM, run `vagrant status NAME`.
```

## ログインして確認
```
$ vagrant ssh ubuntu_1
Welcome to Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-46-generic x86_64)

 * Documentation:  https://help.ubuntu.com/

 System information disabled due to load higher than 1.0

  Get cloud support with Ubuntu Advantage Cloud Guest:
    http://www.ubuntu.com/business/services/cloud

0 packages can be updated.
0 updates are security updates.


_____________________________________________________________________
WARNING! Your environment specifies an invalid locale.
 This can affect your user experience significantly, including the
 ability to manage packages. You may install the locales by running:

   sudo apt-get install language-pack-ja
     or
   sudo locale-gen ja_JP.UTF-8

To see all available language packs, run:
   apt-cache search "^language-pack-[a-z][a-z]$"
To disable this message for all users, run:
   sudo touch /var/lib/cloud/instance/locale-check.skip
_____________________________________________________________________

vagrant@vagrant-ubuntu-trusty-64:~$ uname -a
Linux vagrant-ubuntu-trusty-64 3.13.0-46-generic #77-Ubuntu SMP Mon Mar 2 18:23:39 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
vagrant@vagrant-ubuntu-trusty-64:~$ cat /etc/debian_version
jessie/sid
```

## MySQL 準備
### 5.6 で試してみる。
```
vagrant@vagrant-ubuntu-trusty-64:~$ wget http://dev.mysql.com/get/mysql-apt-config_0.3.2-1ubuntu14.04_all.deb
vagrant@vagrant-ubuntu-trusty-64:~$ sudo dpkg -i mysql-apt-config_0.3.2-1ubuntu14.04_all.deb
vagrant@vagrant-ubuntu-trusty-64:~$ sudo apt-get update
vagrant@vagrant-ubuntu-trusty-64:~$ sudo apt-get install mysql-server-5.6
```

### かくにん
```
vagrant@vagrant-ubuntu-trusty-64:~$ dpkg -l |grep mysql
ii  libdbd-mysql-perl                4.025-1                          amd64        Perl5 database interface to the MySQL database
ii  libmysqlclient18:amd64           5.5.41-0ubuntu0.14.04.1          amd64        MySQL database client library
ii  mysql-apt-config                 0.3.2-1ubuntu14.04               all          Auto configuration for MySQL APT Repo.
ii  mysql-client-5.6                 5.6.19-0ubuntu0.14.04.1          amd64        MySQL database client binaries
ii  mysql-client-core-5.6            5.6.19-0ubuntu0.14.04.1          amd64        MySQL database core client binaries
ii  mysql-common                     5.5.41-0ubuntu0.14.04.1          all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-common-5.6                 5.6.19-0ubuntu0.14.04.1          all          MySQL 5.6 specific common files, e.g. /etc/mysql/conf.d/my-5.6.cnf
iF  mysql-server-5.6                 5.6.19-0ubuntu0.14.04.1          amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.6            5.6.19-0ubuntu0.14.04.1          amd64        MySQL database server binaries
vagrant@vagrant-ubuntu-trusty-64:~$ sudo /etc/init.d/mysql status
 * MySQL is stopped.
```

### MySQL 起動
```
vagrant@vagrant-ubuntu-trusty-64:~$ sudo /etc/init.d/mysql start
 * Starting MySQL database server mysqld                                                                       No directory, logging in with HOME=/
                                                                                                        [ OK ]
 * Checking for tables which need an upgrade, are corrupt or were
not closed cleanly.
vagrant@vagrant-ubuntu-trusty-64:~$ sudo /etc/init.d/mysql status
 * /usr/bin/mysqladmin  Ver 8.42 Distrib 5.6.19, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.6.19-0ubuntu0.14.04.1
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			17 sec

Threads: 1  Questions: 919  Slow queries: 0  Opens: 758  Flush tables: 1  Open tables: 80  Queries per second avg: 54.058
```


### master
- bind-address : slave と接続するため
- log_bin : BinLog を吐き出すようにする
- server-id : レプリケーションのサーバ識別のため

```
vagrant@vagrant-ubuntu-trusty-64:~$ sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
vagrant@vagrant-ubuntu-trusty-64:~$ sudo vim /etc/mysql/my.cnf

vagrant@vagrant-ubuntu-trusty-64:~$ diff -u /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
--- /etc/mysql/my.cnf	2015-04-05 01:29:23.611131280 +0000
+++ /etc/mysql/my.cnf.bak	2015-04-05 00:53:27.685535027 +0000
@@ -44,7 +44,7 @@
 #
 # Instead of skip-networking the default is now to listen only on
 # localhost which is more compatible and is not less secure.
-# bind-address		= 127.0.0.1
+bind-address		= 127.0.0.1
 #
 # * Fine Tuning
 #
@@ -84,9 +84,8 @@
 # The following can be used as easy to replay backup logs or for replication.
 # note: if you are setting up a replication slave, see README.Debian about
 #       other settings you may need to change.
-server-id		= 1
-log_bin			= /var/log/mysql/mysql-bin.log
-log_bin_index		= /var/log/mysql/mysql-bin.log
+#server-id		= 1
+#log_bin			= /var/log/mysql/mysql-bin.log
 expire_logs_days	= 10
 max_binlog_size         = 100M
 #binlog_do_db		= include_database_name

vagrant@vagrant-ubuntu-trusty-64:~$ sudo /etc/init.d/mysql restart
```

#### レプリケーション用のユーザ作成
```
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.33.11' IDENTIFIED BY 'hogehoge';
Query OK, 0 rows affected (0.00 sec)

mysql> select Host, User, Password from mysql.user;
+--------------------------+------------------+-------------------------------------------+
| Host                     | User             | Password                                  |
+--------------------------+------------------+-------------------------------------------+
| localhost                | root             | *0B025000DCF68B1D374153A7005639E320D41D5B |
| vagrant-ubuntu-trusty-64 | root             | *0B025000DCF68B1D374153A7005639E320D41D5B |
| 127.0.0.1                | root             | *0B025000DCF68B1D374153A7005639E320D41D5B |
| ::1                      | root             | *0B025000DCF68B1D374153A7005639E320D41D5B |
| localhost                | debian-sys-maint | *C1513E8C4BBD810581FD6394971195E07D57C66D |
| 192.168.33.11            | repl             | *0B025000DCF68B1D374153A7005639E320D41D5B |
+--------------------------+------------------+-------------------------------------------+
6 rows in set (0.00 sec)
```
 
### slave
- log-slave-updates : スレーブでもBinLog をはきだすようにする。（masterに昇格することを想定）
- read_only : SUPER権限のないユーザは更新が出来なくなる。

```
vagrant@vagrant-ubuntu-trusty-64:~$ sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
vagrant@vagrant-ubuntu-trusty-64:~$ sudo vim /etc/mysql/my.cnf

vagrant@vagrant-ubuntu-trusty-64:~$ diff -u /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
--- /etc/mysql/my.cnf	2015-04-05 07:21:50.709444657 +0000
+++ /etc/mysql/my.cnf.bak	2015-04-05 07:19:29.955104256 +0000
@@ -84,7 +84,7 @@
 # The following can be used as easy to replay backup logs or for replication.
 # note: if you are setting up a replication slave, see README.Debian about
 #       other settings you may need to change.
-server-id		= 2
+#server-id		= 1
 #log_bin			= /var/log/mysql/mysql-bin.log
 expire_logs_days	= 10
 max_binlog_size         = 100M
@@ -107,9 +107,7 @@
 # ssl-cert=/etc/mysql/server-cert.pem
 # ssl-key=/etc/mysql/server-key.pem

-# slave setting
-log-slave-updates
-read_only
+

 [mysqldump]
 quic
```

## DB, Table を作成（Master）
### チェック

```
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```

### サンプルデータ作成

```
mysql> create database sample;
Query OK, 1 row affected (0.00 sec)

mysql> create table sample.users(id int auto_increment, name varchar(20), index(id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sample.users (name) values ("7kaji");
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample.users;
+----+-------+
| id | name  |
+----+-------+
|  1 | 7kaji |
+----+-------+
1 row in set (0.00 sec)
```

### BinLog を確認
```
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# File             BinLogのファイル名
# Position         BinLogの位置情報
# Binlog_Do_DB     BinLogに記録するように指定されているDB名
# Binlog_ognore_DB BinLogに記録しないように指定されているDB名

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)
```

### dump

```
vagrant@vagrant-ubuntu-trusty-64:~$ mysqldump -uroot -p --all-databases --lock-all-tables --flush-logs --master-data=2 > dump.sql
vagrant@vagrant-ubuntu-trusty-64:~$ ls
dump.sql  mysql-apt-config_0.3.2-1ubuntu14.04_all.debf
```

### Mac に転送する
```
$ vagrant ssh-config ubuntu_1 > ubuntu_1_ssh.config
$ vagrant ssh-config ubuntu_2 > ubuntu_2_ssh.config
$ ls
Vagrantfile         dump.sql            ubuntu_1_ssh.config ubuntu_2_ssh.config

$ scp -F ubuntu_1_ssh.config ubuntu_1:dump.sql ./
dump.sql                                                                   100%  596KB 595.8KB/s   00:00
$ ls
Vagrantfile         dump.sql            ubuntu_1_ssh.config ubuntu_2_ssh.config
```

### もう１つの VM (slave) に渡す
```
$ scp -F ubuntu_2_ssh.config ./dump.sql vagrant@ubuntu_2:~/
dump.sql                                                                   100%  596KB 595.8KB/s   00:00
```

---

- mysqld を停止することが可能なら、とめてからやるとよい。
- mysqldump ではなく、datadir をまるごとコピーして渡してもよい。
- 本番だったら、tar で圧縮したものを送るとか、帯域制限かけるなど適宜にやるとよい。
- （Vagrant でたてたVM 同士でいい感じに転送する方法ありそう。。）

## slave
dumpから生成

```
vagrant@vagrant-ubuntu-trusty-64:~$ ls
dump.sql  mysql-apt-config_0.3.2-1ubuntu14.04_all.deb
vagrant@vagrant-ubuntu-trusty-64:~$ mysql -uroot -p < dump.sql
vagrant@vagrant-ubuntu-trusty-64:~$ mysql -uroot -p -e 'select * from sample.users;'
Enter password:
+----+-------+
| id | name  |
+----+-------+
|  1 | 7kaji |
+----+-------+
```

### change master
- file, postion は master で確認したもの

```
mysql> change master to
    -> master_host = '192.168.33.10',
    -> master_user = 'repl',
    -> master_password = 'hogehoge',
    -> master_log_file = 'mysql-bin.000002',
    -> master_log_pos = 120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
```

### かくにん
- Master_Host とか、file pos など

```
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.33.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 120
              Relay_Log_Space: 120
              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: NULL
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: 0
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
```

### start slave
```
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
```

もっかい show slave status\G でかくにん。
Slave_IO_Running, Slave_SQL_Running が Yes になっているはず。
（I/OスレッドとI/Oスレッド）


## レプリケーションの動作確認

### master に insert 発行
```
mysql> insert into sample.users (name) values ("hogehogeman");
Query OK, 1 row affected (0.00 sec)
```

### slave でも確認
```
mysql> select * from sample.users;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | 7kaji       |
|  2 | hogehogeman |
+----+-------------+
2 rows in set (0.00 sec)
```

## memo

- 次は手動fail over してみる
- mysqlbinlog を使ってみる
- 困ったら、ショースレーブステータ( ˘ω˘)ｽﾔｧ

## REF
> 
- my.cnfの場所にハメられた。。。 – sawara.me : http://sawara.me/mysql/2073/ 
- Vagrant の仮想マシンと SCP でファイルをやり取りする | CUBE SUGAR STORAGE : http://momijiame.tumblr.com/post/81676862588/vagrant-scp
- mysqldumpを用いたMySQLのバックアップについて - サーバサイドWiki - Confluence : http://confluence.sharuru07.jp/pages/viewpage.action?pageId=360679