using mysql replication 5.6

case : ubuntu

mysql 5.6의 경우 GTID(Global Transection Identifier)를 이용하여 기존의 Mysql Replication에서 Master Server의 bin-log 의 파일명과 포지션을 이용하지 않고, GTID로 리플리케이션을 할수 있게 하였다. 또한 추가로 모니터링 및 리플리케이션 구조를 단순화 시켜 관리의 편의성을 높였다.

reference : http://www.clusterdb.com/mysql-replication/standalone-mysql-utilities-now-ga-includes-running-mysqlfailover-as-a-daemon

# ubuntu01,master:10.211.55.6
# ubuntu02,slave:10.211.55.7

sudo apt-get install mysql-server-5.6

sudo vi /etc/mysql/my.cnf

#bind-address           = 127.0.0.1

sudo mv /etc/mysql/conf.d/my5.6.cnf /etc/mysql/conf.d/mysql.cnf
sudo vi /etc/mysql/conf.d/mysql.cnf

[client]
default-character-set = utf8

[mysqld]
character-set-server  = utf8
collation-server      = utf8_general_ci
character_set_server   = utf8
collation_server       = utf8_general_ci
log_error                = /var/log/mysql/error.log

log-bin
server-id=XX # XX is numeric
gtid-mode=ON
log-slave-updates
enforce-gtid-consistency
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=YY # YY is server's ip

# at mysql - root
CREATE USER 'replication'@'10.211.55.%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'replication'@'10.211.55.%' WITH GRANT OPTION;
CREATE USER 'replication'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'replication'@'localhost' WITH GRANT OPTION;

sudo service mysql restart

여기까지가 M-S Replication 준비가 완료된 것이다.

wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.1.zip
unzip mysql-utilities-1.5.1.zip
cd mysql-utilities-1.5.1
python ./setup.py build
sudo python ./setup.py install

# install /usr/local/bin/mysql* and /usr/local/lib/python2.7/dist-packages/mysql/ and /usr/local/lib/python2.7/dist-packages/mysql_utilities-1.5.1.egg-info

# or ver 1.3.5
sudo apt-get install mysql-utilities
# replicate from ubuntu01 to ubuntu02
mysqldbcopy --source=replication:password@10.211.55.6 --destination=replication:password@10.211.55.7 --all
mysqlreplicate --master=replication:password@10.211.55.6 --slave=replication:password@10.211.55.7 --rpl-user=replication:password

# at master server
mysql -ureplication -ppassword -h10.211.55.6 -e "SHOW MASTER STATUS"
mysql -ureplication -ppassword -h10.211.55.6 -e "SHOW SLAVE HOSTS"
mysql -ureplication -ppassword -h10.211.55.7 -e "SHOW SLAVE STATUS"

# mysqlrplshow
mysqlrplshow --master=replication:password@10.211.55.6 --discover-slaves-login=replication:password

# mysqlrpladmin
mysqlrpladmin --master=replication:password@10.211.55.6 --slave=replication:password@10.211.55.7 health

# Checking privileges.
#
# Replication Topology Health:
+--------------+-------+---------+--------+------------+---------+
| host         | port  | role    | state  | gtid_mode  | health  |
+--------------+-------+---------+--------+------------+---------+
| 10.211.55.6  | 3306  | MASTER  | UP     | ON         | OK      |
| 10.211.55.7  | 3306  | SLAVE   | UP     | ON         | OK      |
+--------------+-------+---------+--------+------------+---------+

# at failover-monitoring server
mysqlfailover --master=replication:password@10.211.55.6 --discover-slaves-login=replication:password --candidates=replication:password@10.211.55.7 --rpl-user=replication:password --rediscover --interval=5 auto

다음과 같은 화면으로 나온다.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Thu Sep  4 02:18:57 2014

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql-bin.000002  191

GTID Executed Set
4cc40ec6-33d3-11e4-9a23-001c42a0769e:1

Replication Health Status
+--------------+-------+---------+--------+------------+---------+
| host         | port  | role    | state  | gtid_mode  | health  |
+--------------+-------+---------+--------+------------+---------+
| 10.211.55.6  | 3306  | MASTER  | UP     | ON         | OK      |
| 10.211.55.7  | 3306  | SLAVE   | UP     | ON         | OK      |
+--------------+-------+---------+--------+------------+---------+

