Tareas comunes de administración de bases de datos para las instancias de bases de datos MySQL - Amazon Relational Database Service

Tareas comunes de administración de bases de datos para las instancias de bases de datos MySQL

En esta sección se describen las implementaciones específicas de Amazon RDS de algunas tareas comunes de administración de bases de datos para las instancias de bases de datos que ejecutan el motor de base de datos MySQL. Para ofrecer una experiencia de servicio administrado, Amazon RDS no proporciona acceso de shell a las instancias de base de datos y restringe el acceso a ciertos procedimientos y tablas del sistema que requieren privilegios avanzados.

Para obtener información acerca de cómo trabajar con archivos de registro de MySQL en Amazon RDS, consulte Archivos de registro de base de datos de MySQL.

Finalización de una sesión o una consulta

Puede finalizar sesiones de usuario o consultas en instancias de bases de datos utilizando los comandos rds_kill y rds_kill_query. En primer lugar, conéctese a la instancia de la base de datos MySQL y, a continuación, emita el comando adecuado como se muestra a continuación. Para obtener más información, consulte Conexión a una instancia de base de datos que ejecuta el motor de base de datos de MySQL.

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

Por ejemplo, para finalizar la sesión que se está ejecutando en el subproceso 99, debería escribir lo siguiente:

CALL mysql.rds_kill(99);

Para finalizar la consulta que se está ejecutando en el subproceso 99, debería escribir lo siguiente:

CALL mysql.rds_kill_query(99);

Omisión del error de replicación actual

Amazon RDS proporciona un mecanismo para omitir un error en las réplicas de lectura si el error está haciendo de la réplica de lectura deje de responder y el error no afecta a la integridad de los datos. En primer lugar, conéctese a la instancia de la base de datos MySQL y, a continuación, emita los comandos apropiados como se muestra a continuación. Para obtener más información, consulte Conexión a una instancia de base de datos que ejecuta el motor de base de datos de MySQL.

nota

Primero debe comprobar que el error se puede omitir con seguridad. En una utilidad MySQL, conéctese a la réplica de lectura y ejecute el siguiente comando MySQL:

SHOW SLAVE STATUS\G

Para obtener información sobre los valores devueltos, consulte la documentación de MySQL.

Para omitir el error, puede emitir el siguiente comando:

CALL mysql.rds_skip_repl_error;

Este comando no tiene ningún efecto si lo ejecuta en la instancia de base de datos de origen o en una réplica de lectura en la que no se ha detectado un error de replicación.

Para obtener más información, como las versiones de MySQL que admiten mysql.rds_skip_repl_error, consulte mysql.rds_skip_repl_error.

importante

Si intenta llamar a mysql.rds_skip_repl_error y aparece el error ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist, actualice la instancia de la base de datos MySQL a la última versión secundaria o a una de las versiones secundarias mínimas que se indican en mysql.rds_skip_repl_error.

Uso de espacios de tablas de InnoDB para mejorar los tiempos de recuperación tras bloqueo

Cada tabla de MySQL consta de una definición de tabla, datos e índices. El motor de almacenamiento InnoDB de MySQL almacena los datos y los índices de las tablas en un espacio de tabla. InnoDB crea un espacio de tablas global compartido que contiene un diccionario de datos y otros metadatos relevantes, y puede contener los datos e índices de las tablas. InnoDB también puede crear espacios de tabla independientes para cada tabla y partición. Estos espacios de tabla independientes se almacenan en archivos con la extensión .ibd y el encabezado de cada espacio de tabla contiene un número que lo identifica de forma inequívoca.

Amazon RDS proporciona un parámetro en un grupo de parámetros de MySQL denominado innodb_file_per_table. Este parámetro controla si InnoDB añade los datos e índices de las tablas nuevas al espacio de tablas compartido (cuando se establece el valor del parámetro en 0) o a espacios de tabla individuales (cuando se establece el valor del parámetro en 1). Amazon RDS establece el valor predeterminado del parámetro innodb_file_per_table en 1, lo que permite eliminar tablas individuales de InnoDB y recuperar el almacenamiento utilizado por esas tablas para la instancia de base de datos. En la mayoría de los casos de uso, se recomienda establecer el parámetro innodb_file_per_table en 1.

