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/