Ghidul Definitiv pentru mysqldump: Backup, Restaurare și Automatizare a Bazei de Date MySQL

mysqldump este un utilitar de linie de comandă inclus cu MySQL și MariaDB care generează copii de rezervă logice prin serializarea obiectelor și datelor din baze de date ca o secvență de instrucțiuni SQL. Fișierul dump rezultat poate recrea o bază de date identică pe orice server compatibil, făcându-l instrumentul standard în industrie pentru copii de rezervă, migrări între servere, actualizări de versiuni și fluxuri de lucru pentru recuperare în caz de dezastru.

Spre deosebire de instrumentele de backup fizic precum Percona XtraBackup sau MySQL Enterprise Backup, mysqldump operează la nivelul SQL — citește date live prin protocolul MySQL și scrie SQL portabil, lizibil de oameni. Această portabilitate este cel mai mare avantaj al său și, la scară, principala sa limitare.

Ce face mysqldump de fapt în culise

Când invocați mysqldump, clientul se conectează la serverul MySQL, interoghează schema de informații și dicționarul de date și emite un flux de instrucțiuni `CREATE DATABASE`, `CREATE TABLE`, `INSERT` și DDL către ieșirea standard. Redirecționați acel flux către un fișier, un pipe sau un utilitar de compresie.

Pentru tabelele InnoDB cu `–single-transaction`, mysqldump deschide o tranzacție cu citire repetabilă înainte de a citi orice date. Aceasta vă oferă un instantaneu consistent la un moment dat fără a achiziționa blocări globale de citire — baza de date rămâne complet inscriptibilă în timpul dump-ului. Pentru tabelele MyISAM, nu există un astfel de mecanism; mysqldump revine la `FLUSH TABLES WITH READ LOCK`, care blochează temporar scrierile.

Înțelegerea acestei distincții este critică înainte de a alege mysqldump pentru sarcini de lucru în producție. Dacă schema dvs. combină tabele InnoDB și MyISAM, `–single-transaction` singur este insuficient — veți avea nevoie de `–lock-all-tables` sau de o fereastră de mentenanță.

Cerințe preliminare și privilegii necesare

Înainte de a rula orice comandă dump, verificați următoarele:

• MySQL sau MariaDB este instalat și accesibil (socket local sau TCP/IP).
• Utilizatorul de backup deține privilegiile minime necesare:
• `SELECT` pe toate tabelele țintă
• `LOCK TABLES` (necesar dacă nu se folosește exclusiv `–single-transaction` cu InnoDB)
• `SHOW VIEW` pentru a include vizualizări
• `TRIGGER` pentru a include triggere
• `PROCESS` când se folosește `–single-transaction` pe MySQL 8+
• `RELOAD` pentru `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` dacă aveți nevoie de coordonate din jurnalul binar pentru configurarea replicării

Creați un utilizator dedicat pentru backup în loc să rulați dump-uri ca root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Rularea mysqldump ca root cu o parolă inclusă în comanda shell expune credențialele în listele de procese și istoricul shell — un risc de securitate semnificativ pe orice sistem partajat sau multi-utilizator.

Sintaxa de bază

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Referință completă a opțiunilor

Opțiuni de conexiune

Opțiuni de domeniu

Opțiuni de consistență și blocare

Opțiuni de ieșire și conținut

mysqldump vs. metode alternative de backup

Alegerea strategiei corecte de backup depinde de dimensiunea bazei de date, cerințele RTO/RPO și infrastructură. Iată cum se compară mysqldump cu cele mai comune alternative:

Pentru baze de date sub 10–20 GB pe un mediu de VPS Hosting, mysqldump rămâne cea mai practică și portabilă soluție. Dincolo de acest prag, instrumentele de backup fizic oferă ferestre de backup și restaurare dramatic mai rapide.

Exemple practice de utilizare

Exemplul 1: Backup al unei singure baze de date

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

