UNIVERSIDADE DE SOROCABA

TRABALHO DE PS-GRADUAO
MBA EM BUSINESS INTELLIGENCE

SISTEMA DE GERENCIAMENTO DE BANCO DE DADOS

MYSQL

Autores: Everton Luiz Belarmino e

Joo Paulo e
Wilbert Canno

Banco de Dados MySQL

Definio:

O MySQL um sistema gerenciador de banco de dados relacional de

cdigo aberto usado na maioria das aplicaes gratuitas para gerir suas bases
de dados. O servio utiliza a linguagem SQL (Structure Query Language
Linguagem de Consulta Estruturada), que a linguagem mais popular para
inserir, acessar e gerenciar o contedo armazenado num banco de dados.
um dos sistemas de banco de dados Open Source mais populares no
mundo.
Gravao e Fragmentao do Dado:

O MySQL faz o armazenamento de cada banco de dados como um

subdiretrio do seu diretrio principal.
(imagem)

Ao criar uma tabela, o MySQL armazena a definio da tabela em um

arquivo (.frm) como o mesmo nome da tabela. Por exemplo, ao criar uma
tabela chamada cliente, o MySQL armazena a definio da tabela como
cliente.frm.

(imagem)

Assim como vrios sistemas de arquivos GNU/Linux, possui vrias

ferramentas de armazenamento. Cada uma dessas ferramentas possui suas
particularidades, o servidor de banco de dados se comunica atravs delas
transparentemente com as aplicaes na camada de consultas.
As ferramentas de armazenamento no interpretam SQL* nem se
comunicam umas com as outras, elas simplesmente respondem s requisies
do servidor.
(imagem)

A Engine MyISAM
O MyISAM oferece um bom acordo entre desempenho e caractersticas
teis, como indexao de texto completo, compactao e funes espaciais
(GIS).
No disco, o MySQL representa cada tabela MyISAM usando trs arquivos:
um arquivo de formato que armazena a definio da estrutura da tabela, um
aquivo de dados que armazena o contedo das tuplas da tabela e um arquivo
de index que armazena cada ndice da tabela. A saber, o formato, os dados e
os arquivos de ndices para uma tabela chamda tabelaexemplo, so gerados
os arquivos tabelaexemplo.frm, tabelaexemplo.MYD e tabelaexemplo.MYI.
Principais Caractersticas da Engine MyISAM

MyISAM tem o manipulador de colunas AUTO_INCREMENT mais

flexvel dentre todos os mecanismos de armazenamento.

Tabelas MyISAM podem ser convertidas de forma rpida,

comprimidas e definidas como somente leituras para salvar espao
em disco. MyISAM bloqueia tabelas inteiras, no linha.

possvel inserir novas linhas na tabela enquanto consultas

selecionadas esto rodando nela (inseres concorrentes).

Certos modificadores de programao so permitidos para alterar

as prioridades de requisies, LOW_PRIORITY, HIGH_PRIORITY
e DELAYED. MySQL suporta checagem e reparao de tabelas
MyISAM.

Os comandos usados para checar os erros de uma tabela e

consert-los so CHECK TABLE mytable e REPAIR table. Tambm
possvel usar a ferramenta de linha de comando myisamchk para
checar e reparar tabelas quando o servidor estiver off-line.

Voc pode criar ndices nos primeiros 500 caracteres das colunas
BLOB e TEXT nas tabelas MyISAM.

O MyISAM suporta ndices de textos completos FULLTEXT e tipo

de dados espaciais, o que indexa palavras individuais para
operaes complexas de busca.

A Engine InnoDB
InnoDB

foi

desenvolvido

para

processamento

de

transao,

principalmente para o processamento de muitas transaes de vida curta. Seu

desempenho e recuperao de travamento automtico tambm o tornam
popular para armazenamentos no-transacional.
O InnoDB utiliza arquivos para a armazenamento conhecidos como
tablespace. Uma tablespace gerenciado unicamente pelo InnoDB sozinho.
Para as altas concorrncias ele usa MVCC (Controle de Concorrncia de
verso mltipla) com os quatro nveis de isolamento padro do SQL. Tabelas
InnoDB so construdas em um ndice agrupado, as estruturas de ndice do
InnoDB so muito diferentes das estruturas da maioria das outras ferramentas
de armazenamento do MySQL. Como um resultado, ele oferece buscas de
chave primria muito rpida.
Principais Caractersticas da Engine InnoDB

