Início / dba / Perguntas / 11043
Accepted
Ran
Asked: 2012-01-21 05:31:00 +0800 CST

MySql - alterando innodb_file_per_table para um banco de dados ao vivo

  • 772

Eu tenho um grande banco de dados MySql (150 GB) e só agora notei que o innodb_file_per_tableestá definido para offo que faz com que todo o banco de dados seja hospedado em um único arquivo ( ibdata1). Eu quero ativar innodb_file_per_tablee dividir retroativamente o banco de dados em vários arquivos, qual é a melhor maneira de fazer isso?

mysql innodb

2 respostas

  1. Best Answer

    Há realmente apenas uma maneira de fazer isso. Você terá que exportar os dados usando mysqldumps, descartar todos os bancos de dados, desligar o mysqld, excluir ib_logfile0, excluir ib_logfile1, excluir ibdata1, adicionar innodb_file_per_tablesob o [mysqld]título, iniciar o mysql.

    Eu postei esta resposta no StackOverflow em outubro de 2010

    Aqui estão as etapas listadas verticalmente:

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

    Etapa 02) Elimine todos os bancos de dados (exceto o esquema mysql)

    Etapa 03) Desligar o mysql

    CAVEAT : Para limpar totalmente as transações não confirmadas dos arquivos InnoDB, execute este

    mysql -uroot -p... -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

    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.mytable e o arquivo /var/lib/mysql/mydb/mytable.ibd será 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. !!!

    Existe uma alternativa que extrairá a tabela InnoDB sem reduzir ibdata1.

    Etapa 01) 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

    Etapa 02)service mysql restart

    Etapa 03) Para extrair uma única tabela InnoDB chamada mydb.mytable, faça o seguinte:

    ALTER TABLE mydb.mytable ENGINE=InnoDB;

    Isto irácriar um arquivo pleus manter o arquivo de estrutura original

    • /var/lib/mysql/mydb/mytable.frm
    • /var/lib/mysql/mydb/mytable.ibd

    Você pode fazer isso para cada tabela InnoDB. Infelizmente, o ibdata1 permanecerá com 150 GB.

    • 33

  2. Se você deseja recuperar o espaço do ibdata, um dump/restore é sua única opção, como Rolando aponta. Também é provavelmente melhor para o desempenho fazer isso.

    No entanto, se você quiser apenas reduzir suas perdas e 'perder' aqueles 150 GB no disco rígido, basta habilitar innodb_file_per_tableno my.cnf e reiniciar o servidor.

    Em seguida, para cada tabela, emita:

    ALTER TABLE x DISABLE KEYS;
ALTER TABLE x ENGINE=InnoDB;
ALTER TABLE x ENABLE KEYS;

    O problema aqui é que grandes tablespaces demoram um pouco.

    O que eu sugiro é configurar um escravo do seu banco de dados ao vivo, execute a conversão no escravo, desligue o mestre/escravo e copie o novo espaço de dados para o mestre ou promova o escravo a mestre assim que ele alcançar .

    Você terá dificuldade em fazer essa alteração sem nenhum tempo de inatividade.

    • 5

relate perguntas