Trabalho 3 - Recuperação de falhas

Prévia do material em texto

UNIVERSIDADE FEDERAL DE GOIÁS 
INSTITUTO DE INFORMÁTICA 
BANCO DE DADOS 2 
 
 
 
 
Florentino dos Santos Filho 
Julio Sousa 
Gustavo Alves 
Orlando da C. P. Júnior 
 
 
 
MySQL 
Recuperação de falhas 
 
 
 
 
 
 
 
 
 
 
Goiânia 
2019 
 
1. Introdução 3 
2. InnoDB e o modelo ACID 3 
3. Tipos de backup e recuperação 5 
3.1 Backups físicos versus lógicos 5 
3.2 Backups on-line versus off-line 8 
3.3 Backup local versus remoto 9 
3.4 Backups instantâneos 9 
3.5 Backups completos versus incrementais 9 
3.6 Recuperação Completa Versus Point-in-Time (Incremental) 10 
4. Exemplo de estratégia de backup e recuperação 10 
4.1 Estabelecendo uma política de backup 12 
4.2 Usando backups para recuperação 15 
4.3 Resumo da estratégia de backup 16 
5. Recuperação do InnoDB 16 
5.1 Recuperação de corrupção de dados ou falha de disco 16 
5.2 Recuperação de falha do InnoDB 17 
6. Undo e Redo logs 19 
6.1 Undo log 19 
6.2 Redo log 20 
7. Doublewrite Buffer 21 
8. InnoDB Checkpoints (Ponto de verificação) 22 
8.1 Como o processo de checkpoint funciona 22 
9. Conclusão 23 
Bibliografia 23 
 
 
 
1. Introdução 
O MySQL é conhecido por sua estabilidade, mas como qualquer outro aplicativo, possui 
bugs e pode travar em algum momento. Além disso, o sistema operacional pode ter falhas, o 
hardware tem problemas ou simplesmente podemos ter uma queda de energia, o que pode gerar 
várias inconsistências nos dados. Felizmente, bancos de dados como o MySQL possui algumas 
ferramentas para auxiliar o processo de restauração. Neste estudo abordaremos os processos de 
recuperação de falhas utilizados no SGBD relacional MySQL. 
2. InnoDB e o modelo ACID 
O modelo ACID é um conjunto de princípios de design de banco de dados que enfatizam 
aspectos de confiabilidade importantes para dados de negócios e aplicativos de missão crítica. O 
MySQL inclui componentes como o mecanismo de armazenamento InnoDB que se adere ao 
modelo ACID, para que os dados não sejam corrompidos e os resultados não sejam distorcidos 
por condições excepcionais, como falhas de software e defeitos de hardware. 
Quando você confia em recursos compatíveis com ACID, não precisa reinventar os 
mecanismos de verificação de consistência e recuperação de falhas. Nos casos em que você 
possui salvaguardas de software adicionais, hardware ultra-confiável ou um aplicativo que pode 
tolerar uma pequena quantidade de perda ou inconsistência de dados, você pode ajustar as 
configurações do MySQL para trocar parte da confiabilidade do ACID para obter melhor 
desempenho ou taxa de transferência. 
 
As seções a seguir discutem como os recursos do MySQL, em particular o InnoDB, 
interagem com as categorias do modelo ACID: 
● A : atomicidade. 
● C : consistência. 
● I : isolamento. 
● D : durabilidade. 
 
Atomicidade 
O aspecto da atomicidade do modelo ACID envolve principalmente transações do 
InnoDB. Os recursos relacionados ao MySQL incluem: 
● Configuração de confirmação automática. 
● COMMIT. 
● ROLLBACK. 
● Dados operacionais das tabelas INFORMATION_SCHEMA. 
 
Consistência 
O aspecto de consistência do modelo ACID envolve principalmente o processamento 
interno do InnoDB para proteger os dados de falhas. Os recursos relacionados ao MySQL 
incluem: 
● Buffer de gravação dupla InnoDB. 
● Recuperação de falhas InnoDB. 
 
Isolamento 
O aspecto de isolamento do modelo ACID envolve principalmente transações do 
InnoDB, em particular o nível de isolamento que se aplica a cada transação. Os recursos 
relacionados ao MySQL incluem: 
● Configuração de confirmação automática . 
● Declaração SET ISOLATION LEVEL. 
● Os detalhes de baixo nível de bloqueio no InnoDB. Durante o ajuste de desempenho, 
você vê esses detalhes nas tabelas INFORMATION_SCHEMA. 
 