Cada tabela InnoDB representada no disco por um arquivo .frm

no diretrio principal do banco de dados, assim como os dados e
ndices so armazenados no tablespace InnoDB.

O InnoDB tem suporte a transaes, com commit e rollback. Isto

fornece

atomicidade,

consistncia,

nvel

de

isolamento

durabilidade dos dados. Mltiplo versionamento usado para isolar

uma transao de outra.

InnoDB fornece auto recuperao em caso de queda do servidor

ou do host em que o servidor esteja rodando.

O MySQL gerencia querys de contenes para tabelas InooDB

usando mltiplos versionamentos em conjunto com o bloqueio de
linhas em nvel. Assim, cada transao tem sua prpria viso do
banco de dados e mantm o nvel de conteno no mnimo. O
resultado uma boa manipulao de concorrncia, mesmo que um
cliente faa um mix de consultas e escritas no banco.

InnoDB suporta chaves estrangeiras e integridade referencial,

incluindo deletes e updates em cascatas.

O formato de armazenamento em tablesapace pode ser porttil em

algumas situaes, dessa forma o InnoDB pode ser copiado diretamente
para outro host e usando pelo servidor.

Consideraes sobre as ferramentas:

Se sua aplicao exigir transaes, InnoDB a escolha mais

comprovada, estvel e bem integrada.

MyISAM uma boa escolha se uma tarefa no exigir transaes e

realizar primariamente consultas SELECT ou INSERT.

Tabelas MyISAM geralmente se corrompem mais facilmente e

demoram mais para recuperar-se do que tabelas InnoDB.

Somente MyISAM suporta indexaes Geoespacial e FULLTEXT

no MySQL.

Somente InnoDB trabalha em conformidade com a ACID.

Fragmentao do MySQL.
No MySQL, quando voc excluir registros de um espao de tabela no
remapeado automaticamente. O problema com isto que se uma tabela de
executar muitas operaes DELETE, o espao fsico da mesa ser cada vez
mais fragmentado e reduzido desempenho.
Para a correo deste problema tambm usamos as ferramentas MyISAM
ou InnoDB, ambas ao executar o comando OPTIMIZE TABLE, fazem
uma desfragmentao automtica da tabela corrigindo este problema.

altamente

recomendvel

usar

este

comando

periodicamente,

especialmente em tabelas que so mais declaraes de excluso de registros.

Nas tabelas MyISAM o OPTIMIZE TABLE faz o seguinte:

Se a tabela tem linhas deletadas ou divididas, repara a tabela.

Se as pginas de ndice no esto organizadas, as organiza.
Se as estatsticas da tabela no esto atualizadas (e a reparao no pode ser
completada organizando o ndice), as atualiza.

Para tabelas InnoDB, o OPTIMIZE TABLE ligado ao ALTER TABLE, que recria a tabela para
atualizar as estatsticas do ndice e liberar espao no utilizado no ndice clusterizado.

Comando SQL para verificar quais as tabelas mais fragmentadas:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH,

INDEX_LENGTH, DATA_FREE, (DATA_FREE*100/DATA_LENGTH) as PRC FROM
INFORMATION_SCHEMA.TABLES WHERE (DATA_FREE*100/DATA_LENGTH);
O comando acima mostra no campo PRC o percentual de fragmentao levando em considerao o
'DATA_LENGTH' e o 'DATA_FREE', teoricamente quanto maior o datafree mais fragmentada est a
tabela, porm este comando mostra tambm as tabelas InnoDB, onde o 'datafree' pode ter um valor
maior e fixo, isto no indica fragmentao.
Para verificar as engines das tabelas e tambm poder visualizar o 'DATA_FREE' voc

pode rodar o seguinte comando:

SHOW TABLE STATUS;

Qualquer valor no campo 'DATA_FREE' das tabelas MyIsam indcio de fragmentao.

Replicao (Total e Parcial)

