Como posso otimizar um mysqldump de um banco de dados grande?

O principal gargalo no dump como esse é a E/S da unidade. Você está lendo uma carga de dados e gravando-os novamente. Você pode acelerar isso de várias maneiras:

• Certifique-se de que sua saída esteja indo para uma(s) unidade(s) diferente(is) daquela(s) em que os arquivos de banco de dados estão armazenados - isso fará uma enorme diferença com discos giratórios, pois as cabeças da unidade não estarão constantemente alternando entre o local que está sendo lido e o local que está sendo gravado.
• A saída do mysqldump será muito compressível, portanto, se você não puder separar a saída da entrada, conforme mencionado acima, canalize a saída gzipou similar. Isso reduzirá a quantidade de escrita sendo feita (assim, reduza a carga geral de E/S e a quantidade de movimento da cabeça) às custas de algum tempo de CPU (que você pode ter muito sobressalente nesses momentos).
• Além disso, (também ou em vez de compactação) passe a saída através de um utilitário de pipe (como pv ) que suporta grandes buffers de gravação para agrupar mais blocos gravados nas unidades, novamente para reduzir o efeito da latência do movimento da cabeça - isso fará com que uma grande diferença se estiver usando a --quickopção para reduzir o impacto da RAM ao fazer backup de tabelas grandes).
• Execute o processo de backup apenas quando a carga de E/S estiver baixa.

No entanto, você pode estar corrigindo o problema errado: pode ser mais fácil resolver as quedas de conexão (embora reduzir a carga de E/S imposta por seus backups ajude a reduzir o efeito que você tem sobre outros usuários, então vale a pena tentar de qualquer maneira). Você poderia executar seus backups manuais através da tela (ou ferramentas semelhantes como tmux )? Dessa forma, se sua conexão com o servidor cair, você pode simplesmente reconectar e reconectar à screensessão sem que nenhum processo seja interrompido.

Se você estiver enviando os dados diretamente pela conexão (ou seja, você está executando o mysqldump em sua máquina local em um banco de dados remoto, para que o dump apareça localmente), talvez seja melhor executar o dump no servidor primeiro, compactando conforme necessário e depois transferindo os dados pela rede usando uma ferramenta (como rsync) que suporta transferências parciais para que você possa retomar a transferência (em vez de reiniciar) se uma queda de conexão a interromper.

Como parte de sua "redução do tamanho do banco de dados geral para resolver esse problema", acho que uma grande parte dos seus dados não muda. Você pode mover uma grande parte dos 1,2 Gb dessa tabela principal para outra e removê-la daquelas que são copiadas pela mysqldumpchamada. Você não precisa fazer backup desses dados todas as vezes se eles nunca forem alterados. A divisão de dados entre tabelas e bancos de dados dessa maneira geralmente é chamada de particionamento de dados e também pode permitir que você distribua os dados e a carga de E/S em várias unidades. Banco de dados high-end tem suporte embutido para particionamento automático, embora no mysql você provavelmente terá que fazer isso manualmente e alterar sua camada de acesso a dados para explicar isso.

Fugindo do tópico para este site (então você provavelmente deve ir até ServerFault ou SuperUser para perguntar se precisa de mais detalhes): Se você parece estar perdendo conexões devido à inatividade, verifique as opções em seu servidor SSH e cliente SSH para fazer certifique-se de que os pacotes keep-alive estão habilitados e sendo enviados com frequência suficiente. Se ver quedas mesmo que a conexão esteja ativa, você também pode tentar usar OpenVPN ou similar para encerrar a conexão - ele deve lidar com uma queda curta, até mesmo uma queda completa se toda a sua conexão estiver inativa por alguns segundos, de modo que o cliente SSH e servidor não percebe.

INSIGHT PARA FAZER BACKUPS COM mysqldump

IMHO Fazer backups se tornou mais uma forma de arte se você souber como abordá-lo

Você tem opções

Opção 1: mysqldump uma instância inteira do mysql

Este é o mais fácil, o óbvio!!!

mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz

Tudo escrito em um arquivo: estruturas de tabelas, índices, gatilhos, procedimentos armazenados, usuários, senhas criptografadas. Outras opções do mysqldump também podem exportar diferentes estilos de comandos INSERT, arquivo de log e coordenadas de posição de logs binários, opções de criação de banco de dados, dados parciais (opção --where) e assim por diante.