Substituția `$(date +%F)` adaugă automat data ISO (de ex., `2025-07-15`) la numele fișierului, prevenind suprascrierea.

Exemplul 2: Backup al mai multor baze de date specifice

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

Indicatorul `–databases` determină mysqldump să emită instrucțiuni `CREATE DATABASE` și `USE`, făcând dump-ul autonom pentru restaurare.

Exemplul 3: Backup al tuturor bazelor de date

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Includeți întotdeauna `–events`, `–routines` și `–triggers` în dump-urile complete ale serverului. Aceste obiecte sunt omise în tăcere fără indicatori expliciți.

Exemplul 4: Backup InnoDB consistent (sigur pentru producție)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` rotează jurnalul binar la începutul dump-ului. `–source-data=2` scrie numele curent al fișierului jurnal binar și poziția ca un comentariu SQL, permițând recuperarea la un moment dat prin reluarea binlog-urilor ulterioare de la acea poziție.

Exemplul 5: Backup comprimat cu gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

Pentru servere cu CPU limitat, înlocuiți cu `pigz` (gzip paralel) pentru a utiliza mai multe nuclee:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Exemplul 6: Backup doar al schemei (structură fără date)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Util pentru controlul versiunilor schemei în Git sau pentru implementarea într-un mediu de staging fără a copia datele de producție.

Exemplul 7: Backup doar al datelor (fără schemă)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Utilizați aceasta când schema țintă există deja și trebuie doar să populați sau să reîmprospătați datele.

Exemplul 8: Backup al unui singur tabel

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Exemplul 9: Exportul unui subset filtrat de rânduri

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

Opțiunea `–where` este subutilizată, dar extrem de puternică pentru exporturi parțiale, arhivarea datelor și depanarea unor seturi specifice de înregistrări.

Exemplul 10: Excluderea unor tabele specifice

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Excluderea tabelelor mari, efemere (cache-uri, stocuri de sesiuni, tabele de jurnale) poate reduce dimensiunea și durata dump-ului cu un ordin de mărime.

Exemplul 11: Includerea procedurilor stocate, funcțiilor și triggerelor

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Exemplul 12: Backup al unei baze de date la distanță

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

Când faceți backup unui server la distanță, traficul traversează rețeaua necriptat implicit. Adăugați indicatorii `–ssl-ca`, `–ssl-cert` și `–ssl-key` sau tunelizați prin SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Restaurarea unui backup mysqldump

Restaurarea unei singure baze de date

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Dacă baza de date țintă nu există încă, creați-o mai întâi:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Restaurarea tuturor bazelor de date dintr-un dump complet al serverului

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Deoarece `–all-databases` include instrucțiuni `CREATE DATABASE` și `USE`, nu este necesar niciun argument pentru baza de date țintă.

Restaurarea dintr-un backup comprimat

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Sau folosind substituția de proces:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Restaurarea unui singur tabel dintr-un dump complet al bazei de date

Acesta este un scenariu operațional frecvent pe care fișierul dump original îl face netrivial. Utilizați `sed` sau `grep` pentru a extrage secțiunea relevantă:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Alternativ, utilizați `mysql_extract_table.sh` sau importați într-o bază de date temporară și copiați tabelul:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Recuperare la un moment dat folosind jurnalele binare

Dacă dump-ul a fost realizat cu `–source-data=2` și jurnalizarea binară este activată, puteți recupera la orice moment după dump:

1. Identificați poziția jurnalului binar din comentariul antetului fișierului dump.
2. Restaurați dump-ul de bază.
3. Aplicați evenimentele ulterioare din jurnalul binar până la marca temporală dorită:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Automatizarea backup-urilor cu Cron

Job de backup zilnic de bază

Stocați credențialele în `~/.my.cnf` în loc să le includeți în comenzile cron:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Setați permisiuni stricte:

“`bash

chmod 600 ~/.my.cnf