INTRODUO
O objetivo de um mecanismo de replicao de dados permitir a manuteno de vrias
cpias idnticas de um mesmo dado em vrios servidores de bancos de dados (SGBD). Os
principais benefcios da replicao de dados so a redundncia, o que torna o sistema tolerante
a falhas, a possibilidade de um balanceamento de carga do sistema, j que o acesso pode ser
distribudo entre as rplicas, e finalmente, ter-ser o backup online dos dados, j que todas as
replicas estariam sincronizadas. Este artigo, apresenta uma introduo ao mecanismo de
replicao do MySQL, bem como as configuraes bsicas para realizao desta tarefa.
VISO GERAL DA REPLICAO

O MySQL permite um tipo de replicao conhecido como Master-Slave, onde temos um

servidor atuando como master e um ou mais servidores atuando como slave. O master grava
em um log binrio de alterao todos os comandos de atualizaes da base de dados. Desta
forma, todas as alteraes ocorridas no master so imediatamente replicadas para os outros
servidores slave.
A replicao no mysql principalmente compatvel com a anterior, isto , um servidor
mais novo pode normalmente ser um escravo de um servidor mais velho sem nenhum
problema. Porm, verses mais antigas dos servidores so, freqentemente, incapazes de
servir como slaves de verses mais novas, pois eles no podem entender novas caractersticas
ou a sintaxe SQL que o servidor mais novo utiliza, e pode haver diferenas no formato dos
arquivos que a replicao usa, por exemplo, voc no pode replicar de um master MySQL 5.0
para um slave MySQL 4.0.
Com a replicao voc possui uma srie de vantagens como:
Equilbrio de carga: a replicao, geralmente no precisa de uma largura de banda muito
intensiva, voc pode inicia e parar conforme a sua vontade e o slave pode trabalhar distante do
master.
Distribuio de dados: a replicao pode ajudar a distribuir as consultas de leitura
atravs de diversos servidores, ou seja, o mesmo dado pode est armazenados em todos os
servidores envolvidos da replicao.
Backup: a replicao uma tcnica valiosa para ajudar com backups, porm um slave
no nem um backup nem um substituto para backups.
Alta disponibilidade: se um servidor falhar tem outro servidor para suprir aquele servidor
que falhou.

COMO A REPLICAO FUNCIONA

O MySQL realiza a replicao em um simples processo de trs fases, a

Figura 1 ilustra a replicao com mais detalhes:

O master registra alteraes aos seus dados no seu log binrio (estes
registros so chamados de evento de log binrio): antes de cada transao que
atualiza dados no master, o mesmo registra as alteraes no seu log binrio,
ou seja, o mysql escreve as transaes no seu log binrio e logo em seguida o
master diz as ferramentas de armazenamento para comitar as transaes.
O slave copia os eventos de log binrio do master no seu relay log (log de
vigilncia): o servidor slave, atravs de uma thread de I/O, abre uma conexo
com o master e logo inicia o processo de esvaziamento de binlog. O
esvaziamento de binlog consiste na leitura do evento a partir do log binrio do
master. A Thread de I/O escreve os eventos no relay log do slave.
O slave repete aos eventos no relay log: a thread l e repete eventos a
partir do relay log, dessa forma atualizando os dados do slave para
combinarem com os do master.
Fonte: http://www.devmedia.com.br/mysql-replicacao-de-dados/22923

Cluster

