Backup compactado em MySQL – mysqldump – gzip – bzip2

Hoje vamos falar de um assunto muito importante para administradores de banco de dados, o BACKUP.
Existem várias estratégias de backup, snapshot, dump, dump + binlog, tudo depende da sua necessidade e do tamanho do seu banco de dados. Vou explanar um pouco sobre a ferramenta que o próprio MySQL nos provê, que é o mysqldump, eu indico esta ferramenta para bancos de dados que possuam até 15gb/20gb, mais que isso, a probabilidade de você ter problemas para restaurar um backup e a grande demora, o tornam inviável.
Como o mysqldump funciona? Ele nada mais faz do criar comandos sql para nossa estrutura e dados e os jogar no arquivo indicado.
Primeiramente, vou explicar algumas das mais importantes opções do mysqldump depois, para quem tem o MySQL instalado em um servidor LINUX, vou ensinar como compactar o dump em tempo real.

    Fazendo backup de todos os bancos de dados:

mysqldump -u usuario -p --all-databases > dump.sql

    Fazendo backup de somente um banco de dados:

mysqldump -u usuario -p --databases db1 > dump.sql

    Fazendo backup de varios banco de dados:

mysqldump -u usuario -p --databases db1 db2 db... > dump.sql

    Fazendo backup com triggers

mysqldump -u usuario -p --triggers --all-databases > dump.sql

    Fazendo backup com procedures e functions

mysqldump -u usuario -p --routines --all-databases > dump.sql

    Agora vamos compactar o nosso dump em tempo real com o gzip:

mysqldump -u usuario -p --all-databases | gzip > dump.sql.gz

    Podemos ainda atingir uma maior taxa de compressão utilizando o bzip2:

mysqldump -u usuario -p --all-databases | bzip2 > dump.sql.bz2

Fazendo um comparativo entre o dump com as 3 opções de compressão (nenhuma, gzip e bzip2) tivemos o seguinte resultado:

    Dump normal – 947k
    Dump com gzip – 297k
    Dump com bzip2 – 205k

E como eu faço para restaurar o dump?

    Normal:

mysql -u usuario -p < dump.sql

    gzip:

gunzip < dump.sql.gz | mysql -u usuario -p

    bzip2:

bunzip2 < dump.sql.bz2 | mysql -u usuario -p

Mais detalhes sobre mysqldump podem ser encontrados no manual online do MySQL, até a próxima.