Durabilidade 
O aspecto de durabilidade do modelo ACID envolve recursos do software MySQL 
interagindo com sua configuração de hardware específica. Devido às muitas possibilidades, 
dependendo dos recursos da sua CPU, rede e dispositivos de armazenamento, esse aspecto é o 
mais complicado para fornecer diretrizes concretas. Os recursos relacionados ao MySQL 
incluem: 
● Buffer de gravação dupla InnoDB, ativado e desativado pela opção de configuração 
innodb_doublewrite. 
● Opção de configuração innodb_flush_log_at_trx_commit. 
● Opção de configuração sync_binlog. 
● Opção de configuração innodb_file_per_table. 
● Grave buffer em um dispositivo de armazenamento, como uma unidade de disco, SSD ou 
matriz RAID. 
● Cache com bateria em um dispositivo de armazenamento. 
● O sistema operacional usado para executar o MySQL, em particular o suporte a chamada 
do sistema fsync(). 
● Fonte de alimentação ininterrupta (UPS), protegendo a energia elétrica de todos os 
servidores e dispositivos de armazenamento que executam servidores MySQL e 
armazenam dados MySQL. 
● Sua estratégia de backup, como frequência e tipos de backup, e períodos de retenção de 
backup. 
● Para aplicativos de dados distribuídos ou hospedados, as características específicas dos 
data centers em que o hardware dos servidores MySQL está localizado e as conexões de 
rede entre os data centers. 
3. Tipos de backup e recuperação 
Nesta seção iremos descrever as características dos diferentes tipos de backups. 
3.1 Backups físicos versus lógicos 
Os backups físicos consistem em cópias brutas dos diretórios e arquivos que armazenam 
o conteúdo do banco de dados. Esse tipo de backup é adequado para bancos de dados grandes e 
importantes que precisam ser recuperados rapidamente quando ocorrem problemas. 
Backups lógicos salvar informações representados como estrutura de banco de dados 
lógico (declarações CREATE DATABASE, CREATE TABLE) e conteúdo (declarações 
INSERT ou de texto delimitado). Esse tipo de backup é adequado para quantidades menores de 
dados, nas quais é possível editar os valores ou a estrutura da tabela ou recriar os dados em uma 
arquitetura de máquina diferente. 
 
Os métodos de backup físico têm essas características: 
● O backup consiste em cópias exatas dos diretórios e arquivos do banco de dados. 
Normalmente, esta é uma cópia de todo ou parte do diretório de dados do MySQL. 
● Os métodos de backup físico são mais rápidos que lógicos porque envolvem apenas cópia 
de arquivo sem conversão. 
● A saída é mais compacta do que para o backup lógico. 
● A granularidade de backup e restauração varia do nível de todo o diretório de dados até o 
nível de arquivos individuais. Isso pode ou não fornecer granularidade no nível da tabela, 
dependendo do mecanismo de armazenamento. Por exemplo, cada tabela no InnoDB 
pode estar em um arquivo separado ou compartilhar o armazenamento de arquivos com 
outras tabelas InnoDB; cada tabela MyISAM corresponde exclusivamente a um conjunto 
de arquivos. 
● Além dos bancos de dados, o backup pode incluir arquivos relacionados, como arquivos 
de log ou de configuração. 
● Os backups são portáteis apenas para outras máquinas que possuem características de 
hardware idênticas ou semelhantes. 
● Os backups podem ser executados enquanto o servidor MySQL não estiver em execução. 
Se o servidor estiver em execução, é necessário executar o bloqueio apropriado para que 
o servidor não altere o conteúdo do banco de dados durante o backup. O MySQL 
Enterprise Backup faz essebloqueio automaticamente para as tabelas que o exigem. 
● As ferramentas de backup físico incluem o mysqlbackup do MySQL Enterprise Backup 
para o InnoDB ou quaisquer outras tabelas ou comandos no nível do sistema de arquivos 
(como cp, scp, tar, rsync) para tabelas MyISAM. 
 
● Para restauração: 
○ O MySQL Enterprise Backup restaura InnoDB e outras tabelas das quais ele fez 
backup. 
○ O ndb_restore restaura tabelas NDB. 
○ Os arquivos copiados no nível do sistema de arquivos podem ser copiados de 
volta para seus locais originais com os comandos do sistema de arquivos. 
 
Os métodos de backup lógico têm essas características: 
● O backup é feito consultando o servidor MySQL para obter a estrutura do banco de dados 
e informações de conteúdo. 
● O backup é mais lento que os métodos físicos porque o servidor deve acessar as 
informações do banco de dados e convertê-las para o formato lógico. Se a saída for 
gravada no lado do cliente, o servidor também deverá enviá-la para o programa de 
backup. 
● A saída é maior que a do backup físico, principalmente quando salva em formato de 
texto. 
● A granularidade de backup e restauração está disponível no nível do servidor (todos os 
bancos de dados), no nível do banco de dados (todas as tabelas em um banco de dados 
específico) ou no nível da tabela. Isso é verdade independentemente do mecanismo de 
armazenamento. 
● O backup não inclui arquivos de log ou de configuração ou outros arquivos relacionados 
ao banco de dados que não fazem parte dos bancos de dados. 
● Os backups armazenados no formato lógico são independentes da máquina e altamente 
portáteis. 
● Backups lógicos são executados com o servidor MySQL em execução. O servidor não 
está offline. 
● As ferramentas de backup lógico incluem o programa mysqldump e a instrução SELECT 
... INTO OUTFILE. Eles funcionam para qualquer mecanismo de armazenamento, até o 
MEMORY. 
● Para restaurar backups lógicos, os arquivos de despejo no formato SQL podem ser 
processados ​​usando o cliente mysql. Para carregar arquivos de texto delimitado, use a 
instrução LOAD DATA ou o cliente mysqlimport 
3.2 Backups on-line versus off-line 
Os backups online ocorrem enquanto o servidor MySQL está em execução, para que as 
informações do banco de dados possam ser obtidas no servidor. Os backups offline ocorrem 
enquanto o servidor está parado. Essa distinção também pode ser descrita como backups 
"quentes" versus "frios". Um backup “quente” é aquele que está em execução, mas bloqueado 
contra modificação de dados enquanto você acessar os arquivos de banco de dados externamente. 
 