(https://en.wikipedia.org/wiki/MySQL_Cluster)

Foi projetado para proporcionar alta disponibilidade e alto rendimento com

baixa latncia, permitindo simultaneamente uma escalabilidade quase linear.

MySQL Cluster implementado atravs do mecanismo de armazenamento

NDB ou NDBCLUSTER para MySQL ("NDB" Network Database).
NDB (https://en.wikipedia.org/wiki/NDB_Cluster)
NDB Cluster o sistema de banco de dados distribudo subjacente MySQL
Cluster. Ele pode ser usado de forma independente de um servidor MySQL
com os usurios acessando o Cluster atravs da API NDB (C ++). "NDB"
significa banco de dados de rede.
Do ponto de vista do servidor MySQL Cluster NDB um mecanismo de
armazenamento para armazenar tabelas de linhas.
Do ponto de vista Cluster NDB, uma instncia MySQL Server um processo de
API conectado ao cluster. NDB Cluster pode suportar simultaneamente o
acesso de outros tipos de processos de API, incluindo Memcached,
JavaScript / Node.js, Java, JPA e HTTP / REST. Todos os processos de API
podem operar com as mesmas tabelas e os dados armazenados no Cluster
NDB.
Ndb Cluster tem a seguinte capacidade independente de qualquer servidor
MySQL:
Persistente, armazenamento distribudo de tabelas com ndices exclusivos ou
encomendados.
Redo logging, pontos de verificao, a recuperao do sistema.
Auto-sharding de tabelas em todos os ns de dados.
Hashing parcial ou total de chaves primrias.
Chave primria, chave nica, varredura da tabela e verificao de ndice
ordenado.
Execuo da varredura paralelo com filtros "pushed-down '
Bloqueios de linha, atualizaes transacionais.
Replicao de dados sncrona dentro do cluster, fail over automrico +
recuperao

fail over de conexo automtica para os processos de 'API'

tipos de dados SQL padro, incluindo Blobs etc.
O armazenamento de dados em disco
alterao de dados assncrona, mecanismo de gatilhos(triggers)
operaes DDL (embora DDL independente no vai ser actualmente
reconhecido por qualquer servidor MySQL)
Online index add, online table alter
Online add node, table reorganisation
backup on-line.
atualizao on-line.
MySQL Cluster atualmente usa o servidor MySQL para fornecer as seguintes
capacidades em cima do Ndb Cluster:
SQL parsing / otimizao / capacidade de execuo.
Conectores para aplicaes via JDBC, ODBC ...
Cross-tabela de juno mecanismo.
autenticao e autorizao.
replicao assncrona de dados para outros sistemas.
Arquitectura
MySQL Cluster projetado em torno de uma arquitetura multi-mestre distribudo,compatvel
com ACID. MySQL Cluster usa particionamento automrico para dimensionar operaes de
leitura e escrita e pode ser acessada via SQL e no-SQL (NoSQL).
Replicao
Internamente o MySQL Cluster usa replicao sncrona atravs de um mecanismo de
confirmao de duas fases, a fim de garantir que os dados sero gravados em vrios ns ao
"commitar" os dados. (em contraste com o que usualmente referido como "replicao
MySQL", que assncrona.) Duas cpias (rplicas) conhecidas como as de dados so
necessrias para garantir a disponibilidade. MySQL Cluster cria automaticamente "grupos de
ns" a partir do nmero de rplicas e ns de dados especificados pelo usurio. As atualizaes

so sincronicamente replicado entre os membros do grupo de ns para proteger contra perda

de dados e rpido suporte entre falhas nos ns.
Tambm possvel replicar de forma assncrona entre agregados; esta muitas vezes referida
como "Replicao MySQL Cluster" ou "replicao geogrfica". Isso normalmente usado para
replicar grupos entre os centros de dados para recuperao de desastres ou para reduzir os
efeitos da latncia da rede, localizando os dados fisicos mais perto de um conjunto de usurios.
Ao contrrio de replicao do MySQL padro, a replicao geogrfica do MySQL Cluster usa
controle de concorrncia otimista(1).
(1) No controle de simultaneidade otimista, os usurios no bloqueiam os dados quando os
lem. Quando um usurio atualiza os dados, o sistema verifica se outro usurio alterou os
dados depois de lidos. Se outro usurio tiver atualizado os dados, um erro ativado
A partir do MySQL Cluster 7.2, suporte para a replicao sncrona entre os centros de dados foi
suportado com o recurso multi-site Clustering.
particionamento de dados horizontal
MySQL Cluster implementado como um banco de dados multi-master totalmente distribudo
assegurando atualizaes feitas por qualquer aplicao ou n SQL que esto disponveis
imediatamente para todos os outros ns que acessam o cluster, e cada n de dados pode
aceitar operaes de gravao.
Os dados dentro das tabela MySQL Cluster tabelas (NDB) particionado automaticamente em
todos os ns de dados no sistema. Isso feito com base em um algoritmo de hash com base
na chave primria na tabela, e transparente para a aplicao final. MySQL Cluster capaz de
suportar consultas entre fragmentos e transaes.
Os usurios podem definir seus prprios esquemas de particionamento. Isso permite aos
desenvolvedores adicionar "conscincia de distribuio" para aplicaes por partio com base
em uma sub-chave que comum a todas as linhas que est sendo acessado por meio de
operaes de funcionamento elevados. Isso garante que os dados utilizados para completar as
transaces localizada sobre o mesmo fragmento, reduzindo assim picos na rede.(network
Hops no sei ao certo a traduo)
Hybrid Storage
MySQL Cluster mantm todas as colunas indexadas em memria distribuda. colunas no
indexadas tambm pode ser mantido na memria distribuda ou pode ser mantido no disco com
um cache de pgina na memria. Armazenar colunas no indexadas no disco permite MySQL
Cluster armazenar conjuntos de dados maiores do que a memria total de mquinas em
cluster.
MySQL Cluster escreve redo logs em disco para todas as alteraes de dados, bem como
dados de verificao apontando para o disco regularmente. Isso permite o cluster recuperar os

dados de forma consistente a partir do disco aps uma queda completa de cluster. medida
que os redo logs so escritos de forma assncrona em relao transao de confirmao, um
pequeno nmero de transaes podem ser perdidos se o cluster falhar por completo, no
entanto, isso pode ser atenuado pelo uso de replicao geogrfica ou cluster multi-site
discutido acima. Geralmente o atraso de gravao assncrona padro 2 segundos, e
configurvel.
Quando uma tabela MySQL Cluster mantida na memria, o cluster s ir acessar
armazenamento em disco para escrever registros de Redo e checkpoints. Como essas
gravaes so sequenciais e acesso aleatrio limitados esto envolvidos, MySQL Cluster pode
alcanar taxas de transferncia de gravao mais elevadas com hardware disco limitado em
comparao com um tradicional caching RDBMS baseada em disco. Este ponto de verificao
no disco de dados da tabela em memria pode ser desativado (em uma base por tabela) se a
persistncia baseada em disco no necessrio.

APIs SQL e NoSQL

Como o MySQL Cluster armazena tabelas em ns de dados, em vez de MySQL Server,
existem vrias interfaces disponveis para acessar o banco de dados:
Acesso SQL atravs do servidor MySQL.
APIs NoSQL onde as bibliotecas Cluster MySQL podem ser incorporadas em um aplicativo
para fornecer acesso direto aos ns de dados, sem passar por uma camada de SQL. Esses
incluem:
memcached
Node.js / JavaScript
Java e JPA
HTTP / REST
NDB API (C ++)

Implementao
MySQL Cluster usa trs tipos diferentes de ns (processos):
N de dados (Data Node) (ndbd / processo ndbmtd): Esses ns armazenar os dados. As
tabelas so automaticamente compartilhadas entre os ns de dados que tambm tratam de
forma transparente o balanceamento de carga, replicao, failover e auto-cura(SELF-HEALT).

n de gerenciamento (Management Node) (processo ndb_mgmd): Utilizado para

configurao e monitoramento do cluster. Eles so necessrios apenas para iniciar ou reiniciar
um n de cluster. Eles tambm podem ser configurados como rbitros, mas isto no
obrigatrio (Servidores MySQL pode ser configurado como rbitros em vez disso).
n do aplicativo ou n SQL (Application node or Sql Node) (processo mysqld): Um
servidor MySQL (mysqld) que se conecta a todos os ns de dados a fim de realizar o
armazenamento e recuperao de dados. Este tipo de n opcional; possvel consultar os
ns de dados diretamente atravs da API NDB, nativamente usando a API C ++ ou uma das
APIs NoSQL adicionais descritos acima.
Geralmente, espera-se que cada n ser executado em um host fsico separado, VM ou
instncia de nuvem (embora seja muito comum a co-localizar ns de gerenciamento com
servidores MySQL). Para as melhores prticas, recomenda-se no co-localizar ns dentro do
mesmo grupo de ns em um nico host fsico (como que representaria um nico ponto de
falha).