Tarefas comuns de administrador de banco de dados para instâncias de banco de dados MySQL

Esta seção descreve implantações específicas ao Amazon RDS de algumas tarefas comuns de administrador de banco de dados para instâncias de bancos de dados em execução no mecanismo de banco de dados MySQL. A fim de oferecer uma experiência de serviço gerenciado, o Amazon RDS não fornece acesso ao shell para instâncias de bancos de dados e restringe o acesso a determinados procedimentos e tabelas do sistema que exigem privilégios avançados.

Para obter informações sobre como trabalhar com arquivos de log do MySQL no Amazon RDS, consulte Arquivos de log do banco de dados MySQL

Encerrar uma sessão ou consulta

É possível encerrar sessões ou consultas de usuários em instâncias de banco de dados usando os comandos rds_kill e rds_kill_query. Primeiro, conecte-se à sua instância de banco de dados MySQL. Depois disso, emita o comando apropriado, conforme mostrado a seguir. Para obter mais informações, consulte Conexão a uma instância de banco de dados executando o mecanismo de banco de dados do MySQL.

CALL mysql.rds_kill(thread-ID)
CALL mysql.rds_kill_query(thread-ID) 

Por exemplo, para encerrar a sessão que está sendo executada no thread 99, digite o seguinte:

CALL mysql.rds_kill(99); 

Para encerrar a consulta que está sendo executada no thread 99, digite o seguinte:

CALL mysql.rds_kill_query(99); 

Ignorar o erro de replicação atual

O Amazon RDS fornece um mecanismo para que você ignore um erro nas réplicas de leitura, caso o erro esteja fazendo com que a réplica de leitura pare de responder e o mesmo não afete a integridade de seus dados. Primeiro, conecte-se à sua instância de banco de dados MySQL. Depois disso, emita os comandos apropriados, conforme mostrado a seguir. Para obter mais informações, consulte Conexão a uma instância de banco de dados executando o mecanismo de banco de dados do MySQL.

nota

Primeiro, você deve verificar se o erro pode ser ignorado com segurança. Em um utilitário do MySQL, conecte-se à réplica de leitura e execute o seguinte comando do MySQL:

SHOW SLAVE STATUS\G 

Para obter informações sobre os valores retornados, consulte a documentação do MySQL.

Para ignorar o erro, você pode emitir o seguinte comando:

CALL mysql.rds_skip_repl_error; 

Esse comando não tem efeito se você executá-lo na instância de banco de dados de origem ou em uma réplica de leitura que não apresentou um erro de replicação.

Para mais informações, como as versões compatíveis com o MySQL mysql.rds_skip_repl_error, veja mysql.rds_skip_repl_error.

Importante

Se você tentar chamar mysql.rds_skip_repl_error e obter o seguinte erro: ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist, atualize sua instância de banco de dados MySQL para a versão secundária mais recente ou uma das versões secundárias mínimas listadas em mysql.rds_skip_repl_error.

Como trabalhar com espaços de tabelas do InnoDB para melhorar os tempos de recuperação de falha

Cada tabela no MySQL consiste de uma definição, de dados e de índices de tabela. O mecanismo de armazenamento InnoDB do MySQL armazena dados e índices de tabela em um espaço de tabela. O InnoDB cria um tablespace compartilhado global que contém um dicionário de dados e outros metadados relevantes, e pode conter dados e índices de tabela. O InnoDB também pode criar tablespaces separados para cada tabela e partição. Esses tablespaces separados são armazenados em arquivos de extensão .ibd e o cabeçalho de cada tablespaces contém um número que o identifica de forma exclusiva.

O Amazon RDS fornece um parâmetro em um parameter group do MySQL chamado innodb_file_per_table. Esses parâmetros controlam se o InnoDB adiciona novos dados de tabela e índices ao espaço de tabela compartilhado (definindo o valor do parâmetro como 0) ou aos espaços de tabelas individuais (definindo o valor do parâmetro como 1). O Amazon RDS define o valor padrão do parâmetro innodb_file_per_table como 1, o que permite a você descartar tabelas individuais do InnoDB e recuperar o armazenamento usado por essas tabelas para a instância de banco de dados. Na maioria dos casos de uso, definir o parâmetro innodb_file_per_table como 1 é a configuração recomendada.