Os métodos de backup online têm essas características: 
● O backup é menos invasivo para outros clientes, que podem se conectar ao servidor 
MySQL durante o backup e podem acessar dados, dependendo de quais operações eles 
precisam executar. 
● Deve-se tomar cuidado para impor o bloqueio apropriado, para que não ocorram 
modificações nos dados que comprometam a integridade do backup. 
 
Os métodos de backup offline têm essas características: 
● Os clientes podem ser afetados negativamente porque o servidor não está disponível 
durante o backup. Por esse motivo, esses backups geralmente são obtidos de um servidor 
escravo de replicação que pode ser colocado offline sem prejudicar a disponibilidade. 
● O procedimento de backup é mais simples, porque não há possibilidade de interferência 
da atividade do cliente. 
 
Uma distinção semelhante entre online e offline se aplica às operações de recuperação. 
No entanto, é mais provável que os clientes sejam afetados pela recuperação online do que pelo 
backup online, porque a recuperação requer um bloqueio mais forte. Durante o backup, os 
clientes podem ler dados enquanto estão sendo copiados. A recuperação modifica os dados e não 
apenas os lê, portanto, os clientes devem ser impedidos de acessar dados enquanto eles estão 
sendo restaurados. 
3.3 Backup local versus remoto 
Um backup local é realizado no mesmo host em que o servidor MySQL é executado, 
enquanto um backup remoto é feito a partir de um host diferente. Para alguns tipos de backup, o 
backup pode ser iniciado a partir de um host remoto, mesmo que a saída seja gravada localmente 
no servidor. hospedeiro. 
● O mysqldump pode se conectar a servidores locais ou remotos. Para saída (instruções 
CREATE e INSERT) de SQL, dumps locais ou remotos podem ser feitos e gerar saída no 
cliente. Para saída de texto delimitado (com a opção --tab), os arquivos de dados são 
criados no host do servidor. 
● SELECT ... INTO OUTFILE pode ser iniciado a partir de um host de cliente local ou 
remoto, mas o arquivo de saída é criado no host do servidor. 
● Os métodos de backup físico geralmente são iniciados localmente no host do servidor 
MySQL, para que o servidor possa ficar offline, embora o destino dos arquivos copiados 
possa ser remoto. 
3.4 Backups instantâneos 
Algumas implementações de sistemas de arquivos permitem backups “instantâneos”. Eles 
fornecem cópias lógicas do sistema de arquivos em um determinado momento, sem exigir uma 
cópia física de todo o sistema de arquivos. (Por exemplo, a implementação pode usar técnicas de 
cópia na gravação para que apenas partes do sistema de arquivos modificadas após o tempo da 
captura instantânea precisem ser copiadas.) O próprio MySQL não oferece a capacidade de obter 
capturas instantâneas do sistema de arquivos. Está disponível através de soluções de terceiros, 
como Veritas, LVM ou ZFS. 
3.5 Backups completos versus incrementais 
Um backup completo inclui todos os dados gerenciados por um servidor MySQL em um 
determinado momento. Um backup incremental consiste nas alterações feitas nos dados durante 
um determinado período de tempo (de um ponto no tempo para outro). O MySQL possui 
maneiras diferentes de executar backups completos, como os descritos anteriormente nesta 
seção. Os backups incrementais são possíveis graças à ativação do log binário do servidor, que o 
servidor usa para registrar alterações de dados. 
3.6 Recuperação Completa Versus Point-in-Time (Incremental) 
Uma recuperação completa restaura todos os dados de um backup completo. Isso restaura 
a instância do servidor ao estado em que estava quando o backup foi feito. Se esse estado não for 
suficiente, uma recuperação completa poderá ser seguida pela recuperação de backups 
incrementais feitos desde o backup completo, para trazer o servidor para um estado mais 
atualizado. 
Recuperação incremental é a recuperação de alterações feitas durante um determinado 
período de tempo. Isso também é chamado de recuperação point-in-time, porque atualiza o 
estado de um servidor até um determinado momento. A recuperação pontual é baseada no log 
binário e geralmente segue uma recuperação completa dos arquivos de backup que restauram o 
servidor ao seu estado quando o backup foi feito. Em seguida, as alterações de dados gravadas 
nos arquivos de log binário são aplicadas como recuperação incremental para refazer as 
modificações de dados e levar o servidor ao ponto desejado no tempo. 
4. Exemplo de estratégia de backup e recuperaçãoEsta seção discute um procedimento para executar backups que permite recuperar dados 
após vários tipos de falhas: 
● Falha no sistema operacional 
● Falha de energia 
● Falha no sistema de arquivos 
● Problema de hardware (disco rígido, placa-mãe etc.) 
 
