Tareas comunes de administración de bases de datos para las instancias de bases de datos MySQL - Amazon Relational Database Service
Usuarios predefinidosModelo de privilegios basado en rolesFinalización de una sesión o una consultaOmisión del error de replicación actualUso de espacios de tablas de InnoDB para mejorar los tiempos de recuperación tras un bloqueoAdministración del historial de estado global (Global Status History)

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

en el siguiente contenido, puede encontrar descripciones de las implementaciones específicas de Amazon RDS de algunas tareas comunes de DBA 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 mediante shell a las instancias de base de datos. También 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.

Usuarios predefinidos

Amazon RDS crea automáticamente varios usuarios predefinidos con nuevas instancias de base de datos de RDS para MySQL. Los usuarios predefinidos y sus privilegios no se pueden cambiar. No se pueden eliminar, cambiar de nombre ni modificar los privilegios de estos usuarios predefinidos. Intentar realizar una de estas operaciones producirá un error.

  • rdsadmin: un usuario creado para administrar muchas de las tareas de administración que el administrador con privilegios de superuser realizaría en una base de datos PostgreSQL independiente. Este usuario lo utiliza internamente RDS para MySQL para muchas tareas de administración.

  • rdsrepladmin: usuario que Amazon RDS utiliza internamente para respaldar las actividades de réplica en instancias y clústeres de bases de datos de RDS para MySQL.

Modelo de privilegios basado en roles

A partir de RDS para MySQL versión 8.0.36, no se pueden modificar las tablas de base de datos de mysql directamente. En concreto, no puede crear usuarios de bases de datos realizando operaciones de lenguaje de manipulación de datos (DML) en las tablas grant. En su lugar, se utilizan instrucciones de administración de cuentas de MySQL como CREATE USER, GRANT y REVOKE para conceder privilegios basados en roles a los usuarios. Tampoco puede crear otros tipos de objetos, como procedimientos almacenados en la base de datos mysql. Aún puede consultar las tablas de mysql. Si utiliza la replicación de registros binarios, los cambios realizados directamente en las tablas mysql de la instancia de base de datos de origen no se replican en el clúster de destino.

En algunos casos, la aplicación puede utilizar accesos directos para crear usuarios u otros objetos insertándolos en las tablas de mysql. Si es así, cambie el código de la aplicación para utilizar las declaraciones correspondientes, como CREATE USER.

Para exportar metadatos para usuarios de bases de datos durante la migración desde una base de datos MySQL externa, utilice uno de los siguientes métodos:

  • Utilice la utilidad de volcado de instancias del intérprete de comandos de MySQL con un filtro para excluir usuarios, roles y concesiones. En el siguiente ejemplo, se muestra la sintaxis de comandos que se utilizarán. Asegúrese de que outputUrl esté vacío.

    mysqlsh user@host -- util.dumpInstance(outputUrl,{excludeSchemas:['mysql'],users: true})

    Para obtener más información, consulte Instance Dump Utility, Schema Dump Utility, and Table Dump Utility en el Manual de referencia de MySQL.

  • Utilice la utilidad de cliente de mysqlpump. Este ejemplo incluye todas las tablas, excepto las tablas de la base de datos del sistema mysql. También incluye las instrucciones CREATE USER y GRANT para reproducir todos los usuarios de MySQL de la base de datos migrada.

    mysqlpump --exclude-databases=mysql --users

Para simplificar la administración de permisos para muchos usuarios o aplicaciones, puede utilizar la instrucción CREATE ROLE para crear un rol que tenga un conjunto de permisos. A continuación, puede utilizar las instrucciones GRANT y SET ROLE y la función current_role para asignar roles a usuarios o aplicaciones, cambiar el rol actual y verificar qué roles están en vigor. Para obtener más información sobre el sistema de permisos basado en roles de MySQL 8.0, consulte Uso de roles en el Manual de referencia de MySQL.

importante

Le recomendamos encarecidamente que no utilice el usuario maestro directamente en sus aplicaciones. En lugar de ello, es mejor ceñirse a la práctica recomendada de utilizar un usuario de base de datos creado con los privilegios mínimos necesarios para su aplicación.

A partir de la versión 8.0.36, RDS para MySQ incluye un rol especial que tiene todos los siguientes privilegios. El rol se denomina rds_superuser_role. El usuario administrativo principal de cada instancia de base de datos ya tiene asignado este rol. El rol rds_superuser_role incluye los siguientes privilegios para todos los objetos de base de datos:

  • ALTER

  • APPLICATION_PASSWORD_ADMIN

  • ALTER ROUTINE

  • CREATE

  • CREATE ROLE

  • CREATE ROUTINE

  • CREATE TEMPORARY TABLES

  • CREATE USER

  • CREATE VIEW

  • DELETE

  • DROP

  • DROP ROLE

  • EVENT

  • EXECUTE

  • INDEX

  • INSERT

  • LOCK TABLES

  • PROCESS

  • REFERENCES

  • RELOAD

  • REPLICATION CLIENT

  • REPLICATION SLAVE

  • ROLE_ADMIN

  • SET_USER_ID

  • SELECT

  • SHOW DATABASES

  • SHOW VIEW

  • TRIGGER

  • UPDATE

  • XA_RECOVER_ADMIN