“`

Apoi creați job-ul cron:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Script de backup pentru producție

Pentru Servere Dedicate care găzduiesc mai multe baze de date, un script mai robust gestionează jurnalizarea erorilor, verificările spațiului pe disc și descărcarea la distanță:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

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

“`

Întărirea securității pentru operațiunile mysqldump

Gestionarea credențialelor este aspectul cel mai frecvent neglijat al securității backup-urilor. Nu transmiteți niciodată `-pYourPassword` direct pe linia de comandă — este vizibil în ieșirea `ps aux` și în istoricul shell. Utilizați în schimb una dintre aceste abordări:

• `~/.my.cnf` cu `chmod 600` (per utilizator)
• `/etc/mysql/backup.cnf` cu `chmod 640`, deținut de root, lizibil de grupul de backup
• Variabila de mediu `MYSQL_PWD` (vizibilă în `/proc`, utilizați doar în containere izolate)
• MySQL Vault sau HashiCorp Vault pentru medii enterprise

Permisiunile fișierelor de backup trebuie să fie restrictive:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Criptare în repaus: Pentru date sensibile, criptați fișierele de backup înainte de a le stoca sau transfera:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Criptarea transportului: Când faceți dump de pe un server la distanță, utilizați întotdeauna SSL/TLS sau un tunel SSH. Pe un mediu VPS cu cPanel, interfața de backup a cPanel gestionează acest lucru automat, dar operațiunile manuale mysqldump necesită indicatori SSL expliciți.

Capcane frecvente și cum să le evitați

Nepotrivirile setului de caractere sunt cea mai frecventă cauză a restaurărilor corupte. Specificați întotdeauna setul de caractere explicit:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

Lipsa `–column-statistics=0` cauzează eșecuri când un client MySQL 8.0 face dump de pe un server MySQL 5.7 sau MariaDB. Clientul MySQL 8 încearcă să facă dump statisticilor de coloane pe care serverele mai vechi nu le suportă:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Uitarea `–routines`, `–triggers` și `–events` omite în tăcere obiecte critice ale bazei de date. Acești indicatori nu sunt activați implicit (cu excepția `–triggers`) și sunt frecvent uitați în dump-urile ad-hoc.

Dump-urile de tabele mari cauzând OOM: mysqldump bufferează implicit seturi întregi de rezultate în memorie. Pentru tabele foarte mari, adăugați `–quick` (activat implicit în majoritatea versiunilor, dar merită verificat) pentru a transmite rândurile unul câte unul în loc să le buffereze:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Restaurarea pe o versiune diferită de MySQL: Dump-urile din MySQL 8.0 pot conține sintaxă nesuportată în MySQL 5.7 (de ex., indecși funcționali, coloane invizibile). Testați întotdeauna restaurările într-un mediu cu versiune corespunzătoare înainte de a vă baza pe migrări între versiuni.

Deriva valorii auto-increment: Dacă restaurați un tabel într-o schemă existentă care are deja rânduri, instrucțiunile `INSERT` vor eșua din cauza conflictelor de cheie primară dacă nu includeți `–add-drop-table` sau nu trunchiați manual tabelul țintă mai întâi.

Utilizarea mysqldump pentru migrări de baze de date

mysqldump este abordarea standard pentru migrarea bazelor de date între servere — de exemplu, când mutați un site WordPress de la Găzduire Web Partajată pe un VPS, sau când replatformați pe un mediu VPS Control Panels cu mai multe resurse.

Fluxul de lucru recomandat pentru migrare:

1. Faceți dump bazei de date sursă cu opțiuni complete:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Transferați în siguranță folosind rsync prin SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Creați baza de date țintă cu setul de caractere corespunzător:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Restaurați și verificați:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Actualizați configurația aplicației pentru a indica noul host al bazei de date.