Os comandos de exemplo não incluem opções como --usere --password para os 
programas clientes mysqldump e mysql. Você deve incluir as opções necessárias para permitir 
que os programas clientes se conectem ao servidor MySQL. 
Suponha que os dados sejam armazenados no mecanismo de armazenamento InnoDB, 
que oferece suporte para transações e recuperação automática de falhas. Suponha também que o 
servidor MySQL esteja carregado no momento da falha. Caso contrário, nenhuma recuperação 
seria necessária. 
Para casos de falhas no sistema operacional ou de energia, podemos assumir que os dados 
do disco do MySQL estão disponíveis após uma reinicialização. Os arquivos de dados do 
InnoDB podem não conter dados consistentes devido à falha, mas o InnoDB lê seus logs e 
encontra neles a lista de transações confirmadas e não confirmadas pendentes que não foram 
liberadas para os arquivos de dados. O InnoDB reverte automaticamente as transações que não 
foram confirmadas e libera para seus arquivos de dados aquelas que foram confirmadas. 
Informações sobre esse processo de recuperação são transmitidas ao usuário através do log de 
erros do MySQL. A seguir, um exemplo de trecho de log: 
InnoDB​:​ Database was not shut down normally​. 
InnoDB​:​ Starting recovery from log files​... 
InnoDB​:​ Starting log scan based on checkpoint at 
InnoDB​:​ log sequence number 0 13674004 
InnoDB​:​ Doing recovery​:​ scanned up to log sequence number 0 13739520 
InnoDB​:​ Doing recovery​:​ scanned up to log sequence number 0 13805056 
InnoDB​:​ Doing recovery​:​ scanned up to log sequence number 0 13870592 
InnoDB​:​ Doing recovery​:​ scanned up to log sequence number 0 13936128 
... 
InnoDB​:​ Doing recovery​:​ scanned up to log sequence number 0 20555264 
InnoDB​:​ Doing recovery​:​ scanned up to log sequence number 0 20620800 
InnoDB​:​ Doing recovery​:​ scanned up to log sequence number 0 20664692 
InnoDB​:​ 1 uncommitted transaction​(​s​)​ which must be rolled back 
InnoDB​:​ Starting rollback of uncommitted transactions 
InnoDB​:​ Rolling back trx no 16745 
InnoDB​:​ Rolling back of trx no 16745 completed 
InnoDB​:​ Rollback of uncommitted transactions completed 
InnoDB​:​ Starting an apply batch of log records to the database​... 
InnoDB​:​ Apply batch completed 
InnoDB​:​ Started 
mysqld​:​ ready for connections 
 
Para os casos de falhas no sistema de arquivos ou problemas de hardware, podemos 
assumir que os dados do disco do MySQL não estão disponíveis após uma reinicialização. Isso 
significa que o MySQL falha ao iniciar com êxito porque alguns blocos de dados do disco não 
são mais legíveis. Nesse caso, é necessário reformatar o disco, instalar um novo ou corrigir o 
problema subjacente. Então é necessário recuperar nossos dados de backups do MySQL, o que 
significa que os backups já devem ter sido feitos. Para garantir que esse seja o caso, projete e 
implemente uma política de backup. 
4.1 Estabelecendo uma política de backup 
Para ser útil, os backups devem ser agendados regularmente. Um backup completo (uma 
captura instantânea dos dados em um determinado momento) pode ser feito no MySQL com 
várias ferramentas. Por exemplo, o MySQL Enterprise Backup pode executar um backup físico 
de uma instância inteira, com otimizações para minimizar a sobrecarga e evitar interrupções ao 
fazer backup de arquivos de dados do InnoDB. O mysqldump fornece backup lógico online. 
Neste exemplo usaremos o mysqldump. 
Suponha que façamos um backup completo de todas as nossas tabelas InnoDB em todos 
os bancos de dados usando o seguinte comando no domingo às 13:00, quando a carga for baixa: 
shell> mysqldump ​--all-databases ​--master-data ​--single-transaction > 
backup_sunday_1_PM​.​sql 
 
O arquivo .sql resultante produzido pelo mysqldump contém um conjunto de instruções 
SQL INSERT que podem ser usadas para recarregar as tabelas despejadas posteriormente. Essa 
operação de backup adquire um bloqueio de leitura global em todas as tabelas no início do dump 
(usando FLUSH TABLES WITH READ LOCK). 
Assim que esse bloqueio é adquirido, as coordenadas do log binário são lidas e o 
bloqueio é liberado. Se instruções longas de atualização estiverem em execução quando a 
instrução FLUSH for emitida, a operação de backup poderá parar até que essas instruções sejam 
concluídas. Depois disso, o despejo fica livre de bloqueios e não perturba as leituras e gravações 
nas tabelas. 
Foi assumido anteriormente que as tabelas para backup são tabelas InnoDB, portanto, 
--single-transaction utiliza uma leitura consistente e garante que os dados vistos pelo mysqldump 
não sejam alterados. Se a operação de backup incluir tabelas não transacionais, a consistência 
exigirá que elas não sejam alteradas durante o backup. Por exemplo, para as tabelas MyISAM no 
banco de dados mysql, não deve haver alterações administrativas nas contas do MySQL durante 
o backup. 
Os backups completos são necessários, mas nem sempre é conveniente criá-los. Eles 
produzem grandes arquivos de backup e levam tempo para serem gerados. Eles não são ideais no 
sentido de que cada backup completo sucessivo inclui todos os dados, mesmo a parte que não foi 
alterada desde o backup completo anterior. É mais eficiente fazer um backup completo inicial e, 
em seguida, fazer backups incrementais. 
Os backups incrementais são menores e levam menos tempo para serem produzidos. A 
desvantagem é que, no momento da recuperação, você não pode restaurar seus dados apenas 
recarregando o backup completo. Você também deve processar os backups incrementais para 
recuperar as alterações incrementais. 
Para fazer backups incrementais, precisamos salvar as alterações incrementais. No 
MySQL, essas alterações são representadas no log binário, portanto, o servidor MySQL sempre 
deve ser iniciado com a opção --log-bin, para habilitar esse log. Com o log binário ativado, o 
servidor grava cada alteração de dados em um arquivo enquanto atualiza os dados. Observando o 
diretório de dados de um servidor MySQL que foi iniciado com a opção --log-bin e que está em 
execução há alguns dias, encontramos os seguintes arquivos de log binário do MySQL: 
-​rw​-​rw​----​ 1 guilhem guilhem 1277324 Nov 10 23​:​59 gbichot2​-​bin​.​000001 
-​rw​-​rw​----​ 1 guilhem guilhem 4 Nov 10 23​:​59 gbichot2​-​bin​.​000002 
-​rw​-​rw​----​ 1 guilhem guilhem 79 Nov 11 11​:​06 gbichot2​-​bin​.​000003 
-​rw​-​rw​----​ 1 guilhem guilhem 508 Nov 11 11​:​08 gbichot2​-​bin​.​000004 
-​rw​-​rw​----​ 1 guilhem guilhem 220047446 Nov 12 16​:​47 gbichot2​-​bin​.​000005 
-​rw​-​rw​----​ 1 guilhem guilhem 998412 Nov 14 10​:​08 gbichot2​-​bin​.​000006 
-​rw​-​rw​----​ 1 guilhem guilhem 361 Nov 14 10​:​07 gbichot2​-​bin​.​index 
 
