Os dois métodos principais para fazer backup do banco de dados são usar o programa mysqldump ou copiar diretamente o arquivo do banco de dados (como usar cp, cpio ou tar, etc.). Este artigo detalha o plano de backup do banco de dados da plataforma MySQL. Caso uma tabela do banco de dados seja perdida ou corrompida, é importante fazer backup do seu banco de dados. Se ocorrer uma falha no sistema, você definitivamente desejará restaurar suas tabelas para o estado em que estavam quando a falha ocorreu, com a menor perda de dados possível. Às vezes, é o administrador do MySQL quem causa estragos. O administrador já sabe que as tabelas estão corrompidas e tentar editá-las diretamente usando um editor como vi ou Emacs definitivamente não é uma coisa boa para as tabelas.
Os dois métodos principais para fazer backup do banco de dados são usar o programa mysqldump ou copiar diretamente o arquivo do banco de dados (como usar cp, cpio ou tar, etc.). Cada método tem seus prós e contras:
mysqldump funciona com servidor MySQL. O método de cópia direta é executado fora do servidor e você deve tomar medidas para garantir que nenhum cliente modifique a tabela que você está copiando. Se você quiser usar o backup do sistema de arquivos para fazer backup do banco de dados, o mesmo problema ocorrerá: se a tabela do banco de dados for modificada durante o processo de backup do sistema de arquivos, o assunto do arquivo da tabela de backup será inconsistente e a tabela será sem sentido para recuperação futura. A diferença entre um backup do sistema de arquivos e uma cópia direta do arquivo é que com esta última você tem controle total sobre o processo de backup, para que possa tomar medidas para garantir que o servidor deixe a tabela intacta.
mysqldump é mais lento que a cópia direta.
mysqldump gera arquivos de texto que são portáveis para outras máquinas, mesmo aquelas com arquiteturas de hardware diferentes. A cópia direta de arquivos não pode ser portada para outras máquinas, a menos que a tabela que você está copiando use o formato de armazenamento MyISAM. As tabelas ISAM só podem ser copiadas em máquinas com estruturas de hardware semelhantes. O formato de armazenamento de tabelas MyISAM introduzido no MySQL 3.23 resolve esse problema porque o formato é independente da máquina, portanto, a cópia direta do arquivo pode ser transplantada para máquinas com diferentes estruturas de hardware. Desde que duas condições sejam atendidas: a outra máquina também deve estar executando o MySQL 3.23 ou posterior, e o arquivo deve ser representado no formato MyISAM, não no formato ISAM.
Não importa qual método de backup você utilize, se precisar restaurar seu banco de dados, existem vários princípios que devem ser seguidos para garantir os melhores resultados:
Implemente backups regulares. Estabeleça um plano e cumpra-o.
Deixe o servidor realizar o registro de atualização. O changelog irá ajudá-lo quando você precisar recuperar dados após uma falha. Depois de usar o arquivo de backup para restaurar os dados ao estado em que estavam no momento do backup, você poderá reaplicar as alterações feitas após o backup executando uma consulta no log de atualização, que restaurará as tabelas no banco de dados para o estado em que se encontravam quando ocorreu o acidente.
Em termos de backup do sistema de arquivos, o arquivo de backup do banco de dados representa um dump completo, enquanto o log de atualização representa um dump incremental.
Use um esquema de nomenclatura consistente e compreensível para arquivos de backup. Coisas como backup1, buckup2, etc. não são particularmente significativas. Ao realizar sua recuperação, você perderá tempo tentando descobrir o que há nos arquivos. Talvez seja útil usar o nome e a data do banco de dados para formar o nome do arquivo de backup. Por exemplo:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
% mysqldump zoológico >/usr/archives/mysql/menagerie.1999-10-02
Você pode querer compactar os backups depois de gerá-los. Os backups tendem a ser grandes! Você também precisa expirar seus arquivos de backup para evitar que eles ocupem seu disco, assim como expira seus arquivos de log.
Faça backup de seus arquivos de backup com um backup do sistema de arquivos. Se você sofrer uma falha completa que não apenas limpe seu diretório de dados, mas também a unidade de disco que contém os backups de seu banco de dados, você terá sérios problemas.
Faça também backup do seu changelog.
Coloque seus arquivos de backup em um sistema de arquivos diferente daquele usado para seu banco de dados. Isso reduzirá a possibilidade de preenchimento do sistema de arquivos que contém o diretório de dados como resultado da geração do backup.
As técnicas utilizadas para criar backups também são úteis para copiar um banco de dados para outra máquina. Mais comumente, um banco de dados é movido para um servidor em execução em outro host, mas você também pode mover os dados para outro servidor no mesmo host.
1 Use mysqldump para fazer backup e copiar o banco de dados
Quando você usa o programa mysqldumo para gerar um arquivo de backup de banco de dados, por padrão, o conteúdo do arquivo contém a instrução CREATE que cria a tabela que está sendo despejada e a instrução INSERT que contém os dados da linha na tabela. Em outras palavras, a saída produzida pelo mysqldump pode posteriormente ser usada como entrada para o mysql para reconstruir o banco de dados.
Você pode despejar todo o banco de dados em um único arquivo de texto da seguinte maneira:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
O início do arquivo de saída é assim:
# MySQL Dump 6.0##Host: localhost Banco de dados: samp_db
#-----------------------------------------------------#
Versão do servidor 3.23.2-alpha-log## Estrutura da tabela para ausência de tabela
#CREATE TABLE ausência( student_id int(10) unsigned DEFAULT 0 NOT NULL, data data DEFAULT 0000-00-00 NOT NUL L,
CHAVE PRIMÁRIA (student_id,data));## Despejando dados para ausência de tabela #INSERT INTO ausência VALUES (3,1999-09-03);INSERT INTO ausência VALUE S (5,1999-09-03);INSERT INTO ausência VALUES (10,1999-09-08);......
O restante do arquivo consiste em mais instruções INSERT e CREATE TABLE. Se você deseja compactar o backup, use um comando semelhante ao seguinte:
%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz
Se você tiver um banco de dados grande, os arquivos de saída também serão grandes e poderão ser difíceis de gerenciar. Se desejar, você pode listar os nomes das tabelas individuais após o nome do banco de dados na linha de comando mysqldump para despejar seu conteúdo, o que dividirá o arquivo de despejo em arquivos menores e mais gerenciáveis. O exemplo a seguir mostra como despejar algumas tabelas do banco de dados samp_db em arquivos separados:
%mysqldump samp_db ausência do evento de pontuação do aluno> grapbook.sql
%mysqldump presidente membro do samp_db >hist-league.sql
Se você estiver gerando arquivos de backup destinados a serem usados para atualizar periodicamente o conteúdo de outro banco de dados, você pode usar a opção --add-drop-table. Isso instrui o servidor a gravar a instrução DROP TABLE IF EXISTS no arquivo de backup e, então, ao pegar o arquivo de backup e carregá-lo no segundo banco de dados, você não receberá um erro se a tabela já existir.
Se você fizer dump de um banco de dados para poder movê-lo para outro servidor, nem precisará criar um arquivo de backup. Certifique-se de que o banco de dados exista em outro host e, em seguida, use um canal para despejar o banco de dados para que o mysql possa ler diretamente a saída do mysqldump. Por exemplo: você deseja copiar o banco de dados samp_db do host pit-viper.snake.net para boa.snake.net. Você pode fazer isso facilmente assim:
% mysqladmin -h boa.snake.net cria samp_db
%mysqldump samp_db | mysql -h boa.snake.net samp_db
No futuro, se você quiser atualizar o banco de dados em boa.snake.net novamente, pule o comando mysqladmin, mas adicione --add-drop-table ao mysqldump para evitar o erro de que a tabela já existe:% mysqldump --add- tabela suspensa samp_db | mysql -h boa.snake.net samp_db |
Outras opções úteis do mysqldump incluem: A combinação --flush-logs e --lock-tables será útil para verificar seu banco de dados. --lock-tables bloqueia todas as tabelas que você está despejando e --flush-logs fecha e reabre o arquivo de log de atualização. O novo log de atualização incluirá apenas consultas que modificaram o banco de dados do ponto de backup. Isso definirá o tempo de backup do ponto de verificação do log de atualização. (No entanto, se você tiver clientes que precisam realizar uma atualização, bloquear todas as tabelas não é uma boa ideia para acesso do cliente durante o backup.)
Se você usar --flush-logs para fazer checkpoint em um backup, provavelmente é melhor despejar todo o banco de dados.
Se você despejar arquivos separados, será mais difícil sincronizar os pontos de verificação do log de atualização com os arquivos de backup. Durante a recuperação, você geralmente extrai o conteúdo do log de atualização por banco de dados. Não há opção para extrair atualizações para tabelas individuais, portanto, você mesmo deve extraí-las.
Por padrão, o mysqldump lê todo o conteúdo de uma tabela na memória antes de escrever. Isso geralmente é realmente desnecessário e, na verdade, é quase um fracasso se você tiver uma mesa grande. Você pode usar a opção --quick para dizer ao mysqldump para escrever cada linha sempre que recuperar uma. Para otimizar ainda mais o processo de vazamento, use --opt em vez de --quick. A opção --opt ativa opções adicionais para acelerar o despejo de dados e sua leitura.
Implementar backups com --opt é provavelmente o método mais comum devido à vantagem de velocidade dos backups. No entanto, esteja avisado, a opção --opt tem um custo. --opt otimiza seu processo de backup, e não o acesso de outros clientes ao banco de dados. A opção --opt evita que alguém atualize qualquer tabela que você esteja descarregando, bloqueando todas as tabelas de uma vez. Você pode ver facilmente o efeito no acesso geral ao banco de dados. Quando seu banco de dados geralmente é usado com muita frequência, basta ajustar o backup uma vez por dia.
Uma opção que tem o efeito oposto de --opt é --dedayed. Esta opção faz com que o mysqldump escreva instruções INSERT DELAYED em vez de instruções INSERT. --delayed é útil se você estiver carregando um arquivo de dados em outro banco de dados e quiser que esta operação tenha impacto mínimo nas consultas que podem aparecer nesse banco de dados.
A opção --compress é útil quando você copia o banco de dados para outra máquina porque reduz o número de bytes transferidos pela rede. Aqui está um exemplo. Observe que --compress é fornecido para programas que se comunicam com um servidor em um host remoto, não para programas que se conectam ao host local:
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump tem muitas opções, consulte o "Manual de Referência do MySQL" para obter detalhes.
2 Método de backup e cópia usando banco de dados de cópia direta
Outra maneira de fazer backup do banco de dados e das tabelas que não envolve o mysqldump é copiar diretamente os arquivos da tabela do banco de dados. Normalmente, isso é feito usando utilitários como cp, tar ou cpio. Os exemplos neste artigo usam cp.
Ao usar um método de backup direto, você deve garantir que a tabela não esteja mais em uso. Se o servidor alterar uma tabela enquanto você a copia, a cópia não terá sentido.
A melhor maneira de garantir a integridade da sua cópia é desligar o servidor, copiar os arquivos e reiniciar o servidor. Se não quiser desligar o servidor, bloqueie-o enquanto executa a verificação da tabela. Se o servidor estiver em execução, as mesmas restrições se aplicam à cópia de arquivos e você deverá usar o mesmo protocolo de bloqueio para "silenciar" o servidor.
Supondo que o servidor esteja inativo ou que você tenha bloqueado a tabela que deseja copiar, veja a seguir como fazer backup de todo o banco de dados samp_db em um diretório de backup (DATADIR representa o diretório de dados do servidor): %cd DATADIR%cp -r samp_db /usr /arquivo/mysql
Uma única tabela pode ter backup da seguinte maneira:
%cd DATADIR/samp_db%cp membro.* /usr/archive/mysql/samp_db%cp pontuação.* /usr/archive/mysql/samp_db ....
Ao concluir o backup, você pode reiniciar o servidor (se você desligá-lo) ou liberar os bloqueios colocados na tabela (se você deixou o servidor em execução).
Para copiar um banco de dados de uma máquina para outra usando arquivos de cópia direta, basta copiar os arquivos para o diretório de dados apropriado no outro host do servidor. Certifique-se de que o arquivo esteja no formato MyIASM ou que ambas as máquinas tenham a mesma estrutura de hardware, caso contrário seu banco de dados terá conteúdos estranhos na outra máquina. Você também deve garantir que o servidor em outra máquina não acesse as tabelas do banco de dados enquanto você as instala.
3 Replicando Banco de Dados
A replicação é semelhante a copiar um banco de dados para outro servidor, mas seu significado exato é garantir que os dois bancos de dados estejam totalmente sincronizados em tempo real. Este recurso aparecerá na versão 3.23 e ainda não está muito maduro, portanto este artigo não irá apresentá-lo em detalhes.
4 Restaurar dados do backup
A corrupção do banco de dados pode ocorrer por vários motivos e em graus variados. Se você tiver sorte, poderá corromper apenas uma ou duas tabelas (como uma queda de energia); se não tiver sorte, poderá ter que substituir todo o diretório de dados (como uma corrupção de disco). A recuperação também é necessária em determinadas situações, como quando um usuário exclui um banco de dados ou tabela por engano. Independentemente da causa desses acontecimentos infelizes, você precisará implementar algum tipo de recuperação.
Se as tabelas estiverem danificadas, mas não perdidas, tente repará-las com myisamchk ou isamchk. Se esse dano puder ser reparado por um programa de reparo, talvez você não precise usar o arquivo de backup. Para o processo de reparo de tabelas, consulte "Manutenção e Reparo de Banco de Dados".
O processo de recuperação envolve duas fontes de informações: seus arquivos de backup e seus logs de alterações. O arquivo de backup restaura a tabela para o estado em que estava quando o backup foi executado. No entanto, normalmente a tabela foi modificada no período entre o backup e o problema, e o log de atualização contém as consultas usadas para fazer essas modificações. Você pode usar arquivos de log como entrada para o mysql para repetir consultas. É por isso que você deve habilitar o log de alterações.
O processo de recuperação varia dependendo da quantidade de informações que você precisa recuperar. Na verdade, é mais fácil restaurar o banco de dados inteiro do que uma única tabela porque é mais fácil aplicar o log de atualização ao banco de dados do que a uma única tabela.
4.1 Restaurar todo o banco de dados
Primeiro, se o banco de dados que você deseja restaurar for um banco de dados mysql que contém uma tabela de permissões, você precisará executar o servidor com a opção --skip-grant-table. Caso contrário, reclamará que a tabela de autorização não foi encontrada. Depois de restaurar a tabela, execute mysqladmin flush-privileges para informar ao servidor para carregar os tokens de autorização e usá-los.
Copie o conteúdo do diretório do banco de dados para algum outro local se precisar deles posteriormente.
Reinstale o banco de dados com o arquivo de backup mais recente. Se você usar o arquivo gerado pelo mysqldump, use-o como entrada para o mysql. Se você estiver usando arquivos copiados diretamente do banco de dados, copie-os diretamente de volta para o diretório do banco de dados. No entanto, neste caso você precisará fechar o banco de dados e reiniciá-lo antes de copiar os arquivos.
Use o log de atualização para repetir consultas que modificam tabelas de banco de dados após o backup. Para quaisquer logs de alterações aplicáveis, passe-os como entrada para o mysql. Especificar a opção --one-database faz com que o mysql execute consultas apenas para o banco de dados que você está interessado em restaurar. Se você sabe que precisa aplicar todos os arquivos de log de atualização, pode usar este comando no diretório que contém os logs:
% ls -t -r -1 atualização.[0-9]* | xargs cat | mysql --one-database nome_bd
O comando ls gera uma lista de coluna única de arquivos de log de atualização, classificados de acordo com a ordem em que o servidor os gerou (Ideia: se você modificar algum dos arquivos, você alterará a ordem de classificação, o que faz com que o log de atualização seja usado na ordem errada.)
Provavelmente você usará determinados logs de alterações. Por exemplo, se os logs de atualização gerados desde o seu backup forem nomeados update.392, update.393, etc., você poderá executar novamente assim:
%mysql --one-database nome_bd <atualização.392
%mysql --one-database nome_bd <atualização.393
.....
Se você estiver executando uma recuperação e usando o log de atualização para recuperar informações perdidas devido a uma instrução DROP DATABASE, DROP TABLE ou DELETE recomendada incorretamente, certifique-se de excluir essas instruções do log de atualização antes de usá-lo.
4.2 Restaurando uma única tabela
Restaurar uma única tabela é mais complexo. Se você usar um arquivo de backup gerado pelo mysqldump e ele não contiver dados para as tabelas de seu interesse, você precisará extraí-los das linhas relevantes e usá-los como entrada para o mysql. Esta é a parte fácil. A parte difícil é extrair o fragmento do log de atualização que se aplica apenas a essa tabela. Você pode achar o utilitário mysql_find_rows útil para isso, que extrai consultas de várias linhas do log de alterações.
Outra possibilidade é usar outro servidor para restaurar todo o banco de dados e depois copiar os arquivos da tabela desejados para o banco de dados original. Isso pode ser muito fácil! Ao copiar os arquivos de volta para o diretório do banco de dados, certifique-se de que o servidor do banco de dados original esteja desligado.