SGBD_Atualizado

8/17/2019 SGBD_Atualizado

1/30

UNIVERSIDADE DE SOROCABAPRÓ-REITORIA ACADÊMICA

PROGRAMA DE PÓS-GRADUAÇÃO EM MBA EM BUSINESS INTELLIGENCE

Everton Luiz Belarmino

João PauloWilbert Canno

SISTEMA DE GERENCIAMENTO DE BANCO DE DADOS

MYSQL

Sorocaba/SP2016


2/30

LISTA DE FIGURAS

Figura 1: Armazenamento dos bancos de dados no diretório principal ....................................... 5

Figura 2: Banco de dados sshop .................................................................................................... 5

Figura 3: Tabela MyISAM - diretório ............................................................................................. 6

Figura 4: Tabela InnoDB - Diretório ............................................................................................... 8Figura 5 - Resultado do Select na tabela projetos – Maio/2016 ................................................. 10

Figura 6 - Resultado do Select na tabela projetos selecionando campos específicos –

Maio/2016 ................................................................................................................................... 11

Figura 7 - Resultado do Select na tabela projetos com WHERE – Maio/2016 ............................ 11

Figura 8 - Resultado do Select na tabela projetos com Where e Like – Maio/2016 ................... 12

Figura 9 - Resultado do comando mysqlshow – Maio/2016 ...................................................... 14

Figura 10 - Replicação em três fases – Maio/2016 ..................................................................... 21



http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897596http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897596http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897596http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897599http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897599http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897599http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897601http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897601http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897601http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897602http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897602http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897602http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897603http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897603http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897603http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897603http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897602http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897601http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897599http://c/Users/paulo/Desktop/SGBD_Atualizado.docx%23_Toc450897596


3/30

Sumário

INTRODUÇÃO ............................................................................................................................ 4

OBJETIVO ................................................................................................................................... 4

BANCO DE DADOS MYSQL .................................................................................................... 4

Definição: ................................................................................................................................. 4

Gravação e Fragmentação do Dado: .................................................................................. 4

A ENGINE MyISAM ................................................................................................................... 6

Principais Características da Engine MyISAM................................................................... 6

A ENGINE INNODB ................................................................................................................... 7

Principais Características da Engine InnoDB .................................................................... 8

Considerações sobre as ferramentas: ................................................................................ 8

FRAGMENTAÇÃO DO MYSQL ............................................................................................... 9

CONSULTA DE DADOS E ÍNDICES .................................................................................... 10

METADADOS E DICIONÁRIOS DE DADOS ...................................................................... 13

REDUNDÂNCIA CONTROLADA ........................................................................................... 14

CONTROLE DE CONCORRÊNCIA ...................................................................................... 15

SEGURANÇA ........................................................................................................................... 16

RESTRIÇÕES DE INTEGRIDADE ........................................................................................ 17

ACESSO CONTROLADO ....................................................................................................... 17REPLICAÇÃO (Total e Parcial) .............................................................................................. 20

Visão geral da replicação .................................................................................................... 20

Como funciona a replicação ............................................................................................... 21

CLUSTER .................................................................................................................................. 22

NDB ........................................................................................................................................ 22

Arquitetura ............................................................................................................................. 24

Replicação ............................................................................................................................. 24

Particionamento de dados horizontal ................................................................................ 24

Hybrid Storage ...................................................................................................................... 25

APIs SQL e NoSQL .............................................................................................................. 26

Implementação ..................................................................................................................... 26

CONSIDERAÇÕES FINAIS .................................................................................................... 28

REFERÊNCIAS .................................................................................................................... 29


4/30

4

INTRODUÇÃODevido a disciplina de sistemas de gerenciamento de banco de dados

(SGBD), e as inúmeras ferramentas de armazenamento de dados disponíveis

no mercado, escolhemos o MySQL. Uma ferramenta Open Source atualmente

da Oracle sua concorrente paga. Estaremos estudando seu funcionamento e

as suas características “internas” que a cercam.

OBJETIVOO objetivo do trabalho é fazer uma pesquisa mais a fundo sobre o SGBD

do MYSQL, entendendo como trabalha, como se comporta internamente, quais

suas necessidades e possibilidades de uso. Buscamos entender o porque ela étão usada no mercado diante de um mercado tão amplo.

BANCO DE DADOS MYSQL

Definição:

O MySQL é um sistema gerenciador de banco de dados relacional de

código aberto usado na maioria das aplicações gratuitas para gerir suas bases

de dados. O serviço utiliza a linguagem SQL (Structure Query Language –

Linguagem de Consulta Estruturada), que é a linguagem mais popular para

inserir, acessar e gerenciar o conteúdo armazenado num banco de dados.

É um dos sistemas de banco de dados Open Source mais populares no

mundo.

Gravação e Fragmentação do Dado:

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

subdiretório do seu diretório principal.


5/30

5

Figura 1: Armazenamento dos bancos de dados no diretório principal

Fonte: Os autores.

Ao criar uma tabela, o MySQL armazena a definição da tabela em um

arquivo (.frm) como o mesmo nome da tabela. Por exemplo, ao criar uma

tabela chamada cliente, o MySQL armazena a definição da tabela como

cliente.frm. Ou um diretório com o nome do banco e os arquivos de cada tabela

no banco de dados específico.

Figura 2: Banco de dados sshop

Fonte: Os autores.


6/30

6

Assim como vários sistemas de arquivos GNU/Linux, possui várias

