Defendendo gestores e profissionais de TI contra interrupções nos processos.

[MySQL] Caiu um banco de dados no seu colo? Comece pelo backup.

head_post_backup_rapido

CENÁRIO: Você acabou de herdar um banco de dados MySQL de produção, e não há nenhuma certeza se há uma rotina de backup rodando, nem mesmo se existe alguma. Como reza o mantra dos DBAs, “Primeiro faça um Backup”.
E antes de implementar qualquer estratégia de backup você precisa saber algumas coisas sobre o tamanho do banco de dados e o tipo de armazenamento, porque eles tem impacto direto sobre a disponibilidade do sistema, em qualquer abordagem que se use.

Vamos falar aqui sobre o que é estritamente necessário em matéria de funcionalidade para realizar o seu trabalho com tranquilidade, incluindo:

  • Determinar qual é o tamanho do banco de dados
  • Determinar qual mecanismo de armazenamento está em uso (engine)
  • Consequências de bloqueios e indisponibilidades

Preparando seu backup MySQL

Há mais de uma estratégia para fazer backup de um ambiente MySQL. Isto pode depender também do número de servidores na sua topologia e se eles estão em cluster ou não. Existem muitas ferramentas gratuitas e pagas disponíveis para executar backups, podemos aprofundar isto num próximo post.

Neste momento vamos supor que seu ambiente está com um único servidor e você deseja criar um backup consistente. Você tem à sua disposição duas opções imediatas. A primeira opção é parar sua instância MySQL e tirar um backup completo “a frio” de TODOS os arquivos. Isso deixaria seu sistema indisponível por tempo indeterminado, e você precisa ter certeza de que fez uma cópia de todas as informações corretas, incluindo o diretório de dados MySQL, os logs de transação e logs binários (se houver), e também a configuração atual.

Sua segunda opção é usar uma ferramenta cliente incluída na instalação padrão do MySQL. O comando mysqldump pode produzir um backup MySQL consistente sem parar a instância do MySQL. Antes de executar o mysqldump é preciso coletar várias informações importantes, para que você possa tomar uma decisão informada sobre o melhor caminho disponível. Elas são:

  • Qual é o tamanho do banco de dados?
  • Que estratégia de bloqueio é necessário para produzir um backup consistente?
  • Quanto tempo o backup vai levar?

Determinando o tamanho de seu banco

Ao fazer backup em disco local é importante saber qual é o tamanho do seu backup. Você precisa disso para garantir que existe espaço em disco disponível para armazenar o arquivo de backup. A seguinte instrução SQL retorna o tamanho total em MB dos seus dados e índices atuais:

USE INFORMATION_SCHEMA;
SELECT ROUND(SUM(data_length+index_length)/1024/1024)
AS total_mb,
ROUND(SUM(data_length)/1024/1024) AS data_mb,
ROUND(SUM(index_length)/1024/1024) AS index_mb
FROM tables;
+----------+---------+----------+
| total_mb | data_mb | index_mb |
+----------+---------+----------+
| 927      |     847 |       80 |
+----------+---------+----------+

O backup via mysqldump terá aproximadamente o mesmo tamanho que os seus dados, incluindo uma margem de segurança de 10 a 15 por cento. Não há cálculo exato, pois o seu backup produz uma saída de seus dados em formato de texto. Por exemplo, um inteiro de 4 bytes no banco de dados pode ter 10 bytes de caracteres em tamanho no arquivo de backup mysqldump. É possível comprimir seu backup simultaneamente ou transferir para um dispositivo de rede diferente.

De acordo com a instrução SQL do exemplo o tamanho dos dados de todos os bancos é 847MB. Ainda no mesmo exemplo, o tamanho do arquivo de cópia de segurança, se executarmos o mysqldump usando as opções padrão, terá 818MB.

Escolhendo uma estratégia de bloqueio

A estratégia de bloqueio (locking) escolhida vai determinar se sua aplicação vai poder executar operações de escrita no banco durante a execução de um backup. O mysqldump impõe um bloqueio das tabelas para garantir uma versão consistente de todos os dados, usando o comando LOCK TABLES. É o equivalente de usar a opção –lock-tables na linha de comando. É uma das opções que estão habilitadas por padrão. Você pode optar por não bloquear as tabelas, mas isto pode levar a um backup que não tenha dados consistentes. Onde o mecanismo de armazenamento for MyISAM é necessário usar –lock-tables para garantir um backup consistente.

