Curso de MariaDB / Práctica final de pedidos, permisos, copia y restauración

Esta práctica final crea una base de pedidos, comprueba una transacción de pedido y existencias con una cuenta restringida y verifica una copia del estado confirmado mediante una restauración separada. La estructura, los cambios de negocio, los permisos y la recuperación necesitan comprobaciones propias; un único mensaje de SQL correcto no las sustituye.

Definir las funciones de las cuentas y los resultados esperados

Utilice una base nueva final48_lab, separada de servicios reales de pedidos o pagos. Cree clientes, productos, pedidos y líneas y confirme el pedido 105 antes de copiar. Distinga la cuenta administrativa, la cuenta de prácticas con permisos de estructura y la cuenta restringida de la aplicación. Un programa real necesita además validar entradas, vincular parámetros, autenticar peticiones y evitar que una petición repetida duplique su efecto.

La guía de transacciones InnoDB explica los límites de confirmación y cancelación; la práctica de usuarios y permisos muestra cómo comprobar rechazos desde la cuenta correcta.

Etapa Comprobación de finalización
Estructura Cuatro tablas InnoDB con referencias y restricciones
Pedido nuevo Existencias del producto 1: 20 → 18; productos del pedido 105: 7000
Permisos Lecturas, inserciones y cambios de existencias necesarios
Restauración Otra base vacía: recuentos 4, 4, 5 y 7
Conciliación Importe pagado 46000; filas y restricciones coincidentes

Crear una base dedicada y el juego inicial completo

Desde una conexión administrativa al servidor de prácticas, revise SELECT VERSION();, SHOW DATABASES y las cuentas. Confirme que final48_lab, final48_restore y 'final48_app'@'127.0.0.1' no están en uso. Si existen, elija nombres exclusivos y adapte los bloques; no los sobrescriba ni los elimine para ejecutar el ejemplo.

CREATE DATABASE final48_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE final48_lab;
CREATE TABLE c48_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c48_order_customer FOREIGN KEY (customer_id)
  REFERENCES c48_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c48_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c48_item_order FOREIGN KEY (order_id)
  REFERENCES c48_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c48_item_product FOREIGN KEY (product_id)
  REFERENCES c48_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c48_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c48_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c48_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c48_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Se conservan los nombres y las ciudades originales en coreano. Los productos 1–4 son un cuaderno, un bolígrafo, una taza y una lámpara. Los clientes 1 y 3 viven en Seúl; el 2 en Busan y el 4 en Daejeon. Mantenga los literales UTF-8 para reproducir los resultados.

Desde la conexión administrativa, conceda a course_user acceso de gestión solo a esta base de prácticas si lo necesita. Su permiso previo sobre shopcourse no se extiende automáticamente a ella.

GRANT ALL PRIVILEGES ON final48_lab.* TO 'course_user'@'127.0.0.1';

Los recuentos iniciales son cuatro clientes, cuatro productos, cuatro pedidos y seis líneas. Inspeccione las claves foráneas y las condiciones de cantidad y precio mediante SHOW CREATE TABLE. El precio actual del catálogo y unit_price, el precio guardado en cada compra, representan hechos distintos.

Conectar con una cuenta limitada al flujo previsto

Como administrador, sustituya la contraseña de demostración por una única y cree la cuenta siguiente. El flujo requiere leer productos, insertar pedidos y líneas y actualizar existencias; no necesita eliminar estructuras, administrar usuarios, cambiar precios ni borrar pedidos. Mantenga privada la salida de SHOW GRANTS, que puede incluir información de autenticación.

CREATE USER 'final48_app'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON final48_lab.* TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_orders TO 'final48_app'@'127.0.0.1';
GRANT INSERT ON final48_lab.c48_items TO 'final48_app'@'127.0.0.1';
GRANT UPDATE(stock) ON final48_lab.c48_products TO 'final48_app'@'127.0.0.1';
SHOW GRANTS FOR 'final48_app'@'127.0.0.1';

Desde la terminal del sistema operativo, abra una conexión nueva con esa cuenta e introduzca la contraseña cuando se solicite. Ajuste el puerto si corresponde:

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=final48_app --password --default-character-set=utf8mb4 final48_lab

Dentro del cliente, confirme SELECT CURRENT_USER(); y una lectura de productos. Una operación ALTER TABLE no concedida debe ser rechazada. En una aplicación, puede limitar más el acceso según las tablas, columnas y roles realmente necesarios; SELECT sobre toda esta base es una elección explícita del ejercicio. La referencia oficial de GRANT documenta los ámbitos de permisos.

Comprobar y confirmar juntos existencias, pedido y línea

USE final48_lab;
START TRANSACTION;
SELECT product_id,price,stock FROM c48_products WHERE product_id=1 FOR UPDATE;
UPDATE c48_products SET stock=stock-2 WHERE product_id=1 AND stock>=2;
SELECT ROW_COUNT() AS reserved_rows;

Ejecute el siguiente bloque solo si reserved_rows vale 1. Si vale cero o recibe un error, ejecute ROLLBACK; en esta conexión y no inserte el pedido ni sus líneas. La condición impide descontar unidades cuando no se cumple el saldo mínimo; el bloqueo se mantiene dentro de la transacción.