ferramentas de armazenamento. Cada uma dessas ferramentas possui suas

particularidades, o servidor de banco de dados se comunica através delas

transparentemente com as aplicações na camada de consultas.

As ferramentas de armazenamento não interpretam SQL* nem se

comunicam umas com as outras, elas simplesmente respondem às requisições

do servidor.

A ENGINE MyISAMO MyISAM oferece um bom acordo entre desempenho e características

úteis, como indexação de texto completo, compactação e funções espaciais

(GIS).

No disco, o MySQL representa cada tabela MyISAM usando três arquivos:

um arquivo de formato que armazena a definição da estrutura da tabela, um

aquivo de dados que armazena o conteúdo 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”, são gerados

os arquivos “tabelaexemplo.frm”, “tabelaexemplo.MYD” e “tabelaexemplo.MYI”.

Figura 3: Tabela MyISAM - diretório

Fonte: Os autores.

Principais Características da Engine MyISAM

• MyISAM tem o manipulador de colunas AUTO_INCREMENT mais

flexível dentre todos os mecanismos de armazenamento.


7/30

7

• Tabelas MyISAM podem ser convertidas de forma rápida,

comprimidas e definidas como somente leituras para salvar espaço

em disco. MyISAM bloqueia tabelas inteiras, não linha.

• É possível inserir novas linhas na tabela enquanto consultas

selecionadas estão rodando nela (inserções concorrentes).

• Certos modificadores de programação são permitidos para alterar

as prioridades de requisições, LOW_PRIORITY, HIGH_PRIORITY

e DELAYED. MySQL suporta checagem e reparação de tabelas

MyISAM.

• Os comandos usados para checar os erros de uma tabela e

consertá-los são CHECK TABLE mytable e REPAIR table.

Também é possível 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

operações complexas de busca.

A ENGINE INNODBInnoDB foi desenvolvido para processamento de transação,

principalmente para o processamento de muitas transações de vida curta. Seu

desempenho e recuperação de travamento automático também o tornam

popular para armazenamentos não-transacional.

O InnoDB utiliza arquivos para a armazenamento conhecidos comotablespace. Uma tablespace é gerenciado unicamente pelo InnoDB sozinho.

Para as altas concorrências ele usa MVCC (Controle de Concorrência de

versão múltipla) com os quatro níveis de isolamento padrão do SQL. Tabelas

InnoDB são construídas em um índice agrupado, as estruturas de índice do

InnoDB são muito diferentes das estruturas da maioria das outras ferramentas

de armazenamento do MySQL. Como um resultado, ele oferece buscas de

chave primária muito rápida.


8/30

8

Figura 4: Tabela InnoDB - Diretório

Principais Características da Engine InnoDB

• Cada tabela InnoDB é representada no disco por um arquivo .frm

no diretório principal do banco de dados, assim como os dados e

índices são armazenados no tablespace InnoDB. • O InnoDB tem suporte a transações, com commit e rollback. Isto

fornece atomicidade, consistência, nível de isolamento e

durabilidade dos dados. Múltiplo versionamento é usado para isolar

uma transação de outra.

• InnoDB fornece auto recuperação em caso de queda do servidor

ou do host em que o servidor esteja rodando.

• O MySQL gerencia querys de contenções para tabelas InooDB

usando múltiplos versionamentos em conjunto com o bloqueio de

linhas em nível. Assim, cada transação tem sua própria visão do

banco de dados e mantém o nível de contenção no mínimo. O

resultado é uma boa manipulação de concorrência, mesmo que um

cliente faça 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 portátil em

algumas situações, dessa forma o InnoDB pode ser copiado diretamente

para outro host e usando pelo servidor.

Considerações sobre as ferramentas:

• Se sua aplicação exigir transações, InnoDB é a escolha mais

comprovada, estável e bem integrada.


9/30

9

• MyISAM é uma boa escolha se uma tarefa não exigir transações 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 indexações Geoespacial e FULLTEXT

no MySQL.

• Somente InnoDB trabalha em conformidade com a ACID.

FRAGMENTAÇÃO DO MYSQLNo MySQL, quando você excluir registros de um espaço de tabela não é

remapeado automaticamente. O problema com isto é que se uma tabela de

executar muitas operações DELETE, o espaço físico da mesa será cada vez

mais fragmentado e reduzido desempenho.

Para a correção deste problema também usamos as ferramentas MyISAM

ou InnoDB, ambas ao executar o comando OPTIMIZE TABLE, fazem

uma desfragmentação automática da tabela corrigindo este problema.

É altamente recomendável usar este comando periodicamente,

especialmente em tabelas que são mais declarações de exclusão de registros.

Nas tabelas MyISAM o OPTIMIZE TABLE faz o seguinte:

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

Se as páginas de índice não estão organizadas, as organiza.

