MySQL Best Practice r o c k P L A C E I n c. C o m p a n y I n t r o d u c t i o n www. ro c k p lace. co. kr 2013 rockplace Inc.
CONTENTS Chapter 1. MySQL Overviews 1. MySQL 소개 2. MySQL Architecture 3. MySQL Storage Features Chapter 2. MySQL Refs. Architectures 1. MySQL Replication 2. MySQL with Pacemaker + DRBD 또는 MySQL with Lifekeeper 3. MySQL Cluster(Carrier Grade Edition) 4. MySQL with OS Cluster 5. HA 구성별신뢰수준 6. 기타솔루션 Chapter 3. Case Study 1. 사례 1. MySQL Replication & OS Cluster 2. 사례 2. MySQL Cluster 3. 사례 3. Private Cloud MySQL+Pacemaker+DRBD 4. 사례 4. (MySQL+MMM) * N 5. 사례 5. MySQL Multi-Zone H/A Appendix. MySQL 5.6 NEW FEATURES
>>> Chapter 1. MySQL Overviews
Chapter 1. MySQL 개요 Overviews 1. MySQL 소개 MySQL is a very fast, fully multi-threaded, multi-user SQL database server. MySQL databases are relational. MySQL software is Dual Licensed. GPL vs. Commercial License. Provides transactional and non-transactional storage engines. (pluggable storage engine) MySQL server works in client/server or embedded systems. A large amount of contributed MySQL software is available.
Chapter 1. MySQL Overviews 2. MySQL Architecture
Chapter 1. MySQL 개요 Overviews 3. MySQL Storage Engine Features
>>> Chapter 2. MySQL Refs. Architectures
Chapter 2. MySQL Reference Architectures 1. MySQL Replication Queries Writes relay binlog mysqld I/O Thread SQL Thread data index & binlogs Replication mysqld binlog data MySQL Master MySQL Slave
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 2-1. MySQL with Pacemaker + DRBD IP Management Active DRBD Server Passive DRBD Server Synchronous Block Replication
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 2-1. MySQL with Pacemaker + DRBD 장점 Pacemaker(Heartbeat) 을통해 Auto-failover 가능 장애노드복구후 Data auto-resynchronization Transactional storage engines 지원 Shared-nothing 구조 단점및제약사항 Fail-over time이 30초이내 Network latency에의해성능이영향을받는다. Write-intensive system의경우부적합할수있다
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 2-2. MySQL with Lifekeeper IP Management Active Server Passive Server Synchronous Block Replication
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 2-2. MySQL with Lifekeeper 장점 Lifekeeper내 Steeleye-Lkcheck를통해서장애감지. Auto-failover 가능 장애노드복구후 Data auto-resynchronization Transactional storage engines 지원 Shared-nothing, Disk Mirrored 구조 제약은있지만 Shared storage 사용가능 단점및제약사항 Kernel 은 Redhat 5.8 이상커널사용가능 Redhat 5.4 이상버전사용가능, 현재 Redhat 6.0 은미지원. 6.1 이상부터지원가능 최초 Disk 간 Initial 을위한오랜시간소요 서비스라인과 Replication 라인은반드시분리해야함
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 3. MySQL Cluster (Carrier Grade Edition) Clients Application/SQL No des (NDB API and/or MySQL Serv er) Management Nodes Data Nodes NDB Storage Engine
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 3. MySQL Cluster (Carrier Grade Edition) 장점 Auto fail-over 가능 Memory based storage engine 사용으로성능향상 (Disk based 지원 ) Cluster 동작중새로운 node 추가가능 (Scale-out) Data node 의장애복구후자동으로 Data resynchronization Failover time 이없음 Active-Active 방식 Shared nothing 구조 단점및제약사항 대용량의 Data 에는적합하지않음 Data 용량이상의 Memory 필요 NDB storage engine 을사용해야만 Clustering 가능 Management node, SQL node, Data node 를분리하여필요한시스템수에따른비용증가
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 4. MySQL with OS cluster OS Cluster(RHCS, Veritas) Active MySQL Server Stand-by MySQL Server Shared Storage
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 4. MySQL with OS cluster 장점 RHCS(VCS) 를통한 Auto-failover 가능 Active node의장애발생시 Stand-by node가 Active되어서비스 MySQL에서제공되는모든 Storage engine 사용가능 단점및제약사항 Failover를위한 RHCS나 Veritas Cluster Suite와같은 3 rd party OS 솔루션필요 Failover time : 1min 30sec (generally) TCO 가매우높다.(SAN, 3 rd party solution)
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 5. HA 구성별신뢰수준 Clustering & Geographical Redundancy Clustering Technologies Replication Technologies Well-Managed Unmanaged Small Business MySQL Replication ISPs & Mainstream Business Data Centers Banking Medical MySQL Cluster MySQL + DRBD Telco Military Defense MySQL Cluster 99.999% MySQL with Pacemaker + DRBD 99.99% MySQL Replication 95.0% MySQL with RHCS 99.95% 9 9. 9 9 9 35 days 4 days 8 hours 50 mins 5 mins %
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 6-1 기타 : MySQL with MMM Read & Write VIP for reads VIP for write Failover Read-Only VIP for reads : Read-Only Read & Write Active MySQL Server Passive MySQL Server VIP for reads VIP for write Storage MySQL Monitoring Multi Master Replication Storage
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 6-1 기타 : MySQL with MMM 장점 Master 의 Active 에장애가발생하면기존 Passive 로 Failover 진행되며모든서비스 IP(VIP) 를자동이관 Replication 사용시 Auto Failover 기능이추가된구성 단점및제약사항 모든네트워크는동일세그먼트 (segment) 에연결되어야함. 구성나름이긴하나, 일반적으로 1 세트 2node 기준으로시스템 IP 2 개 ( 접근, Replication 전용 ), Write 전용 VIP, Read 전용 VIP 2 개총 7 개의 IP 가필요함. IP or Host Failover 기능만제공
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 6-2 기타 : MySQL with MHA VIP 장애발생시 Master role 변경 (VIP, Replication 변경 ) Replication zone Master 감지 MySQL Master MySQL Slave MySQL Slave MHA Manager Failover 후 New master
Chapter 3. 2. MySQL H/A Reference 구성종류 Architectures 6-2 기타 : MySQL with MHA 장점 Replication 서비스에서 Auto-Failover 기능추가 Master role 및 VIP 자동이관지원 단점및제약사항 VIP 사용이가능해야함 Third Party 이므로검증이필요함
Chapter 2. MySQL Reference Architectures 6-3 기타 : Amazon EC2 EC2 는 HA 클러스터환경을제공하지않음 MySQL Cluster 의경우대용량환경에적합하지않으며 Oracle VM 이외의 VM 환경은 Certify 가되어있지않은상태 MySQL with Pacemaker + DRBD 로구성가능 Custom 으로 zone 내에서 EIP 를컨트롤하는스크립트작성이필요함 MySQL Replication 구성가능 Read 작업을분산시킴으로써부하를나누어처리할수있음 Slave 노드를증설하는것으로쉽게 Scale-out 이가능 Cascading replication 구성을통해 Master 를이중화하여장애에대응 기본구성만으로는 Auto-failover 불가 Zone#1 Master#1 Zone#1 EIP Master Standby Master#2 DRBD DRBD Slave Slave Slave Slave [Cascading replication 구성 ] [Pacemaker+DRBD 구성 ]
>>> Chapter 3. Case Study
Chapter 3. MySQL Case Study H/A 구성종류 사례 1. MySQL Replication & OS Cluster Requirements Replication 서비스에서 Failover 기능추가될것 대용량분산처리시스템이어야할것 확장에용이할것 (scale-out) Master 서버이중화 Promotion DB를통한 Failover가실패될경우제 3의서버를 Master role로변경 부하분산과 Scale-out을위해 Replication 구성 지역별분산구조로서버운영
Chapter 3. Case Study 사례 1. MySQL Replication & OS Cluster Shard#1 Shard#2 Master Standby Master Standby SAN SAN VIP VIP Slave(Pro) Slave Slave(Pro) Slave Shard#3 Shard#4 Master Standby Master Standby SAN SAN VIP VIP Slave(Pro) Slave Slave(Pro)
Chapter 3. MySQL Case Study H/A 구성종류 사례 2. MySQL Cluster Requirements Active-Active 서비스구성 Downtime 최소화 Push 메시지타입의무중단서비스요청 적은용량의작지만빠른데이터 Read 작업요청 Read/Write Scale-out
Chapter 4. 3. 구축 Case 사례 Study 사례 2. MySQL Cluster( 6 nodes) MGM MGM Heartbeat Data SQL SQL DATA NDB DATA NDB
Chapter 3. MySQL Case Study H/A 구성종류 사례 3. Private Cloud MySQL+Pacemaker+DRBD Requirements HA(High Availability) & TB 단위데이터처리 SAN Storage 대체방안 Data Redundancy 요구 Application Level + H/W Level 장애감지가능 Auto failover 요구 Scale-out : Replication( 1Master( HA : Pacemaker+DRBD), 3 slaves(1slave bkup))
Chapter 4. 3. 구축 Case 사례 Study 사례 3. Private Cloud MySQL+Pacemaker+DRBD Hypervisor pool 1 Hypervisor pool 2 VIP Active MySQL Passive MySQL DRBD DRBD
Chapter 3. MySQL Case Study H/A 구성종류 사례 4. (MySQL+MMM) * N Requirements Big data 처리 Data Redundancy 확보 Read & Write Scale-out Auto Fail-over Fail-over시 Slave가자동으로변경된 Master를인지한후 data sync 향후 traffic 증가에따른 scale-out 방안
Chapter 4. 3. 구축 Case 사례 Study 사례 4. (MySQL+MMM) * N Client/Web Apps IP for system VIP for writes1 VIP for reads1 IP for system VIP for Reads2 VIP for writes1 MySQL Monitoring VIP for reads1 Active MySQL Multi-master Replication Fail-over Passive MySQL Master role change (Passive->Active)
Chapter 3. MySQL Case Study H/A 구성종류 사례 5. MySQL Multi-Zone H/A(Amazon EC2) Requirements Duplex Configuration Multi-Zone HA : EC2 환경에서 Active / Stand-by 구성 Read Scale-out : 1 Master, 2 Slaves Auto failover, failback
Chapter 3. Case Study 사례 5. MySQL Multi-Zone H/A WAS (JBOSS) Active Zone H/A Stand-by Zone Read/Write Fail-over Read/Write MySQL Master @1 MySQL Master #2 Replication Replication Slave Slave Slave Slave Read-only Read-only Read-only Read-only Fail-back
Q&A
>>> Appendix. MySQL 5.6 NEW FEATURES
Chapter 4. MySQL 5.6 NEW FEATURES 1. MySQL Replication Automatic failover GTID(Global Transaction ID)
Chapter 3. 4. MySQL H/A 5.6 NEW 구성 FEATURES 종류 1. MySQL Replication Automatic failover 장점 Replication Automatic failover 구현 특이사항 선행조건 Slave 사용가능 GTID enabled GTID Replication 사용 replication user 필요 Binary logging enabled GTID Replication 사용 autofailover.exe 를사용하여 candidates 설정함 MySQL Utilities 사용
Chapter 4. MySQL 5.6 NEW FEATURES 1. MySQL Replication Automatic failover 실습 실습환경 MySQL Workbench 서버 1 대 1Master 2 Slave MySQL replication 구성 Configuration 필요사항 gtid-mode=on enforce-gtid-consistency log-slave-updates log-bin master-info-repository=table relay-log-info-repository=table
Chapter 4. MySQL 5.6 NEW FEATURES GTID Replication 사용 mysql$ change master to master_host=master_ip', master_user= replication_id', master_password= password', master_auto_position=1; autofailover.exe 를사용하여 candidates 설정함 - MySQL Utilities for mysqlfailover 사용 - Mysqlfailover 용계정사용 (ID: test, Password: 1111) - $mysqlfailover --master=test:1111@master:ip:3306 --slaves=test:1111@slave1_ip:3306,test:1111@slave2_ip:3306 --discover-slaves-login=test:1111 --candidates=test:1111@cantidates_server_ip:3306 --force
Chapter 3. 4. MySQL H/A 5.6 NEW 구성 FEATURES 종류 1. MySQL Replication Automatic failover Failover 전
Chapter 4. MySQL 5.6 NEW FEATURES 1. MySQL Replication Automatic failover Failover 후
Chapter 4. MySQL 5.6 NEW FEATURES 2. Online DDL Test table schema Alter 및 DDL 구문 alter table a3 add test char(10); insert into sbtest values(null,0,0,'',''); update sbtest set pad='testtest' where id< 100; delete from sbtest where id < 100;
Chapter 4. MySQL 5.6 NEW FEATURES 2. Online DDL Session 1 altering alter table a3 add test char(10); Session 2 DDL 5.6 insert into sbtest values(null,0,' ',' '); update sbtest set pad='testtest' where id< 100; delete from sbtest where id < 100; 5.5