Corso MariaDB / Eseguire il backup di un database InnoDB con mariadb-dump

mariadb-dump crea un backup logico contenente SQL per ricostruire definizioni e righe. Scegliere opzioni di snapshot coerente e un percorso esplicito, controllare gli errori e verificare il ripristino in un database separato: la sola esistenza del file non dimostra che il backup sia utilizzabile.

Definire oggetti del backup e obiettivi di recupero

L'esercizio salva quattro tabelle InnoDB nel database separato backup46_lab. Non è un backup completo di tutti gli account, delle configurazioni o dei file esterni di un server. Perdita di dati e tempo di recupero accettabili influenzano frequenza, posizione delle copie e necessità di log aggiuntivi.

Area Ambito dell'esercizio Ulteriori controlli
Dati Quattro tabelle InnoDB Altri motori, volume e DDL concorrenti
Oggetti Tabelle di base e chiavi Viste, definenti, routine, trigger ed eventi
Account e impostazioni Fuori da questo dump del database Procedure di recupero distinte
Verifica Ripristino in un altro database vuoto Righe, vincoli, totali del dominio e accessi

Dal database sorgente al file SQL, poi ripristino in un altro database vuoto e confronto di struttura, righe, vincoli e importi

Un backup logico registra istruzioni di ricostruzione, anziché limitarsi a copiare file di dati del server. Il riferimento di mariadb-dump spiega ambito e opzioni. Conservare la fonte finché il ripristino non sia stato verificato.

Creare un database sorgente autonomo

Come amministratore del server di esercitazione controllare SELECT VERSION(); e SHOW DATABASES per accertare che backup46_lab non sia usato. Se esiste, fermarsi e scegliere un altro nome aggiornando tutti i riferimenti. Non sostituire né eliminare dati esistenti per eseguire l'esempio. Lo schema completo seguente rende autonoma la prova.

CREATE DATABASE backup46_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE backup46_lab;
CREATE TABLE c46_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 c46_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 c46_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_c46_order_customer FOREIGN KEY (customer_id)
  REFERENCES c46_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c46_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_c46_item_order FOREIGN KEY (order_id)
  REFERENCES c46_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c46_item_product FOREIGN KEY (product_id)
  REFERENCES c46_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c46_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c46_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c46_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 c46_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);

Il codice conserva nomi e città coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Mantenere i testi UTF-8 e confrontare gli ID.

I privilegi già presenti su shopcourse non danno automaticamente accesso al nuovo database. L'amministratore deve concedere all'account di studio l'ambito necessario. ALL qui è limitato al database di prova eliminabile; un account di backup in produzione richiede privilegi adeguati agli oggetti e alle opzioni effettivamente usati.

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

Controllare versione del client, nuovo file e codice di uscita

Usare mariadb-dump dalla directory bin installata o dal PATH. Eseguire i comandi seguenti nel terminale del sistema operativo, non nel prompt SQL. Il blocco PowerShell crea la directory di lavoro se necessario e usa --result-file per evitare differenze di codifica nella redirezione di PowerShell.

mariadb-dump --version
mariadb --version
New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$backup46Path='C:/mariadb-lab/backup46-first.sql'
if (Test-Path -LiteralPath $backup46Path) { 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=C:/mariadb-lab/backup46-first.sql backup46_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed: inspect the error and verify the file.' }
Get-Item -LiteralPath $backup46Path | Select-Object Name,Length
Get-FileHash -LiteralPath $backup46Path -Algorithm SHA256

Inserire la password al prompt. Se il programma termina con errore, il file può essere vuoto o contenere soltanto parte del backup. Una dimensione positiva o un hash SHA-256 calcolato correttamente non dimostrano completezza o ripristinabilità. L'hash serve a confrontare l'identità dei byte dopo una copia o un controllo successivo.

Alternativa per la shell Ubuntu: scegliere questa versione oppure PowerShell secondo il computer client. La subshell limita umask restrittiva e divieto di sovrascrittura alla sola operazione di backup.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/backup46-first.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 backup46_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
)

Una destinazione esistente causa un errore anziché una sovrascrittura. Non ripristinare un file parziale dopo un comando fallito: esaminare la diagnosi e riprovare con un nome nuovo. Verificare che la versione del client di ripristino possa leggere l'output del mariadb-dump usato.

In caso di errore TLS controllare versioni di server e client, nome del certificato, autorità di certificazione attendibile e certificati client eventualmente richiesti secondo i requisiti TLS ufficiali di MariaDB. Applicare file di opzioni o impostazioni di certificato adeguati ai client di backup e ripristino; non disattivare abitualmente la verifica per superare l'errore.

Comprendere le condizioni dello snapshot coerente

--single-transaction supporta una lettura coerente delle tabelle transazionali come InnoDB. Coordinare ALTER, CREATE, DROP, RENAME e TRUNCATE concorrenti, perché le modifiche strutturali possono compromettere le ipotesi dello snapshot. I motori non transazionali non ottengono automaticamente la stessa coerenza.

--quick recupera le righe progressivamente. --skip-lock-tables esplicita la scelta di non richiedere i blocchi di tabella previsti da quella modalità: non significa backup senza carico, senza alcun blocco o senza impatto sulla rete. Il comando omette intenzionalmente --databases per consentire la prova di ripristino con un nome di database diverso.

Mantenere stabili le definizioni sorgenti durante il processo e registrare inizio e fine del backup. La scelta dello snapshot riguarda la coerenza dei dati nel suo ambito; capacità di ripristinare oggetti e applicazioni vanno comunque verificate separatamente.

Inventariare oggetti aggiuntivi e proteggere il backup

I dati di prova non contengono routine, eventi, trigger o viste. In un database reale inventariarli e controllare opzioni della versione usata, privilegi, attributi DEFINER e contesto di esecuzione dopo il ripristino. Valutare --routines e --events quando servono e controllare l'output effettivo dei trigger, senza dedurre la presenza di tutte le dipendenze dal solo nome di un'opzione.

Il dump contiene definizioni e dati. Applicare accessi, cifratura, conservazione e copie in posizioni con rischi di guasto separati secondo le proprie regole. Un file soltanto sullo stesso disco non protegge dal guasto di quel disco. Backup contenenti dati non vanno collocati in repository pubblici o directory web.

Conservare la fonte fino alla verifica del ripristino

Conservare database sorgente e backup finché un ripristino in un altro database vuoto non dimostri corrispondenza di struttura, tutti i campi, chiavi e totali del dominio. I dati iniziali sono quattro clienti, quattro prodotti, quattro ordini, sei dettagli e merci pagate per 39000. Registrare l'importo con la query seguente.

SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM backup46_lab.c46_orders o
JOIN backup46_lab.c46_items i ON i.order_id=o.order_id
WHERE o.status='paid';

Non eliminare automaticamente la fonte dopo aver creato il dump. Confrontare non soltanto conteggi, ma anche testi UTF-8, decimali, relazioni di chiave esterna e rifiuto degli input che violano i vincoli nel database ripristinato. Distinguere la verifica di questo piccolo database da un piano di recupero dell'intero servizio.