-
Backup e recuperação do MySQL Sábado, 30/09/2006 - 14:21 — yejr
Backup e recuperação MySQL
Autor/Tradutor: Ye Jinrong (E-mail: ), Fonte: http://imysql.cn Por favor, indique o autor/tradutor e a fonte ao reimprimir. Os infratores não serão processados.
Data: 01/10/2006
Este artigo discute o mecanismo de backup e recuperação do MySQL e como manter tabelas de dados, incluindo os dois tipos principais de tabelas: MyISAM e Innodb. A versão do MySQL projetada neste artigo é 5.0.22.
As ferramentas de backup gratuitas atualmente suportadas pelo MySQL incluem: mysqldump, mysqlhotcopy Você também pode usar a sintaxe SQL para backup: BACKUP TABLE ou SELECT INTO OUTFILE, ou fazer backup de logs binários (binlog) ou copiar diretamente arquivos de dados e arquivos de configuração relacionados. As tabelas MyISAM são salvas como arquivos, portanto, são relativamente fáceis de fazer backup. Vários dos métodos mencionados acima podem ser usados. Todas as tabelas no Innodb são armazenadas no mesmo arquivo de dados ibdata1 (também podem ser vários arquivos ou arquivos de espaço de tabela independentes), o que é relativamente difícil de fazer backup. As soluções gratuitas podem ser copiar arquivos de dados e fazer backup do log binário. .
1.mysqldump
1.1 Backup
mysqldump usa um mecanismo de backup em nível SQL. Ele exporta tabelas de dados para arquivos de script SQL. É relativamente adequado para atualização entre diferentes versões do MySQL.
Agora vamos falar sobre alguns dos principais parâmetros do mysqldump:
--compatível=nome
Ele informa ao mysqldump com qual banco de dados ou versão mais antiga do servidor MySQL os dados exportados serão compatíveis. Os valores podem ser ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc. É claro que não garante compatibilidade total, mas tenta ser compatível.
--complete-inserir, -c
Os dados exportados utilizam o método INSERT completo incluindo os nomes dos campos, ou seja, todos os valores são escritos em uma linha. Fazer isso pode melhorar a eficiência da inserção, mas pode ser afetado pelo parâmetro max_allowed_packet e causar falha na inserção. Portanto, esse parâmetro precisa ser usado com cautela, pelo menos não recomendo.
--default-character-set=conjunto de caracteres
Especifique qual conjunto de caracteres usar ao exportar dados. Se a tabela de dados não usar o conjunto de caracteres latin1 padrão, esta opção deverá ser especificada durante a exportação, caso contrário, ocorrerão caracteres distorcidos após a importação dos dados novamente.
--disable-keys
Diga ao mysqldump para adicionar instruções /*!40000 ALTER TABLE DISABLE KEYS */; e /*!40000 ALTER TABLE ENABLE KEYS */; porque é O índice é reconstruído após todos os dados terem sido inseridos. Esta opção só é adequada para tabelas MyISAM.
--extended-insert = verdadeiro|falso
Por padrão, o mysqldump ativa o modo --complete-insert, então se você não quiser usá-lo, basta usar esta opção e definir seu valor como falso.
--hex-blob
Exporte campos de string binária usando formato hexadecimal. Esta opção deve ser usada se houver dados binários. Os tipos de campo afetados são BINARY, VARBINARY e BLOB.
--lock-todas-tabelas,-x
Antes de iniciar a exportação, envie uma solicitação para bloquear todas as tabelas em todos os bancos de dados para garantir a consistência dos dados. Este é um bloqueio de leitura global e é automaticamente desativado com as opções --single-transaction e --lock-tables.
--lock-tables
É semelhante a --lock-all-tables, mas bloqueia a tabela de dados atualmente exportada em vez de bloquear todas as tabelas do banco de dados de uma só vez. Esta opção só é aplicável a tabelas MyISAM. Se for uma tabela Innodb, você pode usar a opção --single-transaction.
--no-create-info, -t
Exporte apenas os dados sem adicionar uma instrução CREATE TABLE.
--sem dados,-d
Nenhum dado é exportado, apenas a estrutura da tabela do banco de dados é exportada.
--optar
Esta é apenas uma opção rápida, equivalente a adicionar também --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set- opções de conjunto de caracteres. Esta opção permite que o mysqldump exporte dados rapidamente e os dados exportados possam ser importados de volta rapidamente. Esta opção está habilitada por padrão, mas pode ser desabilitada com --skip-opt. Observe que se você executar o mysqldump sem especificar a opção --quick ou --opt, todo o conjunto de resultados será colocado na memória. Podem ocorrer problemas se você exportar um banco de dados grande.
--rápido,-q
Esta opção é útil ao exportar tabelas grandes. Ela força o mysqldump a gerar os registros obtidos diretamente da consulta do servidor, em vez de recuperar todos os registros e armazená-los em cache na memória.
--rotinas, -R
Exporte procedimentos armazenados e funções personalizadas.
--transação única
Esta opção envia uma instrução SQL BEGIN antes de exportar dados. BEGIN não bloqueia nenhum aplicativo e garante um estado consistente do banco de dados durante a exportação. Funciona apenas com tabelas transacionais como InnoDB e BDB.
Esta opção e a opção --lock-tables são mutuamente exclusivas porque LOCK TABLES faz com que qualquer transação pendente seja confirmada implicitamente.
Para exportar tabelas grandes, a opção --quick deve ser usada em combinação.
--triggers
Exporte também gatilhos. Esta opção está habilitada por padrão, use --skip-triggers para desativá-la.
Consulte o manual para obter detalhes de outros parâmetros. Normalmente uso o seguinte SQL para fazer backup de tabelas MyISAM:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob -x nome_bd > nome_bd.sql
Use o seguinte SQL para fazer backup de tabelas do Innodb:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --single-transaction nome_bd > nome_bd.sql
Além disso, se quiser implementar backup online, você também pode usar o parâmetro --master-data, como segue:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--single-transaction --flush-logs nome_bd > nome_bd.sql
Ele apenas solicita a tabela de bloqueio no início, depois atualiza o log binário e, em seguida, adiciona a instrução CHANGE MASTER ao arquivo exportado para especificar a localização do log binário do backup atual. Se desejar restaurar este arquivo para o escravo, você pode usar. desta forma para fazer isso.
1.2 Restaurar O arquivo de backup com mysqldump é um script SQL que pode ser importado diretamente. Existem duas maneiras de importar os dados.
Use o cliente mysql diretamente, por exemplo:
/usr/local/mysql/bin/mysql -uyejr -pyejr nome_bd < nome_bd.sql
Usar a sintaxe SOURCE na verdade não é uma sintaxe SQL padrão, mas uma função fornecida pelo cliente mysql, por exemplo:
FONTE /tmp/db_name.sql;
Aqui você precisa especificar o caminho absoluto do arquivo, e deve ser um arquivo que o usuário que executa o mysqld (por exemplo, ninguém) tenha permissão para ler.
2. mysqlhotcopy
2.1 Backup
mysqlhotcopy é um programa PERL originalmente escrito por Tim Bunce. Ele usa LOCK TABLES, FLUSH TABLES e cp ou scp para fazer backup rápido do banco de dados. É a maneira mais rápida de fazer backup de um banco de dados ou de uma única tabela, mas só pode ser executado na máquina onde os arquivos do banco de dados (incluindo arquivos de definição de tabela de dados, arquivos de dados e arquivos de índice) estão localizados. mysqlhotcopy só pode ser usado para fazer backup do MyISAM e só será executado em sistemas do tipo Unix e NetWare.
mysqlhotcopy suporta a cópia de vários bancos de dados ao mesmo tempo e também suporta expressões regulares. Aqui estão alguns exemplos:
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp (altere o diretório do banco de dados db_name
Copiar para /tmp
Abaixo)
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
nome_bd_1 ... nome_bd_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
nome_bd./regex/ /tmp
Consulte o manual para uso mais detalhado ou chame o seguinte comando para ver a ajuda do mysqlhotcopy:
perldoc /usr/local/mysql/bin/mysqlhotcopy
Observe que se você quiser usar mysqlhotcopy, você deve ter permissões SELECT e RELOAD (para executar FLUSH TABLES) e também deve ter permissão para ler o diretório datadir/db_name.
2.2 Restaurar
Mysqlhotcopy faz backup de todo o diretório do banco de dados. Quando usado, ele pode ser copiado diretamente para o datadir especificado pelo mysqld (aqui é /usr/local/mysql/data/). como no exemplo a seguir:
root#cp -rf nome_bd /usr/local/mysql/data/
root#chown -R ninguém:nobody /usr/local/mysql/data/ (altere o proprietário do diretório db_name para mysqld
usuário em execução)
3. Backup de sintaxe SQL
3.1 Backup
A sintaxe BACKUP TABLE é na verdade semelhante ao princípio de funcionamento do mysqlhotcopy. Ambos bloqueiam a tabela e depois copiam o arquivo de dados. Pode conseguir backup online, mas o efeito não é ideal, por isso não é recomendado. Ele copia apenas arquivos de estrutura de tabela e arquivos de dados, mas não copia arquivos de índice ao mesmo tempo, portanto a recuperação é mais lenta.
exemplo:
BACK TABLE nome_tabela TO '/tmp/nome_bd/';
Observe que você deve ter permissão FILE para executar este SQL, e o diretório /tmp/db_name/ deve ser gravável pelo usuário mysqld. O arquivo exportado não pode sobrescrever o arquivo existente para evitar problemas de segurança.
SELECT INTO OUTFILE exporta os dados para um arquivo de texto comum. Você pode personalizar o intervalo do campo para facilitar o processamento desses dados.
exemplo:
SELECIONE * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
Observe que você deve ter permissão FILE para executar este SQL, e o arquivo /tmp/db_name/tbl_name.txt deve ser gravável pelo usuário mysqld. O arquivo exportado não pode sobrescrever o arquivo existente para evitar problemas de segurança.
3.2 Para restaurar arquivos de backup usando o método BACKUP TABLE, você pode executar a instrução RESTORE TABLE para restaurar a tabela de dados.
exemplo:
RESTAURAR TABELA DE '/tmp/db_name/';
Os requisitos de permissão são semelhantes aos descritos acima.
Para arquivos cujo backup foi feito usando o método SELECT INTO OUTFILE, você pode executar a instrução LOAD DATA INFILE para restaurar a tabela de dados.
exemplo:
CARREGAR DADOS NO ARQUIVO '/tmp/db_name/tbl_name.txt' NA TABELA tbl_name;
Os requisitos de permissão são semelhantes aos descritos acima. Antes de importar os dados, a tabela de dados já deve existir. Se estiver preocupado com a duplicação de dados, você pode adicionar a palavra-chave REPLACE para substituir os registros existentes ou usar a palavra-chave IGNORE para ignorá-los.
4. Habilite o log binário (binlog)
O método de uso do binlog é relativamente mais flexível, economiza preocupações e esforços e também pode oferecer suporte a backup incremental.
O Mysqld deve ser reiniciado quando o binlog estiver habilitado. Primeiro, feche o mysqld, abra my.cnf e adicione as seguintes linhas:
id do servidor = 1
log-bin=binlog
log-bin-index=binlog.index
Então inicie o mysqld. Binlog.000001 e binlog.index serão gerados durante a operação. O primeiro arquivo é o mysqld que registra todas as operações de atualização nos dados, e o último arquivo é o índice de todos os binlogs, que não podem ser facilmente excluídos. Consulte o manual para obter informações sobre o binlog.
Quando precisar fazer backup, você pode primeiro executar a instrução SQL para permitir que o mysqld termine a gravação no log binário atual e, em seguida, faça backup do arquivo diretamente. Desta forma, o propósito do backup incremental pode ser alcançado:
FLUSH LOGS; Se você estiver fazendo backup do servidor escravo no sistema de replicação, você também deve fazer backup dos arquivos master.info e relay-log.info.
O arquivo binlog de backup pode ser visualizado usando a ferramenta mysqlbinlog fornecida pelo MySQL, como:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
Esta ferramenta permite exibir todas as instruções SQL em um banco de dados especificado e também pode limitar o intervalo de tempo, o que é bastante conveniente. Consulte o manual para obter detalhes.
Ao restaurar, você pode usar instruções semelhantes às seguintes:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr nome_bd
Use as instruções SQL geradas pelo mysqlbinlog diretamente como entrada para executá-las.
Se você tiver uma máquina ociosa, também poderá usar este método para fazer backup dela. Como os requisitos de desempenho das máquinas escravas são relativamente baixos, o custo é baixo. O backup incremental pode ser alcançado com baixo custo e parte da pressão de consulta de dados pode ser compartilhada.
5. Backup direto de arquivos de dados Em comparação com os métodos anteriores, o backup de arquivos de dados é o mais direto, rápido e conveniente. A desvantagem é que o backup incremental é basicamente impossível. Para garantir a consistência dos dados, a seguinte instrução SQL precisa ser executada antes de fazer backup do arquivo:
FLUSH TABLES WITH READ LOCK; isto é, liberar todos os dados da memória para o disco e bloquear a tabela de dados para garantir que nenhum dado novo seja gravado durante o processo de cópia. Os dados armazenados em backup por este método também são muito simples de restaurar, basta copiá-los de volta para o diretório original do banco de dados.
Observe que para tabelas do tipo Innodb, você também precisa fazer backup de seus arquivos de log, ou seja, arquivos ib_logfile*. Porque quando a tabela Innodb está danificada, você pode contar com esses arquivos de log para recuperação.
6. Estratégia de backup Para sistemas com volume de negócios de nível médio, a estratégia de backup pode ser determinada da seguinte forma: backup completo pela primeira vez, backup incremental uma vez por dia, backup completo uma vez por semana e assim por diante. Para sistemas importantes e ocupados, você pode precisar de um backup completo uma vez por dia, um backup incremental uma vez por hora ou até com mais frequência. Para obter backup online e backup incremental sem afetar os negócios online, a melhor maneira é usar o mecanismo de replicação mestre-escravo (replicação) para fazer backups na máquina escrava.
7. Manutenção de dados e recuperação de desastres Como DBA (ainda não sou, haha), uma das tarefas mais importantes é garantir que as tabelas de dados possam ser usadas de forma segura, estável e em alta velocidade. Portanto, suas tabelas de dados precisam ser mantidas regularmente. A seguinte instrução SQL é útil:
CHECK TABLE ou REPAIR TABLE, verifique ou mantenha tabelas MyISAM
OPTIMIZE TABLE, otimize a tabela MyISAM
ANALYZE TABLE, analise a tabela MyISAM É claro que os comandos acima podem ser executados através da ferramenta myisamchk e não serão descritos em detalhes aqui.
As tabelas Innodb podem ser desfragmentadas e melhorar a velocidade de indexação executando as seguintes instruções:
ALTER TABLE nome_tabela ENGINE = Innodb;
Na verdade, esta é uma operação NULL. Superficialmente, não faz nada, mas na verdade reorganiza os fragmentos.
As tabelas MyISAM comumente usadas podem ser restauradas usando os métodos mencionados acima. Se o índice estiver quebrado, você poderá usar a ferramenta myisamchk para reconstruí-lo. Para tabelas Innodb, não é tão simples, porque armazena todas as tabelas em um espaço de tabela. No entanto, o Innodb possui um mecanismo de verificação chamado ponto de verificação difuso. Contanto que o arquivo de log seja salvo, os erros podem ser reparados com base no arquivo de log. Você pode adicionar os seguintes parâmetros no arquivo my.cnf para permitir que o mysqld verifique automaticamente o arquivo de log quando for iniciado:
innodb_force_recovery = 4
Consulte o manual para obter informações sobre este parâmetro.
8. Resuma o backup de dados e determine a estratégia de backup apropriada Esta é uma pequena parte do que um DBA faz. Tudo é difícil no início.