Debe establecer el parámetro innodb_file_per_table en 0 si tiene un gran número de tablas, por ejemplo, más de 1 000 tablas si utiliza almacenamiento estándar (magnético) o SSD de uso general, o más de 10 000 tablas si utiliza almacenamiento de IOPS provisionadas. Cuando se establece este parámetro en 0, no se crean espacios de tabla individuales, y esto puede mejorar el tiempo que tarda la recuperación tras bloqueo de la base de datos.

MySQL procesa cada archivo de metadatos, lo que incluye los espacios de tabla, durante el ciclo de recuperación tras bloqueo. El tiempo que tarda MySQL en procesar la información de metadatos del espacio de tablas compartido es insignificante en comparación con el tiempo que tarda en procesar miles de archivos de espacio de tabla si hay múltiples espacios de tabla. Debido a que el número del espacio de tabla se almacena en el encabezado de cada archivo, el tiempo global necesario para leer todos los archivos de espacios de tabla puede llegar a ser de varias horas. Por ejemplo, un millón de espacios de tabla de InnoDB en almacenamiento estándar pueden tardar entre cinco y ocho horas en procesarse durante un ciclo de recuperación tras bloqueo. En algunos casos, InnoDB puede determinar que necesita realizar una limpieza adicional después de un ciclo de recuperación tras bloqueo, por lo que iniciará otro ciclo que alargará el tiempo de recuperación. Tenga en cuenta que un ciclo de recuperación tras bloqueo, además del procesamiento de la información de los espacios de tabla, también conlleva la reversión de transacciones, la reparación de páginas dañadas y otras operaciones.

Dado que el parámetro innodb_file_per_table reside en un grupo de parámetros, puede cambiar el valor del parámetro editando el grupo de parámetros utilizado por la instancia de base de datos sin tener que reiniciarla. Después de cambiar la configuración, por ejemplo, de 1 (crear tablas individuales) a 0 (utilizar el espacio de tablas compartido), las tablas de InnoDB nuevas se añadirán al espacio de tablas compartido, mientras que las tablas existentes continuarán teniendo espacios de tabla individuales. Para mover una tabla InnoDB al espacio de tablas compartido, debe utilizar el comando ALTER TABLE.

Migración de varios espacios de tabla al espacio de tablas compartido

Puede mover los metadatos de una tabla de InnoDB desde su propio espacio de tabla al espacio de tablas compartido, lo que reconstruirá los metadatos de la tabla de acuerdo con el valor del parámetro innodb_file_per_table. En primer lugar, conéctese a la instancia de la base de datos MySQL y, a continuación, emita los comandos apropiados como se muestra a continuación. Para obtener más información, consulte Conexión a una instancia de base de datos que ejecuta el motor de base de datos de MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY;

Por ejemplo, la siguiente consulta devuelve una instrucción ALTER TABLE para cada tabla de InnoDB que no está en el espacio de tablas compartido.

Para instancias de base de datos MySQL 5.6 y 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 instancias de base de datos 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','');

La reconstrucción de una tabla de MySQL para mover los metadatos de la tabla al espacio de tablas compartido requiere espacio de almacenamiento adicional temporalmente para reconstruir la tabla, por lo que la instancia de base de datos debe tener espacio de almacenamiento disponible. Durante la reconstrucción, la tabla está bloqueada e inaccesible para las consultas. Para las tablas pequeñas o las tablas a las que no se tiene acceso con frecuencia, esto puede no ser un problema. Para las tablas de gran tamaño o las tablas a las que se tiene acceso con frecuencia en un entorno con un gran número de accesos simultáneos, es posible reconstruir las tablas en una réplica de lectura.

Es posible crear una réplica de lectura y migrar los metadatos de las tablas al espacio de tablas compartido en la réplica de lectura. Aunque la instrucción ALTER TABLE bloquea el acceso en la réplica de lectura, la instancia de base de datos de origen no se ve afectada. La instancia de base de datos de origen continuará generando sus registros binarios, mientras que la réplica de lectura se retrasará con respecto a ella durante el proceso de reconstrucción de tablas. Dado que la reconstrucción requiere espacio de almacenamiento adicional y el archivo registro de reproducción puede ser muy grande, al crear una réplica de lectura, debe asignar una cantidad de almacenamiento mayor que la instancia de base de datos de origen.

