My SQL

Introdução ao Banco de Dados

MySQL Fernando Lozano www.lozano.eti.br

fernando@lozano.eti.br Consultor Independente Prof. Faculdades UniABEU Prof. Universidade Estácio de Sá

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 1

Sobre o Autor ●

●

●

●

●

●

Red Hat Certified Engineer LPI Certified Professional Level I Sair GNU/Linux Certified Professional IBM Certified Network Engineer IBM Certified DB2 Administrator & Developer Microsoft Certified Systems Engineer Microsoft Certified Solutions Developer Conselheiro do LPI Brasil Webmaster da Free Software Foundation Autor do Livro "Java em GNU/Linux" ed. Alta Books, 2002,

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 2

Objetivos e Pré-requisitos ●

●

Demonstrar o básico necessário para utilizar o MySQL no desenvolvimento de um aplicativo Espera-se do aluno: ●

Conhecimento de alguma linguagem de programação

●

Conhecimentos de SQL

●

Conceitos de bancos de dados cliente/servidor

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 3

Agenda ●

Por que Bancos de Dados Livres

●

Por que MySQL

●

Histórico e Características do MySQL

●

Quando não usar o MySQL

●

Instalação do MySQL em Linux

●

Administração do MySQL

●

Desenvolvendo para o MySQL em Linux

●

Instalação do MySQL em Windows

●

Desenvolvendo para o MySQL em Windows

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 4

Por que Bancos de Dados Livres?

"Bancos de dados se tornaram parte integrante da infraestrutura tecnológica de qualquer empresa, por isso devem ser abertos" SAP AG, http://www.sapdb.org

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 5

Software Livre e a InfraEstrutura de TI ●

Softwares livres já são dominantes em diversos nichos e participantes importantes na maioria dos serviços de rede, tais como: ●

Web servers, portais

●

Firewall, Proxy caches, VPN

●

Arquivos e Impressão

●

E-mail, fax

●

O IDC estima que em 2004 o Linux será o NOS mais utilizado nas empresas

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 6

Bancos de Dados Livres ●

Será que os bancos de dados são o próximo nicho a ser ocupado pelos bancos livres? ●

São tão confiáveis quanto os bancos proprietários?

●

São tão rápidos quanto?

●

Há serviços de suporte e consultoria?

●

●

Funcionam com as ferramenas de desenvolvimento legadas (Visual Basic, Delphi, PowerBuilder) ou apenas com as ferramentas da era Web (Java, PHP, Perl) ?

Sim, Sim, Sim e Sim!

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 7

Bancos Proprietários e o Software Livre ●

Oracle em Linux é 40% mais rápido do que em Windows, segundo a própria Oracle

●

DB2 vem com interface DBI para Perl

●

Oracle AAS 9i é baseado no Apache e no Jserv

●

IBM Websphere é baseado no Apache e no Tomcat

●

Sybase, OpenIngres, Informix... todos os bancos importantes tem versões “tier1” para Linux, com uma única exceção (advinhem qual!)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 8

Bancos Livres x Proprietários ●

Os bancos livres em geral suportam: ●

Otimização de consultas

●

Histogramas sobre colunas

●

Comandos SQL sofisticados

●

Grants

●

Campos binários longos

●

●

Grandes volume de dados, usuários e transações (com ajuda de recursos de SMP, RAID e Journaling) Replicação

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 9

Bancos Livres x Proprietários ●

Os bancos livres ainda não suportam: ● ●

Organização física em Tablespaces Clustering (não com discos compartilhados para HA e failover, mas sim com heartbeat e replicação)

●

Transações distribuídas

●

Consultas paralelas

●

Particionamento

●

Views Materializadas

●

Bancos Multidimensionais / Aplicações OLAP

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 10

Por que MySQL ●

É o banco de dados livres mais popular

●

É um projeto antigo e maduro

●

Download relativamente pequeno, viável para quem utiliza modem de 28.8Kbps

●

Não tem uma curva de aprendizado muito íngreme

●

