Backup no MySQL com MySQLDUMP – PARTE 1

Motivado pela importância dada à assuntos relacionados à backup, resolvi escrever esse artigo para referenciar um utilitário de grande valor dentre os muitos utilitários que são disponibilizados pelo MySQL durante a sua instalação. Basicamente, este artigo se concentrará em exibir como utilizar o MySQLDUMP em linha de comando, assim como algumas de suas variações para termos melhor proveito em determinadas situações, como fazer backup de todos os bancos do servidor, de um banco de dados específico ou de tabelas de uma banco de dados específico. Ao final, faremos uma restauração ou restore de um dos backups.

Introdução

É bem verdade que em muitos cursos e estudos que são encontrados pela internet, muito se falam sobre políticas e estratégia de backup, mas na realidade, são poucos os profissionais que se preocupam com o fato de que problemas podem acontecer e em momentos muito complicados. Quando você menos espera, o banco de dados pede sua atenção para uma recuperação de dados, dados são excluídos erroneamente ou mesmo corrompidos. O MySQLDUMP é um utilitário, uma ferramenta padrão do MySQL para efetuar backup’s lógicos disponibilizado na sua instalação.

O Utilitário

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 texto, chamados de DUMP. É ú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.

Funcionamento

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 utilizaremos nos exemplos.

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 ao efetuarmos o backup. Isso garante que um novo log seja criado e no caso de um backup on-line, as informações concorrentes ao backup sejam colocadas em logs separados, ou seja, além do backup você ainda terá os logs e poderá sincronizá-los no momento do restore. A leitura de logs para sincronia será apresentada em um outro artigo, onde falaremos do utilitário MySQLBINLOG.

mysql> flush logs;

Abra o prompt ou terminal (Linux/Unix) e digite o seguinte comando:

shell> 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:

--
-- 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 um banco de dados, que tal copiar 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 se torna 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 para 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 backup 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 também.

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:

shell> mysqldump -u <usuario> -p<senha> mysql user > mysqluser.sql
            Após o comando, verifique o arquivo, seria algo como:

--
-- 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 seu eu quiser copier para o backup mais de uma tabela?"  E só você digitar os nomes das tabelas em sequência como no exemplo abaixo:

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

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. Basta que redirecionemos a entrada padrão para o mysql, como segue:

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

Conclusão

Esta foi uma apresentação de como funciona o utilitário MySQLDUMP, disponibilizado durante a instalação do MySQL. No próximo artigo, comentarei sobre as opções e variações do comando MySQLDUMP.

Happy MySQL’ing!