O mysqldump oferece a alternativa de usar a opção –single-transaction que cria uma versão snapshot consistente de todas as tabelas em uma única transação. Esta opção só é aplicável quando o mecanismo de armazenamento suporta multiversionamento. Numa instalação padrão do MySQL o InnoDB é o único mecanismo que se aplica. Esta opção desliga automaticamente
–lock-tables quando selecionada.
A seguinte instrução SQL lista os mecanismos de armazenamento em uso na sua instância MySQL:

USE INFORMATION_SCHEMA; 
SELECT table_schema, engine, COUNT(*) AS tables
FROM tables
WHERE table_schema NOT IN
('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA')
GROUP BY table_schema, engine
ORDER BY 3 DESC;
+--------------------+--------+--------+
| table_schema       | engine | tables |
+--------------------+--------+--------+
| ecommerce          | MyISAM |    109 |
| cliente_db         | InnoDB |     48 |
| mysql              | MyISAM |     21 |
| analytics          | InnoDB |     20 |
| phpmyadmin         | MyISAM |      8 |
| newsletter         | MyISAM |      8 |
| cliente_db         | MyISAM |      3 |
| mysql              | CSV    |      2 |
+--------------------+--------+--------+

Neste exemplo, a instância MySQL tem vários esquemas diferentes dando suporte a variados aplicativos como uma loja online, uma newsletter e a ferramenta de administração. Uma aplicação que usasse somente InnoDB teria um banco parecido com isso:

+--------------------+--------+--------+
| table_schema       | engine | tables |
+--------------------+--------+--------+
| prod_db            | InnoDB |    122 |
| mysql              | MyISAM |     21 |
| mysql              | CSV    |      2 |
+--------------------+--------+--------+

Como pode ser visto no exemplo o meta-esquema mysql usa o mecanismo MyISAM. Atualmente não há como mudar isto. Se seus bancos de dados (esquemas) são totalmente InnoDB, você ainda terá pelo menos um esquema MyISAM, pertencente ao servidor.

Tempo de Execução

Você vai precisar saber quanto tempo leva a execução do backup, e não há cálculo que possa da uma resposta precisa. O tamanho do banco de dados, quanta memória há no servidor, qual o mecanismo de armazenamento em uso, a configuração do MySQL, a velocidade dos discos e a carga atual de trabalho influenciam o resultado. O mais importante é que você registre estas informações para referência futura.
A importância do tempo de execução reside na capacidade de definir uma janela de manutenção para o seu banco de dados. Se não houver uma janela de tempo separada, durante o backup pode haver uma limitação nas funcionalidades dos aplicativos que dependem do banco, também uma sobrecarga na performance, ou seu backup pode bloquear outras operações ocorrendo no servidor, como processamento em lote ou manutenção de software.

Combinando as Informações

Com a seguinte instrução SQL você pode trazer em conjunto as informações necessárias para uma primeira auditoria do tamanho dos seus bancos de dados:

USE INFORMATION_SCHEMA; 
SELECT table_schema, engine,
ROUND(SUM(data_length+index_length)/1024/1024) AS total_mb,
ROUND(SUM(data_length)/1024/1024) AS data_mb,
ROUND(SUM(index_length)/1024/1024) AS index_mb,
COUNT(*) AS tables
FROM tables
GROUP BY table_schema, engine
ORDER BY 3 DESC;

Executando um Backup MySQL

Agora que você coletou as informações, você tem os detalhes necessários para tomar uma decisão informada.
Uma consideração adicional durante o processo de backup seria desabilitar quaisquer processos programados (cron, batch, agendador de tarefas) para minimizar a carga de trabalho. Isto pode diminuir a contenção no banco e encurtar a janela de tempo necessária.

Rodando o mysqldump

A forma mais simples de executar seu backup usando mysqldump usa a seguinte sintaxe:

$ time mysqldump -uroot -p --all-databases > backup.sql
$ echo $?
$ ls -lh backup.sql
  • O primeiro comando roda o mysqldump para todos os bancos de dados e fornece um dump ASCII dentro do arquivo backup.sql.
  • O segundo comando confirma o status de saída do primeiro comando. Um resultado diferente de zero indica que houveram problemas durante o processo de backup. Erros geralmente são mostrados diretamente na tela, mas se não forem, o status de saída vai informar você.
  • O terceiro comando mostra o tamanho do arquivo de backup, que você vai registrar para referência futura.

Exemplo:

$ time mysqldump -uroot -p --all-databases > backup.sql
real 0m35.493s
user 0m9.808s
sys 0m3.021s
$ echo $?
0
$ ls -lh backup.sql
-rw-rw-r-- 1 uid gid 818M Aug 10 21:37 backup.sql

Esta é a execução bem sucedida de um backup gerando um arquivo de 818MB que levou 35 segundos para terminar. O tamanho original da soma dos bancos de dados, conforme visto anteriormente neste exemplo, era de 847MB.

