Per raggiungere una gestione efficace del database MySQL, sono stati sviluppati diversi strumenti e tecniche di backup, ciascuno con punti di forza e casi d'uso specifici. Questo articolo esplora le caratteristiche principali, i meccanismi e le considerazioni relative a questi strumenti popolari per il backup e la gestione di MySQL, aiutandoti a prendere decisioni informate per le operazioni sul tuo database.
1. Backup logico: mysqldump
In MySQL, mysqldump è uno degli strumenti più comunemente utilizzati per esportare database sotto forma di file SQL portabili. Supporta il backup di singole tabelle, singoli database o più database e offre un'ampia gamma di opzioni per soddisfare diverse esigenze di backup. Il suo meccanismo di backup si connette al database MySQL tramite un protocollo, esegue query sui dati da salvare e converte i dati recuperati in corrispondenti istruzioni “INSERT”. Quando dobbiamo ripristinare i dati, l'esecuzione di queste istruzioni “INSERT” ripristinerà i dati corrispondenti.
Il processo di backup di mysqldump è approssimativamente il seguente:
Applica blocchi di lettura a tutte le tabelle di un database specifico;
Scorrere e creare una copia di sicurezza dei dati delle tabelle;
Rilascia il blocco di lettura;
Ripetere i tre passaggi sopra indicati;
Il backup è completo.
Dal processo sopra descritto, è chiaro che durante il backup con mysqldump, quando si esegue il backup di un database specifico, tutte le tabelle di quel database saranno in modalità sola lettura, impedendo qualsiasi modifica fino al completamento del backup. Questo è generalmente inaccettabile per ambienti online. Se viene specificata l'opzione “--master-data” o “--dump-slave”, un blocco globale in lettura (“FLUSH TABLES WITH READ LOCK”) verrà applicato all'inizio del backup e rimarrà attivo fino al completamento dello stesso. Un'alternativa consiste nell'eseguire il backup su un database replica per evitare di influenzare le operazioni online.
Un altro grande vantaggio dell'utilizzo di mysqldump è che, essendo in grado di generare istruzioni SQL, supporta la migrazione o il ripristino dei dati tra diverse piattaforme e versioni, cosa invece impossibile con i backup fisici. Tuttavia, dato che mysqldump produce istruzioni SQL, richiede particolare attenzione durante l'utilizzo per evitare problemi gravi. I problemi comuni nell'uso di mysqldump includono:
Trasferire solo una parte dei dati a una nuova istanza ma eliminando accidentalmente i dati originali;
A causa di problemi di fuso orario, le tabelle ripristinate e i dati temporali potrebbero essere errati;
Dopo aver importato i dati di backup nel database principale, il database secondario non si sincronizza;
A causa di problemi di set di caratteri, i dati ripristinati appaiono corrotti.
Pertanto, quando si utilizza mysqldump, è fondamentale comprendere la funzione di ogni opzione, verificare quali operazioni sono incluse nel file SQL esportato e valutare l'impatto sui dati esistenti.
2. Mydumper
Il principio di Mydumper è simile a quello di mysqldump, ma la sua principale differenza è l'introduzione del backup multi-thread, dove ciascun thread di backup elabora una parte delle tabelle. La granularità della concorrenza può arrivare persino al livello delle righe, permettendo backup multi-thread.
Caratteristiche principali di Mydumper:
Leggero, scritto in C
La velocità di esecuzione è 10 volte più veloce rispetto a mysqldump
Snapshot coerenti di tabelle transazionali e non transazionali (per versioni 0.2.2 e successive)
Compressione rapida dei file
Supporta l'esportazione dei binlogs
Ripristino multi-thread (per versioni 0.2.1 e successive)
Funziona come demone per snapshot pianificati e log binari continui (per versioni 0.5.0 e successive)
Open-source (GNU GPLv3)
Mydumper utilizza esportazioni multi-threaded, quindi non può garantire lo stesso ordine di esportazione di mysqldump, il che potrebbe causare inconsistenze nei dati per funzionalità dipendenti dal tempo come routine ed eventi. Si consiglia di esportare e importare il database di sistema MySQL separatamente dagli altri database.
Le funzioni di estrazione e unione del database di Mydumper dipendono dai delimitatori, quindi i nomi dei database non dovrebbero contenere punti (".") e i nomi delle tabelle non dovrebbero contenere trattini ("-").
3. mysqlbackup
mysqlbackup, abbreviato in MEB, è uno strumento di backup sviluppato da Oracle per la versione aziendale di MySQL. Questo strumento offre potenti funzionalità di backup per MySQL, incluse copie di backup a caldo, backup incrementali, backup selettivi dei dati e ripristino dei dati.
Utilizzando l'elaborazione parallela, mysqlbackup esegue backup a livello di blocco in parallelo dei dati del database, migliorando l'efficienza e la velocità del backup.
MEB supporta tre tipi di backup del database MySQL: Completo, Differenziale e Incrementale. Supporta inoltre l'archiviazione compressa di questi backup. Dopo aver effettuato un backup, vengono generati tre tipi di file: ibdata, ibd e ibz. Tra questi:
“ibdata” contiene i dati dello spazio di tabella system InnoDB;
“Ibd” contiene i file di dati reali;
“Ibz” contiene file di dati compressi.
Inoltre, MEB genera file come "backup-mysql.cnf" (una copia di backup della configurazione MySQL al momento del backup) e file di backup incrementale come "ibbackup_ibd_files" e "ibbackup_logfile", che memorizzano dati e log incrementali. MySQL ha inoltre introdotto un meccanismo separato per il backup dei log redo per migliorare l'integrità dei dati.
4. Backup fisico: Percona XtraBackup
Percona XtraBackup è uno strumento di backup caldo per MySQL sviluppato da Percona, progettato per backup fisici basati sul meccanismo di recupero da crash di InnoDB. Il suo principio di funzionamento di base è il seguente:
1. All'avvio, viene creato un processo di copia del log di redo per acquisire e registrare il numero di sequenza del log corrente (LSN) e copiare continuamente i log di redo modificati da quel punto in avanti.
2. Viene avviato un thread per copiare il file "ibdata1", gli spazi tablespace di undo e tutti i file "ibd".
3. Una volta completata la copia del file "ibd", viene attivato un comando "FLUSH TABLES WITH READ LOCK" (oppure applicato un backup lock).
4. Successivamente esegue il backup dei file di dati non InnoDB (.frm, .MRG, .MYD, .MYI, ecc.).
5. Esegue inoltre il backup delle informazioni relative al database replica e ai log binari.
6. Infine, svuota i log, copia gli ultimi redo log, esce dal thread di copia dei log e rilascia i blocchi globali mentre registra i metadati del backup per completare il processo.
Durante il ripristino, Percona XtraBackup applica i log redo copiati, conferma le transazioni completate e annulla le transazioni non confermate per riportare il database a uno stato consistente.
Dato che Percona XtraBackup produce file fisici, evita molte problematiche associate ai backup logici come mysqldump durante il ripristino o la migrazione dei database. Tuttavia, diversi parametri di backup possono influenzare il database in misura variabile.
XtraBackup presenta il vantaggio di un impatto minimo sul database e un recupero rapido, rendendolo la scelta preferita per i backup quotidiani. Al contrario, mysqldump è più flessibile ma richiede attenzione per evitare di compromettere i dati esistenti.
5. phpMyAdmin
phpMyAdmin è uno strumento di gestione dei database MySQL basato su PHP, strutturato come applicazione web che funziona su un host web. Consente agli amministratori di gestire i database MySQL attraverso un'interfaccia web, fornendo un modo pratico per immettere sintassi SQL complesse, particolarmente utile per importare ed esportare grandi quantità di dati.
phpMyAdmin offre un'interfaccia grafica per il database simile a Microsoft Access e SQL Server, permettendo agli utenti di eseguire varie operazioni MySQL come la creazione di database, tabelle e la generazione di file script per il database MySQL.
Un importante vantaggio di phpMyAdmin è che, come altre applicazioni PHP, funziona su un server web. Tuttavia, gli utenti possono accedere alle pagine HTML generate da qualsiasi luogo, permettendo la gestione remota del database MySQL. Questo semplifica la creazione, la modifica e l'eliminazione di database e tabelle. Inoltre, phpMyAdmin può generare la sintassi PHP più comune, garantendo la correttezza delle istruzioni SQL durante lo sviluppo di applicazioni web.
6. MySQL Workbench
MySQL Workbench è uno strumento grafico ufficiale e gratuito per la gestione dei database MySQL, che offre un'interfaccia utente intuitiva per progettazione, sviluppo e amministrazione dei database. Consente agli utenti di creare e modificare strutture di database in modo visivo tramite diagrammi ER, generare script SQL e sincronizzare le modifiche con il database. L'editor SQL integrato supporta l'evidenziazione della sintassi, il completamento automatico e l'esecuzione delle query con risultati visualizzati. Inoltre, include strumenti per l'importazione/esportazione dei dati, la gestione dei permessi degli utenti, il monitoraggio delle prestazioni e la migrazione del database. Compatibile con Windows, macOS e Linux, si rivolge sia agli utenti principianti che agli sviluppatori esperti, semplificando le operazioni sui database senza richiedere una conoscenza approfondita della riga di comando.
7. Vinchin Backup & Recovery
Vinchin Backup & Recovery offre una soluzione più automatizzata, efficiente e sicura, semplificando i processi di backup MySQL grazie alla gestione centralizzata, al rapido ripristino, ai backup incrementali e alle strategie di archiviazione flessibili per garantire la disponibilità a lungo termine dei dati. Inoltre, fornisce strategie di backup flessibili per Oracle DB, SQL Server, SQL Server, PostgreSQL, Postgres Pro e MariaDB.
Le sue funzioni di archiviazione ottimizzate, come la deduplica e la compressione dei dati, riducono significativamente i requisiti di archiviazione dei file di backup e abbreviano il tempo necessario per il backup. Allo stesso tempo, le sue funzioni di crittografia e sicurezza durante la trasmissione garantiscono la sicurezza dei dati di backup, rendendolo adatto alle aziende che necessitano di proteggere rigorosamente i dati.
Per creare attività di backup del database MySQL, accedere alla pagina Physical Backup > Database Backup > Backu:
1. Selezionare i database da cui effettuare il backup.
2. Selezionare un nodo di backup su cui si desidera che i dati di backup vengano elaborati e archiviati.
3. Configura le strategie di backup in base alle tue esigenze.
4. Rivedi e conferma le impostazioni.
Fai clic sul pulsante qui sotto per provare la prova gratuita di 60 giorni di Vinchin e scoprire una soluzione efficiente e affidabile per il backup e il ripristino dei dati!
Domande frequenti sugli strumenti di backup MySQL
1. Qual è la differenza tra backup logici e fisici?
I backup logici memorizzano i dati come istruzioni SQL, mentre i backup fisici copiano i file grezzi del database.
2. Cos'è il recupero point-in-time (PITR) in MySQL?
Consente di ripristinare un database a un momento specifico utilizzando i log binari (binlog).
Conclusione
La scelta dello strumento di backup appropriato per il proprio database MySQL è fondamentale per garantire la sicurezza dei dati e l'efficienza operativa. Comprendere i punti di forza e le limitazioni di ogni strumento permette di personalizzare la strategia di backup per soddisfare esigenze specifiche, assicurando una protezione solida dei dati e un recupero senza problemi in qualsiasi situazione.