Cada vez que reinicia, o servidor MySQL cria um novo arquivo de log binário usando o 
próximo número na sequência. Enquanto o servidor estiver em execução, você também pode 
dizer para fechar o arquivo de log binário atual e iniciar um novo manualmente, emitindo umainstrução SQL FLUSH LOGS ou com um comando mysqladmin flush-logs. O mysqldump 
também tem uma opção para liberar os logs. O arquivo .index no diretório de dados contém a 
lista de todos os logs binários do MySQL no diretório. 
Os logs binários do MySQL são importantes para recuperação, pois formam o conjunto 
de backups incrementais. Se você limpar os logs ao fazer o backup completo, os arquivos 
binários criados posteriormente conterão todas as alterações de dados feitas desde o backup. 
Vamos modificar um pouco o comando mysqldump anterior para liberar os logs binários do 
MySQL no momento do backup completo e para que o arquivo de despejo contenha o nome do 
novo log binário atual: 
shell>​ mysqldump​ ​--single-transaction​ ​--flush-logs​ ​--master-data​=​2​ \ 
 ​--all-databases​ > backup_sunday_1_PM​.​sql 
 
Após executar este comando, o diretório de dados contém um novo arquivo de log 
binário gbichot2-bin.000007, porque a opção --flush-logs faz com que o servidor limpe seus 
logs. A opção --master-data faz com que o mysqldump grave informações de log binário em sua 
saída, portanto o arquivo .sql de despejo resultante inclui estas linhas: 
--​ Position to start replication or point​-​in​-​time recovery from 
--​ CHANGE MASTER TO MASTER_LOG_FILE​=​'gbichot2​-​bin​.​000007'​,​MASTER_LOG_POS​=​4​; 
 
Como o comando mysqldump fez um backup completo, essas linhas significam duas 
coisas: 
● O arquivo de despejo contém todas as alterações feitas antes de quaisquer alterações 
gravadas no arquivo de log binário gbichot2-bin.000007 ou superior. 
● Todas as alterações de dados registradas após o backup não estão presentes no arquivo de 
despejo, mas estão presentes no arquivo de log binário gbichot2-bin.000007 ou superior. 
 
Na segunda feira às 13:00, podemos criar um backup incremental liberando os logs para 
iniciar um novo arquivo de log binário. Por exemplo, a execução de um comando mysqladmin 
flush-logs cria o arquivo gbichot2-bin.000008. Todas as alterações entre o backup completo das 
13h e às 13h de segunda feira estarão no arquivo gbichot2-bin.000007. Esse backup incremental 
é importante, portanto, é uma boa ideia copiá-lo para um local seguro. (Por exemplo, faça 
backup em fita ou DVD ou copie-o para outra máquina.) Na terça-feira às 13:00, execute outro 
comando mysqladmin flush-logs. Todas as alterações entre segunda-feira às 13:00 e terça-feira 
às 13:00 estarão no arquivo gbichot2-bin.000008 (que também deve ser copiado em algum lugar 
seguro). 
 
Os logs binários do MySQL ocupam espaço em disco. Para liberar espaço, limpe-os de 
tempos em tempos. Uma maneira de fazer isso é excluindo os logs binários que não são mais 
necessários, como quando fazemos um backup completo: 
shell>​ mysqldump​ ​--single-transaction​ ​--flush-logs​ ​--master-data​=​2​ \ 
 ​--all-databases​ ​--delete-master-logs​ > backup_sunday_1_PM​.​sql 
4.2 Usando backups para recuperação 
Agora, suponha que tenhamos um acidente catastrófico na quarta-feira às 8 da manhã que 
exija recuperação de backups. Para recuperar, primeiro restauramos o último backup completo 
que temos (o de domingo às 13 horas). O arquivo de backup completo é apenas um conjunto de 
instruções SQL, portanto, é muito fácil restaurá-lo: 
shell>​ mysql​ < backup_sunday_1_PM​.​sql 
 
Nesse momento, os dados são restaurados para seu estado a partir das 13h do domingo. 
Para restaurar as alterações feitas desde então, precisamos usar os backups incrementais; ou seja, 
os arquivos de log gbichot2-bin.000007 e gbichot2-bin.000008. Busque os arquivos, se 
necessário, de onde eles foram copiados e, em seguida, processe seu conteúdo da seguinte 
maneira: 
shell>​ mysqlbinlog​ gbichot2-bin​.​000007 gbichot2-bin​.​000008 | mysql 
 