DICA: usar o comando time antes do comando mysqldump fornece informações valiosas sobre o tempo gasto na execução. Registrar o tempo gasto e o tamanho do arquivo de backup são dois passos administrativos importantes que todo DBA deveria fazer. Este tempo é útil na hora de agendar outras tarefas do servidor, também como passo adicional de verificação se houver alguma diferença gritante. Também é útil na hora de comparar com a execução que utiliza argumentos diferentes, com diferentes configurações do MySQL ou para comparar depois de mudanças no hardware.

Um exemplo de ocorrência de erro se pareceria com isso:

$ time mysqldump -uroot -p --all-databases > backup.sql
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user
'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
real 0m7.692s
user 0m1.780s
sys 0m0.313s
$ echo $?
2
$ ls -lh backup.sql
-rw-rw-r-- 1 uid gid 94M Aug 10 21:28 backup.sql

Um arquivo de backup como o deste exemplo poderia isoladamente parecer completamente válido. Isto é, este arquivo contém instruções SQL completas e válidas, e poderia até ser restaurado com sucesso em um ou mais esquemas; entretanto, não representa um backup completo de todos os dados. O tempo de execução, status de retorno e o tamanho são todos informações importantes na verificação de um backup bem sucedido.

Criar um backup é só o primeiro passo numa estratégia viável. É indispensável que este arquivo de backup possa ser usado numa recuperação bem sucedida.

Protegendo seu Backup

O passo final numa abordagem mínima é garantir a segurança dos seus dados. Nest altura do exemplo seu backup ainda está no mesmo servidor que o banco. Perder este servidor significaria dar adeus aos seus dados e ao seu banco ao mesmo tempo. É necessário que você copie o seu backup para outro servidor. Por exemplo:

$ time gzip backup.sql
$ time scp backup.sql.gz another-server:backup-dir

Benefícios do mysqldump

O comando mysqldump fornece um arquivo de backup no formato SQL. Isto é ideal se você precisar restaurá-lo em diferentes versões do MySQL ou em servidores diferentes, e até mesmo em sistemas operacionais diferentes. E você pode visualizar diretamente o conteúdo deste arquivo e suas instruções SQL. Por exemplo:

$ more backup.sql
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql`
8 Effective MySQL: Backup and Recovery
/*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
--
-- Table structure for table `help_topic`
--
DROP TABLE IF EXISTS `help_topic`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `help_topic` (
`help_topic_id` int(10) unsigned NOT NULL,
`name` char(64) NOT NULL,
`help_category_id` smallint(5) unsigned NOT NULL,
`description` text NOT NULL,
`example` text NOT NULL,
`url` char(128) NOT NULL,
PRIMARY KEY (`help_topic_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help topics';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `help_topic`
--
LOCK TABLES `help_topic` WRITE;/*!40000 ALTER TABLE `help_topic` DISABLE KEYS */;
INSERT INTO `help_topic` VALUES (0,'MIN',16,'Syntax:\nMIN([DISTINCT] expr)\n\n
Returns the minimum value of expr. MIN() may take a string argument; in\nsuch cases, it returns the minimum string value.See\nhttp://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html. The DISTINCT\nkeyword can be used to find the minimum of ...

Mais Informações

Você pode obter mais informações sobre as várias opções do mysqldump, listando as opções com a seguinte sintaxe:

$ mysqldump --help

E para informações detalhadas consulte o Manual de Referência do MySQL em http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html.

Outras Opções

Se seu banco de dados usa somente tabelas InnoDB, a estratégia de bloqueio padrão será restritiva. Você precisa levar em consideração o impacto do esquema mysql nas tabelas MyISAM. Numa situação normal geralmente você pode ignorar o requisito de consistência desde que você não execute operações que alterem os meta-dados: colunas, índices, novas tabelas, inclusive adicioinar ou alterar privilégios de usuários, e criar ou apagar esquemas.
Alternativamente você pode fazer dois backups separados. O primeiro sem o dicionário de dados, ou seja, sem o esquema mysql, usando a opção –single-transaction. O segundo backup incluindo somente o esquema mysql e usando o bloqueio apropriado.

Conclusão

Uma boa estratégia de backup MySQL é um componente essencial em qualquer sistema em produção. Para uma instalação simples, a implantação da estratégia de backup pode ser feita em minutos e testada como mostrado aqui.
Entretanto, uma estratégia de backup só é tão boa quanto o processo de recuperação completo, bem sucedido e em tempo. Discutiremos a recuperação mais detalhadamente num artigo posterior.

João Reis • 20 de maio de 2016


Deixe uma resposta

Your email address will not be published / Required fields are marked *