ScriptFácil.com - Sua fonte de scripts
mysql
17/08/2004 - Por Otavio James Bernardes Junior Tutorial sobre o novo MySQL 4 Nota dos usuários: Visualizações: 2980 visualizações.
Eis a notícia: Sai como estável a versão 4.0 do banco de dados MySQL , agora adotando o handler InnoDB (http://www.innodb.com) como padrão na distribuição, possibilitando assim que o mesmo possa ter Integridade referencial (Incluindo CASCADE DELETE/UPDATE) e transação já de fábrica ;). Disponível tambem para download a versão 4.1 do MySQL (oficialmente anunciada como beta) que traz 'derived tables', 'sub-querys' e suporte até 64GB de RAM na arquitetura I386.
MySQL - Como utilizar todo o poder da versão 4.x
O MySQL é um banco de dados conhecido e utilizado pelo mundo todo, agora em sua versão 4.x vem agregar alguns recursos que não eram disponíveis em sua versão estável (3.23) e que são de extrema importância, como transações , cache de querys mais utilizadas , integridade referencial e muito mais. Neste tutorial eu pretendo explicar como funciona e como habilitar transações e integridade referencial.
O MySQL trabalha de uma maneira diferente de outros bancos de dados , ele possue "Tipo de tabela" ou "Table handler" que é onde se esconde os segredos , para utilizar o total poder do MySQL 4.x voce deverá utilizar o "Tipo de tabela" InnoDB.
O InnoDB oferece suporte a transação e a integridade referencial (com CASCADE REFERS) bem como suporte a "crash recovery" no caso de uma queda de luz ou falha fisica, a maneira de gravação e manipulação dos dados é semelhante ao Oracle , ele trabalha com um tablespace ou seja , um tamanho pre-defenido (que pode ser alterado a qualquer momento) de espaco em disco onde vão ser armazenados os dados e indices de suas tabelas, também possue um sistema transacional baseado em logs onde se por qualquer motivo o banco de dados for "interrompido" o mesmo recupera o ultimo estado íntegro do banco.
O tablespace pode ser definido por um arquivo, ou uma partição diretamente , caso voce queira maior performance a partição é a melhor escolha pois o MySQL não vai depender do sistema de arquivos para agilizar a procura e gravação. A versão 4.x traz consigo suporte a UNION e também a MULTI-TABLE DELETE. Talvez uma das maiores implementações da versão 4.x seja o "Query Cache" que armazena o resultado de querys repetitivas, para portais no estilo do PHPNuke e PostNUKE isso é muito comum tendo em vista que quase nada se altera, e a informação é sempre a mesma, o "Query Cache" pode ser utilizado automaticamente ou somente naquelas querys que o programador decidir.
Outra facilidade da versão 4.x é o "Embedded MySQL Server Library" , que nada mais é do que o MySQL linkado diretamente com a sua aplicação , ou seja... nao precisa instalar o banco de dados , ele vai dentro da sua aplicação. Ainda em fase beta , o "Embedded MySQL Server Library"* se demonstra estáel e funcional.
A transação proporcionada pelo subsistema InnoDB do MySQL 4.x é funcional desde casos pequenos a grandes transações onde muitos registros são alterados, com o suporte a integridade referencial bem maduro a dupla MySQL+InnoDB tem conquistado muitos fans adeptos de tal funcionalidade. O funcionamento é bastante simples, toda vez que o mysql inicia ele tambem inicia o InnoDB , o mesmo entra no ar fazendo uma verificação de integridade do banco de dados. Abaixo vemos uma situação que o "servidor foi desligado da tomada" e o InnoDB ao retornar fez as alterações necessárias para que o banco voltasse a posição que ele estava antes de sair repentinamente:
021111 11:53:08 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 8 613915027
InnoDB: Doing recovery: scanned up to log sequence number 8 613945266
021111 11:53:08 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
021111 11:53:09 InnoDB: Flushing modified pages from the buffer pool...
021111 11:53:09 InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections
Como visto acima , o InnoDB detectou que algo teria acontecido de errado , verificou seus logs , constatou que o banco de dados estava em situação "irregular" , voltou as transações em aberto e iniciou normalmente. Esta funcionalidade de "crash recovery" faltava no mysql para consolidar ele como um banco de dados confiavel para operações de risco como por ex: transações financeiras , sistemas de controle de caixa, etc...
As aplicações que hoje funcionam com MyISAM ( Tipo de tabela oficial do MySQL ) podem ser convertidos para utilizar InnoDB com um simples "ALTER TABLE nome_da_tabela Type = InnoDB;" , mas para isso voce precisa habilitar o InnoDB no seu MySQL , siga os passos a seguir:
Vamos pelo inicio de tudo , baixe a versão 4.x no site http://www.mysql.com/downloads/mysql-pro-4.0.html , no site de download voce tera uma infinidade de plataformas , desde Linux até Windows , baixe e instale. Após a instalação edite o seu arquivo my.cfg e descomente as seguintes linhas:
innodb_data_file_path = ibdata1:300M
Este parâmetro serve para definir o tamanho do tablespace que o seu banco de dados vai utilizar , no caso trabalharemos com arquivos pelo fato de ser mais facil de explicar. O arquivo deve conter a seguinte sintaxe: nomedoarquivo:TAMANHO , no caso estamos criando um arquivo chamado ibdata1 que vai armazenar em si 300M para tabelaspace, no caso de precisar ampliar o tamanho do seu tablespace voce deve separar os arquivos por ; ex: ibdata1:300M;ibdata2:2G .
innodb_data_home_dir = c:\mysql\data
Este parâmetro serve para definir aonde vão ficar armazenados os arquivos que vão formar o tablespace , neste caso vamos armazenar dentro do diretório data do mysql
innodb_log_group_home_dir = c:\mysql\data\
innodb_log_arch_dir = c:\mysql\data\
Este parâmetro serve para definir aonde vão ficar armazenados os arquivos de logs que serão gerados para o sistema de transação.
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
Estes dois parâmetros definem se os logs do InnoDB vão ser duplicados e quantos arquivos de logs ele vai gerar respectivamente. Normalmente são valores padrão e não devem ser alterados
set-variable = innodb_log_file_size=30M
Este parâmetro define o tamanho do arquivo de LOG, este arquivo vai ser utilizado para realizar as transações do banco de dados , normalmente o tamanho dele é de 30M , se voce tiver transações mais robustas , aumente este valor.
set-variable = innodb_log_buffer_size=8M
Este parâmetro indica a quantidade de BUFFER DE MEMORIA que o InnoDB vai utilizar para gravar suas transações antes de recorrer aos arquivos fisicos, este valor varia de 1M até 8M , normalmente eu sugiro deixar em 8M , pois quanto menor for a dependencia do disco maior vai ser a velocidade.
innodb_flush_log_at_trx_commit=2
innodb_log_archive=0
Este dois parâmetros definem a maneira de gravação da transação no banco de dados.Normalmente são valores padrão e não devem ser alterados.
set-variable = innodb_buffer_pool_size=80M
Este parâmetro é um dos mais importantes , ele diz ao InnoDB o quanto ele pode utilizar de memoria para guardar indices , referencias e fazer cache de tabelas , quanto maior este valor melhor. Mas atenção, este valor nunca deve ultrapassar 80% da memoria fisica do computador , isso ocasiona lentidão extrema.
set-variable = innodb_additional_mem_pool_size=2M
Este parâmetro define o quanto o InnoDB vai poder utilizar de memoria para a estrutura interna do sistema , normalmente um valor bom é 2M , se caso o InnoDB detectar que precisa de mais memoria ele vai avisar pelo do log MySQL e alocara automaticamente.
set-variable = innodb_file_io_threads=4
Este parâmetro define quantas threads o InnoDB vai utilizar para ler/gravar simultaneamente, normalmente um valor muito bom é 4 , se vc estiver utilizando o MySQL para Windows incremente este valor para 9.
set-variable = innodb_lock_wait_timeout=50
Este parâmetro define o quanto o InnoDB vai esperar antes de voltar uma transação automaticamente, ex: Se vc iniciar uma transacao , alterar uma tabela e deixar parado e outra conexao tenta alterar o mesmo dado , o innodb espera 50 segundos a primeira transação se concretizar , se isso não ocorrer o mesmo volta a transação corrente para o primeiro estado. O valor de 50 segundos normalmente é suficiente.
Pronto , apos setar estes valores é só iniciar o mysql , o log vai conter linhas parecidas com estas:
InnoDB: The first specified data file c:\mysql\data\ibdata1 did not exist:
InnoDB: a new database to be created!
021113 9:16:18 InnoDB: Setting file c:\mysql\data\ibdata1 size to 300 MB
InnoDB: Database physically writes the file full: wait...
021113 9:16:38 InnoDB: Log file c:\mysql\data\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file c:\mysql\data\ib_logfile0 size to 30 MB
InnoDB: Database physically writes the file full: wait...
021113 9:16:41 InnoDB: Log file c:\mysql\data\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file c:\mysql\data\ib_logfile1 size to 30 MB
InnoDB: Database physically writes the file full: wait...
021113 9:16:43 InnoDB: Log file c:\mysql\data\ib_logfile2 did not exist: new to be created
InnoDB: Setting log file c:\mysql\data\ib_logfile2 size to 30 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
021113 9:16:46 InnoDB: Started
mysqld: ready for connections
Pronto , você está com o innodb instalado e funcionando, agora já pode criar suas tabelas e usufruir de transações , integridade referencial e mais uma porção de coisas que esse "Table Handler" pode oferecer. Contudo lembre-se de especificar o tipo da tabela na hora da criação , ex: CREATE TABLE TESTE ( cod Integer(4) ) Type = InnoDB . Qualquer referência a mais pode ser obtida na pagina do MySQL ( http://www.mysql.com ) , na página do InnoDB ( http://www.innodb.com ) ou atravéz do meu email dyego@escriba.com.br. Agradeço pelo espaço cedido e espero ter exclarecido um grande mito no mundo do MySQL , a tal da "transação".
MySQL - Como utilizar todo o poder da versão 4.x
O MySQL é um banco de dados conhecido e utilizado pelo mundo todo, agora em sua versão 4.x vem agregar alguns recursos que não eram disponíveis em sua versão estável (3.23) e que são de extrema importância, como transações , cache de querys mais utilizadas , integridade referencial e muito mais. Neste tutorial eu pretendo explicar como funciona e como habilitar transações e integridade referencial.
O MySQL trabalha de uma maneira diferente de outros bancos de dados , ele possue "Tipo de tabela" ou "Table handler" que é onde se esconde os segredos , para utilizar o total poder do MySQL 4.x voce deverá utilizar o "Tipo de tabela" InnoDB.
O InnoDB oferece suporte a transação e a integridade referencial (com CASCADE REFERS) bem como suporte a "crash recovery" no caso de uma queda de luz ou falha fisica, a maneira de gravação e manipulação dos dados é semelhante ao Oracle , ele trabalha com um tablespace ou seja , um tamanho pre-defenido (que pode ser alterado a qualquer momento) de espaco em disco onde vão ser armazenados os dados e indices de suas tabelas, também possue um sistema transacional baseado em logs onde se por qualquer motivo o banco de dados for "interrompido" o mesmo recupera o ultimo estado íntegro do banco.
O tablespace pode ser definido por um arquivo, ou uma partição diretamente , caso voce queira maior performance a partição é a melhor escolha pois o MySQL não vai depender do sistema de arquivos para agilizar a procura e gravação. A versão 4.x traz consigo suporte a UNION e também a MULTI-TABLE DELETE. Talvez uma das maiores implementações da versão 4.x seja o "Query Cache" que armazena o resultado de querys repetitivas, para portais no estilo do PHPNuke e PostNUKE isso é muito comum tendo em vista que quase nada se altera, e a informação é sempre a mesma, o "Query Cache" pode ser utilizado automaticamente ou somente naquelas querys que o programador decidir.
Outra facilidade da versão 4.x é o "Embedded MySQL Server Library" , que nada mais é do que o MySQL linkado diretamente com a sua aplicação , ou seja... nao precisa instalar o banco de dados , ele vai dentro da sua aplicação. Ainda em fase beta , o "Embedded MySQL Server Library"* se demonstra estáel e funcional.
A transação proporcionada pelo subsistema InnoDB do MySQL 4.x é funcional desde casos pequenos a grandes transações onde muitos registros são alterados, com o suporte a integridade referencial bem maduro a dupla MySQL+InnoDB tem conquistado muitos fans adeptos de tal funcionalidade. O funcionamento é bastante simples, toda vez que o mysql inicia ele tambem inicia o InnoDB , o mesmo entra no ar fazendo uma verificação de integridade do banco de dados. Abaixo vemos uma situação que o "servidor foi desligado da tomada" e o InnoDB ao retornar fez as alterações necessárias para que o banco voltasse a posição que ele estava antes de sair repentinamente:
021111 11:53:08 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 8 613915027
InnoDB: Doing recovery: scanned up to log sequence number 8 613945266
021111 11:53:08 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
021111 11:53:09 InnoDB: Flushing modified pages from the buffer pool...
021111 11:53:09 InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections
Como visto acima , o InnoDB detectou que algo teria acontecido de errado , verificou seus logs , constatou que o banco de dados estava em situação "irregular" , voltou as transações em aberto e iniciou normalmente. Esta funcionalidade de "crash recovery" faltava no mysql para consolidar ele como um banco de dados confiavel para operações de risco como por ex: transações financeiras , sistemas de controle de caixa, etc...
As aplicações que hoje funcionam com MyISAM ( Tipo de tabela oficial do MySQL ) podem ser convertidos para utilizar InnoDB com um simples "ALTER TABLE nome_da_tabela Type = InnoDB;" , mas para isso voce precisa habilitar o InnoDB no seu MySQL , siga os passos a seguir:
Vamos pelo inicio de tudo , baixe a versão 4.x no site http://www.mysql.com/downloads/mysql-pro-4.0.html , no site de download voce tera uma infinidade de plataformas , desde Linux até Windows , baixe e instale. Após a instalação edite o seu arquivo my.cfg e descomente as seguintes linhas:
innodb_data_file_path = ibdata1:300M
Este parâmetro serve para definir o tamanho do tablespace que o seu banco de dados vai utilizar , no caso trabalharemos com arquivos pelo fato de ser mais facil de explicar. O arquivo deve conter a seguinte sintaxe: nomedoarquivo:TAMANHO , no caso estamos criando um arquivo chamado ibdata1 que vai armazenar em si 300M para tabelaspace, no caso de precisar ampliar o tamanho do seu tablespace voce deve separar os arquivos por ; ex: ibdata1:300M;ibdata2:2G .
innodb_data_home_dir = c:\mysql\data
Este parâmetro serve para definir aonde vão ficar armazenados os arquivos que vão formar o tablespace , neste caso vamos armazenar dentro do diretório data do mysql
innodb_log_group_home_dir = c:\mysql\data\
innodb_log_arch_dir = c:\mysql\data\
Este parâmetro serve para definir aonde vão ficar armazenados os arquivos de logs que serão gerados para o sistema de transação.
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
Estes dois parâmetros definem se os logs do InnoDB vão ser duplicados e quantos arquivos de logs ele vai gerar respectivamente. Normalmente são valores padrão e não devem ser alterados
set-variable = innodb_log_file_size=30M
Este parâmetro define o tamanho do arquivo de LOG, este arquivo vai ser utilizado para realizar as transações do banco de dados , normalmente o tamanho dele é de 30M , se voce tiver transações mais robustas , aumente este valor.
set-variable = innodb_log_buffer_size=8M
Este parâmetro indica a quantidade de BUFFER DE MEMORIA que o InnoDB vai utilizar para gravar suas transações antes de recorrer aos arquivos fisicos, este valor varia de 1M até 8M , normalmente eu sugiro deixar em 8M , pois quanto menor for a dependencia do disco maior vai ser a velocidade.
innodb_flush_log_at_trx_commit=2
innodb_log_archive=0
Este dois parâmetros definem a maneira de gravação da transação no banco de dados.Normalmente são valores padrão e não devem ser alterados.
set-variable = innodb_buffer_pool_size=80M
Este parâmetro é um dos mais importantes , ele diz ao InnoDB o quanto ele pode utilizar de memoria para guardar indices , referencias e fazer cache de tabelas , quanto maior este valor melhor. Mas atenção, este valor nunca deve ultrapassar 80% da memoria fisica do computador , isso ocasiona lentidão extrema.
set-variable = innodb_additional_mem_pool_size=2M
Este parâmetro define o quanto o InnoDB vai poder utilizar de memoria para a estrutura interna do sistema , normalmente um valor bom é 2M , se caso o InnoDB detectar que precisa de mais memoria ele vai avisar pelo do log MySQL e alocara automaticamente.
set-variable = innodb_file_io_threads=4
Este parâmetro define quantas threads o InnoDB vai utilizar para ler/gravar simultaneamente, normalmente um valor muito bom é 4 , se vc estiver utilizando o MySQL para Windows incremente este valor para 9.
set-variable = innodb_lock_wait_timeout=50
Este parâmetro define o quanto o InnoDB vai esperar antes de voltar uma transação automaticamente, ex: Se vc iniciar uma transacao , alterar uma tabela e deixar parado e outra conexao tenta alterar o mesmo dado , o innodb espera 50 segundos a primeira transação se concretizar , se isso não ocorrer o mesmo volta a transação corrente para o primeiro estado. O valor de 50 segundos normalmente é suficiente.
Pronto , apos setar estes valores é só iniciar o mysql , o log vai conter linhas parecidas com estas:
InnoDB: The first specified data file c:\mysql\data\ibdata1 did not exist:
InnoDB: a new database to be created!
021113 9:16:18 InnoDB: Setting file c:\mysql\data\ibdata1 size to 300 MB
InnoDB: Database physically writes the file full: wait...
021113 9:16:38 InnoDB: Log file c:\mysql\data\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file c:\mysql\data\ib_logfile0 size to 30 MB
InnoDB: Database physically writes the file full: wait...
021113 9:16:41 InnoDB: Log file c:\mysql\data\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file c:\mysql\data\ib_logfile1 size to 30 MB
InnoDB: Database physically writes the file full: wait...
021113 9:16:43 InnoDB: Log file c:\mysql\data\ib_logfile2 did not exist: new to be created
InnoDB: Setting log file c:\mysql\data\ib_logfile2 size to 30 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
021113 9:16:46 InnoDB: Started
mysqld: ready for connections
Pronto , você está com o innodb instalado e funcionando, agora já pode criar suas tabelas e usufruir de transações , integridade referencial e mais uma porção de coisas que esse "Table Handler" pode oferecer. Contudo lembre-se de especificar o tipo da tabela na hora da criação , ex: CREATE TABLE TESTE ( cod Integer(4) ) Type = InnoDB . Qualquer referência a mais pode ser obtida na pagina do MySQL ( http://www.mysql.com ) , na página do InnoDB ( http://www.innodb.com ) ou atravéz do meu email dyego@escriba.com.br. Agradeço pelo espaço cedido e espero ter exclarecido um grande mito no mundo do MySQL , a tal da "transação".