Pentru aplicațiile care se bazează și pe infrastructura de email, asigurați-vă că înregistrările DNS și configurațiile de Găzduire Email sunt actualizate în paralel cu migrarea bazei de date pentru a evita întreruperea serviciului.

Verificarea integrității backup-ului

Un backup care nu a fost niciodată testat nu este un backup — este o presupunere netestată. Implementați o rutină de verificare:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Rulați acest script de verificare săptămânal prin cron și alertați în caz de eșec.

Matrice de decizie: Când să utilizați mysqldump

Listă de verificare a punctelor tehnice cheie

• Utilizați întotdeauna `–single-transaction` pentru bazele de date exclusiv InnoDB pentru a evita blocările de scriere în timpul backup-ului.
• Includeți întotdeauna `–routines –triggers –events` în orice dump destinat ca backup complet.
• Stocați credențialele în `~/.my.cnf` sau `/etc/mysql/backup.cnf` cu `chmod 600/640` — niciodată direct în scripturi sau comenzi cron.
• Adăugați `–column-statistics=0` când utilizați un client MySQL 8.0 față de un server MySQL 5.7 sau MariaDB.
• Specificați întotdeauna `–default-character-set=utf8mb4` atât la dump cât și la restaurare pentru a preveni coruperea codificării caracterelor.
• Comprimați toate backup-urile cu gzip sau pigz; criptați dump-urile sensibile cu AES-256 înainte de transferul offsite.
• Includeți `–flush-logs –source-data=2` în dump-urile de producție pentru a permite recuperarea la un moment dat prin jurnale binare.
• Automatizați curățarea retenției cu `find … -mtime +N -delete` pentru a preveni epuizarea discului.
• Testați restaurările periodic — verificați numărul de rânduri și verificați spot integritatea datelor față de producție.
• Pentru scheme cu motoare mixte, utilizați `–lock-all-tables` în loc de `–single-transaction` pentru a garanta consistența.

Întrebări frecvente

mysqldump blochează tabelele în timpul backup-ului?

Cu `–single-transaction` pe o bază de date exclusiv InnoDB, nu se achiziționează blocări de tabel dincolo de o scurtă golire inițială. Tabelele MyISAM necesită întotdeauna o blocare de citire (`LOCK TABLES`) deoarece nu au suport pentru tranzacții. Schemele cu motoare mixte necesită `–lock-all-tables` pentru un instantaneu consistent, care blochează scrierile pe durata dump-ului.

Cum fac backup doar al schemei bazei de date fără date?

Utilizați indicatorul `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Acesta exportă toate instrucțiunile `CREATE TABLE`, `CREATE VIEW`, procedurile stocate și triggerele fără nicio instrucțiune `INSERT`.

De ce eșuează mysqldump cu erori de „column statistics”?

Aceasta apare când un client MySQL 8.0 se conectează la un server MySQL 5.7 sau MariaDB. Adăugați `–column-statistics=0` la comanda dvs. Alternativ, actualizați serverul la MySQL 8.0 sau utilizați un binar client care corespunde versiunii serverului.

Poate mysqldump efectua backup-uri incrementale?

Nu. mysqldump produce întotdeauna un dump logic complet al domeniului specificat. Capacitatea de backup incremental necesită arhivarea jurnalului binar (`mysqlbinlog`) combinată cu un mysqldump de bază realizat cu `–flush-logs –source-data=2`. Backup-urile fizice incrementale adevărate necesită Percona XtraBackup sau MySQL Enterprise Backup.

Care este cel mai sigur mod de a automatiza mysqldump fără a expune parolele?

Creați un utilizator MySQL dedicat pentru backup cu privilegiile minime necesare, stocați credențialele sale într-o secțiune `[mysqldump]` din `~/.my.cnf` sau un fișier de opțiuni separat cu `chmod 600`, și referențiați-l cu `–defaults-file=/path/to/backup.cnf`. Această abordare păstrează credențialele complet în afara listelor de procese, istoricului shell și definițiilor job-urilor cron.