La definición de rol también incluye WITH GRANT OPTION para que un usuario administrativo pueda conceder ese rol a otros usuarios. En particular, el administrador debe conceder los privilegios necesarios para realizar la replicación de registros binarios con el clúster de MySQL como destino.

sugerencia

Para ver todos los detalles de los permisos, utilice la siguiente instrucción.

SHOW GRANTS FOR rds_superuser_role@'%';

Cuando concede acceso mediante roles en RDS para MySQL versión 8.0.36 y posteriores, también activa el rol mediante la instrucción SET ROLE role_name o SET ROLE ALL. El siguiente ejemplo muestra cómo. Sustituya el nombre de rol apropiado por CUSTOM_ROLE.

# Grant role to user
mysql> GRANT CUSTOM_ROLE TO 'user'@'domain-or-ip-address'

# Check the current roles for your user. In this case, the CUSTOM_ROLE role has not been activated.
# Only the rds_superuser_role is currently in effect.
mysql> SELECT CURRENT_ROLE();
+--------------------------+
| CURRENT_ROLE()           |
+--------------------------+
| `rds_superuser_role`@`%` |
+--------------------------+
1 row in set (0.00 sec)

# Activate all roles associated with this user using SET ROLE.
# You can activate specific roles or all roles.
# In this case, the user only has 2 roles, so we specify ALL.
mysql> SET ROLE ALL;
Query OK, 0 rows affected (0.00 sec)

# Verify role is now active
mysql> SELECT CURRENT_ROLE();
+--------------------------------------------------+
| CURRENT_ROLE()                                   |
+--------------------------------------------------+
| `CUSTOM_ROLE`@`%`,`rds_superuser_role`@`%` |
+--------------------------------------------------+

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

Puede omitir un error en la réplica de lectura si el error está haciendo que la réplica de lectura deje de responder y el error no afecta a la integridad de los datos.

nota

Primero verifique que el error en cuestión se puede omitir con seguridad. En una utilidad MySQL, conéctese a la réplica de lectura y ejecute el siguiente comando MySQL. 

SHOW REPLICA STATUS\G

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

Las versiones anteriores de MySQL usaban SHOW SLAVE STATUS en lugar de SHOW REPLICA STATUS. Si usa una versión de MySQL anterior a la 8.0.23, utilice SHOW SLAVE STATUS.

Puede omitir un error en su réplica de lectura de las siguientes maneras.

Llamar al procedimiento mysql.rds_skip_repl_error

Amazon RDS proporciona un procedimiento almacenado al que puede llamar para omitir un error en las réplicas de lectura. En primer lugar, conéctese a la réplica de lectura y, a continuación, emita los comandos correspondientes 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 omitir el error, emita 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.

Configuración del parámetro slave_skip_errors

Para omitir uno o varios errores, puede configurar el parámetro estático slave_skip_errors en la réplica de lectura. Puede configurar este parámetro para omitir uno o varios códigos de error de replicación específicos. Actualmente, puede establecer este parámetro solo para instancias de bases de datos de RDS for MySQL 5.7. Después de cambiar la configuración de este parámetro, asegúrese de reiniciar su instancia de base de datos para que la nueva configuración surta efecto. Para obtener información sobre cómo funciona este parámetro, consulte la documentación de MySQL.

Recomendamos configurar este parámetro en un grupo de parámetros de base de datos independiente. Puede asociar este grupo de parámetros de base de datos solo a las réplicas de lectura que necesitan omitir errores. Seguir esta práctica recomendada reduce el impacto potencial en otras instancias de base de datos y réplicas de lectura.

importante

Establecer un valor no predeterminado para este parámetro puede provocar una incoherencia de la replicación. Establezca este parámetro solo en un valor no predeterminado si ha agotado otras opciones para resolver el problema y está seguro del posible impacto en los datos de la réplica de lectura.

Uso de espacios de tablas de InnoDB para mejorar los tiempos de recuperación tras un 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 agrega 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 para el parámetro innodb_file_per_table en 1, que le 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.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 AWS Management Console 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.

Administración del historial de estado global (Global Status History)

sugerencia

Para analizar el rendimiento de la base de datos, también puede utilizar Información de rendimiento en Amazon RDS. Para obtener más información, consulte Monitoreo de la carga de base de datos con Performance Insights en Amazon RDS.

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 habilitar GoSH, primero debe habilitar el programador de eventos desde un grupo de parámetros de base de datos estableciendo el parámetro event_scheduler en ON. Para las instancias de base de datos MySQL que ejecutan MySQL 5.7, configure también el parámetro show_compatibility_56 en 1. Para obtener información acerca de cómo crear y modificar un grupo de parámetros de base de datos, consulte Working with parameter groups (Trabajar con grupos de parámetros). Para obtener información acerca de los efectos secundarios de la habilitación de este parámetro, consulte show_compatibility_56 en el Manual de referencia de MySQL 5.7.

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'