Você deve definir o parâmetro innodb_file_per_table como 0 quando você tiver um grande número de tabelas, por exemplo mais de 1.000 tabelas, ao usar armazenamento SSD padrão (magnético) ou de propósito geral ou mais de 10.000 tabelas ao usar armazenamento de IOPS provisionadas. Quando você define esse parâmetro como 0, os tablespaces individuais não são criados e isso pode melhorar o tempo necessário para a recuperação da falha no banco de dados.

O MySQL processa cada arquivo de metadados, que inclui tablespaces, durante o ciclo de recuperação de falhas. O tempo que o MySQL leva para processar as informações de metadados no tablespace compartilhado é insignificante em comparação ao tempo necessário para processar milhares de arquivos de tablespace quando há vários tablespaces. Como o número do tablespace é armazenado no cabeçalho de cada arquivo, o tempo total para ler todos os arquivos do tablespace pode levar até várias horas. Por exemplo, pode levar de cinco a oito horas para processar um milhão de tablespaces do InnoDB no armazenamento padrão durante um ciclo de recuperação de falhas. Em alguns casos, o InnoDB pode determinar que precisa de limpeza adicional após um ciclo de recuperação de falhas para poder começar outro ciclo de recuperação de falhas, o que prolongará o tempo de recuperação. Lembre-se que um ciclo de recuperação de falhas também envolve transações de retorno, correção de páginas com defeito e outras operações além do processamento de informações de tablespace.

Como o parâmetro innodb_file_per_table reside em um parameter group, você pode alterar o valor do parâmetro editando o parameter group usado por sua instância de banco de dados sem ter que reiniciar a instância de banco de dados. Após a configuração ser alterada, por exemplo, de 1 (criar tabelas individuais) para 0 (usar tablespace compartilhado), novas tabelas do InnoDB serão adicionadas ao tablespace compartilhado, enquanto as tabelas existentes continuam a ter tablespaces individuais. Para mover uma tabela do InnoDB para o tablespace compartilhado, você deve usar o comando ALTER TABLE.

Migração de vários tablespaces ao tablespace compartilhado

Você pode mover os metadados da tabela do InnoDB de seu próprio tablespace ao tablespace compartilhado, que reconstruirá os metadados da tabela de acordo com a configuração do parâmetro innodb_file_per_table. Primeiro, conecte-se à sua instância de banco de dados MySQL. Depois disso, emita os comandos apropriados, conforme mostrado a seguir. Para obter mais informações, consulte Conexão a uma instância de banco de dados executando o mecanismo de banco de dados do MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Por exemplo, a seguinte consulta retorna uma instrução ALTER TABLE para cada tabela InnoDB que não esteja no espaço de tabela compartilhado.

Para instâncias de banco de dados MySQL 5.6 e 5.7:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Para instâncias de banco de dados MySQL 8.0:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Recriar uma tabela do MySQL para mover os metadados da tabela para o tablespace compartilhado requer temporariamente espaço de armazenamento adicional para recriar a tabela, portanto a instância do banco de dados deve ter espaço de armazenamento disponível. Durante a recriação, a tabela é bloqueada e inacessível para consultas. Para tabelas pequenas ou tabelas que não são acessadas com frequência, isso pode não ser um problema. Para tabelas grandes ou tabelas acessadas com frequência em um ambiente fortemente simultâneo, é possível recriar tabelas em uma réplica de leitura.

É possível criar uma réplica de leitura e migrar metadados da tabela para o tablespace compartilhado na réplica de leitura. Embora a instrução ALTER TABLE bloqueie o acesso na réplica de leitura, a instância de banco de dados de origem não é afetada. A instância de banco de dados de origem continuará a gerar seus logs binários enquanto a réplica de leitura apresentar atraso durante o processo de recriação da tabela. Como a recriação requer espaço de armazenamento adicional e o arquivo de log de reprodução pode ficar grande, você deve criar uma réplica de leitura com o armazenamento alocado maior que a instância de banco de dados de origem.

Para criar uma réplica de leitura e recriar tabelas do InnoDB a fim de usar o tablespace compartilhado, execute as seguintes etapas:

1. Verifique se a retenção de backup está habilitada na instância do banco de dados de origem, de modo que o registro de log binário esteja habilitado

