MySQL Alta Disponibilidade com Replicação

MySQL ServerArchitecture and Technology

MySQL

Marcelo Souzamarcelo.t.souza@oracle.comMySQL Account Manager

Henrique Leandrohenrique.leandro@oracle.comMySQL Sales Consultant

Agenda

• Introdução• Opções de Alta Disponibilidade• Conceito de Alta Disponibilidade• Soluções para Alta Disponibilidade• MySQL 5.5 Update • MySQL Enterprise Overview• Q&A

Filesystems, Files and LogsRedo, Undo, Data, Index, Binary, Error, Query and Slow

Pluggable Storage Engines Architecture

ConnectorsNative C API, JDBC, ODBC, .Net, PHP, Ruby, Python, VB, Perl

Connection PoolAuthentication – Thread Reuse – Connection Limits – Check Memory – Caches

Enterprise Management Services and Utilities

Backup & RecoverySecurity

ReplicationCluster

PartitioningInstance Manager

Information_SchemaMySQL Workbench

SQL InterfaceDDL, DML, Stored Procedures, Views,

Triggers, Etc..

ParserQuery Translation, Object Privileges

OptimizerAccess Paths, Statistics

CachesGlobal and Engine

Specific Caches and Buffers

Pluggable Storage EnginesMemory, Index and Storage Management

InnoDB MyISAM Cluster Etc… Partners Community More..

MySQL Server

Clients and Apps

© 2010 Oracle Corporation – Proprietary and Confidential

High Availability & Disaster Recovery

• MySQL replication- Built into MySQL database

- Fast & easy implementation

• Sync disk replication- DRBD, AVS

- Replicate at disk level

Shared disk cluster- Shared storage device

- Cluster mgt. software

MySQL Cluster- Active/Active solution

- Five 9’s

- Very high performance

- Not for all workloadsActive Node

Passive

DRBD

High Availability

software upgrades

hardware upgrades

maintenance operations

software failures

network failures

power failures

disasters

hardware failures

35 days 4 days 50 mins 5 mins8 hours

9 9 9 9 9 %

Clusteringand geographical

redundancy

ClusteringTechnologies

ReplicationTechnologies

Well-Managed

Unmanaged

SmallBusiness

ISP & Mainstream

Business

DataCenters

BankingMedical

TelcoMilitaryDefense

.

Applications

Virtual IP

Master HA Slave

Replication

Scale-out Slave Scale-out Slave Scale-out Slave

MySQL Replication

Replication

Replication TopologiesA Flexible Way to Scale

Multiple

Single Chain

Circular

Master

Slave

© 2010 Oracle Corporation – Proprietary and Confidential

Shared Disk Clusters

>Active/Passive server or process configuration

• Links servers with 3rd party software and virtual IP>Reliability

• Commonly used solution>Fault Tolerance

• No single point of failure with appropriate hardware>High Availability

• Data handled by a SAN or NAS and always available

• Automatic fail-over>Simplified Management

• Cluster management utilities

MySQLServer Nodes

Shared Storage

Applications

• Sun Cluster• Red Hat Enterprise Server• Novell Suse Enterprise Server• Veritas Cluster Server• Linux HA with Heartbeat• HP Serviceguard• SCO Reliant-HA• Microsoft Cluster Server

DRBD

Distributed Replicated Block Device

•Runs over standard IP networks•Distributed storage•Similar to network RAID

Synchronous

Characteristics•No special networking components (except Heartbeat)•Excellent performance (blocks vs. rows of data)•Manages inconsistencies of data during a failure•Hides the complexity of many recovery actions•Linux heartbeat manages fail over and virtual IPs

Applications

Virtual IPActive Node Passive Node

DRBD

Data Nodes

Management

Server

Client Applications

MySQLServer

Access through

native API

MySQLServer

Management

Client

ApplicationLayer

Interface/SQLLayer

DataLayer

MySQL Cluster

MySQL Server 5.5

Making MySQL Better Everyday

Announced at MySQL Sunday:

MySQL 5.5 Release Candidate

MySQL 5.5 SysBench BenchmarksWindows

MySQL 5.1.50(InnoDB built-in)

MySQL 5.1.50 (InnoDB Plug-in)

MySQL 5.5.6 (New InnoDB)

Intel x86_644 CPU x 2 Cores/CPU3.166 GHz, 8GB RAMWindows Server 2008

1561% performance gain

for MySQL 5.5 over 5.1.50; at scale

RC

© 2010 Oracle Corporation – Proprietary and Confidential

A Virtual MySQL DBA Assistant!

MySQL Enterprise Monitor

MySQL Enterprise Monitor 2.3

Confidential, Oracle Internal Only

MySQL Enterprise Monitor

© 2010 Oracle Corporation – Proprietary and Confidential

MySQL

Marcelo Souzamarcelo.t.souza@oracle.comMySQL Account Manager

Henrique Leandrohenrique.leandro@oracle.comMySQL Sales Consultant