MySQLDUMP - Aprenda a fazer backup's de sua base de dados

[Wagner Bianchi 0]

Olá pessoALL,

 

Após um tempo que postei o primeiro artigo sobre backup no MySQL, vejo que este foi de grande valia, visto que as visitas estão em um número considerável e, agradeço à todos pelo prestígio e saliento que este foi um feito pensando na comunidade IMASTERS.

 

Bom, nessa segunda parte, vou somente listar as variações para manipular backup's com o MySQLDUMP, utilizando o prompt (Windows) ou terminal (Unix-Like).

 

shell> mysqldump -u <usuario> -p<senha> --add-drop-table <nome_banco> > <nome_arquivo.sql>

Instrui o mysqldump a preceder a inserção em cada tabela com uma declaração DROP TABLE que apaga a tabela. Esta opção assegura que a operação de recarga remova qualquer cópia pré-existente da tabela antes de recriá-la.

 

shell> mysqldump -u <usuario> -p<senha> --all <nome_banco> > <nome_arquivo.sql>

(--all ou -a) Instrui o mysqldump a produzir declarações CREATE TABLE que incluam todas as opções específicas do MySQL (como o tipo de storage engine e comentários) com as quais cada tabela foi criada. Por padrão, o mysqldump não incluir todas essas opções, resultando em arquivos que poderiam ser mais portáveis para serem carregados com um SGBD que não seja o MySQL.

 

shell> mysqldump -u <usuario> -p<senha> --extended-insert <nome_banco> > <nome_arquivo.sql>

(--extended_insert ou --e) Por padrão, os dados são copiados para o arquivo de dump com INSERTs separados, o que seria natural e manteria o arquivo mais portável. Esta opção, produz INSERTs de múltiplas linhas e adiciona diversas linhas na tabela de cada vez. Declarções de múltiplas linhas podem ser carregadas com mais eficiência, embora sejam menos legíveis que declarações de uma única linha se você examinar a saída da carga. Menos portáveis, esse é o maior problema.

 

shell> mysqldump -u <usuario> -p<senha> --no-create <nome_banco> > <nome_arquivo.sql>

(--no-create ou -t) Geralmente, o mysql adiciona uma linha no início do arquivo de backup que é CREATE DATABASE. Com esta opção, você solicita ao mysqldump que não inclua no arquivo esta opção. Observe que a presença desta linha não será problema algum, pois, a mesma inclue, além do CREATE DATABASE os comandos IF NOT EXISTS, ou seja, caso seu banco de dados já se encontre criado, ele não será apagado nem criado novamente.

 

shell> mysqldump -u <usuario> -p<senha> --opt <nome_banco> > <nome_arquivo.sql>

A principal variação do comando e a mais usada, pois une o que o mysqldump tem de melhor. Utilizando o comando acima, você provocará uma saída derivada de outras variações como: --add-drop-table, --add-locks, --all, --quick, --extended-insert, --lock-tables e --disable-keys, ou seja, este é o backup mais completo e menos problemático para o restore.

 

shell> mysqldump -u <usuario> -p<senha> --all-databases <nome_banco> > <nome_arquivo.sql>

Para finalizar, esse é o backup de todo o servidor, gerando um arquivão com todos os banco de dados.

 

http://forum.imasters.com.br/public/style_emoticons/default/excl.gif FORMATO XML (Adicionado em 19/10/2007)

 

Pessoal, não há de se negar a portabilidade da linguagem XML e o MySQL tem dentre os vários recursos de trabalhos com XML, gerar um arquivo de backup nesse formato. Não tenho certeza desde que versão do 5.0 est recurso está disponível, mas, creio que seja do 5.0 ou posterior, eu confirmo isso aqui neste artigo em breve. Sem mais delongas:

 

shell> mysqldump -u <usuario> -p<senha> --xml <nome_banco> > <nome_arquivo.xml>

Após gerar o arquivo, abra o mesmo co um clique duplo...o browser será solicitado. http://forum.imasters.com.br/public/style_emoticons/default/shifty.gif

 

OBS.: Como a escrita do arquivo de DUMP gerado pelo MySQLDUMP é sequencial e os comandos acima podem ser emitidos com intensão de fazer um backup on-line, algumas alterações podem não estar presentes neste arquivo. Sendo assim, aconselha-se utilizar a opção -F para que o SGBD crie mais um arquivo de log (bin-log.00009 por exemplo), diferente do atual e guarde nele todas as novas alterações, desde o início do backup. Após o término do backup, sabemos que as alterações ocorridas durante o backup com o MySQLDUMP estão no novo arquivo de log que geramos (o bin-log.00009) que será um arquivo que teremos que copiar juntamente com aquele gerado pelo MySQLDUMP.

 