Gostou ? Ajude e Compartilhe!
Esta entrada foi publicada em MySQL, mysqldump. Adicione o link permanente aos seus favoritos.
  • Leandro

    Cara muito bacana as maneiras de compactação,mas como você citou isso é ideal para 10/20 GB, e no caso de uma base mysql com 90GB, o que você indica. to com um baita pronlema se puder ajudar agradeço.

    • marceloaltmann

      No teu caso eu indicaria parar o banco e fazer uma copia dos arquivos de dados (datadir), eu geralmente tenho um slave replicando os dados do master, e periodicamente eu pauso o slave (o que nao gera indisponibilidade no master) faco uma copia compactada da pasta e tomo nota da posicao do binlog para uma possivel restauracao. exemplo: vamos supor que eu faca o backup 2 vezes na semana, no domingo e na quarta, hoje eh quarta, eu iria rodar o backup hoje a noite, em caso de uma possivel falha, eu simplesmente subo o mysql com a copia do data dir do ultimo domingo e disparo contra ele, todos os comandos que estao no binlog em uma posicao maior do que a do momento da copia do datadir.
      Claro, tens que analizar qual o teu volume de dados, talvez 2 vezes na semana pode ser pouco.
      Caso tenha mais alguma duvida, so comentar!
      Espero ter ajudado!

  • Andre Luiz

    Boa tarde Marcelo, tenho um Bd mysql instalado em um win2008R2 eu utilizo a bat abaixo para fazer backup do banco de dados, porém esse script não me traz as procedures.
    Poderia me ensinar um que seria mais completo?

    O comando que uso é esse:

    mysqldump.exe -B -c –single-transaction –default-character-set=latin1 activedb -u root –password=”XXXXXX” -h localhost > activedb.sql

    Muito Obrigado.

    • marceloaltmann

      Fala Andre,

      por padrao, o mysql nao faz o dump das procedures,
      basta adicionar –routines
      Ficando assim o teu comando:
      mysqldump.exe -B -c –single-transaction –routines –default-character-set=latin1 activedb -u root –password=”XXXXXX” -h localhost > activedb.sql

      Abs.

  • Andre Luiz

    Boa tarde Marcelo, funcionou. Muito Obrigado.
    Você saberia me dizer como faço o backup somente das procedures? Ou me dizer onde encontro no seu blog? Dei uma olhada e não achei.
    Cara, já me ajudou d+.

    Obrigado

    • marceloaltmann

      Oi Andre,
      Desculpa pelo atraso (mais de 1 ano 🙂 )
      segue o comando para fazer backup somente das procedures:

      mysqldump –routines –no-data –no-create-info –no-create-db –skip-opt banco(ou –all-databases) > procedures.sql

  • Aldo Giordano

    Tenho um backup de todos os banco gerado através do comando:
    mysqldump -u usuario -p –all-databases > dump.sql
    Como faço para recuperar apenas um banco dentre os vários que estão no arquivo dump.sql ?
    Não quis arriscar executar:
    mysql -u usuario -p -B banco < dump.sql
    Será se isso funciona ?

    • marceloaltmann

      Aldo,
      Tens algumas opções:
      1. mysql -u usuario -p –one-database banco < dump.sql (mais detalhes aqui http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_one-database ) 2. sed -n '/^-- Current Database: `banco`/,/^-- Current Database: `/p' dump.sql.sql > banco.sql
      3. Restaurar todos os banco em uma nova instancia, e de la, copiar o banco necessário.

      A Opção 2 me agrada mais.

  • Gostaria de saber com os amigos. Sou consultor de Sistema e trabalho com o Mysql 5.0. O meu sistema ele trabalha em modulos, ou seja, financeiro, faturamento, atendimento etc. como eu faria para fazer backup via dump, por modulo ou seja, pegando todas as tabelas do financeiro, todas do faturamento, atendimento e criando backups separados para cada modulo, ou seja finaceiro.sql, faturamento.sql, atendimento.sql ??

    • marceloaltmann

      Você pode especificar qual banco e qual tabela deseja fazer o backup com o mysqldump:

      mysqldump [opções] banco tabela_1_financeiro > financeiro.sql
      mysqldump [opções] banco tabela_2_financeiro >> financeiro.sql

  • Diego

    Olá Marcelo.
    Queria saber como jogar o backup feito, para o Banco em outra máquina ?

    • marceloaltmann

      Olá Diego,

      Pode utilizar ferramentas como SCP ou FTP.

  • Claudio

    Bom dia, Marcelo, gostaria de saber onde fica salvo o dump feito, e se por a caso teria algum problema se eu fizesse um dump em um SO fedora e restaurasse ele em um SO debian, grato desde ja!

    • Marcelo Altmann

      Bom Dia Claudio.

      O backup vai ser salvo onde você redirecionar o “output” do comando mysqldump. Por exemplo, se você quer que ele seja salvo em /tmp/ vais digitar mysqldump [opções] > /tmp/backup.sql

      Quanto a portabilidade do arquivo não terás problema algum, visto que este arquivo contem somente comandos SQL.

      Abs

  • Edson Dario

    Marcelo, esse tipo de backup funciona em tempo de execução sem nenhum problema?

    • Marcelo Altmann

      Boa Tarde Edson,

      Se utiliza tabelas InnoDB consegue fazer ele de forma que não interfira com as outras transações adicionando a opção –single-transaction ao chamar o mysqldump. Caso tenha tabelas MyISAM ou outra storage engine, terás que fazer um lock das tabelas, o que irá imposibilitar escritas enquanto o backup estiver sendo gerado. Em termos de performance, o mysqldump é um cliente, ele vai se conectar no servidor ler todas as tabelas e escrever estes dados em um arquivo de texto, isso vai gerar uma certa carga no servidor (MySQL lendo os dados e mandando para o mysqldump) e vai notar também uma certa carga nos discos (IO) pois estes dados serão salvos em um arquivo de texto. O processo de compactação do backup também gera uma carga na CPU.

      Em resumo, sim, vais notar uma alteração de performance, pois os recursos vão estar sendo alocados no processo de backup, porém não é nada que irá impossibilitar outras transações de continuar operando no servidor. Se não está confortável em rodar isso em produção, sugiro rodar em um ambiente de teste primeiro.
      Outra possibilidade é ter mais de uma sessão aberta no servidor e monitorar a utilização de recursos. Se verificar que o processo de backup está alocando mais do que deve, ele pode ser cancelado a qualquer momento.

      Qualquer dúvida, posta aí de novo.

      • Edson Dario

        Poxa. Vlw mesmo. Obrigado pela atenção. Pensei em criar dois bancos. Na virada do dias mudo para o banco 2, faço backup do 1 e zero no mysql. No outro dia altero para o banco 1, faço backup do 2 e limpo ele no mysql. Estou montando um servidor de logs de conexão e acesso a equipamentos de uma infraestrutura.

  • Alexandre Alves

    Tenho um tabela com milhões de registros. Estou querendo dividi-la por ano. É possível um dump com consulta? E dump apenas de tabela?

    • Marcelo Altmann

      Alexandre,
      Dump apenas de uma tabela tu consegue fazer digitando:
      mysqldump [opções] banco tabela > tabela.sql

      Quanto ao dump seletivo, o mysqldump tem uma opção chamada –where , neste caso tu vai usar a mesma sintaxe de um comando where:

      mysqldump [opções] –where=’ano=2016′ banco tabela > tabela.sql
      Mais informações: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_where

      Qualquer dúvida, prende o grito aí.

      • Alexandre Alves

        Muito obrigado amigão.