Amplo suporte de ferramentas livres:

PHP, Perl, Python, Glade / Gnome-DB, ... ●

Amplo suporte de ferramentas proprietárias

Kylix, Java, Delphi, Visual Basic, ColdFusion, ER-Win, ...

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 11

Sobre o MySQL ●

●

Surgiu à partir de um formato de arquivos ISAM desenvolvido pela Datakonsult AB Implementava o mesmo protocolo de rede e APIs do mSQL (miniSQL), um banco de dados freeware

●

A versão Windows era shareware

●

Há dois anos foi re-licenciado sob a GNU GPL

●

A empresa mudou de nome para MySQL AB

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 12

Características do MySQL ●

●

●

Foco em facilidade de administração e baixo consumo de recursos do hardware Se tornou popular graças à Internet, pois os bancos tradicionais tinham tempos de conexão extremamente elevados, inadequados para aplicações CGI Para atingir seus objetivos, não implementava funções de overhead elevado, como integridade referencial, commit, rollback e níveis de isolamento de transação

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 13

Características do MySQL ●

●

●

●

Servidor multithreaded: um único processo mysqld atende a todos os clientes Tabelas e índices correspondem à arquivos físicos e bancos de dados correspondem à diretórios Distribuição de dados em múltiplos discos deve ser feita manualmente, por meio de links simbólicos Segurança via SSL (4.x)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 14

O MySQL Hoje e Amanhã ●

Hoje (versões 3.23.x e 4.0.x): ●

● ●

●

●

Mecanismos de armazenamento (tipos de tabela) com suporte à transações Recursos SQL mais sofisticados, como agregados Ainda não implementa alguns recursos básicos como views e foreign keys Full text search (4.x)

Próximas versões (4.1, 4,2, ... 5.x, ...): ●

Maior compatibilidade com o padrão ANSI

●

Views e procedimentos armazenados

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 15

Plataformas Suportadas pelo MySQL ●

Servidor ●

●

●

Sistemas Unix-Like: GNU/Linux, FreeBSD, Solaris, AIX, HP-UX, ...

Win32: Windows 95/98/ME, Windows NT/2000/XP/NET

●

OS/2 (Em desenvolvimento para BeOS e LynxOS)

●

MacOS X e Netware

Cliente: ●

Drivers ODBC (3.0) e dbExpress para Unix e Windows

●

Várias ofertas de drivers JDBC 2.0 100% Java

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 16

Quando Usar o MySQL ●

●

●

●

Back-end para geração de conteúdo de web sites Aplicações envolvendo basicamente consultas e inserção de dados Sistemas altamente replicados, onde o custo de licenças de banco se torna proibitivo Sistemas com ampla variação de demanda, pois o MySQL (ao contrário de outros bancos "light") foi validado para grandes volumes de dados ou de usuários

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 17

Quando Não Usar o MySQL ●

●

●

●

Aplicações com fortes demandas transacionais, especialmente se houverem muitas atualizações concorrentes!

Aplicações que necessitem de recursos sofisticados do SQL, como agrupamentos e agregados para geração de relatórios Aplicações OLAP Empresas como o Yahoo Finance combinam o MySQL (aplicações web) com um outro banco de dados (retaguarda financeira)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 18

Instalação do MySQL em Linux ●

●

●

Três formas de instalar o MySQL ●

À partir dos fontes (não recomendado)

●

À partir dos binários fornecidos em mysql.com

●

À partir dos pacotes RPM fornecidos em mysql.com

Os binários e pacotes RPM estão prontos para execução, sem necessidade de configuração prévia Recomenda-se criar um usuário "mysql" para o banco em vez de rodar como root

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 19

Pacotes RPM do MySQL ●

MySQL

Servidor básico, sem transações ●

MySQL-client

Utilitários de cliente ●

MySQL-devel

Headers e bibliotecas estáticas para linguagem C ●

MySQL-shared

bibliotecas dinâmicas para aplicações cliente ●

MySQL-max

Servidor completo, com transações © 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 20

