LoginSignup
16

More than 5 years have passed since last update.

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

Last updated at Posted at 2015-04-05

Vagrant 準備

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

もう1つの 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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
16