O backup consistente do banco de dados é de extrema importância para que possamos manter a integridade dos dados caso haja uma falha do sistema, hardware ou até mesmo para corrigir eventuais falhas de usuários, como por exemplo, a remoção acidental de um banco de dados. Para isto, é importante a adoção de uma política consistente de backup (diariamente), bem como conhecer as possíveis técnicas para fazê-lo. No MySQL é possível fazermos backup binário do banco, isto é, será guardado uma cópia da estrutura de arquivos e diretórios que constituem os seus dos bancos de dados e tabelas. Além disto, pode-se optar pelo backup dos dados, onde serão armazenados os dados em formato texto ou em forma de comandos SQL. Vamos descrever aqui como utilizar estas duas formas de backup para a execução de uma cópia consistente de dados.
Ao realizar o procedimento de backup cria-se uma imagem dos seus dados no momento da execução da rotina de backup. Quando houver problemas com o seu banco de dados que necessite do backup, você pode utilizar o seu último backup retornando só os dados para a situação em que o banco se encontrava no momento deste backup. O que acontece com os dados alterados ou inseridos entre o backup e a falha? No MySQL você pode habilitar um log binário de alterações (opção log-bin no arquivo de configuração), que armazenam todos os comandos que modificam a estrutura do banco de dados, sendo que estes podem ser utilizados para recuperar os dados não contidos no backup. Os logs são criados com a extensão que indica o número de sequência do log, que é incrementado sempre que um novo log é criado. Para "traduzir" o log binário em comandos SQL, utilize a ferramenta mysqlbinlog, sendo que a saída deste poderá ser utilizada diretamente como entrada para o MySQL, como no exemplo:
shell>mysqlbinlog mysql-bin.012 | mysql
shell> mysqlbinlog mysql-bin.012 > dump.log
shell> mysql < dump.log
Estes comandos deverão ser executados após a restauração do backup. Para facilitar a manipulação do log na restauração, isto é, como identificar quais os comandos foram executados após o backup, é importante manter o sincronismo entre o log binário e o backup. Como os logs possuem um número sequencial, utilize o comando FLUSH LOGS para criar um novo arquivo de log no momento de backup. Assim, estará garantido que todas alterações após o backup serão armazenadas nos logs criados a partir deste momento. Na recuperação dos dados basta executartodos os logs a partir do momento do backup, uma vez que as alterações dos logs anteriores já estarão contidas no próprio backup.
Uma vez apresentada a utilização dos arquivos de log para restauração de dados, analisamos a primeira forma de backup que é baseada na cópia dos arquivos do banco de dados. Esta cópia pode ser feita manualmente com os comandos de cópia do sistema operacional (SO) ou utilizando ferramentas de backup do próprio SO. Vale ressaltar que para garantir uma cópia consistente destes arquivos é preciso garantir que não haverá escritas na base de dados durante a execução da rotina de backup. Esta condição pode ser garantida através de uma parada no gerenciador de banco de dados (SGBD) ou por meio de um bloqueio das tabelas permitindo apenas a leituras dos dados (lock) durante o backup. É possível fazer um backup online no MySQL, isto é, sem restrições de utilização do banco para escrita durante o processo de backup, mas estes métodos serão abordados no próximo artigo.
Para realizar o backup da estrutura física de tabelas MyISAM, basta copiar todos os arquivos MYI, MYD e frm. No caso de tabelas InnoDB, você deverá copiar os arquivos frm, todos os arquivos de log do InnoDB e os arquivos que constituem o seu tablespace, que são definidos na variável innodb_data_file_path colocada no arquivo de configuração do MySQL. Além disto, devem-se copiar o arquivo de configuração, já que o InnoDB consiste o tamanhos dos arquivos definidos na configuração com os arquivos existentes, em tempo de inicialização do SGBD, caso estes tamanhos não coincidam o SGBD não iniciará a sua execução. Para restaurar este tipo de backup, basta copiar os arquivos de volta para os seus locais de origem e aplicar os logs binários para recuperar os dados alterados entre o backup e o problema com o banco, conforme apresentado anteriormente.
Para tabelas MyISAM existem algumas ferramentas próprias do MySQL para a realização deste tipo de backup. O primeiro deles é o comando BACKUP TABLE, que copia os arquivos MYD e frm para o local especificado. Para restaurar o backup feito com o BACKUP TABLE, execute o comando RESTORE TABLE, que copiará os arquivos de volta e recriará os índices das tabelas, uma vez que os mesmos não são copiados pelo BACKUP TABLE. É importante ressaltar que antes de executar o RESTORE TABLE você terá que remover a tabela, já que o MySQL não sobrescreve arquivos por questões de segurança. Outra possibilidade é a utilização do script mysqlhotcopy, que bloqueia as tabelas para escrita durante a cópia dos arquivos, realiza a cópia dos arquivos para o local especificado e libera o lock das tabelas.
O principal problema do backup com cópia de arquivos é o fato de que caso existam arquivos corrompidos o seu backup herdará esta estrutura inconsistente, e possivelmente acarretará problemas durante a restauração da base de dados através deste backup. Para contornar esta situação é melhor que se faça uma cópia apenas dos dados e não dos arquivos. Este tipo de backup pode ser realizado através do comando SELECT .. INTO OUTFILE ou do cliente mysqldump. Para exportar os dados de uma tabela para um arquivo texto proceda da seguinte forma:
mysql>SELECT * INTO OUTFILE 'backup-t1.txt'
-> FIELDS TERMINATED BY ''
-> LINES TERMINATED BY '\n'
->FROM t1;Neste caso as colunas serão separadas por "tab" e o fim de linha será o caracter "\n", e a importação destes dados pode ser feita como a seguir:
mysql>LOAD DATA INFILE 'backup-t1.txt' INTO TABLE t1;A segunda forma para a exportação de dados é através do mysqldump que é uma ferramenta para a conversão da estrutura do banco de dados em comandos SQL. Existem muitas opções para a execução deste cliente, estas opções podem ser vistas executandoshell>mysqldump --help
O modo padrão de execução do mysqldump gera o comando CREATE TABLE para cada tabela e os comandos INSERTs para cada uma das linhas de dados da tabela. O exemplo a seguir ilustra a exportação de uma tabela via mysqldump:
shell>mysqldump banco1 tabela1 > backup-tabela1.sql
shell>cat backup-tabela1.sql
CREATE TABLE tabela1 ( id INT NOT NULL PRIMARY KEY,
nome CHAR(30) NOT NULL DEFAULT '' ) type=MyISAM;
INSERT INTO tabela1 VALUES (1, 'Teste 1');
INSERT INTO tabela1 VALUES (2, 'Teste 2');
INSERT INTO tabela1 VALUES (3, 'Teste 3');
INSERT INTO tabela1 VALUES (4, 'Teste 4');
INSERT INTO tabela1 VALUES (5, 'Teste 5');
...
No exemplo acima foi gerado o dump apenas de uma tabela, mas poderíamos obter o backup de todas as tabelas, informando somente o nome do banco, ou ainda gerar a estrutura para todos os bancos de dados através da opção --all-databases. Para restaurar o backup feito com o mysqldump basta executar o arquivo SQL gerado:
mysql>source backup-tabela1.sql;
ou ainda,
shell>mysql banco1 < backup-tabela1.sql;A vantagem do métodos de backup de cópia de arquivos sobre os de cópia de dados, é que os primeiros executam em um menor tempo, já que não há necessidade de ler a base de dados e converter os dados em texto ou comandos SQL. Por outro lado, a cópia de dados é mais segura, já que não há eventuais arquivos corrompidos, e além disto o backup gerado é portável uma vez que a saída gerada é compatível com vários outros SGBDs. No próximo artigo vamos examinar técnicas para a realização de backups online do banco de dados.