INSERT INTO c48_orders(order_id,customer_id,ordered_at,status,shipping_fee)
VALUES(105,4,'2026-03-01 10:00:00','paid',1000);
INSERT INTO c48_items(order_id,product_id,quantity,unit_price)
VALUES(105,1,2,3500);
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c48_items WHERE order_id=105 GROUP BY order_id;

Con los datos iniciales, la reserva debe afectar una fila y el total del pedido 105 debe ser 7000.00. Si una sentencia falla, deténgase antes de continuar y cancele todo el intento. La secuencia usa entradas conocidas y supervisión manual: un programa debe implementar esas ramas y pasar valores mediante parámetros del controlador.

Solo después de las comprobaciones satisfactorias, confirme:

COMMIT;
SELECT stock FROM c48_products WHERE product_id=1;
SELECT COUNT(*) FROM c48_orders;
SELECT COUNT(*) FROM c48_items;

Las existencias del producto 1 son 18 y hay cinco pedidos y siete líneas. Repetir el ID 105 provoca un error de clave duplicada. Diseñe la detección de peticiones repetidas en lugar de ocultar el error con IGNORE o REPLACE. Si ya descontó existencias en un intento fallido, ROLLBACK debe cancelar ese descuento antes de reintentar. Un ROLLBACK posterior no deshace la operación que ya confirmó correctamente.

Copiar el estado confirmado y preparar otra base para restaurar

Utilice una conexión course_user con permisos para copiar, en lugar de la cuenta restringida de la aplicación. En Windows PowerShell, cree un nombre de archivo nuevo, compruebe el estado de salida y revise el contenido del volcado para detectar cambios de base. El bloque siguiente se ejecuta en la terminal, no dentro del cliente SQL.

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/final48-checked.sql'
if (Test-Path -LiteralPath $courseBackupPath) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=$courseBackupPath final48_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256

Como administrador, cree la base de restauración que confirmó libre y conceda solo el acceso de prácticas necesario:

CREATE DATABASE final48_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON final48_restore.* TO 'course_user'@'127.0.0.1';

Desde la terminal, conéctese al destino con interrupción de SOURCE ante errores:

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore

Dentro del cliente, compruebe la base y que no existen tablas antes de importar el archivo de confianza. Si el destino no está vacío, deténgase.

SELECT DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/final48-checked.sql;

Las condiciones de mariadb-dump se aplican a la instantánea InnoDB y a los cambios estructurales concurrentes; la guía de restauración y comprobación explica la compatibilidad del cliente y los fallos parciales. El volcado de la base no restaura todas las cuentas ni la configuración del servidor. No redirija automáticamente la cuenta de la aplicación a la restauración de prueba.

Alternativa de copia en una terminal de Ubuntu: elija este bloque o el de PowerShell. El subshell limita los permisos restrictivos y la prohibición de sobrescritura a esta operación.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/final48-checked.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables final48_lab > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

Si ya existe el archivo, el comando debe fallar sin sobrescribirlo. No restaure un archivo parcial tras un error: diagnostique la causa y use un nombre nuevo al reintentar. El tamaño y la huella SHA-256 identifican el archivo, pero no demuestran recuperabilidad.

Para restaurar desde Ubuntu, abra el mismo destino con el comando siguiente y sustituya la ruta SOURCE por la ruta absoluta real de su archivo. learner es un nombre de usuario de ejemplo.

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 final48_restore
SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/final48-checked.sql;

Ejecute solo una variante de restauración por destino. Detener SOURCE impide que continúen las siguientes sentencias del archivo, pero no revierte objetos o cambios anteriores. Tras un fallo, corrija la causa y utilice otro destino recién preparado y vacío.

Ante errores TLS, compruebe versiones, nombre de certificado, autoridad de confianza y cualquier certificado de cliente necesario según los requisitos oficiales de cifrado. Aplique las opciones adecuadas a ambas herramientas, sin desactivar rutinariamente la verificación. La referencia oficial de mariadb-dump amplía sus condiciones y opciones.

Comparar el origen, la restauración y los accesos rechazados

SELECT (SELECT COUNT(*) FROM final48_restore.c48_customers) AS customers,
 (SELECT COUNT(*) FROM final48_restore.c48_products) AS products,
 (SELECT COUNT(*) FROM final48_restore.c48_orders) AS orders_count,
 (SELECT COUNT(*) FROM final48_restore.c48_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM final48_restore.c48_orders o JOIN final48_restore.c48_items i ON i.order_id=o.order_id
WHERE o.status='paid';
SELECT stock FROM final48_restore.c48_products WHERE product_id=1;
SHOW CREATE TABLE final48_restore.c48_items;

Los recuentos esperados son 4, 4, 5 y 7, el importe pagado es 46000.00 y las existencias del producto 1 son 18. Compare los valores de las filas por sus claves, las restricciones y las consultas JOIN importantes, no solo un estado correcto o recuentos iguales. Verifique por separado el rechazo de referencias inexistentes, claves duplicadas y cambios no autorizados.

Guarde versiones, hash del archivo, consultas y resultados de validación y alcance de permisos. Cierre las sesiones de prácticas y revise propiedad y conservación antes de retirar cuentas o bases. La práctica no elimina automáticamente el origen, la copia ni la base de verificación.