Agora, recuperamos os dados para seu estado a partir da terça-feira às 13 horas, mas 
ainda faltam as alterações dessa data para a data do acidente. Para não perdê-los, precisaríamos 
que o servidor MySQL armazene seus logs binários do MySQL em um local seguro (discos 
RAID, SAN, ...) diferente do local em que ele armazena seus arquivos de dados, para que esses 
logs não sejam no disco destruído. (Ou seja, podemos iniciar o servidor com uma opção --log-bin 
que especifica um local em um dispositivo físico diferente daquele em que o diretório de dados 
reside. 
Dessa forma, os logs são seguros, mesmo que o dispositivo que contém o diretório seja 
perdido.) Se havíamos feito isso, teríamos o arquivo ogbichot2-bin.000009 (e quaisquer arquivos 
subseqüentes) em mãos, e poderíamos aplicá-los usando mysqlbinlog e mysql para restaurar as 
alterações mais recentes dos dados sem perda até o momento do travamento: 
shell>​ mysqlbinlog​ gbichot2-bin​.​000009 ​...​ | mysql 
4.3 Resumo da estratégia de backup 
No caso de uma falha no sistema operacional ou falha de energia, o próprio InnoDB faz 
todo o trabalho de recuperar dados. Mas, para garantir que você possa dormir bem, observe as 
seguintes diretrizes: 
● Sempre execute o servidor MySQL com a opção --log-bin, ou mesmo , onde o nome do 
arquivo de log está localizado em uma mídia segura diferente da unidade em que o 
diretório de dados está localizado. Se você tiver uma mídia segura, essa técnica também 
pode ser boa para balanceamento de carga de disco (o que resulta em uma melhoria de 
desempenho). --log-bin=log_name 
● Faça backups completos periódicos, usando o comando mysqldump. 
● Faça backups incrementais periódicos. 
5. Recuperação do InnoDB 
Nesta seção iremos abordar como o Mysql faz a recuperação de dados no InnoDB. 
5.1 Recuperação de corrupção de dados ou falha de disco 
Se o seu banco de dados for corrompido ou ocorrer uma falha no disco, você deverá 
executar a recuperação usando um backup. No caso de corrupção, primeiro encontre um backup 
que não esteja corrompido. Após restaurar o backup base, faça uma recuperação pontual dos 
arquivos de log binário usando mysqlbinlog e mysql para restaurar as alterações que ocorreram 
após o backup. 
Em alguns casos de corrupção do banco de dados, é suficiente despejar, descartar e 
recriar uma ou algumas tabelas corrompidas. Você pode usar a instrução CHECK TABLE para 
verificar se uma tabela está corrompida, embora CHECK TABLE naturalmente não seja possível 
detectar todos os tipos de corrupção. Você pode usar o Tablespace Monitor para verificar a 
integridade do gerenciamento de espaço no arquivo dentro dos arquivos do espaço de tabela. 
Em alguns casos, a corrupção aparente da página do banco de dados é realmente devida 
ao sistema operacional corrompendo seu próprio cache de arquivos e os dados no disco podem 
estar corretos. É melhor tentar reiniciar o computador primeiro. Fazer isso pode eliminar erros 
que pareciam estar corrompendo a página do banco de dados. 
5.2 Recuperação de falha do InnoDB 
Para se recuperar de uma falha no servidor MySQL, o único requisito é reiniciar o 
servidor MySQL. O InnoDB verifica automaticamente os logs e executa um rollforward do 
banco de dados para o presente. O InnoDB reverte automaticamente as transações não 
confirmadas presentes no momento do travamento. Durante a recuperação, o mysqld exibe umasaída semelhante a esta: 
InnoDB: Log scan progressed past the checkpoint lsn 452854464 
InnoDB: Database was not shut down normally! 
InnoDB: Starting crash recovery. 
InnoDB: Reading tablespace information from the .ibd files... 
InnoDB: Restoring possible half-written data pages from the doublewrite 
InnoDB: buffer... 
InnoDB: Doing recovery: scanned up to log sequence number 457028695 
InnoDB: 1 transaction(s) which must be rolled back or cleaned up 
InnoDB: in total 990682 row operations to undo 
InnoDB: Trx id counter is 500 
InnoDB: Starting an apply batch of log records to the database... 
InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 
94 95 96 97 98 99 
InnoDB: Apply batch completed 
InnoDB: Waiting for the background threads to start 
InnoDB: Starting in background the rollback of uncommitted transactions 
InnoDB: Rolling back trx with id 3B1, 990682 rows to undo 
... 
InnoDB: 5.5.55 started; log sequence number 457028695 
... 
./mysqld: ready for connections. 
 