Opção 2: mysqldump bancos de dados separados em arquivos de dados separados

Comece criando uma lista de bancos de dados (2 técnicas para fazer isso)

Técnica 1

mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Técnica 2

mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

A técnica 1 é o caminho mais rápido. A técnica 2 é a mais segura e segura. A técnica 2 é melhor porque, às vezes, os usuários criam pastas para propósitos gerais em /var/lib/mysql (datadir) que não são relacionadas ao banco de dados. O information_schema registraria a pasta como um banco de dados na tabela information_schema.schema. A técnica 2 ignoraria as pastas que não contêm dados mysql.

Depois de compilar a lista de bancos de dados, você pode continuar percorrendo a lista e mysqldump, mesmo em paralelo, se desejar.

for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

Se houver muitos bancos de dados para iniciar ao mesmo tempo, faça um dump paralelo deles 10 de cada vez:

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Opção 3: mysqldump separar tabelas em arquivos de dados separados

Comece criando uma lista de tabelas

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt

Em seguida, despeje todas as tabelas em grupos de 10

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump -h... -u... -p... --hex-blob --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Opção 4: USE SUA IMAGINAÇÃO

Experimente variações das opções acima mencionadas, além de técnicas para instantâneos limpos

Exemplos

1. Ordene a lista de tabelas pelo tamanho de cada tabela crescente ou decrescente.
2. Usando um processo separado, execute "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" antes de iniciar o mysqldumps. Mate este processo após a conclusão do mysqldumps. Isso é útil se um banco de dados contiver InnoDB e MyISAM
3. Salve os mysqldumps em pastas datadas e gire as pastas de backup antigas.
4. Carregue mysqldumps de instância inteira em servidores autônomos.

EMBARGO

Apenas a Opção 1 traz tudo. A desvantagem é que os mysqldumps criados desta forma só podem ser recarregados na mesma versão majot do mysql que o mysqldump foi gerado. Em outras palavras, um mysqldump de um banco de dados MySQL 5.0 não pode ser carregado em 5.1 ou 5.5. A razão ? O esquema mysql é totalmente diferente entre os principais lançamentos.

As opções 2 e 3 não incluem salvar nomes de usuário e senhas.

Aqui está a maneira genérica de despejar o SQL Grants para usuários que é legível e mais portátil

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

A opção 3 não salva os procedimentos armazenados, então você pode fazer o seguinte

mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &

Outro ponto que deve ser observado é em relação ao InnoDB. Se você tiver um pool de buffers InnoDB grande, faz sentido liberá-lo da melhor maneira possível antes de realizar qualquer backup. Caso contrário, o MySQL gasta o tempo liberando as tabelas com a página suja restante do buffer pool. Aqui está o que eu sugiro:

Cerca de 1 hora antes de realizar o backup execute este comando SQL

SET GLOBAL innodb_max_dirty_pages_pct = 0;

No MySQL 5.5 o padrão innodb_max_dirty_pages_pct é 75. No MySQL 5.1 e posterior, o padrão innodb_max_dirty_pages_pct é 90. Ao definir innodb_max_dirty_pages_pct como 0, isso acelerará a liberação de páginas sujas para o disco. Isso evitará ou pelo menos diminuirá o impacto de limpar quaisquer commits de duas fases incompletos de dados InnoDB antes de executar qualquer mysqldump em qualquer tabela InnoDB.

PALAVRA FINAL NO mysqldump

A maioria das pessoas evita o mysqldump em favor de outras ferramentas e essas ferramentas são realmente boas.

Tais ferramentas incluem

1. MAATKIT ( scripts de despejo / restauração paralelos , de Percona [Descontinuado, mas ótimo])
2. XtraBackup (Backup de instantâneos TopNotch da Percona)
3. CDP R1Soft ( Opção de módulo MySQL que tira instantâneos point-in-time)
4. MySQL Enterprise Backup (anteriormente InnoDB Hot Backups [comercial])