Iniciando o MySQL ●

●

●

●

Basta invocar o comando mysqld ou então utilizar o script instalado em /etc/rc.d/init.d No Red Hat: service mysqld start Início automático no boot do sistema: chkconfig on mysqld Testando a disponibilidade do servidor: mysqladmin version

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 21

Encerrando o MySQL ●

●

●

Três opções ●

mysqladmin shutdown

●

/etc/rc.d/init.d/mysqld stop

●

service mysqld stop (Red Hat)

Ele será desligado corretamente durante o shutdown do sistema se foi configurado para início no boot com o comando chkconfig Não utilize o comando kill para encerrar o MySQL, pois pode acarretar perda de dados!

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 22

Criação e Destruição de Bds ●

mysqladmin create nome-do-banco

●

mysqladmin drop nome-do-banco

●

mysqlshow lista os bancos existentes

●

Também é possível copiar (ou remover) o diretório do banco de um servidor para o outro, com o servidor inativo (/var/lib/mysql/nome-do-banco)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 23

Acessando um Banco no MySQL ●

●

●

Utilize o utilitário mysql, que é similar ao isql do Sybase ou ao sqlplus do Oracle Encerre os comandos SQL com um ponto-e-vírgula ou \g Retorne ao prompt do shell com um [Ctrl+D] ou \q

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 24

Exemplo de Uso do MySQL ●

[root@laptop root]# mysql banco Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9 to server version: 3.23.49 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table teste ( i integer primary key, s varchar (20) ); Query OK, 0 rows affected (0.00 sec) mysql> insert into teste values (1, 'primeiro'); Query OK, 1 row affected (0.00 sec) mysql> insert into teste values (2, 'segundo'); Query OK, 1 row affected (0.00 sec)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 25

Exemplo de Uso do MySQL ●

mysql> select * from teste; +---+----------+ | i | s | +---+----------+ | 1 | primeiro | | 2 | segundo | +---+----------+

2 rows in set (0.00 sec) mysql>\q Bye [root@laptop root]#

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 26

Mude a senha do root! ●

●

●

A instalação padrão do MySQL inclui um usuário "root" sem senha, e um usuário anônimo local (@localhost) também sem senha Qualquer usuário local pode especificar o nome de usuário "root" e criar ou remover bancos de dados set password for root@localhost = password ('senha');

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 27

Opções do Cliente MySQL ●

Os utilitários de linha de comando do MySQL aceitam as seguintes opções ●

-u login

nome do usuário ●

-p

pede a digitação da senha ●

-h host

especifica o servidor MySQL em outro computador (desde que o firewall não bloqueie o acesso)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 28

Segurança no MySQL ●

●

●

Ao contrário de outros bancos, onde são associados usuários (login) a senhas, no MySQL são associados também a um nome de host ou endereço IP Ou seja, o mesmo login do banco pode ter senhas diferentes, de acordo com o host de origem Também é possível especificar restrições de acesso por banco, tabela e mesmo coluna

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 29

Criando um Usuário no MySQL ●

[root@laptop root]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12 to server version: 3.23.49 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> grant all on banco.* to teste@localhost identified by 'senha'; Query OK, 0 rows affected (0.05 sec)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 30

Utilizando o Usuário RecémCriado ●

[lozano@laptop lozano]$ mysql -u teste -p banco Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15 to server version: 3.23.49 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from teste; +---+----------+ | i | s | +---+----------+ | 1 | primeiro | | 2 | segundo | +---+----------+

2 rows in set (0.00 sec) © 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 31

Acesso Remoto ao MySQL ●

●

Normalmente o MySQL é acessado por aplicações web, sendo que o web server está na mesma máquina. Assim sendo basta criar o usuário @localhost Aplicações cliente/servidor tradicionais (ODBC, dbExpress, JDBC, DBI) irão acessar o banco em outra máquina. Pode ser definido um host máscara ou especificar o usuário host a host.

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 32

Criando um Usuário Remoto no MySQL ●

