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