MySQL compress backup – mysqldump – gzip – bzip2

Today let’s talk about a very important issue for database administrators, BACKUP.
There are several strategies for backup, snapshot, dump, dump binlog, it all depends on your need and the size of your database. I’ll explain a little about the tool provides by MySQL, which is the mysqldump , I indicate this tool for databases that have 15gb/20gb even more than that, the probability that you have problems to restore a backup and long delay, make it unfeasible.
How mysqldump works? He does nothing but create sql commands to our structure and data and put them in the file specified.
First, I will explain some of the most important options mysqldump then, for those who have MySQL installed on a Linux server, I will teach how to compress the dump in real time.

    Making backup all databases:

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

    Making backup only one database:

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

    Making backup many databases:

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

    Making backup with triggers:

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

    Making backup with

procedures and functions:

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

    Now let’s compress our dump in real time with

gzip:

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

    We can still reach a higher compression ratio using the

bzip2:

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

Making a comparison between the dump with three compression options (none, gzip and bzip2) had the following result:

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

And how do I restore the 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

More details about mysqldump can be found on MySQL Online Documentation, see you!!!

MySQL by Marcelo Altmann

MySQL by Marcelo Altmann

20 thoughts on “MySQL compress backup – mysqldump – gzip – bzip2

  1. 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.

    1. 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!

  2. 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.

    1. 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.

  3. 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

    1. 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

  4. 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 ?

    1. 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.

  5. 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 ??

    1. 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

  6. 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!

    1. 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

    1. 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.

      1. 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.

    1. 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í.

Leave a Reply

Your email address will not be published. Required fields are marked *