Era conveniente que o MyISAM armazenasse cada tabela em um arquivo correspondente. O InnoDB fez avanços em muitos aspectos, mas me pergunto por que o InnoDB armazena todos os bancos de dados em um arquivo ( ibdata1por padrão).
Eu entendo que o InnoDB mapeará a localização dos dados no arquivo por arquivos de índice individuais para tabelas, mas não entendo por que ele mistura todos os dados em um arquivo. E mais importante, por que misturar os dados de todos os bancos de dados no servidor?
Um recurso interessante do MyISAM é que se pode copiar/colar uma pasta de banco de dados em outra máquina e depois usar o banco de dados (sem dump).
A arquitetura do InnoDB exige o uso de quatro tipos básicos de páginas de informações
Veja a Representação Pictórica de ibdata1
Por padrão, innodb_file_per_table está desabilitado. Isso faz com que todos os quatro tipos de página de informações aterrem em um único arquivo chamado ibdata1. Muitas pessoas tentam espalhar os dados criando vários arquivos ibdata. Isso pode levar à fragmentação de dados e páginas de índice.
É por isso que muitas vezes recomendo limpar a infraestrutura do InnoDB, usando o arquivo ibdata1 padrão e nada mais .
Copiar é muito perigoso por causa da infraestrutura sob a qual o InnoDB funciona. Existem duas infraestruturas básicas
InnoDB ( innodb_file_per_table desativado)
Com o innodb_file_per_table desabilitado, todos esses tipos de informações do InnoDB ficam dentro do ibdata1. A única manifestação de qualquer tabela InnoDB fora de ibdata1 é o arquivo .frm da tabela InnoDB. Copiar todos os dados do InnoDB de uma vez requer copiar todos os arquivos /var/lib/mysql.
Copiar uma tabela individual do InnoDB é totalmente impossível. Você deve fazer dump do MySQL para extrair um dump da tabela como uma representação lógica dos dados e suas definições de índice correspondentes. Você então carregaria esse dump para outro banco de dados no mesmo servidor ou em outro servidor.
InnoDB ( innodb_file_per_table habilitado)
Com o innodb_file_per_table habilitado, os dados da tabela e seus índices ficam na pasta do banco de dados ao lado do arquivo .frm. Por exemplo, para a tabela db1.mytable, a manifestação dessa tabela InnoDB fora de ibdata1 seria:
/var/lib/mysql/db1/mytable.frm/var/lib/mysql/db1/mytable.ibdEspaço de tabela do sistema
ibdata1Todos os metadados para db1.mytable ainda residem em ibdata1 e não há absolutamente nenhuma maneira de contornar isso . Redo logs e dados MVCC também ainda vivem com ibdata1.
Quando se trata de fragmentação de tabelas, aqui está o que acontece com ibdata1:
ALTER TABLE db1.mytable ENGINE=InnoDB;ouOPTIMIZE TABLE db1.mytable;. Isso faz com que /var/lib/mysql/db1/mytable.ibd seja fisicamente menor sem fragmentação.ALTER TABLE db1.mytable ENGINE=InnoDB;ouOPTIMIZE TABLE db1.mytable;porque ele reside com ibdata1. Executando qualquer comando, na verdade, torne a tabela contígua e mais rápida para ler e gravar. Infelizmente, isso ocorre no final do ibdata1. Isso faz com que o ibdata1 cresça rapidamente. Isso é totalmente abordado no meu InnoDB Cleanup Post .AVISO (ou PERIGO como diria o Robô em Perdidos no Espaço )
Se você está pensando em apenas copiar o arquivo .frm e .ibd, você está na fila para o mundo da dor. Copiar o arquivo .frm e .ibd de uma tabela InnoDB só é bom se e somente se você puder garantir que o id do tablespace do arquivo .ibd corresponda exatamente à entrada do id do tablespace nos metadados do arquivo ibdata1 .
Eu escrevi dois posts no DBA StackExchange sobre esse conceito de id de tablespace
Aqui está um excelente link sobre como reconectar qualquer arquivo .ibd ao ibdata1 no caso de ids de tablespace incompatíveis: http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file . Depois de ler isso, você deve perceber imediatamente que copiar arquivos .ibd é simplesmente uma loucura.
Para o InnoDB, você só precisa de algo para mover
para fazer uma cópia de uma tabela InnoDB.
Se você estiver migrando para outro servidor de banco de dados, use mysqldump.
Com relação à mistura de todas as tabelas InnoDB de todos os bancos de dados, posso realmente ver a sabedoria em fazê-lo. Na empresa de hospedagem de banco de dados/web do meu empregador, tenho um cliente MySQL que possui uma tabela em um banco de dados cujas restrições são mapeadas para outra tabela em outro banco de dados dentro da mesma instância MySQL. Com um repositório de metadados comum, ele possibilita o suporte transacional e a operabilidade do MVCC em vários bancos de dados.
Você pode alternar o InnoDB para armazenar tabelas por arquivo adicionando innodb-file-per-table ao seu cnf.
O Innodb realmente se preocupa com páginas de dados em um nível básico. Na verdade, você pode configurar o InnoDB para usar apenas um dispositivo de bloco bruto sem nenhum sistema de arquivos! http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html
Há conveniências para armazenar tabelas para arquivo, como a capacidade de recuperar mais facilmente o espaço usado por meio da otimização.
Mesmo com arquivos por tabela, você não pode simplesmente copiar os arquivos ibd tão facilmente, pois o InnoDB é transacional e armazena informações sobre seu estado nos arquivos ibdata/log compartilhados globalmente.
Isso não quer dizer que não possa ser feito. Se a tabela estiver offline, você pode descartar/importar os tablespaces e copiar os .idbs em http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
Este é o comportamento padrão, mas não obrigatório. Dos documentos do MySQL, usando espaços de tabela por tabela :
Quanto ao porquê, o motivo é provavelmente as diferentes arquiteturas dos dois mecanismos (MyISAM e InnoDB). Por exemplo, no InnoDB, você não pode simplesmente copiar o arquivo .ibd para outro banco de dados ou instalação. Explicação (da mesma página):