2. Use o Console de gerenciamento da AWS ou a AWS CLI para criar uma réplica de leitura para a instância de banco de dados de origem. Como a criação de uma réplica de leitura envolve muitos dos mesmos processos que os da recuperação de falhas, o processo de criação pode levar algum tempo se houver um grande número de tablespaces do InnoDB. Aloque mais espaço de armazenamento na réplica de leitura do que é usado atualmente na instância de banco de dados de origem.

3. Quando a réplica de leitura tiver sido criada, crie um grupo de parâmetros com as configurações de parâmetro read_only = 0 e innodb_file_per_table = 0. Depois, associe o grupo de parâmetros à réplica de leitura.

4. Emita a seguinte instrução SQL para todas as tabelas que você deseja migrar na réplica:

   ALTER TABLE name ENGINE = InnoDB

5. Quando todas as instruções ALTER TABLE forem concluídas na réplica de leitura, verifique se a réplica de leitura está conectada à instância de banco de dados de origem e se as duas instâncias estão sincronizadas.

6. Use o console ou a CLI para promover a réplica de leitura para ser a instância. Verifique se o grupo de parâmetros usado para a nova instância de banco de dados autônoma tem o parâmetro innodb_file_per_table definido como 0. Altere o nome da nova instância de banco de dados autônoma e aponte todos os aplicativos para a nova instância de banco de dados autônoma.

Como gerenciar o histórico de status global

O MySQL mantém muitas variáveis de status que fornecem informações sobre sua operação. Os valores podem lhe ajudar a detectar problemas de bloqueio ou memória em uma instância de banco de dados. Os valores dessas variáveis de status se acumulam desde a última vez que a instância de banco de dados foi iniciada. Você pode redefinir a maioria das variáveis de status para 0 usando o comando FLUSH STATUS.

Para permitir o monitoramento desses valores ao longo do tempo, o Amazon RDS fornece um conjunto de procedimentos que fará o snapshot dos valores dessas variáveis de status ao longo do tempo e as registrará em uma tabela, juntamente com quaisquer alterações feitas desde o último snapshot. Esta infraestrutura, denominada GoSH (Histórico de status global), está instalada em todas as instâncias de banco de dados MySQL, desde as versões 5.5.23. O GoSH é desabilitado por padrão.

Para habilitar o GoSH, primeiro habilite o programador de eventos a partir de um parameter group de banco de dados, definindo o parâmetro event_scheduler como ON. Para obter informações sobre como criar e modificar um parameter group de banco de dados, consulte Como trabalhar com grupos de parâmetros de banco de dados.

Você poderá, então, usar os procedimentos na tabela a seguir para habilitar e configurar o GoSH. Primeiro, conecte-se à sua instância de banco de dados MySQL. Depois disso, emita os comandos apropriados, conforme mostrado a seguir. Para obter mais informações, consulte Conexão a uma instância de banco de dados executando o mecanismo de banco de dados do MySQL. Para cada procedimento, digite o seguinte:

CALL procedure-name; 

Onde procedure-name é um dos procedimentos na tabela.

Procedimento	Descrição
mysql.rds_enable_gsh_collector	Permite que o GoSH faça snapshots padrão em intervalos especificados por rds_set_gsh_collector.
mysql.rds_set_gsh_collector	Especifica o intervalo em minutos entre snapshots. O valor padrão é 5.
mysql.rds_disable_gsh_collector	Desabilita os snapshots.
mysql.rds_collect_global_status_history	Faz um snapshot sob demanda.
mysql.rds_enable_gsh_rotation	Permite o revezamento do conteúdo da tabela mysql.rds_global_status_history com o da mysql.rds_global_status_history_old em intervalos especificados pelo rds_set_gsh_rotation.
mysql.rds_set_gsh_rotation	Especifica o intervalo em dias entre os revezamentos de tabelas. O valor padrão é 7.
mysql.rds_disable_gsh_rotation	Desabilita o revezamento de tabelas.
mysql.rds_rotate_global_status_history	Reveza o conteúdo da tabela mysql.rds_global_status_history para o da mysql.rds_global_status_history_old sob demanda.

Quando o GoSH estiver sendo executado, você pode consultar as tabelas nas quais ele grava. Por exemplo, para consultar a taxa de acerto do grupo de buffer do Innodb, você deve emitir a seguinte consulta:

select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" 
    from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end
    where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'