다음과 같은 방식으로 Fail-Over 테스트를 해본다.

# ubuntu01(10.21.55.6) down
sudo service mysql stop
Failover starting in 'auto' mode...
# Candidate slave 10.211.55.7:3306 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Disconnecting new master as slave.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at 10.211.55.7:3306

Failover console will restart in 5 seconds.
MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Thu Sep  4 02:31:49 2014

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql_bin.000002  191

GTID Executed Set
4cc40ec6-33d3-11e4-9a23-001c42a0769e:1

Replication Health Status
+--------------+-------+---------+--------+------------+---------+
| host         | port  | role    | state  | gtid_mode  | health  |
+--------------+-------+---------+--------+------------+---------+
| 10.211.55.7  | 3306  | MASTER  | UP     | ON         | OK      |
+--------------+-------+---------+--------+------------+---------+
# ubuntu01(10.21.55.6) up
sudo service mysql start

# replicate from ubuntu02 to ubuntu01
mysqldbcopy --source=replication:password@10.211.55.7 --destination=replication:password@10.211.55.6 --all
mysqlreplicate --master=replication:password@10.211.55.7 --slave=replication:password@10.211.55.6 --rpl-user=replication:password
MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Thu Sep  4 03:47:57 2014

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mysql_bin.000004  231

GTID Executed Set
2452845f-33d3-11e4-9a22-001c4298d1c7:1 [...]

Replication Health Status
+--------------+-------+---------+--------+------------+---------+
| host         | port  | role    | state  | gtid_mode  | health  |
+--------------+-------+---------+--------+------------+---------+
| 10.211.55.7  | 3306  | MASTER  | UP     | ON         | OK      |
| 10.211.55.6  | 3306  | SLAVE   | UP     | ON         | OK      |
+--------------+-------+---------+--------+------------+---------+

mysqlfailover의 경우 –exec-before, –exec-after 사용하면 failover가 일어났을때 관리자에게 메일을 보낸다거나등의 추가 스크립트를 실행할 수 있다.

# at monitoring
sudo mysqlfailover --master=replication:password@10.211.55.6 --discover-slaves-login=replication:password --rediscover --interval=5 --force --rpl-user=replication:password --failover-mode=auto

# run daemon
sudo mysqlfailover --master=replication:password@10.211.55.6 --discover-slaves-login=replication:password --rediscover --interval=5 --force --rpl-user=replication:password --failover-mode=auto --pidfile=/var/run/mysqld/failover.pid --log=/var/log/mysql/failover.log --daemon=start

# restart
sudo mysqlfailover --master=replication:password@10.211.55.6 --discover-slaves-login=replication:password --rediscover --interval=5 --force --rpl-user=replication:password --failover-mode=auto --pidfile=/var/run/mysqld/failover.pid --log=/var/log/mysql/failover.log --daemon=restart

# stop 
sudo mysqlfailover --master=replication:password@10.211.55.6 --discover-slaves-login=replication:password --rediscover --interval=5 --force --rediscover --rpl-user=replication:password --failover-mode=auto --pidfile=/var/run/mysqld/failover.pid --log=/var/log/mysql/failover.log --daemon=stop

추가로 login-paths를 이용해서 관리 계정을 숨길수 있다. 그리고 그 정보로 failover daemon을 로긴정보를 숨겨서 관리가 가능하다.

reference : http://dev.mysql.com/doc/mysql-utilities/1.4/en/mysql-utils-intro-connspec-connect_utility.html

/usr/bin/mysql_config_editor set --login-path=ubuntu01 --host=10.211.55.6 --user=replication --port=3306 --password
/usr/bin/mysql_config_editor set --login-path=ubuntu02 --host=10.211.55.7 --user=replication --port=3306 --password

# print server info
/usr/bin/mysql_config_editor print --login-path=ubuntu01

# run failover daemon
sudo mysqlfailover --master=ubuntu01 --discover-slaves-login=ubuntu01 --force --rpl-user=ubuntu01 auto

slave 를 read-only로 만들어서 수정을 막게 만드는 것도 좋은 선택이다.

reference : http://adminuser.wordpress.com/2013/01/08/check-if-make-a-mysql-instance-read-only/