O processo de recuperação de falhas consiste em várias etapas: 
● Redo Log 
A operação redo Log é a primeira etapa e é executado durante a inicialização, antes de 
aceitar qualquer conexão. Se todas as alterações forem liberadas do buffer pool para os 
espaços de tabela (arquivos ibdata*e *.ibd) no momento do encerramento ou falha, a 
operação redo log será ignorada. O InnoDB também ignora o redo log se os arquivos para 
que serão refeitos estiverem ausentes na inicialização. 
A remoção do redo log para acelerar a recuperação não é recomendada, mesmo se 
alguma perda de dados for aceitável. A remoção do redo logs somente deve ser 
considerada após um desligamento correto, com innodb_fast_shutdown definido como 0 
ou 1. 
● Roll back nas transações incompletas 
Transações incompletas são aquelas que estavam ativas no momento do travamento ou do 
desligamento rápido. O tempo necessário para reverter uma transação incompleta pode 
ser três ou quatro vezes a quantidade de tempo que uma transação está ativa antes de ser 
interrompida, dependendo da carga do servidor. 
Você não pode cancelar transações que estão sendo revertidas. Em casos extremos, 
quando se espera que a reversão de transações leve um tempo excepcionalmente longo, 
pode ser mais rápido começar o InnoDB com a configuração innodb_force_recovery 
igual 3 ou superior. 
● Change buffer merge 
Aplicando alterações do buffer de alterações (parte do espaço de tabela do sistema ) nas 
páginas de folha dos índices secundários, conforme as páginas de índice são lidas no 
buffer pool. 
● Purge 
Exclusão de registros marcados para exclusão que não são mais visíveis para transações 
ativas. 
 
Após refazer o aplicativo de log, InnoDB tenta aceitar conexões o mais cedo possível, 
para reduzir o tempo de inatividade. Como parte da recuperação de falhas, o InnoDB reverte as 
transações que não foram confirmadas ou no estado XA PREPARE em que o servidor travou. A 
reversão é realizada por um encadeamento em segundo plano, executado em paralelo com 
transações de novas conexões. Até que a operação de reversão seja concluída, novas conexões 
podem encontrar conflitos de bloqueio com transações recuperadas. 
Na maioria das situações, mesmo que o servidor MySQL tenha sido morto 
inesperadamente no meio de atividades pesadas, o processo de recuperação ocorre 
automaticamente e nenhuma ação é necessária para o DBA. Se uma falha de hardware ou erro 
grave do sistema corromper os dados do InnoDB, o MySQL pode se recusar a iniciar. 
6. Undo e Redo logs 
Nesta seção serão abordados os métodos undo e redo utilizados no MySQL. 
6.1 Undo log 
Um undo log é uma coleção de registros de log para desfazer associados a uma única 
transação de leitura e gravação. Um registro de undo log contém informações sobre como 
desfazer a alteração mais recente por uma transação em um registro de índice em cluster. Se 
outra transação precisar ver os dados originais como parte de uma operação de leitura 
consistente, os dados não modificados serão recuperados dos registros undo log. Os log undo 
existem nos segmentos de desfazer, contidos nos segmentos de reversão. Os segmentos de 
reversão residem no espaço de tabela do sistema, nos espaços de tabela desfazer e no espaço de 
tabela temporário. 
Os undo logs que residem no espaço de tabela temporário são usados ​​para transações que 
modificam dados em tabelas temporárias definidas pelo usuário. Esses logs de desfazer não são 
registrados novamente, pois não são necessários para a recuperação de falhas. Eles são usados 
​​apenas para reversão enquanto o servidor está em execução. Esse tipo de log de desfazer 
beneficia o desempenho, evitando E/S de redo logs. 
O InnoDB suporta no máximo 128 segmentos de reversão, 32 dos quais são alocados no 
espaço de tabela temporário. Isso deixa 96 segmentos de reversão que podem ser atribuídos a 
transações que modificam dados em tabelas regulares. A variável innodb_rollback_segments 
define o número de segmentos de reversão usados ​​por InnoDB. ​O número de transações 
suportadas por um segmento de reversão depende do número de slots undo no segmento de 
reversão e do número de undo logs necessários para cada transação. 
Uma transação é atribuída até quatro undo logs, um para cada um dos seguintes tipos de 
operação: 
● INSERT em tabelas definidas pelo usuário 
● UPDATE e DELETE em tabelas definidas pelo usuário 
● INSERT em tabelas temporárias definidas pelo usuário 
● UPDATE e DELETE em tabelas temporárias definidas pelo usuário 
 
Os undo logs são atribuídos conforme necessário. Por exemplo, uma transação que 
executa INSERT, UPDATE e DELETE e que opera em tabelas regulares e temporárias requer 
uma atribuição completa de quatro undo logs. Uma transação que executa apenas INSERT em 
tabelas regulares requer um único undo log. 
Uma transação que executa operações em tabelas regulares é designada para desfazer 
logs de um espaço de tabela do sistema designado ou para desfazer um segmento de reversão do 
espaço de tabela. Uma transação que executa operações em tabelas temporárias é designada para 
desfazer logs de um segmento de reversão de espaço de tabela temporário designado. 
Um undo log atribuído a uma transação permanece vinculado à transação por sua 
duração. Por exemplo, um undo log atribuído a uma transação para uma operação INSERT em 
uma tabela regular é usado para todas as operações INSERT em tabelas regulares executadas 
por essa transação. 
6.2 Redo log 
O redo log é uma estrutura de dados baseada em disco usada durante a recuperação de 
falhas para corrigir dados gravados por transações incompletas. Durante operações normais, o 
redo log codifica solicitações para alterar dados da tabela que resultam de instruções SQL ou 
chamadas de API de baixo nível. As modificações que não concluíram a atualização dos 
arquivos de dados antes de um encerramento inesperado são repetidas automaticamente durante a 
inicialização e antes das conexões serem aceitas.Por padrão, o redo log é fisicamente representado no disco por dois arquivos nomeados 
ib_logfile0 e ib_logfile1. O MySQL grava nos arquivos de redo log de maneira circular. Os 
dados no redo log são codificados em termos de registros afetados. Esses dados são 
coletivamente chamados de redo. A passagem de dados pelo redo log é representada por um 
valor crescente de LSN. 
 
