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

[Wagner Bianchi 0]

Olá pessoal,

 

Motivado pelo interesse demonstrado pelos usuários do fórum em assuntos relacionados à backup, resolvi escrever esse artigo para refereciar um utilitário de grande valor dentre os muitos utilitários que são disposinibilizados pelo MySQL quando sua instalação é feita em servidores Windows e Linux. Basicamente, este artigo se concentrará em mostrar a todos como utilizar o mysqldump em linha de comando, assim como algumas de suas variações para termos melhor proveito em determinadas situações.

 

INTRODUÇÃO

 

É bem verdade que todo candidato à analista de sistemas e muitos desenvolvedores, ao ouvir falar quem um cliente deseja um determinado sistema, nem pensam em estruturar o software com um planejamento de administração do ambiente ao qual se propõem a implementar. Chamo atenção para isso pois, a a mioria dos desenvolvedores não se preocupam muito em difinições para o projeto, tais como um Modelo de Dados bem definido utilizando uma ferramenta CASE ou mesmo, armazenando aa definições do sistema em um dicionários de dados. Saliento isso, pois, a partir daí que já começamos um trabalho bem sucedido no aspecto ciclo de vida do software, pois, se num determinado ponto do trabalho o banco de dados que estamos esboçando, por algum motivo, parar de funcionar? Você "senta e chora"?'Não, não pode ser bem assim...

 

No início você tem o modelo, mas o modelo não é o principal após 10 anos de funcionamento do sistema, aí que entram as ferramentas disponibilizados pelos fabricantes dos SGBD`s ou por terceiros.

 

MySQLDUMP

 

Assim como o ORACLE apresenta o seu dinâmico RMAN, o MySQL traz consigo o MySQLDUMP, que nada mais é que uma ferramenta que nos dá possibilidade de copiar os bancos de dados que estão dentro do SGBD MySQL. O Funcionamento deste é algo bem simples, é um programa cliente que coloca o conteúdo de tabelas em arquivos. é útil para fazer cópias de segurança de bancos de dados ou transferir conteúdos de bancos de dados para outro servidor.

 

O MySQLDUMP pode produzir arquivos no formato SQL que contenham declarações CREATE TABLE e INSERT para recriar os arquivos ou para produzir arquivos de dados delimitados por tabulações.

 

COMO FUNCIONA O MySQLDUMP?

 

Com este utilitário, você pode copiar, uma ou mais tabelas, um banco de dados inteiro ou todos os bancos de dados do servidor. Lembrando que este funciona em linha de comando e é o que utilizarremos aqui.

 

http://forum.imasters.com.br/public/style_emoticons/default/excl.gif ATENÇÃO

 

Um detalhe interessante para casos que ocorram acidentes com o hardware ou qualquer outro problema e também para minimizar a perda de informações, é interessante que o comando FLUSH LOGS seja emitido após efetuarmos o backup. Isso garante que um novo log seja criado e terá os dados do momento imediatamente após o backup, ou seja, além do backup você ainda terá os logs e poderá sincronizá-los no momento do restore.

 

mysql> flush logs;

Abra o prompt (Windows, lembre-se, chama prompt e não DOS como chamam!) ou terminal (Linux/Unix) e digite o seguinte comando:

 

mysqldump -u <usuario> -p<senha> mysql > mysql.sql

 

...o comando acima, copia o banco de dados mysql para o diretório raiz do usuário atual do sistema operacional. Para verificarmos a existência do arquivo e o seu conteúdo, no windows, digite "dir" para listar os arquivos e em seguida "edit mysql.sql" , no Linux, ls mysql.sql e em seguida "cat mysql.sql". O conteúdo do arquivo será algo como:

 

-- MySQL dump 10.10

--

-- Host: localhost Database: mysql

-- ------------------------------------------------------

-- Server version 5.0.18-nt-log

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `columns_priv`

--

 

DROP TABLE IF EXISTS `columns_priv`;

CREATE TABLE `columns_priv` (

`Host` char(60) collate utf8_bin NOT NULL default '',

`Db` char(64) collate utf8_bin NOT NULL default '',

`User` char(16) collate utf8_bin NOT NULL default '',

`Table_name` char(64) collate utf8_bin NOT NULL default '',

`Column_name` char(64) collate utf8_bin NOT NULL default '',

`Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`Column_priv` set('Select','Insert','Update','References') character set utf8 NOT NULL default '',

PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';

 

--

-- Dumping data for table `columns_priv`

--

...podem existir pequenas diferenças no conteúdo, mas seria mais que normal.

 

Bom, agora que já fizemos backup de uma banco de dados, que tal copiarmos uma tabela em especial. Só um comentário antes.

 

