Início / dba / Perguntas / 83125
Accepted
SBhojani
Asked: 2014-11-20 12:36:46 +0800 CST

MySQL alguma maneira de importar um despejo sql enorme (32 GB) mais rápido?

  • 772

Eu tenho esse enorme dump SQL de 32 GB que preciso importar para o MySQL. Eu não tive que importar um dump SQL tão grande antes. fiz o normal:

mysql -uroot dbname < dbname.sql

Está demorando muito. Há uma tabela com cerca de 300 milhões de linhas, chegou a 1,5 milhão em cerca de 3 horas. Então, parece que a coisa toda levaria 600 horas (ou seja, 24 dias) e é impraticável. Então, minha pergunta é, existe uma maneira mais rápida de fazer isso?

Mais informações/descobertas

  1. As tabelas são todas InnoDB e não há chaves estrangeiras definidas. Existem, no entanto, muitos índices.
  2. Eu não tenho acesso ao servidor original e ao banco de dados, então não posso fazer um novo backup ou fazer uma cópia "quente" etc.
  3. A configuração innodb_flush_log_at_trx_commit = 2sugerida aqui parece não fazer nenhuma melhoria (claramente visível/exponencial).
  4. Estatísticas do servidor durante a importação (do MySQL Workbench): https://imgflip.com/gif/ed0c8 .
  5. A versão do MySQL é a comunidade 5.6.20.
  6. innodb_buffer_pool_size = 16M e innodb_log_buffer_size = 8M. Preciso aumentar estes?
mysql backup

5 respostas

  1. Best Answer

    Vadim Tkachenko da Percona fez esta bela representação pictórica do InnoDB

    Arquitetura InnoDB

    Você definitivamente precisa alterar o seguinte

    innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

    Por que essas configurações?

    Reinicie o mysql assim

    service mysql restart --innodb-doublewrite=0

    Isso desabilita o buffer de gravação dupla do InnoDB

    Importe seus dados. Quando terminar, reinicie o mysql normalmente

    service mysql restart

    Isso reativa o buffer de gravação dupla do InnoDB

    De uma chance !!!

    NOTA LATERAL: Você deve atualizar para 5.6.21 para obter os patches de segurança mais recentes .

    • 149

  2. Você realmente precisa que todo o banco de dados seja restaurado? Se você não fizer isso, meu 2c:

    Você pode extrair tabelas específicas para fazer sua restauração em "pedaços". Algo assim:

    zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql

    Eu fiz isso uma vez e demorou 10 minutos para extrair a tabela que eu precisava - minha restauração completa levou de 13 a 14 horas, com um despejo de 35 GB (gzipado).

    O /pattern/,/pattern/pcom o -nparâmetro faz uma fatia "entre os padrões" - incluindo-os.

    De qualquer forma, para restaurar os 35GB eu usei uma máquina AWS EC2 (c3.8xlarge), instalei o Percona via yum (Centos) e apenas adicionei/alterei as seguintes linhas em my.cnf:

    max_allowed_packet=256M
wait_timeout=30000

    Eu acho que os números são muito altos, mas funcionou para a minha configuração.

    • 16

  3. A maneira mais rápida de importar seu banco de dados é copiar os arquivos ( .frm, .MYD, .MYI ) se MyISAM, diretamente para o /var/lib/mysql/"nome do banco de dados".

    Caso contrário, você pode tentar:mysql > use database_name; \. /path/to/file.sql

    Essa é outra maneira de importar seus dados.

    • 6

  4. Uma maneira de ajudar a acelerar a importação é bloquear a tabela durante a importação. Use a opção --add-locks para mysqldump.

    mysqldump --add-drop-table --add-locks --database db > db.sql

    ou você pode ativar alguns parâmetros úteis com --opt isso ativa um monte de coisas úteis para o dump.

    mysqldump --opt --database db > db.sql

    Se você tiver outro dispositivo de armazenamento no servidor, use-o - copiar de um dispositivo para outro é uma maneira de acelerar as transferências.

    Você também pode filtrar as tabelas que não são necessárias com --ignore-table

    • 5

  5. Veja como definir a configuração do RolandoMySQLDBA no linux:

    1. Abra o arquivo mysql conf em seu editor de texto favorito:

      sudo vi /etc/mysql/my.conf

    2. Digite a seguinte configuração no final do arquivo:

        [mysqld]
    innodb_buffer_pool_size = 4G
    innodb_log_buffer_size = 256M
    innodb_log_file_size = 1G
    innodb_write_io_threads = 16
    innodb_flush_log_at_trx_commit = 0
    innodb_doublewrite = 0

    1. Salve o arquivo e execute este comando no mysql:

      set global innodb_fast_shutdown = 0

      você pode ver a nova configuração com @@ seguido pelo comando no mysql:

      select @@innodb_buffer_pool_size;

      da mesma forma com outros comandos

    2. Reinicie o mysql:

      service mysql restart

    3. Restaure o estado do seu banco de dados:

      mysql -u username -p database_name < /path/to/file.sql

    Tive que importar 5.8GB de dados o que me levou 40min com a configuração do pc do inter corei5 2.5GHZ e 16gb ram

    • 1

relate perguntas