Configuración y Arquitectura de MySQL

Instalación con Docker

# docker-compose.yml
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: myapp
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

volumes:
  mysql_data:

Motores de Almacenamiento

-- Verificar motor de una tabla
SHOW TABLE STATUS WHERE Name = 'orders';

-- Cambiar motor de almacenamiento
ALTER TABLE my_table ENGINE = InnoDB;

Configuración del Servidor (my.cnf)

[mysqld]
# Memoria
innodb_buffer_pool_size     = 4G
innodb_log_file_size        = 512M
key_buffer_size             = 256M

# Conexiones
max_connections             = 200
thread_cache_size           = 40

# Rendimiento
innodb_flush_log_at_trx_commit = 1
sync_binlog                 = 1
innodb_file_per_table       = ON

# Registro lento de consultas
slow_query_log              = 1
slow_query_log_file         = /var/log/mysql/slow.log
long_query_time             = 1

Charset y Collation

-- Crear base de datos con charset correcto
CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Configurar a nivel de tabla
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    body TEXT
) CHARACTER SET utf8mb4;

Gestión de Usuarios y Privilegios

-- Crear usuario
CREATE USER 'appuser'@'%' IDENTIFIED BY 'SecureP@ss1!';

-- Otorgar privilegios
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';

-- Privilegios de solo lectura para analytics
GRANT SELECT ON myapp.* TO 'analytics'@'%';

-- Ver privilegios
SHOW GRANTS FOR 'appuser'@'%';

-- Revocar privilegio
REVOKE DELETE ON myapp.* FROM 'appuser'@'%';

FLUSH PRIVILEGES;

Copia de Seguridad con mysqldump

# Backup completo
mysqldump -u root -p myapp > myapp_backup.sql

# Backup de una tabla
mysqldump -u root -p myapp orders > orders_backup.sql

# Restaurar
mysql -u root -p myapp < myapp_backup.sql