Optimización y Configuración Esencial de Servidores MySQL: Variables y Transacciones ACID

Configuración Inicial y Tipos de Servidor

Tipos de Servidor

• Developer Machine: Convive con otras aplicaciones.
• Server Machine: Convive con otros servicios, como el alojamiento de ficheros.
• Dedicated MySQL Server Machine: Alojado en un servidor dedicado.

Tipo de Almacenamiento de Datos

• Base de Datos Multifuncional: Almacena tablas con control de transacciones (InnoDB) y sin control (MyISAM).
• Base de Datos Transaccional: Sus tablas deben controlar las transacciones de forma segura.
• Base de Datos No Transaccional: Prioriza la velocidad de acceso a la información contenida en sus tablas.

Número de Conexiones Simultáneas

• DECISION SUPPORT: 100 conexiones simultáneas.
• ONLINE TRANSACTION PROCESSING (OLTP): 500 conexiones simultáneas.
• MANUAL SETTING: El usuario decide cuántas conexiones se van a realizar de forma manual.

Variables de Sistema y Comandos de Diagnóstico

Variables del Sistema (Directorios)

Componentes principales del sistema de gestión de bases de datos:

• Bin: Ejecutable, programa cliente y servidor (mysqld).
• Data: Ficheros de registro y de bases de datos.
• Docs: Documentación.
• Examples: Programas y scripts de ejemplo.
• Include: Ficheros de inclusión.
• Lib: Bibliotecas.
• Scripts: Scripts de utilidades.
• Share: Ficheros con mensajes de error.

Tipos de Variables

• Dinámicas/Estáticas: Definidas por el tiempo de ejecución.
• Globales/De Sesión: Aplicables a todas las sesiones o a un cliente específico.
• De Estado/De Sistema: Reflejan el estado o comportamiento actual del servidor.

Comandos de Visualización (SHOW)

• SHOW VARIABLES;: Muestra los valores de las variables de configuración.
• SHOW STATUS;: Muestra las variables de estado del servidor.
• SHOW STATUS LIKE 'patron';: Filtra las variables de estado que coinciden con el patrón.
• SHOW BINARY LOGS;: Listado de los ficheros de log binarios.
• SHOW ENGINES;: Información sobre el tipo de tablas o motores de almacenamiento.
• SHOW OPEN TABLES;: Muestra las tablas abiertas de la base de datos. Puede incluir filtros (FROM / IN) db_name, LIKE 'pattern' o condición WHERE expr.
• SHOW PROCESSLIST;: Muestra los procesos activos en el servidor.

Gestión de Transacciones y Control de Integridad

Una transacción es una secuencia de una o más sentencias SQL que juntas forman una unidad de trabajo. Dispone de procedimientos que aseguran la integridad de la información ante fallos de tipo físico, lógico o humano, así como proteger los datos frente a accesos no autorizados.

Propiedades ACID

Las transacciones deben cumplir con las siguientes propiedades fundamentales:

• Atomicidad: La transacción se ejecuta completamente o no tiene efecto alguno.
• Consistencia (Conservación de la Consistencia): Deja la base de datos (BD) en un estado consistente.
• Aislamiento: Las transacciones no se ven influenciadas por otras transacciones concurrentes.
• Durabilidad o Permanencia: Los efectos de la transacción son permanentes una vez confirmados.

Control de Transacciones

Para evitar el problema de la información perdida, el SGBD realiza lo que se conoce como transacción, apoyándose en uno o más ficheros diarios de extensión LOG. Cada cierto tiempo, el SGBD realiza un CHECKPOINT. Cuando realiza ese chequeo, graba en ciertas áreas de memoria secundaria el estado de la BD.

El SGBD deshace las transacciones que no hubieran concluido. Si la recuperación de la BD se hace a través de un BACKUP, se conoce como Recuperación en Frío.

Activación y Comandos de Transacción

Por defecto, el sistema funciona en modo autocommit. Para activar las transacciones de múltiples sentencias, hay que establecer el modo:

SET AUTOCOMMIT=OFF;

Todos los comandos SQL tendrán que terminar con una orden COMMIT o una orden ROLLBACK.

1. Empezar: START TRANSACTION;
2. Volver atrás: ROLLBACK;
3. Confirmar cambios: COMMIT;
4. Nueva transacción: START TRANSACTION;

Gestión de Privilegios de Usuario

Niveles de Privilegios

Los privilegios definen el alcance de las acciones que un usuario puede realizar:

• Globales: Se aplican al conjunto de todas las bases de datos en un servidor (es el nivel más alto de privilegio).
• De Base de Datos: Aplicables a bases de datos individuales y a los objetos que contienen.
• De Tabla: Aplicables a tablas individuales y columnas de esas tablas.
• De Columna: Aplicables a una columna específica en una tabla.
• De Script: Aplicables a los procedimientos almacenados.

Sintaxis para Crear Usuarios y Asignar Privilegios (GRANT)

GRANT ALL | [PRIVILEGIO1 (columna1, columna2...), PRIVILEGIO2 (columna1, columna2...)...] ON *(base de datos).*(tabla) TO 'usuario'@'localhost' IDENTIFIED BY 'contraseña' [WITH GRANT OPTION | MAX_QUERIES_PER_HOUR nº | MAX_UPDATES_PER_HOUR nº | MAX_CONNECTIONS_PER_HOUR nº | MAX_USER_CONNECTIONS nº]

Opciones de la Cláusula WITH

• MAX_QUERIES_PER_HOUR nº: Restringe el número de consultas por hora.
• MAX_UPDATES_PER_HOUR nº: Restringe el número de actualizaciones por hora.
• MAX_CONNECTIONS_PER_HOUR nº: Restringe el número de inicios de sesión (logins) por hora.
• MAX_USER_CONNECTIONS nº: Restringe el número de conexiones simultáneas. (Si nº=0, es ilimitado. Si hay más de una opción MAX_, van separadas sin comas).

Uso de REVOKE (Revocación de Privilegios)

REVOKE [PRIV1 (columna1,...), PRIV2 (columna1,...)] ON *(bd).*(tabla) FROM 'usuario'@'localhost';

Para listar usuarios:

USE mysql; SELECT user FROM user;