Qual é a melhor maneira de reduzir o tamanho do ibdata no mysql?

Tenha em mente que o arquivo mais ocupado na infraestrutura do InnoDB é /var/lib/mysql/ibdata1

Este arquivo normalmente abriga muitas classes de informações (quando innodb_file_per_table é 0)

• Dados da Tabela
• Índices de tabela
• Dados MVCC (Multiversioning Concurrency Control)
  • Segmentos de reversões
  • Desfazer espaço de tabela
• Metadados da tabela
• Veja Representação Pictórica

Muitas pessoas criam vários arquivos ibdata esperando um melhor gerenciamento e desempenho do espaço em disco. Isso não ajuda.

Infelizmente, OPTIMIZE TABLE contra uma tabela InnoDB armazenada em ibdata1 faz duas coisas:

• Torna os dados e índices da tabela contíguos dentro de ibdata1
• Faz o ibdata1 crescer porque os dados contíguos são anexados ao ibdata1

Você pode separar dados de tabela e índices de tabela de ibdata1 e gerenciá-los de forma independente usando innodb_file_per_table . Para reduzir o ibdata1 de uma vez por todas, você deve fazer o seguinte

Etapa 01) MySQLDump todos os bancos de dados em um arquivo de texto SQL (chame-o SQLData.sql) ( mais detalhes aqui )

Etapa 02) Elimine todos os bancos de dados (exceto mysql, performance_schemae information_schema)

Etapa 03) Desligar o mysql

Etapa 04) Adicione as seguintes linhas ao /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Nota: Seja qual for o seu conjunto para innodb_buffer_pool_size, certifique-se de que innodb_log_file_size seja 25% de innodb_buffer_pool_size.

Etapa 05) Excluir ibdata1, ib_logfile0 e ib_logfile1

Neste ponto, deve haver apenas o esquema mysql em /var/lib/mysql

Passo 06) Reinicie o mysql

Isso recriará ibdata1 em 10 MB, ib_logfile0 e ib_logfile1 em 1 G cada

Etapa 07) Recarregue SQLData.sql no mysql

ibdata1 crescerá, mas conterá apenas metadados de tabela

Cada tabela InnoDB existirá fora do ibdata1

Suponha que você tenha uma tabela InnoDB chamada mydb.mytable. Se você entrar em /var/lib/mysql/mydb, verá dois arquivos representando a tabela

• mytable.frm (cabeçalho do mecanismo de armazenamento)
• mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 nunca mais conterá dados e índices do InnoDB.

Com a opção innodb_file_per_table em /etc/my.cnf, você pode executar OPTIMIZE TABLE mydb.mytablee o arquivo /var/lib/mysql/mydb/mytable.ibdserá reduzido.

Eu fiz isso muitas vezes na minha carreira como DBA MySQL

Na verdade, na primeira vez que fiz isso, reduzi um arquivo ibdata1 de 50 GB em 500 MB.

De uma chance. Se você tiver mais dúvidas sobre isso, envie-me um e-mail. Confie em mim. Isso funcionará no curto prazo e no longo prazo. !!!

Se você quiser ver quantos dados reais estão armazenados no MyISAM e no InnoDB, execute esta consulta:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;