[root@laptop root]# mysql -p Enter password: ... mensagens suprimidas ... mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> grant all on banco.* to teste@'%' identified by 'remoto'; Query OK, 0 rows affected (0.05 sec)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 33

Utilizando o Usuário Remoto ●

[lozano@laptop lozano]$ mysql -h servidor -u teste -p banco Enter password: ... mensagens suprimidas ...

Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from teste; +---+----------+ | i | s | +---+----------+ | 1 | primeiro | | 2 | segundo | +---+----------+

2 rows in set (0.00 sec)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 34

Scripts SQL ●

●

Em geral inserimos vários comandos DDL (create table, create index) em um script SQL que pode ser executado diretamente pelo mysql redirecionando a entrada padrão: mysql -u teste -p banco < cria_agenda.sql

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 35

Backup do MySQL ●

Pode ser feito de duas formas: ●

●

Cópia do diretório do banco de dados, com o servidor desativado (apenas entre a mesma versão, na mesma plataforma) Utilitário mysqldump, que gera um script SQL para recriação do banco ou das tabelas selecionadas

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 36

Desenvolvendo Para o MySQL em Linux ●

●

●

Distribuições binárias do PHP costumam vir préconfiguradas com um cliente MySQL Pode-se baixar e compilar o DBI e DBD-MySQL para Perl (ou usar os pacotes da sua distrbuição preferida do Linux) Em geral, aplicações em C ou Pascal, além dos drivers Perl e Python, exigem recomplicação para a versão do cliente MySQL instalada

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 37

A API de Cliente do MySQL ●

mysql_connect

Conecta a um servidor ●

mysql_selectdb

Seleciona um banco de dados no servidor ●

mysql_query

Executa um comando SQL qualquer ●

mysql_fetch

Retorna uma linha de resultado ●

mysql_freeresult / mysql_close

liberam os recursos ocupados pelo cliente © 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 38

Outras APIs ●

●

Quando existe uma API independente de banco, recomenda-se o uso desta API com o driver específico, em vez da API de cliente do MySQL API do MySQL

PHP, C/C++, Pascal ●

API independente de banco

Java (JDBC), PHP (Pear), Perl (DBI), Python (PyDB), Kylix (dbExpress)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 39

Exemplos ●

Agenda de Contatos ●

Versão em PHP (API do MySQL)

●

Versão em PHP (PEAR)

●

Versão em Pascal

●

Versão em Java

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 40

Instalando o MySQL em Windows ●

●

●

Um único arquivo auto-instalador (estilo InstallShield) fornece o servidor, cliente e bibliotecas para desenvolvimento É fornecido um utilitário gráfico para início e término do servidor Ou então podemos utilizar a linha de comando do mesmo modo que no Linux (mysqld, mysqladmin, mysqlm, mysqldump)

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 41

Desenvolvendo para o MySQL em Windows ●

●

●

●

Linguagens como PHP, Perl, Java, C e Pascal funcionam da mesma forma nos dois ambientes Java não necessitam do cliente MySQL instalado; as demais exigem recompilação O Delphi fornece um driver dbExpress para o MySQL Pode ser baixado e instalado separadamente um driver ODBC para Visual Basic, Excel, etc

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 42

Exemplos ●

Agenda de Contatos ●

●

PHP, Java e Pascal (iguais aos exemplos no Linux)

●

Access (tabelas vinculadas via ODBC)

●

Open Office (via ODBC)

O uso do driver ODBC exige a definição de chaves primárias nas tabelas, caso contrário os Datasets não serão atualizáveis

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 43

Referências ●

MySQL www.mysql.com

●

Freshmeat

www.freshmeat.net ●

Sourceforge www.sourceforge.net

●

Porque software livre? www.dwheeler.com/oss_fs_why.html

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 44

Perguntas ●

●

Dúvidas: fernando@lozano.eti.br Palestra e atualizações: www.lozano.eti.br

© 2003 Fernando Lozano, http://www.lozano.eti.br

Introdução ao Banco de Dados MySQL, Pag. 45