Se as estatísticas da tabela não estão atualizadas (e a reparação não

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 estatísticas do índice e liberar espaço não

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 fragmentaçãolevando em consideração o 'DATA_LENGTH' e o 'DATA_FREE', teoricamente

quanto maior o datafree mais fragmentada está a tabela, porém este comando


10/30

10

mostra também as tabelas InnoDB, onde o 'datafree' pode ter um valor maior efixo, isto não indica fragmentação.

Para verificar as engines das tabelas e também poder visualizar o

'DATA_FREE' você pode rodar o seguinte comando:SHOW TABLE STATUS;

Qualquer valor no campo 'DATA_FREE' das tabelas MyIsam é indício defragmentação.

CONSULTA DE DADOS E ÍNDICES Para realizar consultas no banco de dados MYSQL usamos o comando

SELECT com as declarações dos dados que buscamos, podemos tambémrefinar essa consulta através da inclusão de funções na declaração original.

A forma básica das consultas é:

“SELECT * FROM projetos”

Fonte: ELIAS PRACIANO OS TUTORIAIS DO GEEK de março/2014 . Disponível em:

. Acesso em: 11 mai. 2016.

Essa consulta retornará todas as colunas e linhas contidas na tabela.

Partindo desse princípio podemos ir refinando a consulta declarando os

campos que desejamos que sejam mostrados como a seguir:

“SELECT id_projeto, nome_projeto FROM projetos”

Figura 5 - Resultado do Select na tabela projetos – Maio/2016

https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/http://wiki.locaweb.com.br/pt-br/Arquivo:Right-arrow-32.png


11/30

11

Figura 6 - Resultado do Select na tabela projetos selecionando campos específicos – Maio/2016

Fonte: ELIAS PRACIANO OS TUTORIAIS DO GEEK de março/2014. Disponível em:


Logo essa consulta mostrará apenas duas colunas com seu dados, o

id_projeto e o nome_projeto. Utilizando a Cláusula condicional (WHERE) nasconsultas, obtemos um resultados mais objetivo ainda como veremos abaixo:

“SELECT id_projeto, nome_projeto FROM projetos WHERE nome_projeto =

“ Aylmer ”;

Figura 7 - Resultado do Select na tabela projetos com WHERE – Maio/2016



Logo obtemos todos os projetos cujo nome é Aylmer. Abaixo um exemplo

do uso do (LIKE) que recebe um termo parcial para a busca e retorna todos os

nome_projeto que iniciem com o termo passado para a consulta.

SELECT nome_projeto FROM projetos WHERE nome_projeto LIKE

'Mo%';

https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/


12/30

12



Existem outras funções que poderão ser introduzidas para adequar daforma mais clara as consultas a serem realizadas, e retornar os resultados

mais precisos possíveis juntamente com a lógica aplicada.

Continuaremos falando sobre consultas a seguir, porém, introduziremos o

conceito de índices no assunto.

No banco de dados MySQL os índices podem ser criados com

considerável facilidade, tanto no momento da concepção da tabela quanto em

uma tabela já existente.Primeiramente veremos como criar o índice junto com a tabela.

CREATE TABLE CLIENTES(

Codigo INT,Nome VARCHAR(50),INDEX (Codigo)

);

Caso já possua a tabela sem o índice, é possível introduzir posteriormenteatravés do comando CREATE:

CREATE INDEX idx_CLIENTES_CODIGO ON CLIENTES(Codigo);Ou ALTER TABLE tabela ADD INDEX(campo1,campo2,campo3);

Com índices no banco de dados posso aumentar a velocidade das minhas

consultas em 100 vezes e em alguns casos muito mais do que isso. Mas é

preciso ter cuidado ao criá-los, pois para cada inclusão ou atualização feita nobanco de dados o índice também precisa ser atualizado.

Figura 8 - Resultado do Select na tabela projetos com Where e Like – Maio/2016

https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/


13/30

13

Como boa prática é recomendada utilizar índices em campos como: data,

número e strings pequenos, pois para cada consulta, o MySQL seleciona o

melhor índice a partir de critérios por ele definidos.

METADADOS E DICIONÁRIOS DE DADOSQuando falamos de “metadados”, estamos falando sobre os dados sobre

outros dados, assim sendo, qualquer coisa que descreva os bancos de dados,

como o oposto de ser o conteúdo do banco de dados, é metadados. Assim

nomes de colunas, banco de dados, usuários, versões e a maioria dos

resultados strings de SHOW, são metadados.

No MySQL os metadados tratados estão disponíveis na

information_schema, o manuseio dos metadados é tarefa do DBA.

A information_schema é a estrutura de dicionário de dados utilizada pelo

MySQL, funcionando como um repositório central para metadados de qualquer

banco de dados.

Na information_schema podemos ter:

Todo schema de objetos.

Estatística do Servidor(Conexões, uso de CPU, Bytes enviados e

recebidos).

Privilégios.

Store procedure e Views.

Podemos acessar os information_schemas de duas formas, uma dela sé

através do comando SELECT. Exemplos abaixo:

SELECT * FROM information_schema.tables WHERE table_schema =

nome_da_database'

SELECT * FROM information_schema.columns WHERE table_name =

'nome_da_tabela'

SELECT * FROM information_schema.views

Ou através dos comandos show e desc, também podemos obter os

metadados de uma forma mais fácil no MySQL.

Algumas instruções SHOW suportadas no MySQL:


14/30

14

show databases: Lista o nome das bases disponíveis

show tables: Lista as tabelas do banco atual

show tables from nomedabase: Lista as tabelas do banco especificado

na consulta, sem precisar de um use. show columns from table_name ou desc table_name: Exibe a

estrutura da coluna para aquela tabela

show index from table_name: Exibe as informações sobre os indices e

a coluna indexada na tabela

show create table: Exibe o esqueleto da tabela, a forma da qual ela foi

criada.

Existe também o cliente mysqlshow, que pode ser utilizada no shell para

retornar a lista das bases de dados disponíveis. Exemplo:

Figura 9 - Resultado do comando mysqlshow – Maio/2016

Fonte: SQL PARA TODOS de agosto/2015. Disponível em:

. Acesso em: 11 mai.

2016.

REDUNDÂNCIA CONTROLADAO MySQL desenvolveu uma solução conhecida como MySQL Cluster,

que consiste em prover capacidade de fail-over automático com o intuito de

prover alta disponibilidade para o banco de dados. Vale destacar que o

termo fail-over significa que havendo problema em um dos componentes

do cluster , outro elemento o substituirá imediatamente, mantendo todo o

sistema em operação como se nada tivesse ocorrido.

https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/https://elias.praciano.com/2014/03/mysql-o-comando-select/


15/30

15

CONTROLE DE CONCORRÊNCIANo Mysql o controle de concorrência é feito em dois níveis: no nível do

servidor e no nível da ferramenta de armazenamento. Como vimos

anteriormente as ferramentas de armazenamento transacionais do MySQL,

como InnoDB, Falcon e PBXT, não usa um simples mecanismo de bloqueio de

linha. Em seu lugar, elas usam um bloqueio em nível de linha em conjunto com

uma técnica para aumentar concorrência conhecida como controle de

concorrência de versão múltipla (MVCC).

MVCC funciona mantendo um snapshot dos dados como eles existiam

em algum ponto no tempo. Isso significa que transações podem enxergar uma

visualização consistente dos dados, não importa quanto

tempo elas executem. Também significa que diferentes transações podem ver

dados diferentes nas mesmas tabelas ao mesmo tempo.

Cada ferramenta de armazenamento implementa MVCC de maneira diferente.

Temos o exemplo de uso do MVCC explicando o comportamento do InnoDB.

InnoDB implementa MVCC armazenando em cada linha dois valores

adicionais, escondidos, que registram quando a linha foi criada e quando ela

expirou (ou foi deletada). Em vez de armazenar os horários reais que estes

eventos ocorreram, a linha armazena o número da versão do sistema na horaem que o evento ocorreu. Este é um número que incrementa toda vez que uma

transação começa. Cada transação mantém seu próprio registro da versão do

sistema atual, na data em que ele iniciou. Toda consulta tem de checar os

números de versão de cada linha em relação à versão da transação.

SELECT

InnoDB deve examinar cada linha para garantir que ela se adapte a doiscritérios:

• InnoDB deve encontrar uma versão da linha que é no mínimo tão velha

quanto a transação (por exemplo, sua versão deve ser menor ou igual à versão

da transação). Isso garante que a linha existia antes da transação começar, ou

que a transação criou ou alterou a linha.

• A versão de apagamento da linha deve ser indefinida ou maior do que a

versão da transação. Isso garante que a linha não foi deletada antes datransação iniciar.


16/30

16

Linhas que passam nos dois testes podem ser retornadas como o resultado da

consulta.

INSERT

InnoDB registra o número da versão do sistema atual com a nova linha.

DELETE

InnoDB registra o número da versão do sistema atual como o ID de

apagamento da linha.

UPDATE

InnoDB escreve uma nova cópia da linha, usando o número da versão do

sistema para a versão da nova linha. Ele também escreve o número da versão

do sistema com a versão de apagamento da velha linha.

SEGURANÇA O MySQL implementa um sistema de autenticação bastante robusto que

é realizado em dois estágios. O primeiro verifica se o usuário pode conectar ao

banco de dados e o segundo verifica se o usuário tem privilégios para realizar

operações no banco de dados. O segundo estágio, portanto, é verificado a

cada operação realizada pelo usuário.

Este sistema de privilégios é armazenado usando a própria estrutura do

sistema em um banco de dados especial chamado mysql e deve ter o acesso

permitido apenas para o usuário root.

Para aceitar a conexão de um usuário, o MySQL não considera apenas o

próprio usuário, mas também a máquina de onde o usuário está conectando.Dessa forma, você pode permitir o acesso de um determinado usuário somente

de algumas máquinas específicas, bloqueando seu acesso de outros hosts que

podem não ser confiáveis.

Existem duas maneiras de conceder privilégios aos usuários: usando os

comandos GRANT e REVOKE, ou alterando diretamente as tabelas do banco

de dados mysql. A melhor escolha é usar os comandos GRANT/REVOKE, pois

o MySQL já altera as tabelas automaticamente, não sendo necessário entenderem detalhes o significado de cada tabela e suas respectivas colunas. Se você


17/30

17

alterar os privilégios manual- 3 Segurança com o MySQL mente além do risco

de manipular dados de forma errada, vocé pode se esquecer de executar o

comando FLUSH PRIVILEGES para tornar as alterações ativas. Veremos um

pouco mais no capitulo “Acesso Controlado”.

RESTRIÇÕES DE INTEGRIDADEO InnoDB implementa as restrições de

integridade CASCADE, RESTRICT, SET NULL e SET DEFAULT. No primeiro

caso, ao se remover um registro da tabela referenciada pela chave estrangeira

os registros relacionados àquele removido serão eliminados em todas as

tabelas relacionadas. ORESTRICT não permite a remoção de registros que

possuam relacionamentos em outras tabelas. Os dois últimos atribuem os

valores DEFAULT ou NULL para as chaves estrangeiras cujos registros

relacionados foram excluídos.

ACESSO CONTROLADOO MySQL possui um mecanismo que permite limitar o acesso de um

usuário a apenas um banco, tabela ou coluna, além de poder controlar o

acesso de acordo com o host a partir de onde está sendo feita a conexão com

o servidor. Pode-se ainda, conceder privilégios diferentes para cada host de

onde o usuário possa estabelecer a conexão.

O MySQL armazena as informações dos seus usuários em 4 tabelas que

estão localizadas no banco de dados mysql. Estas tabelas são

a user , db, tables_priv e columns_priv. A tabela user armazena as

informações de todos os usuários do banco e os privilégios globais deste

usuário. A tabela db armazena os privilégios dos usuários específicos de um

banco de dados. Finalmente, as tabelas tables_priv e columns_priv armazenam

os privilégios associados a tabelas e colunas, respectivamente. Como estas

tabelas possuem as informações dos usuários, bem como os seus privilégios,

recomenda-se que apenas o administrador do banco de dados tenha acesso

ao banco mysql (usuário root).


18/30

18

Para criar usuários e conceder privilégios no MySQL, utiliza-se o comando

GRANT. Ao executar um comando GRANT para um usuário que não existe, o

mesmo será criado. Exemplo abaixo:

GRANT priv [(colunas)] [, priv [(colunas)]] ...

ON {*.* | db.* | db.tabela}

TO usuario [IDENTIFIED BY 'senha']

[, usuario [IDENTIFIED BY 'senha']] ...

[WITH [GRANT OPTION |

MAX_QUERIES_PER_HOUR contador |

MAX_UPDATES_PER_HOUR contador |

MAX_CONNECTIONS_PER_HOUR contador]]

A lista de privilégios existentes no MySQL é descrita abaixo:

Privilégio Descrição

ALL [PRIVILEGES] Todos os privilégios exceto GRANT OPTION

ALTER Permite executar ALTER TABLE

CREATE Permite executar CREATE TABLE

CREATE TEMPORARY

TABLES

Permite executar CREATE TEMPORARY TABLES

DELETE Permite executar DELETE

DROP Permite executar DROP TABLE

EXECUTE Permite executar STORE PROCEDURES

FILE Permite executar SELECT... INTO OUTFILE E

LOAD DATA INFILE

INDEX Permite executar CREATE INDEX e DROPINDEX

INSERT Permite executar INSERT

LOCK TABLES Permite executar LOCK TABLES em tabelas que

tenha privilégio SELECT

PROCESS Permite executar SHOW FULL PROCESSLIST

REFERENCES Ainda não está implementado

RELOAD Permite executar o FLUSH

REPLICATION CLIENT Permite ao usuário obter a localização do


19/30

19

Master ou Slave

REPLICATION SLAVE Necessário para replicação Slave(leitura dos

eventos do log binário do Master)

SELECT Permite executar SELECT

SHOW DATABASES Exibe todos os bancos

SHUTDOWN Permite executar mysqladmin shutdown

SUPER Permite executar CHANGE MASTER, KILL,

PURGE MASTER LOGS E SET GLOBAL. Permite

conectar-se ao servidor uma vez, mesmo que o

max_connections tenha sido atingido

UPDATE Permite executar o UPDATE

USAGE Sinônimo para “NO Privileges”

GRANT OPTION Permite ao usuário repassar os seus privilégios

Uma vez informados os privilégios do usuário, você deverá indicar o nível

ao qual o privilégio se aplica, sendo possível especificar três níveis:

*.* Privilégio Global

Db.* Qualquer tabela no banco db

Db.tb Apenas a tabela tb do banco de dados db. Para especificar apenasuma determinada tabela, estas deverão ser listadas ao lado do

privilégio. (priv(colunas))

Depois do nível você deverá indicar o usuário, ou a lista de usuários, para

os quais os privilégios se aplicam (user@host).

Exemplos:

No exemplo a seguir é criado um usuário com o nome teste que pode seconectar somente do host onde o servidor está em execução (localhost), o

usuário só poderá fazer SELECT nas colunas nome e idade da tabela pessoa,

que se encontra no banco de dados rh. A senha do usuário é 12345.

mysql>GRANT SELECT (nome, idade) ON rh.pessoa TO teste@localhost

IDENTIFIED BY "12345";

Para listar os privilégios deste usuário utilize o comando:

mysql>SHOW GRANTS FOR teste@localhost;


20/30

20

O usuário remoto poderá executar UPDATE e INSERT em qualquer

tabela do banco de dados rh, sendo possível a conexão ao servidor a partir de

qualquer máquina utilizando IP:

mysql>GRANT UPDATE, INSERT ON rh.* TO

remoto@"200.236.13.%" IDENTIFIED BY "remoto";

Para remover um privilégio do usuário utilize o comando REVOKE

mostrado abaixo:

REVOKE priv [(colunas )] [, priv [(colunas )]] ...

ON {*.* | db.* | db.tabela }

FROM usuar io [, usuar io ] ...

REPLICAÇÃO (Total e Parcial)O objetivo de um mecanismo de replicação de dados é permitir a

manutenção de várias cópias idênticas de um mesmo dado em vários

servidores de bancos de dados (SGBD). Os principais benefícios da replicação

de dados são a redundância, o que torna o sistema tolerante a falhas, a

possibilidade de um balanceamento de carga do sistema, já que o acesso pode

ser distribuído entre as réplicas, e finalmente, ter-ser o backup online dos

dados, já que todas as replicas estariam sincronizadas. Este artigo, apresenta

uma introdução ao mecanismo de replicação do MySQL, bem como as

configurações básicas para realização desta tarefa.

Visão geral da replicaçãoO MySQL permite um tipo de replicação 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 binário de alteração todos os

comandos de atualizações da base de dados. Desta forma, todas as alterações

ocorridas no master são imediatamente replicadas para os outros servidores

slave.

A replicação no mysql é principalmente compatível com a anterior, isto é,

um servidor mais novo pode normalmente ser um escravo de um servidor mais

velho sem nenhum problema. Porém, versões mais antigas dos servidores são,

freqüentemente, incapazes de servir como slaves de versões mais novas, pois


21/30

21

eles não podem entender novas características ou a sintaxe SQL que o

servidor mais novo utiliza, e pode haver diferenças no formato dos arquivos

que a replicação usa, por exemplo, você não pode replicar de um master

MySQL 5.0 para um slave MySQL 4.0.

Com a replicação você possui uma série de vantagens como:

Equilíbrio de carga: a replicação, geralmente não 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.

Distribuição de dados: a replicação pode ajudar a distribuir as consultas

de leitura através de diversos servidores, ou seja, o mesmo dado pode está

armazenados em todos os servidores envolvidos da replicação.

Backup: a replicação é uma técnica valiosa para ajudar com backups,

porém um slave não é nem um backup nem um substituto para backups.

Alta disponibilidade: se um servidor falhar tem outro servidor para suprir

aquele servidor que falhou.

Como funciona a replicaçãoO MySQL realiza a replicação em um simples processo de três fases, a

Figura 1 ilustra a replicação com mais detalhes:



2016.

O master registra alterações aos seus dados no seu log binário (estes

registros são chamados de evento de log binário): antes de cada transação que

Figura 10 - Replicação em três fases – Maio/2016



22/30

22

atualiza dados no master, o mesmo registra as alterações no seu log binário,

ou seja, o mysql escreve as transações no seu log binário e logo em seguida o

master diz as ferramentas de armazenamento para comitar as transações.

O slave copia os eventos de log binário do master no seu relay log (log de

vigilância): o servidor slave, através de uma thread de I/O, abre uma conexão

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 binário 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.

CLUSTER Foi projetado para proporcionar alta disponibilidade e alto rendimento

com baixa latência, permitindo simultaneamente uma escalabilidade quase

linear. MySQL Cluster é implementado através do mecanismo de

armazenamento NDB ou NDBCLUSTER para MySQL ("NDB" Network

Database).

NDBNDB Cluster é o sistema de banco de dados distribuído subjacente

MySQL Cluster. Ele pode ser usado de forma independente de um servidor

MySQL com os usuários acessando o Cluster através 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 instância 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.


23/30

23

Ndb Cluster tem a seguinte capacidade independente de qualquer

servidor MySQL:

Persistente, armazenamento distribuído de tabelas com índices

exclusivos ou encomendados.

Redo logging, pontos de verificação, a recuperação do sistema.

Auto-sharding de tabelas em todos os nós de dados.

Hashing parcial ou total de chaves primárias.

Chave primária, chave única, varredura da tabela e verificação de índice

ordenado.

Execução da varredura paralelo com filtros "pushed-down '

Bloqueios de linha, atualizações transacionais.

Replicação de dados síncrona dentro do cluster, fechamento automático

+ recuperação

Fechamento de conexão automática para os processos de 'API' tipos de

dados SQL padrão, incluindo Blobs etc.O armazenamento de dados em disco e alteração de dados assíncrona,

mecanismo de gatilhos(triggers) operações DDL (embora DDL independente

não vai ser actualmente reconhecido por qualquer servidor MySQL) Adição de

Index Online , alteração de tabelas online

Adição de nós Online e reorganização de tabelas, backup on-line e atualização

on-line.

MySQL Cluster atualmente usa o servidor MySQL para fornecer as seguintes

capacidades em cima do Ndb Cluster:

SQL parsing / otimização / capacidade de execução.

Conectores para aplicações via JDBC, ODBC ...

Cross-tabela de junção mecanismo.

Autenticação e autorização.

Replicação assíncrona de dados para outros sistemas.


24/30

24

ArquiteturaMySQL Cluster é projetado em torno de uma arquitetura multi-mestre

distribuído, compatível com ACID. MySQL Cluster usa particionamento

automático para dimensionar operações de leitura e escrita e pode ser

acessada via SQL e não-SQL (NoSQL).

ReplicaçãoInternamente o MySQL Cluster usa replicação síncrona através de um

mecanismo de confirmação de duas fases, a fim de garantir que os dados

serão gravados em vários nós ao "commitar" os dados. (em contraste com o

que é usualmente referido como "replicação MySQL", que é assíncrona.) Duas

cópias (réplicas) conhecidas como as de dados são necessárias para garantir a

disponibilidade. MySQL Cluster cria automaticamente "grupos de nós" a partir

do número de réplicas e nós de dados especificados pelo usuário. As

atualizações são sincronicamente replicado entre os membros do grupo de nós

para proteger contra perda de dados e rápido suporte entre falhas nos nós.

Também é possível replicar de forma assíncrona entre agregados; esta é

muitas vezes referida como "Replicação MySQL Cluster" ou "replicação

geográfica". Isso normalmente é usado para replicar grupos entre os centros dedados para recuperação de desastres ou para reduzir os efeitos da latência da

rede, localizando os dados fisicos mais perto de um conjunto de usuários. Ao

contrário de replicação do MySQL padrão, a replicação geográfica do MySQL

Cluster usa controle de concorrência otimista(1).

(1) No controle de simultaneidade otimista, os usuários não bloqueiam os

dados quando os lêem. Quando um usuário atualiza os dados, o sistema

verifica se outro usuário alterou os dados depois de lidos. Se outro usuário tiveratualizado os dados, um erro é ativado

A partir do MySQL Cluster 7.2, suporte para a replicação síncrona entre os

centros de dados foi suportado com o recurso multi-site Clustering.

Particionamento de dados horizontalMySQL Cluster é implementado como um banco de dados multi-master

totalmente distribuído assegurando atualizações feitas por qualquer aplicação


25/30

25

ou nó SQL que estão disponíveis imediatamente para todos os outros nós que

acessam o cluster, e cada nó de dados pode aceitar operações de gravação.

Os dados dentro das tabela MySQL Cluster tabelas (NDB) é particionado

automaticamente em todos os nós de dados no sistema. Isso é feito com baseem um algoritmo de hash com base na chave primária na tabela, e é

transparente para a aplicação final. MySQL Cluster é capaz de suportar

consultas entre fragmentos e transações.

Os usuários podem definir seus próprios esquemas de particionamento. Isso

permite aos desenvolvedores adicionar "consciência de distribuição" para

aplicações por partição com base em uma sub-chave que é comum a todas as

linhas que está sendo acessado por meio de operações de funcionamento

elevados. Isso garante que os dados utilizados para completar as transacções

é localizada sobre o mesmo fragmento, reduzindo assim picos na rede.

Hybrid StorageMySQL Cluster mantém todas as colunas indexadas em memória distribuída.

colunas não indexadas também pode ser mantido na memória distribuída ou

pode ser mantido no disco com um cache de página na memória. Armazenar

colunas não indexadas no disco permite MySQL Cluster armazenar conjuntos

de dados maiores do que a memória total de máquinas em cluster.

MySQL Cluster escreve redo logs em disco para todas as alterações de dados,

bem como dados de verificação apontando para o disco regularmente. Isso

permite o cluster recuperar os dados de forma consistente a partir do disco

após uma queda completa de cluster. À medida que os redo logs são escritos

de forma assíncrona em relação à transação de confirmação, um pequeno

número de transações podem ser perdidos se o cluster falhar por completo, no

entanto, isso pode ser atenuado pelo uso de replicação geográfica ou cluster

multi-site discutido acima. Geralmente o atraso de gravação assíncrona padrão

é 2 segundos, e é configurável.

Quando uma tabela MySQL Cluster é mantida na memória, o cluster só irá

acessar armazenamento em disco para escrever registros de Redo e

checkpoints. Como essas gravações são sequenciais e acesso aleatóriolimitados estão envolvidos, MySQL Cluster pode alcançar taxas de


26/30

26

transferência de gravação mais elevadas com hardware disco limitado em

comparação com um tradicional caching RDBMS baseada em disco. Este

ponto de verificação no disco de dados da tabela em memória pode ser

desativado (em uma base por tabela) se a persistência baseada em disco não

é necessário.

APIs SQL e NoSQLComo o MySQL Cluster armazena tabelas em nós de dados, em vez de

MySQL Server, existem várias interfaces disponíveis para acessar o banco de

dados:

Acesso SQL através do servidor MySQL.

APIs NoSQL onde as bibliotecas Cluster MySQL podem ser incorporadas em

um aplicativo para fornecer acesso direto aos nós de dados, sem passar por

uma camada de SQL. Esses incluem:

memcached

Node.js / JavaScript

Java e JPA

HTTP / REST

NDB API (C ++)

ImplementaçãoMySQL Cluster usa três tipos diferentes de nós (processos):

Nó de dados (Data Node) (ndb d / processo ndbmtd ): Esses nós armazenar

os dados. As tabelas são automaticamente compartilhadas entre os nós dedados que também tratam de forma transparente o balanceamento de carga,

replicação, failover e auto-cura(SELF-HEALT).

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

para configuração e monitoramento do cluster. Eles são necessários apenas

para iniciar ou reiniciar um nó de cluster. Eles também podem ser configurados

como árbitros, mas isto não é obrigatório (Servidores MySQL pode ser

configurado como árbitros em vez disso).


27/30

27

nó do apl icat ivo ou nó SQL (App l icat ion node or Sql Node) (processo

mysqld) : Um servidor MySQL (mysqld) que se conecta a todos os nós de

dados a fim de realizar o armazenamento e recuperação de dados. Este tipo de

nó é opcional; é possível consultar os nós de dados diretamente através 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 físico

separado, VM ou instância de nuvem (embora seja muito comum a co-localizar

nós de gerenciamento com servidores MySQL). Para as melhores práticas,

recomenda-se não co-localizar nós dentro do mesmo grupo de nós em um

único host físico (como que representaria um único ponto de falha).

Arquitetura de 3 Camadas

Nível Externo: Como os dados são vistos por cada usuário individualmente

• Nível Interno: Como os dados são fisicamente armazenados (númerode bytes de cada campo, etc.);

• Nível Conceitual: intermediário entre os dois.

Primeiro modelo de replicação



2016.




28/30

28

Segundo Modelo de replicação



2016.

CONSIDERAÇÕES FINAIS Com o desenvolvimento deste trabalho conhecemos mais a fundo o

SGBD do MYSQL, sua arquitetura, sua utilização e benefícios. Pudemos

conhecer como trabalha o banco em seu back-end. Vimos um pouco do

trabalho do DBA, suas responsabilidades sobre o banco. Conhecemos tambémas engines de administração do banco MYSQL (MYSAM e INNOB), vimos suas

características, suas vantagens e desvantagens.

Assim consideramos que o trabalho sobre Sistema de Gerenciamento de

banco de dados do MYSQL nos trouxe uma bagagem de informações e

conhecimento, acrescentando em nosso perfil profissional.




29/30

29

REFERÊNCIAS

DEVMEDIA. Visão Geral do Sistema de Gerenciamento de Banco de Dados MySQL. Disponívelem http://www.devmedia.com.br/visao-geral-do-sistema-de-gerenciamento-de-banco-de-dados-mysql/2981;. Acesso em: 09 mai. 2016.

PRACIANO, ELIAS. Mysql: O comando SELECT. Disponível emhttps://elias.praciano.com/2014/03/mysql-o-comando-select/; Acesso em: 09 mai. 2016.

MYSQL. 14.2.9 SELECT Syntax. Disponível emhttp://dev.mysql.com/doc/refman/5.7/en/select.html; Acesso em: 09 mai. 2016.

FERREIRA, THIAGO. Otimização de consultas MySQL. Disponível emhttp://imasters.com.br/artigo/14624/mysql/otimizacao-de-consultas-mysql-parte-01/?trace=1519021197&source=single;. Acesso em: 09 mai. 2016.

WEBGOAL. Como otimizar consultas no MySQL Análise das queries. Disponível emhttp://www.webgoal.com.br/como-otimizar-consultas-no-mysql/; Acesso em: 09 mai. 2016.

NUNES, ANDERSON. ÍNDICES MYSQL – AUMENTANDO A VELOCIDADE DAS SUAS CONSULTASEM ATÉ 100X. Disponível em https://www.profissionaisti.com.br/2011/09/indices-mysql-aumentando-a-velocidade-das-suas-consultas-em-ate-100x/; Acesso em: 09 mai. 2016.

MYSQL. 9.6. UTF8 para Metdados. Disponível emhttp://download.nust.na/pub6/mysql/doc/refman/4.1/pt/charset-metadata.html; Acesso em:09 mai. 2016.

WILSON, JOSÉ. Metadados Information_schema –

MySQL. Disponível emhttp://sqlparatodos.com.br/metadados-information_schema-mysql/; Acesso em: 09 mai.

2016.

DUARTE, EBER. Gerenciamento de Usuários e Controle de Acessos do MySQL. Disponível em

http://www.devmedia.com.br/gerenciamento-de-usuarios-e-controle-de-acessos-do-

mysql/1898; Acesso em: 05 mai. 2016.

DUARTE, EBER. Gerenciamento de usuários, privilégios e controle de acessos em MySQL.

Disponível em http://www.devmedia.com.br/gerenciamento-de-usuarios-privilegios-e-

controle-de-acessos-em-mysql/7077; Acesso em: 05 mai. 2016.

Linden , Marx Gomes Van der . MySQL: Controle de Acesso. Disponível em

http://docplayer.com.br/10947164-Mysql-controle-de-acesso.html; Acesso em: 05 mai. 2016.

DUARTE, EBER. Implementando Integridade Referencial no MySQL. Disponível em

http://www.devmedia.com.br/implementando-integridade-referencial-no-mysql/1996; Acesso

em: 09 mai. 2016.

https://elias.praciano.com/2014/03/mysql-o-comando-select/http://dev.mysql.com/doc/refman/5.7/en/select.htmlhttp://sqlparatodos.com.br/metadados-information_schema-mysql/http://www.devmedia.com.br/implementando-integridade-referencial-no-mysql/1996http://www.devmedia.com.br/implementando-integridade-referencial-no-mysql/1996http://sqlparatodos.com.br/metadados-information_schema-mysql/http://dev.mysql.com/doc/refman/5.7/en/select.htmlhttps://elias.praciano.com/2014/03/mysql-o-comando-select/


30/30

ATAIDES, ANDERSON PEREIRA. Segurança com o MySQL. Disponível em

http://andersonataides.tripod.com/artigos/mysqlseguro.pdf; Acesso em: 09 mai. 2016.

WEBMASTER. MySQL – Integridade Referencial. Disponível em

http://www.webmaster.pt/mysql-integridade-referencial-3598.html; Acesso em: 09 mai. 2016.

BREM, MATHIAS. Segurança no MySQL. Disponível em

http://imasters.com.br/infra/seguranca/seguranca-no-mysql-vulnerabilidades-ou-pontos-

fortes/?trace=1519021197; Acesso em: 09 mai. 2016.

LUCAS, ANDREI. MySQL: Replicação de Dados. Disponível em

http://www.devmedia.com.br/mysql-replicacao-de-dados/22923; Acesso em: 09 mai. 2016.

WIKIPEDIA. MySQL Cluster. Disponível em https://en.wikipedia.org/wiki/MySQL_Cluster;

Acesso em: 09 mai. 2016.

WIKIPEDIA. NDB Cluster . Disponível em https://en.wikipedia.org/wiki/NDB_Cluster ;

Acesso em: 09 mai. 2016.
https://en.wikipedia.org/wiki/NDB_Clusterhttps://en.wikipedia.org/wiki/NDB_Clusterhttps://en.wikipedia.org/wiki/NDB_Clusterhttps://en.wikipedia.org/wiki/NDB_Cluster

Documents

SGBD_Atualizado