Como fazer backup de um grande banco de dados MySQL no Linux

4

Eu quero fazer o backup do banco de dados MySQL, que tem cerca de 200 GB. Como devo fazer isso?

Usando o mysqldump - Eu sinto o backup de 200GB de banco de dados no qual existem gravações contínuas acontecendo. É uma boa ideia? Eu não posso parar as inserções do banco de dados. Então eu quero fazer um backup quente. Estou ciente sobre o mysqlhotcopy .. que apenas faz o backup de tabelas myisam. e eu tenho minhas mesas no Innodb.

Por favor, me dê alguns pensamentos / comentários sobre como devo fazer isso?

    
por HopelessN00b 27.05.2011 / 12:18

4 respostas

4

Há vários métodos de backup possíveis descritos nos documentos do MySQL . No seu caso, eu recomendaria claramente o método descrito na seção "Criando backups usando um instantâneo do sistema de arquivos" onde você emite um FLUSH TABLES WITH READ LOCK, faça um instantâneo do sistema de arquivos (LVM ou outro) e UNLOCK TABLES. Depois disso, você pode simplesmente copiar os arquivos do banco de dados para a mídia de backup.

As outras opções são claramente inferiores. O Mysqldump irá criar picos de carga e bloqueios de tabela que evitarão seus INSERTs por um longo tempo, especialmente com tabelas muito grandes.

A replicação é uma solução ainda menos elegante, já que o MySQL não tem mecanismos para garantir que o seu conjunto de réplicas seja idêntico - você apenas terá que esperar que este seja o caso - realmente não o que você deseja para um backup.

    
por 27.05.2011 / 22:21
1

Sua melhor escolha é criar outro servidor MySQL e configurá-los no modo mestre / mestre. Isso não apenas fornecerá uma solução de backup em tempo real, mas também um failover.

    
por 27.05.2011 / 22:19
1

INSIGHT EM FAZER BACKUPS COM mysqldump

IMHO Fazer backups tornou-se 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 básico!

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

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

Opção 2: mysqldump separar bancos de dados 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 relacionados ao banco de dados. O information_schema registraria a pasta como um banco de dados na tabela information_schema.schemata. A técnica 2 iria ignorar pastas que não contenham dados mysql.

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

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

Se houver muitos bancos de dados para serem executados ao mesmo tempo, faça um paralelismo com eles 10 por vez:

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in 'cat ListOfDatabases.txt'
do
    mysqldump -h... -u... -p... --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 tabelas separadas 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... --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 fotos nítidas

Exemplos

  1. Solicite 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 depois que os mysqldumps estiverem completos.
  3. Salve os mysqldumps em pastas antigas e gire as pastas de backup antigas.
  4. Carregue mysqldumps de instância inteira em servidores independentes.

CAVEAT

Apenas a Opção 1 traz tudo. A desvantagem é que os mysqldumps criados dessa maneira só podem ser recarregados na mesma versão principal 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. O motivo ? 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 descarregar os SQL Grants para usuários que sejam legíveis e mais portáteis

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, portanto, você pode fazer o seguinte

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

Outro ponto que deve ser observado é sobre o InnoDB. Se você tem um grande buffer pool do InnoDB, faz sentido limpá-lo da melhor forma possível antes de executar qualquer backup. Caso contrário, o MySQL gasta o tempo de liberação de tabelas com a página suja restante do buffer pool. Aqui está o que eu sugiro:

Mais de 1 hora antes de executar o backup, execute este comando SQL

SET GLOBAL innodb_max_dirty_pages_pct = 0;

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

PALAVRA FINAL NO mysqldump

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

Essas ferramentas incluem

  1. MAATKIT ( despejo / restaurar scripts, de Percona [Deprecado mas ótimo])
  2. XtraBackup (backup de snapshot do TopNotch da Percona)
  3. CDP R1Soft (MySQL Module Option que captura instantâneos de um ponto no tempo)
  4. MySQL Enterprise Backup (anteriormente Backups InnoDB Hot [comerciais])

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 um DBA MySQL .

    
por 29.05.2011 / 00:46
0

Se todas as suas tabelas forem InnoDB, você deve usar o Xtrabackup , que fornece um backup online. Você também pode fazer instantâneos de LVM, mas problemas de desempenho com instantâneos de LVM ainda sugerem que o Xtrabackup é a solução recomendada.

Felicidades

    
por 27.05.2011 / 22:23

Tags