Alterando o número ou tamanho dos arquivos redo log do InnoDB 
Para alterar o número ou o tamanho dos arquivos redo log no InnoDB , execute as 
seguintes etapas: 
● Pare o servidor MySQL e verifique se ele é desligado sem erros. 
● Edite my.cnf para alterar a configuração do arquivo de log. Para alterar o tamanho do 
arquivo de log, configure innodb_log_file_size. Para aumentar o número de arquivos de 
log, configure innodb_log_files_in_group. 
● Inicie o servidor MySQL novamente. 
 
Se o InnoDB detectar que innodb_log_file_size é diferente do tamanho do arquivo de 
redo log, ele grava um ponto de verificação de log, fecha e remove os arquivos de log antigos, 
cria novos arquivos de log no tamanho solicitado e abre os novos arquivos de log. 
 
Confirmação de grupo para refazer descarga de logs 
O InnoDB, como qualquer outro mecanismo de banco de dados compatível com ACID , 
libera o redo log de uma transação antes de ser confirmada. O InnoDB usa a funcionalidade de 
confirmação de grupo para agrupar várias solicitações de liberação juntas para evitar uma 
liberação para cada confirmação. Com a confirmação de grupo, o InnoDB emite uma única 
gravação no arquivo de log para executar a ação de confirmação para várias transações de 
usuário confirmadas ao mesmo tempo, melhorando significativamente a taxa de transferência. 
7. Doublewrite Buffer 
O buffer de gravação dupla é uma área de armazenamento localizada no espaço de tabela 
do sistema onde InnoDB grava páginas que são liberadas do pool de buffers InnoDB, antes que 
as páginas sejam gravadas em suas posições apropriadas no arquivo de dados. Somente após 
liberar e gravar as páginas no buffer do InnoDB, as páginas são gravadas em suas posições 
apropriadas. Se houver uma falha no sistema operacional, no subsistema de armazenamento ou 
no processo mysqld no meio de uma gravação de página, o InnoDB mais tarde poderá encontrar 
uma boa cópia da página no buffer de gravação dupla durante a recuperação de falhas. 
Embora os dados sejam sempre gravados duas vezes, o buffer de gravação dupla não 
requer duas vezes mais sobrecarga de E/S ou duas vezes mais operações de E/S. Os dados são 
gravados no próprio buffer de gravação dupla como um grande bloco seqüencial, com uma única 
chamada fsync() para o sistema operacional. 
O buffer de gravação dupla é ativado por padrão na maioria dos casos. Para desativar o 
buffer de gravação dupla, defina innodb_doublewrite como 0. Se os arquivos de espaço de tabela 
do sistema (“ arquivos ibdata ”) estiverem localizados nos dispositivos Fusion-io que suportam 
gravações atômicas, o buffer de gravação dupla será automaticamente desativado e as gravações 
atômicas do Fusion-io serão usadas para todos os arquivos de dados. Como a configuração do 
buffer de gravação dupla é global, o buffer de gravação dupla também é desativado para arquivos 
de dados que residem em hardware que não seja o Fusion-io. Esse recurso é suportado apenas no 
hardware Fusion-io e é ativado apenas para o NVMFS Fusion-io no Linux. 
8. InnoDB Checkpoints (Ponto de verificação) 
Tornar seus arquivos de log muito grandes pode reduzir a E/S do disco durante o ponto 
de verificação. Geralmente, faz sentido definir o tamanho total dos arquivos de log tão grande 
quanto o buffer pool ou até maior. Embora no passado os grandes arquivos de log pudessem 
fazer com que a recuperação de falhas levasse um tempo excessivo, a partir do MySQL 5.5, os 
aprimoramentos de desempenho na recuperação de falhas possibilitam o uso de grandes arquivos 
de log com inicialização rápida após uma falha. 
8.1 Como o processo de checkpoint funciona 
InnoDB implementa um mecanismo de ponto de verificação conhecido como ponto de 
verificação difuso. O InnoDB libera páginas de banco de dados modificadas do buffer pool em 
pequenos lotes. Não há necessidade de liberar o buffer pool em um único lote, o que 
interromperia o processamento de instruções SQL do usuário durante o processo de ponto de 
verificação. 
 
Durante a recuperação de falhas, o InnoDB procura um rótulo de ponto de verificação 
gravado nos arquivos de log. Ele sabe que todas as modificações no banco de dados antes do 
rótulo estão presentes na imagem de disco do banco de dados. Em seguida, o InnoDB varre os 
arquivos de log para frente a partir do ponto de verificação, aplicando as modificações 
registradas no banco de dados. 
9. Conclusão 
É apenas uma questão de tempo em que o banco de dados trava ou fica corrompido, por 
isso é bom ter uma cópia para a qual podemos alternar. Pode ser a replicação mestre/escravo por 
exemplo. O aspecto importante aqui é ter uma recuperação automática segura para minimizar a 
complexidade da alternância e minimizar o tempo de recuperação (RTO). A corrupção da tabela 
do InnoDB é bastante comum e pode ter efeitos colaterais duradouros. Mas, se conhecemos as 
técnicas corretas de recuperação, pode minimizar os danos e abrir caminho para uma restauração. 
Bibliografia 
● Documentação do MySQL: 
■ https://dev.mysql.com/doc/ 
■ Acesso em: 30/11/2019 
 
https://dev.mysql.com/doc/