Para se copiar o arquivo, que no nosso exemplo tem o nome bin-log.000009, emita o seguinte comnado:

 

Linux:

shell> mysqlbinlog /caminho/mysql-bin.000010 > backup.log

Windows:

shell> mysqlbinlog \caminho\mysql-bin.000010 > backup.log

Após copiarmos os dois arquivos, podemos gerar um novo arquivo de log, à partir do prompt ou terminal, com a seguinte sintaxe:

 

shell> mysql -u <usuario> -p<senha> -e "FLUSH LOGS"

...esse comando criará um novo arquivo de log (bin-log.000010) que dará continuidade no registro de instruções DDL, INSERT, UPDATE e DELETE que alteram a estrutura e dados dos bancos de dados contindos no MySQL.

 

http://forum.imasters.com.br/public/style_emoticons/default/excl.gif BACKUP DE STORED ROUTINES (PROCEDURES E FUNCTIONS) (Adicionado em 14/11/2007)

 

Caso precisemos copiar todas as stored routines que há em nosso banco de dados, basta acrescentarmos em meio ao comando a opção --routine ou -R. As Stored Routines do seu banco de dados serão copiadas no arquivos de backup gerado, ao final.

 

Para efetuar o restore, utilize o próprio mysql client ou mysql monitor, da seguinte forma:

 

shell> mysql -u <usuario> -p<senha> <nome_do_banco> < nome_arquivo.sql

O restore acima foi para restaurar um banco de dados específico, caso seja para todos os bancos de dados contidos no servidor MySQL (muito cuidado ao substituir o bancos de dados mysql), segue o comando:

 

shell> mysql -u <usuario> -p<senha> < nome_arquivo.sql

Um abraço à todos e caso queiram, façam seus comentários que será muito interessante falar sobre o assunto!!

 

Happy MySQL'In!! http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

[jotace 0]

Olá Wagner,

 

Estou trabalhando com monitoramento de rede via Cacti, por orientação dos desenvolvedores da aplicação, fiz um backup do BD utilizando o seguinte comando:

# mysqldump -l --add-drop-table cacti > mysql.cacti -p

... gostaria de saber seguindo a sequência de comandos que você sugere, se é possível recuperar este BD? É necessário incluir a opção --add-drop-table?

[Wagner Bianchi 0]

Olá Wagner,

 

Estou trabalhando com monitoramento de rede via Cacti, por orientação dos desenvolvedores da aplicação, fiz um backup do BD utilizando o seguinte comando:

# mysqldump -l --add-drop-table cacti > mysql.cacti -p

... gostaria de saber seguindo a sequência de comandos que você sugere, se é possível recuperar este BD? É necessário incluir a opção --add-drop-table?

O restore do banco de dados a partir do arquivo que você gerou com o comando mysqldump com a opção --add-drop-tables é realizado da mesma forma como qualquer outro.

 

mysql -u <usuario> -p<senha> <nome_banco> < <nome_arquivo>

Adicionando a opção --add-drop-tables você somente adiciona ao arquivo de backup criado pelo mysqldump a opção DROP TABLE para cada tabela e antes da excução do CREATE TABLE para cada tabela, ou seja, para cada CREATE TABLE do modelo físico de banco de dados, um comando que apaga a tabela será executado para recriá-la na sequência. Isto é bastante interessante para bancos de dados que tem tabelas controladas pelo Storage Engine InnoDB - técnicas de rebuild.

 

Qualquer dúvida, continue o post.

 

Happy MySQL'ing! http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

[karper1.2 0]

É o seguinte;

 

Sou iniciante em BD e PHP e usava o EasyPHP na minha máquina. Porem as versões de PHP e MySQL eram mais antigas, achei um tópico aqui

http://forum.imasters.com.br/index.php?showtopic=291630

onde me ensinou a instar separadamente as versões mais novas...

A dúvida é a seguinte:

 

Quando entro no phpmyadmin fica a seguinte nota em baixo na tela: Sua versão 5.0.51a da biblioteca MySQL do PHP difere da versão 5.1.32 do seu servidor MySQL. Isso pode causar um comportamento estranho..

 

Isso pode atrapalhar no funciomento no BD?

E como eu faço para colocar os bancos de dados antigos que eu já tinha com essa nova instalação? Vou ter que fazer tudo de novo?

 

Att

 

Renato

[Wagner Bianchi 0]

Faz um backup de todo o servidor (todos os bancos de dados), arruma o seu servidor (host) instalando novamente os softwares que paraceme estar com problema e faça o restore do backup feito anteriormente.

 

Informações sobre Banckup e Restore estão acima.

 

Happy MySQL'ing!! http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif