2. ADMINISTRACIÓN DE BASE DE DATOS

Convertirse en DBA por un día

Vamos a administrar una base de datos de una tienda online pequeña. ¡Manos a la obra!

📦 PASO 1: Crear y Configurar la Base de Datos

1.1 Conectarse como Administrador

sql

-- Conectarse a MySQL como root (DBA)

mysql -u root -p

1.2 Crear la Base de Datos

sql

-- Crear base de datos de tienda

CREATE DATABASE tienda_online;

-- Verificar que se creó

SHOW DATABASES LIKE 'tienda_online';

-- Usar la base de datos

USE tienda_online;

1.3 Crear Tablas Básicas

sql

-- Tabla de productos

CREATE TABLE productos (

id INT PRIMARY KEY AUTO_INCREMENT,

nombre VARCHAR(100) NOT NULL,

precio DECIMAL(10,2) NOT NULL,

stock INT DEFAULT 0,

categoria VARCHAR(50),

fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- Tabla de clientes

CREATE TABLE clientes (

id INT PRIMARY KEY AUTO_INCREMENT,

nombre VARCHAR(100) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,

telefono VARCHAR(20),

activo BOOLEAN DEFAULT TRUE

);

-- Tabla de pedidos

CREATE TABLE pedidos (

id INT PRIMARY KEY AUTO_INCREMENT,

cliente_id INT,

fecha_pedido DATE DEFAULT (CURRENT_DATE),

total DECIMAL(10,2),

estado ENUM('pendiente', 'procesando', 'enviado', 'entregado') DEFAULT 'pendiente',

FOREIGN KEY (cliente_id) REFERENCES clientes(id)

);

👨‍💼 PASO 2: Tareas del DBA - Gestión de Usuarios

2.1 Crear Usuarios con Diferentes Roles

sql

-- 1. Usuario SOLO para consultas (lectura)

CREATE USER 'lector'@'localhost' IDENTIFIED BY 'Lector123!';

GRANT SELECT ON tienda_online.* TO 'lector'@'localhost';

-- 2. Usuario para la aplicación (CRUD básico)

CREATE USER 'app_tienda'@'%' IDENTIFIED BY 'AppTienda123!';

GRANT SELECT, INSERT, UPDATE ON tienda_online.* TO 'app_tienda'@'%';

-- 3. Usuario para reportes (solo algunas tablas)

CREATE USER 'reportes'@'localhost' IDENTIFIED BY 'Reportes123!';

GRANT SELECT ON tienda_online.clientes TO 'reportes'@'localhost';

GRANT SELECT ON tienda_online.pedidos TO 'reportes'@'localhost';

-- 4. Usuario administrador junior

CREATE USER 'admin_junior'@'localhost' IDENTIFIED BY 'AdminJr123!';

GRANT ALL PRIVILEGES ON tienda_online.* TO 'admin_junior'@'localhost';

-- Ver usuarios creados

SELECT user, host FROM mysql.user WHERE user LIKE '%tienda%' OR user LIKE '%lector%' OR user LIKE '%reportes%';

2.2 Probar Permisos

bash

# Conectarse como usuario "lector"

mysql -u lector -p

sql

-- Intentar SELECT (debería funcionar)

USE tienda_online;

SELECT * FROM productos;

-- Intentar INSERT (debería FALLAR)

INSERT INTO productos (nombre, precio) VALUES ('Test', 100);

-- ERROR: Access denied

💾 PASO 3: Backup y Restauración

3.1 Backup Completo de la Base de Datos

bash

# Desde la terminal (no dentro de mysql)

mysqldump -u root -p tienda_online > backup_tienda_$(date +%Y%m%d).sql

# Backup comprimido (recomendado)

mysqldump -u root -p tienda_online | gzip > backup_tienda_$(date +%Y%m%d).sql.gz

# Backup de solo estructura

mysqldump -u root -p --no-data tienda_online > estructura_tienda.sql

# Backup de solo datos

mysqldump -u root -p --no-create-info tienda_online > datos_tienda.sql

3.2 Backup Automático (Cron Job)

bash

# Crear script de backup automático

nano /usr/local/bin/backup_tienda.sh

bash

#!/bin/bash

# backup_tienda.sh

BACKUP_DIR="/backups/tienda"

DATE=$(date +%Y%m%d_%H%M%S)

# Crear directorio si no existe

mkdir -p $BACKUP_DIR

# Hacer backup

mysqldump -u root -pTuPassword tienda_online | gzip > $BACKUP_DIR/tienda_$DATE.sql.gz

# Eliminar backups antiguos (más de 7 días)

find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completado: $BACKUP_DIR/tienda_$DATE.sql.gz"

bash

# Hacer ejecutable

chmod +x /usr/local/bin/backup_tienda.sh

# Agregar a cron para ejecutar diario a las 2 AM

crontab -e

# Agregar esta línea:

0 2 * * * /usr/local/bin/backup_tienda.sh

3.3 Restaurar Base de Datos

bash

# Restaurar desde backup

mysql -u root -p tienda_online < backup_tienda_20240115.sql

# Restaurar desde backup comprimido

gunzip < backup_tienda_20240115.sql.gz | mysql -u root -p tienda_online

# Restaurar en nueva base de datos

mysql -u root -p -e "CREATE DATABASE tienda_online_respaldo"

mysql -u root -p tienda_online_respaldo < backup_tienda_20240115.sql

🔍 PASO 4: Monitoreo y Mantenimiento

4.1 Ver Estado de la Base de Datos

sql

-- Ver tamaño de la base de datos

SELECT

table_schema AS 'Base de Datos',

SUM(data_length + index_length) / 1024 / 1024 AS 'Tamaño (MB)'

FROM information_schema.tables

WHERE table_schema = 'tienda_online'

GROUP BY table_schema;

-- Ver tamaño de cada tabla

SELECT

table_name AS 'Tabla',

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Tamaño (MB)',

table_rows AS 'Filas'

FROM information_schema.tables

WHERE table_schema = 'tienda_online'

ORDER BY (data_length + index_length) DESC;

-- Ver conexiones activas

SHOW PROCESSLIST;

-- Ver estadísticas importantes

SHOW STATUS LIKE 'Threads_connected';

SHOW STATUS LIKE 'Uptime';

SHOW STATUS LIKE 'Questions'; -- Número de consultas

4.2 Optimizar Tablas

sql

-- Analizar uso de tablas

ANALYZE TABLE productos;

ANALYZE TABLE clientes;

ANALYZE TABLE pedidos;

-- Optimizar tablas (reorganiza espacio)

OPTIMIZE TABLE productos;

-- Ver índices de una tabla

SHOW INDEX FROM productos;

-- Crear índice para mejorar búsquedas

CREATE INDEX idx_productos_nombre ON productos(nombre);

CREATE INDEX idx_pedidos_fecha ON pedidos(fecha_pedido);

CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id, fecha_pedido);

4.3 Script de Mantenimiento Diario

sql

-- maintenance_daily.sql

-- Ejecutar estas consultas diariamente

-- 1. Verificar integridad de tablas

CHECK TABLE productos;

CHECK TABLE clientes;

CHECK TABLE pedidos;

-- 2. Actualizar estadísticas

ANALYZE TABLE productos, clientes, pedidos;

-- 3. Limpiar sesiones viejas

-- (MySQL hace esto automáticamente, pero podemos ver)

SHOW PROCESSLIST;

-- 4. Verificar espacio disponible

SELECT

table_schema,

SUM(data_length + index_length) / 1024 / 1024 as 'Tamaño Actual (MB)',

(SUM(data_free) / 1024 / 1024) as 'Espacio Libre (MB)'

FROM information_schema.tables

WHERE table_schema = 'tienda_online'

GROUP BY table_schema;

🚨 PASO 5: Seguridad y Auditoría

5.1 Configurar Logs

sql

-- Habilitar log general (solo para debugging)

SET GLOBAL general_log = 'ON';

SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Habilitar log de queries lentas

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 2; -- Queries que toman más de 2 segundos

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Ver configuración actual

SHOW VARIABLES LIKE '%log%';

5.2 Auditoría Básica

sql

-- Crear tabla de auditoría

CREATE TABLE auditoria_log (

id INT PRIMARY KEY AUTO_INCREMENT,

usuario VARCHAR(100),

accion VARCHAR(50),

tabla VARCHAR(50),

fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

detalles TEXT

);

-- Crear trigger para auditar INSERT en productos

DELIMITER $$

CREATE TRIGGER audit_insert_productos

AFTER INSERT ON productos

FOR EACH ROW

BEGIN

INSERT INTO auditoria_log (usuario, accion, tabla, detalles)

VALUES (USER(), 'INSERT', 'productos',

CONCAT('Nuevo producto: ', NEW.nombre, ' - Precio: ', NEW.precio));

END$$

DELIMITER ;

-- Crear trigger para auditar DELETE

DELIMITER $$

CREATE TRIGGER audit_delete_clientes

BEFORE DELETE ON clientes

FOR EACH ROW

BEGIN

INSERT INTO auditoria_log (usuario, accion, tabla, detalles)

VALUES (USER(), 'DELETE', 'clientes',

CONCAT('Cliente eliminado: ', OLD.nombre, ' - Email: ', OLD.email));

END$$

DELIMITER ;

-- Ver registros de auditoría

SELECT * FROM auditoria_log ORDER BY fecha DESC LIMIT 10;

5.3 Revisar Privilegios

sql

-- Ver permisos de un usuario

SHOW GRANTS FOR 'app_tienda'@'%';

-- Ver todos los usuarios y sus privilegios

SELECT

user,

host,

Grant_priv as 'Puede dar permisos?',

Super_priv as 'Super usuario?'

FROM mysql.user

ORDER BY user;

🚀 PASO 6: Tareas Automáticas con Eventos

6.1 Crear Evento para Limpieza Automática

sql

-- Habilitar scheduler de eventos

SET GLOBAL event_scheduler = ON;

-- Crear evento que se ejecuta diario a las 3 AM

DELIMITER $$

CREATE EVENT limpieza_diaria

ON SCHEDULE EVERY 1 DAY

STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 3 HOUR

BEGIN

-- Archivar pedidos muy antiguos

INSERT INTO pedidos_archivo

SELECT * FROM pedidos

WHERE fecha_pedido < CURDATE() - INTERVAL 2 YEAR;

DELETE FROM pedidos

WHERE fecha_pedido < CURDATE() - INTERVAL 2 YEAR;

-- Actualizar estadísticas

ANALYZE TABLE productos, clientes, pedidos;

-- Registrar en log

INSERT INTO auditoria_log (usuario, accion, tabla, detalles)

VALUES ('Sistema', 'Mantenimiento', 'Todos', 'Limpieza diaria ejecutada');

END$$

DELIMITER ;

-- Ver eventos programados

SHOW EVENTS;

6.2 Evento para Backup de Tablas Críticas

sql

DELIMITER $$

CREATE EVENT backup_clientes_diario

ON SCHEDULE EVERY 1 DAY

STARTS CURRENT_TIMESTAMP

BEGIN

-- Crear copia de seguridad de clientes

CREATE TABLE IF NOT EXISTS clientes_backup LIKE clientes;

TRUNCATE TABLE clientes_backup;

INSERT INTO clientes_backup SELECT * FROM clientes;

INSERT INTO auditoria_log (usuario, accion, tabla, detalles)

VALUES ('Sistema', 'Backup', 'clientes', 'Backup diario de clientes');

END$$

DELIMITER ;

📊 PASO 7: Reportes para el DBA

7.1 Crear Vista de Reporte Diario

sql

-- Vista para ver actividad diaria

CREATE VIEW reporte_diario AS

SELECT

DATE(fecha_pedido) as fecha,

COUNT(*) as total_pedidos,

SUM(total) as venta_total,

AVG(total) as promedio_venta,

COUNT(DISTINCT cliente_id) as clientes_unicos

FROM pedidos

WHERE fecha_pedido = CURDATE()

GROUP BY DATE(fecha_pedido);

-- Consultar la vista

SELECT * FROM reporte_diario;

-- Vista para monitoreo de sistema

CREATE VIEW estado_sistema AS

SELECT

(SELECT COUNT(*) FROM productos) as total_productos,

(SELECT COUNT(*) FROM clientes WHERE activo = 1) as clientes_activos,

(SELECT COUNT(*) FROM pedidos WHERE estado = 'pendiente') as pedidos_pendientes,

(SELECT SUM(stock) FROM productos) as stock_total,

(SELECT COUNT(*) FROM mysql.processlist) as conexiones_activas;

7.2 Script de Salud de la BD

bash

#!/bin/bash

# health_check.sh

# Script para verificar salud de la base de datos

echo "=== VERIFICACIÓN DE SALUD - $(date) ==="

echo ""

# 1. Verificar si MySQL está corriendo

if systemctl is-active --quiet mysql; then

echo "✅ MySQL está activo"

else

echo "❌ MySQL NO está activo"

exit 1

# 2. Verificar conexión

if mysql -u root -pTuPassword -e "SELECT 1" > /dev/null 2>&1; then

echo "✅ Conexión exitosa a MySQL"

else

echo "❌ Error de conexión a MySQL"

exit 1

# 3. Verificar espacio en disco

ESPACIO=$(df -h /var/lib/mysql | awk 'NR==2 {print $5}' | sed 's/%//')

if [ $ESPACIO -lt 80 ]; then

echo "✅ Espacio en disco: $ESPACIO% (OK)"

else

echo "⚠️ Espacio en disco: $ESPACIO% (CRÍTICO)"

# 4. Verificar réplicas si existen

echo ""

echo "=== ESTADÍSTICAS ==="

mysql -u root -pTuPassword -e "

SELECT

'Conexiones activas' as Metrica,

VARIABLE_VALUE as Valor

FROM performance_schema.global_status

WHERE VARIABLE_NAME = 'Threads_connected'

UNION

SELECT

'Uptime (días)',

ROUND(VARIABLE_VALUE/86400, 2)

FROM performance_schema.global_status

WHERE VARIABLE_NAME = 'Uptime'

UNION

SELECT

'Consultas por segundo',

ROUND(VARIABLE_VALUE/@@Uptime, 2)

FROM performance_schema.global_status

WHERE VARIABLE_NAME = 'Questions'

" | column -t -s $'\t'

🎯 Resumen: Checklist del DBA Diario

Tareas Diarias:

Verificar backups automáticos ✓
Revisar logs de errores ✓
Monitorear espacio en disco ✓
Verificar conexiones activas ✓
Revisar queries lentas ✓

Tareas Semanales:

Optimizar tablas grandes ✓
Actualizar estadísticas ✓
Revisar permisos de usuarios ✓
Probar restauración de backup ✓

Tareas Mensuales:

Revisar y limpiar logs viejos ✓
Actualizar software (MySQL) ✓
Revisar plan de crecimiento ✓
Documentar cambios ✓

🆘 Solución de Problemas Comunes

Problema: Base de datos lenta

sql

-- 1. Ver queries lentas

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 2. Ver tablas sin índices

SELECT

table_name,

index_name,

COUNT(*) as missing_index

FROM information_schema.statistics

WHERE table_schema = 'tienda_online'

GROUP BY table_name

HAVING COUNT(*) = 1; -- Solo tiene índice PRIMARY

Problema: Error de espacio en disco

sql

-- 1. Ver tablas más grandes

SELECT

table_name,

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb

FROM information_schema.tables

WHERE table_schema = 'tienda_online'

ORDER BY size_mb DESC;

-- 2. Archivar datos antiguos

CREATE TABLE pedidos_2023 LIKE pedidos;

INSERT INTO pedidos_2023

SELECT * FROM pedidos

WHERE fecha_pedido < '2024-01-01';

DELETE FROM pedidos WHERE fecha_pedido < '2024-01-01';

Problema: Usuario bloqueado

sql

-- Ver procesos y matar si es necesario

SHOW PROCESSLIST;

-- Anotar ID del proceso problemático

KILL [ID_DEL_PROCESO];

-- Desbloquear tablas si hay bloqueo

UNLOCK TABLES;

📚 Comandos de Emergencia para DBA

bash

# 1. Reiniciar MySQL seguro

sudo systemctl restart mysql

# 2. Forzar recuperación si no inicia

sudo mysqld_safe --skip-grant-tables &

# 3. Resetear password de root

sudo systemctl stop mysql

sudo mysqld_safe --skip-grant-tables --skip-networking &

mysql -u root

# Dentro de MySQL:

FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NuevaPassword123!';

FLUSH PRIVILEGES;

exit;

# Luego reiniciar MySQL normal

🎓 Ejercicio Final: Ser DBA por 1 Hora

Tu misión:

Crea la base de datos escuela
Crea tablas: estudiantes, cursos, matriculas
Crea 3 usuarios con diferentes permisos
Configura backup automático
Crea una vista de reporte
Configura un evento de limpieza mensual

Solución guiada:

sql

-- 1. Crear BD

CREATE DATABASE escuela;

USE escuela;

-- 2. Crear tablas (¡completa tú!)

CREATE TABLE estudiantes (...);

CREATE TABLE cursos (...);

-- 3. Crear usuarios

CREATE USER 'profesor'@'%' IDENTIFIED BY '...';

GRANT SELECT, INSERT ON escuela.* TO 'profesor'@'%';

-- 4. Backup (ejecuta en terminal)

# mysqldump -u root -p escuela > backup_escuela.sql

-- ¡Sigue practicando!

💡 Consejo Final: Un buen DBA es proactivo, no reactivo.

Monitorea antes de que haya problemas
Haz backups antes de que sea necesario
Documenta TODO lo que haces

¡Ahora tienes las herramientas básicas para administrar una base de datos SQL!