Para crear una réplica de lectura y reconstruir las tablas de InnoDB para que utilicen el espacio de tablas compartido, siga estos pasos:

  1. Asegúrese de que la retención de copias de seguridad esté habilitada en la instancia de base de datos de origen para que se active el registro binario.

  2. Utilice la Consola de administración de AWS o la AWS CLI para crear una réplica de lectura de la instancia de base de datos de origen. Dado que muchos de los procesos que conlleva la creación de una réplica de lectura coinciden con los de la recuperación tras bloqueo, el proceso de creación puede tardar cierto tiempo si hay un gran número de espacios de tabla de InnoDB. Asigne más espacio de almacenamiento en la réplica de lectura del que se utiliza actualmente en la instancia de base de datos de origen.

  3. Cuando se haya creado la réplica de lectura, cree un grupo de parámetros con los valores read_only = 0 y innodb_file_per_table = 0. A continuación, asocie el grupo de parámetros a la réplica de lectura.

  4. Ejecute la siguiente instrucción SQL para todas las tablas que desea migrar en la réplica:

    ALTER TABLE name ENGINE = InnoDB
  5. Cuando hayan finalizado todas las instrucciones ALTER TABLE en la réplica de lectura, verifique que esta está conectada a la instancia de base de datos de origen y que las dos instancias están sincronizadas.

  6. Utilice la consola o la CLI para convertir la réplica de lectura en la instancia. Asegúrese de que el grupo de parámetros utilizado para la nueva instancia de base de datos independiente tenga el parámetro innodb_file_per_table establecido en 0. Cambie el nombre de la nueva instancia de base de datos independiente y señale las aplicaciones a la nueva instancia de base de datos independiente.

Administrar el historial de estado global

MySQL mantiene muchas variables de estado que proporcionan información acerca de su funcionamiento. Su valor puede ayudarle a detectar problemas de bloqueo o de memoria en una instancia de base de datos. Los valores de estas variables de estado son acumulativos desde la última vez que se inició la instancia de base de datos. Puede restablecer la mayoría de las variables de estado a 0 utilizando el comando FLUSH STATUS.

Para permitir la monitorización de estos valores a lo largo del tiempo, Amazon RDS proporciona un conjunto de procedimientos que van creando snapshots de los valores de estas variables de estado y los escriben en una tabla, junto con cualquier cambio desde el último instantánea. Esta infraestructura, que se denomina Global Status History (GoSH), se instala en todas las instancias de bases de datos MySQL desde la versión 5.5.23. GoSH está deshabilitado de forma predeterminada.

Para activar GoSH, primero debe activar el programador de eventos desde un grupo de parámetros de base de datos estableciendo el parámetro event_scheduler en ON. Para obtener información acerca de cómo crear y modificar un grupo de parámetros de base de datos, consulte Trabajo con los grupos de parámetros de base de datos.

A continuación, puede utilizar los procedimientos de la siguiente tabla para activar y configurar GoSH. En primer lugar, conéctese a la instancia de la base de datos MySQL y, a continuación, emita los comandos apropiados como se muestra a continuación. Para obtener más información, consulte Conexión a una instancia de base de datos que ejecuta el motor de base de datos de MySQL. Para cada procedimiento, escriba lo siguiente:

CALL procedure-name;

Donde procedure-name es uno de los procedimientos de la tabla.

Procedimiento

Descripción

mysql.rds_enable_gsh_collector

Permite que GoSH tome los snapshots predeterminados con los intervalos especificados por rds_set_gsh_collector.

mysql.rds_set_gsh_collector

Especifica el intervalo, en minutos, entre snapshots. El valor predeterminado es 5.

mysql.rds_disable_gsh_collector

Desactiva los snapshots.

mysql.rds_collect_global_status_history

Toma una instantánea a petición.

mysql.rds_enable_gsh_rotation

Permite rotar el contenido de la tabla mysql.rds_global_status_history a mysql.rds_global_status_history_old con los intervalos especificados por rds_set_gsh_rotation.

mysql.rds_set_gsh_rotation

Especifica el intervalo, en días, entre rotaciones de la tabla. El valor predeterminado es 7.

mysql.rds_disable_gsh_rotation

Desactiva la rotación de la tabla.

mysql.rds_rotate_global_status_history

Rota el contenido de la tabla mysql.rds_global_status_history a mysql.rds_global_status_history_old a petición.

Cuando se ejecuta GoSH, es posible consultar las tablas en las que escribe. Por ejemplo, para consultar la tasa de aciertos del grupo del búfer Innodb, debería utilizar la siguiente 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'