Algumas vezes, quando temos tabelas grandes no sistema, tabelas estas que recebem muitas exclusões e atualizações, o acesso a estas começa a ser lento, causando muitos problemas para o usuário final ou mesmo operadores do sistema. Existem outras formas de ser reorganizar as páginas de dados no MySQL e em outros SGBD's como o ORACLE, mas, se isso precisa ser feito logo e você não tem muito tempo pata tunar e nem estudar os caminhos de otimização para uma boa performance de recuperação de dados nessa tabela específica, DROP TABLE dá um jeito, mas antes!!!!!!! não esqueça de fazer o bakcup, desabilitar as chves com um

 

ALTER TABLE <tabela> DISABLE KEYS;

 

e depois dropar a refererida tabela. Seu telefone rapidamente tocará, mas diga aos navegantes que o sistema está sendo restaurado e não deixe de atender aos telefonemas, tirar o telefone do gancho pode tirar seu emprego.

 

Bom, sem mais delongas, vamos ao backup da tabela!!! Abra no prompt ou terminal, digite o seguinte comando, que fará backup da tabela User do banco de dados MySQL:

 

mysqldump -u <usuario> -p<senha> mysql user > mysqluser.sql

 

...após o comando, verifique o arquivo, seria algo como:

 

-- MySQL dump 10.10

--

-- Host: localhost Database: mysql

-- ------------------------------------------------------

-- Server version 5.0.18-nt-log

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `user`

--

 

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`Host` char(60) collate utf8_bin NOT NULL default '',

`User` char(16) collate utf8_bin NOT NULL default '',

`Password` char(41) character set latin1 collate latin1_bin NOT NULL default '',

`Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Reload_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Shutdown_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Process_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`File_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`References_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Show_db_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Super_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Repl_slave_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Repl_client_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Create_user_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`ssl_type` enum('','ANY','X509','SPECIFIED') character set utf8 NOT NULL default '',

`ssl_cipher` blob NOT NULL,

`x509_issuer` blob NOT NULL,

`x509_subject` blob NOT NULL,

`max_questions` int(11) unsigned NOT NULL default '0',

`max_updates` int(11) unsigned NOT NULL default '0',

`max_connections` int(11) unsigned NOT NULL default '0',

`max_user_connections` int(11) unsigned NOT NULL default '0',

PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

 

--

-- Dumping data for table `user`

--

Mas aí a pergunta que não quer calar: "E SE EU QUISER COPIAR MAIS UMA TABELA??" :unsure:

 

E só você digitar os nomes das tabelas em sequência como no exemplo abaixo: http://forum.imasters.com.br/public/style_emoticons/default/shifty.gif

 

mysqldump -u <usuario> -p<senha> mysql user db func > mysqltables.sql

 

EUREKA!! Agora não precisamos mais copiar os arquivos fisicamente, já sabemos trabalhar com backup's no MySQL e não teremos problemas com tabelas InnoDB, pois se você as copia fisicamente, provavelmente você terá problemas para acessá-las no próximo servidor destino!!

 

Bom, vamos restaurar nosso backup? http://forum.imasters.com.br/public/style_emoticons/default/assobiando.gif

 

Basta que redirecionemos a entrada padrão para o mysql, como segue:

 

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

 

PRONTO!!!

 

No próximo artigo, comentarei sobre as opções e variações do comando mysqldump!! Quem tiver alguma dúvida com backup, post aqui neste tópico que vamos trocando umas idéias sobre!!

 

Até a próxima! http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

[Alex Passos 0]

Olá Wagner

 

Eu testei seu exemplo.... ele cria o arquivo na raiz só que cria um arquivo vazio com 0k

 

eu coloquei em um arquivo .bat os comandos

 

@ECHO OFF

 

SET mysqldir=C:\Program Files\MySQL\MySQL Server 5.0\bin

CD %mysqldir%

 

if not exist "C:\backup" mkdir "C:\backup"

SET dirback=C:\backup

 

SET mysqluser=root

SET mysqlpassword=123

 

mysqldump -u %mysqluser% -p%mysqlpassword% --databases virtualnet >%dirback%\backup_bd.sql

 

só que esta ficando do mesmo jeito.... o arquivo vazio com 0k

 

O que pode ser?

 

Obrigado

[Juliano Meinen 0]

Perfeito!

 

Simples e rápido, sem complicações!

 

vlw !!!

 

http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

[Arture 0]

Ola gostei muito da sua dica. Mas na ultima fez eu estava usando um servidor e precisei desistalar ele e instalar de novo. Agora agrupado com apache, php e mysql. Eu simplesmente recortei as pastas do banco de dados do servidor antigo agora ja desistalado e colei de novo no novo servidor. Mas quando eu fui ver o arquivo deu erro. Não me lembro muito bem o erro mas era mais ou menos table não encontrada. Se eu fizer o que você montou nesse artigo ja estarei protegido desse possivel erro? O que pode ter acontecido sera que o novo servidor não entrava por que o novo usuario era diferente do usuario que eu coloquei primeiro? Deste ja agradeço. http://forum.imasters.com.br/public/style_emoticons/default/grin.gif

[Wagner Bianchi 0]

Ola gostei muito da sua dica. Mas na ultima fez eu estava usando um servidor e precisei desistalar ele e instalar de novo. Agora agrupado com apache, php e mysql. Eu simplesmente recortei as pastas do banco de dados do servidor antigo agora ja desistalado e colei de novo no novo servidor. Mas quando eu fui ver o arquivo deu erro. Não me lembro muito bem o erro mas era mais ou menos table não encontrada. Se eu fizer o que você montou nesse artigo ja estarei protegido desse possivel erro? O que pode ter acontecido sera que o novo servidor não entrava por que o novo usuario era diferente do usuario que eu coloquei primeiro? Deste ja agradeço. http://forum.imasters.com.br/public/style_emoticons/default/grin.gif

Copiar e colar é muito problemático, visto que, a versão do MySQL pode ser diferente, o banco de dados mysql pode ser diferente de uma versão para a outra, o tipo de instalação pode ser diferente, enfim, muitos problemas podem acontecer quando se copia fisicamente as pastas do MySQL com finalizadade de se fazer backup. Com todo respeito, isso é um serviço porco.

 

Aplicando o backup com a ferramenta mysqldump você escapa desse risco tendo um backup de forma que você não precisará mexer na estrutura físcia do SGBD. Com tabelas MyISAM você tem portabilidade, mas mesmo assim, arrisca-se muito em copiar, com tabela INNODB, nem pense em copiar os arquivos, faça um dump com a ferramenta mysqldump.

 

Abração. http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

[Waltair Santos 0]

Ola gostei muito da sua dica. Mas na ultima fez eu estava usando um servidor e precisei desistalar ele e instalar de novo. Agora agrupado com apache, php e mysql. Eu simplesmente recortei as pastas do banco de dados do servidor antigo agora ja desistalado e colei de novo no novo servidor. Mas quando eu fui ver o arquivo deu erro. Não me lembro muito bem o erro mas era mais ou menos table não encontrada. Se eu fizer o que você montou nesse artigo ja estarei protegido desse possivel erro? O que pode ter acontecido sera que o novo servidor não entrava por que o novo usuario era diferente do usuario que eu coloquei primeiro? Deste ja agradeço. :grin:

Copiar e colar é muito problemático, visto que, a versão do MySQL pode ser diferente, o banco de dados mysql pode ser diferente de uma versão para a outra, o tipo de instalação pode ser diferente, enfim, muitos problemas podem acontecem quando se copia fisicamente as pastas do MySQL com finalizadade de se fazer backup. Com todo respeito, isso é um serviço porco.

 

Aplicando o backup com a ferramenta mysqldump você escapa desse risco tendo um backup de forma que você não precisará mexer na estrutura físcia do SGBD. Com tabelas MyISAM você tem portabilidade, mas mesmo assim, arrisca-se muito em copiar, com tabela INNODB, nem pense em copiar os arquivos, faça um dump com a ferramenta mysqldump.

 

Abração. :thumbsup:

 

Caros colegas!

 

Gostaria de uma dica de vocês!

 

Tenho uma aplicação em produção utilizando o banco de dados mysql, tudo está perfeito (quanto ao funcionamento do banco), porém ao rodar o backup (através de um script as 23hs - todos os dias) utilizando o mysqldump, o load do servidor sobe de uma tal forma que o servidor simplesmente pára* quando o backup está em execução, segue abaixo a linha que estou utilizando para realizar o backup:

 

nice -n 19 mysqldump -e --default-character-set=latin1 --user=backup --password=xxxxxxxx bancox > bancox.sql

 

Por acaso vocês sabem de alguma opção que eu possa adicionar no my.cf nas opções do mysqldump, meu arquivo está conforme abaixo atualmente:

[mysqldump]

quick

quote-names

max_allowed_packet = 16M #Comment: Hoje vou aumentar o tamanho do pacote permitido para ver se resolve

 

 

Note:

* O backup com mysqldump é realizado com sucesso, porém o servidor fica parado enquanto o backup está em andamento.

 

Agradeço todas as dicas

[zenfra 0]

Olá Wagner,

 

Obirgado pelas dicas de backup e a forma como das os restore posteriormente.

 

Abraços

[jpbraga 0]

Então..

 

Eu tenho um arquivo .sql com backup de todas as minhas bases (--all-databases)

 

Estou precisando de restaurar o backup de apenas uma base específica, como faço?

 

ja tentei colocar os parametros -D database_name e não consegui.. ele restaura tudo mesmo assim!