Se você tem o espírito de um verdadeiro DBA MySQL, você pode abraçar o mysqldump e ter o domínio completo sobre ele que pode ser alcançado. Que todos os seus backups sejam um reflexo de suas habilidades como DBA MySQL .

Dê uma olhada na replicação do MySQL mestre para escravo. Permite clonar o banco de dados do mestre para outro servidor de banco de dados com o mesmo banco de dados. Isso inclui as identidades mestre e escravo. O Slave faz a cópia exata do servidor de banco de dados mestre e/ou de seus bancos de dados. Pode haver uma relação um-um, um-muitos, muitos-um entre mestre(s) e escravo(s).

O escravo lê continuamente o log binário no mestre (o log do bin armazena as consultas escritas no servidor de banco de dados mestre) e obtém entrada para seu servidor de banco de dados escravo. (isso significa que seu banco de dados mestre não será afetado)

A boa notícia é que isso não afetará muito seu servidor MySQL, pois você não notará nenhum tempo de inatividade ou respostas lentas de consulta. Nós o usamos para bancos de dados de 10 Gb e funciona como um encanto sem qualquer tempo de inatividade.

Replicação MySQL na mesma máquina

Plano A: Veja também o Xtrabackup da Percona. Isso permite o backup online do InnoDB, sem nenhum bloqueio significativo.

Plano B: Um Slave pode ser interrompido e você pode fazer um backup consistente por qualquer um dos vários meios (copiar arquivos, mysqldump, xtrabackup, etc)

Plano C: Instantâneo do LVM. Após alguma configuração enigmática, o tempo de inatividade de um backup é inferior a um minuto, independentemente do tamanho do banco de dados. Você para o mysqld, faz o snapshot, reinicia o mysqld e copia o snapshot. A última etapa pode levar muito tempo, mas o MySQL não está inativo.

Plano D: Snapshot de um Slave -- tempo de inatividade zero.

Alguns pontos de administração primeiro: você está se conectando para fazer um ftp ou está ssh'ed e está morrendo? Se ssh, certifique-se de usar a tela para que você possa retomar após a falha do comcast. Se for ftp, certifique-se de comprimi-lo/tar antes do envio.

Tente também o parâmetro --opt ou --quick

--opt Esta opção ativa um conjunto de opções adicionais para tornar as operações de despejo e recarregamento mais eficientes. Especificamente, é equivalente a usar as opções --add-drop-table, --add-locks, --all, --quick, --extended-insert, --lock-tables e --disable-keys juntas. Observe que essa opção torna a saída menos portátil e menos provável de ser compreendida por outros sistemas de banco de dados.

--quick Esta opção diz ao mysqldump para escrever a saída de despejo enquanto lê cada linha do servidor, o que pode ser útil para tabelas grandes. Por padrão, mysqldump lê todas as linhas de uma tabela na memória antes de escrever a saída; para tabelas grandes, isso requer grandes quantidades de memória, possivelmente causando falha no despejo.

Eu costumava ter problemas com tempos limite durante despejos de grandes bancos de dados também. Eu finalmente resolvi enviando comandos individuais para cada tabela no banco de dados e anexando tudo a um arquivo como este:

TABLES=`mysql -u $USER -p$PWD -Bse 'show tables' $DB`
for TABLE in $TABLES
do
    mysqldump -u $USER -p$PWD $DB $TABLE >> dump.sql
done

Acho que a questão é como restaurar mais rapidamente os arquivos de despejo criados pelo mysqldump, não uma solução de backup diferente.

Uma das maneiras de fazer isso é criar grupos de tabelas em seu esquema e criar um usuário de banco de dados separado para cada grupo e, finalmente, usar as permissões do MySQL para não permitir que tabelas sejam inseridas usando todos, exceto um usuário de banco de dados.

Esta é uma técnica comprovada, rápida e quase paralela, mas não 100% certa, quanto tempo levará para restaurar de grandes despejos como 500G ou mais. Mas na minha humilde opinião, você precisa de algo paralelo. Confira no link abaixo um exemplo.

[Restauração rápida e paralela de dumps SQL (mysqldump) para MySQL][1]

http://geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/fast_parallel_restore_from_sql_dumps_mysqldump_for_mysql.php

"Restauração rápida e paralela de dumps SQL (mysqldump) para MySQL"