Situazioni impreviste come crash del sistema operativo, interruzioni di corrente, guasti del file system e malfunzionamenti hardware possono tutte causare un malfunzionamento del database in uso, provocando incoerenze nei dati. Per garantire la sicurezza del database, è necessario eseguirne regolarmente il backup. I backup del database possono essere classificati in backup completi, backup del log, backup incrementali e backup dei file. Per un database di grandi dimensioni, eseguire frequentemente backup completi potrebbe richiedere troppo tempo e spesso si effettua il backup ripetuto di dati invariati, causando spreco di risorse. La strategia di backup del database più comunemente utilizzata oggi consiste nell'eseguire backup incrementali più frequenti sulla base di un backup completo. Ad esempio, possiamo eseguire un backup completo una volta alla settimana durante i periodi di basso utilizzo e poi eseguire un backup incrementale ogni giorno, acquisendo le operazioni che potrebbero aver modificato il database durante quel periodo.
Introduzione al backup incrementale
Il backup incrementale consiste nel salvare solo i file aggiunti o modificati dall'ultimo backup completo o dall'ultimo backup incrementale precedente. Ciò significa che il primo backup incrementale comprende i file aggiunti o modificati dopo il backup completo; il secondo backup incrementale comprende i file aggiunti o modificati dopo il primo backup incrementale e così via. Il vantaggio più significativo di questo metodo di backup è l'assenza di dati di backup duplicati, con conseguente dimensione del backup ridotta e tempi di backup più brevi. Tuttavia, il ripristino dei dati dai backup incrementali è più complesso. È necessario disporre dell'ultimo backup completo e di tutti i backup incrementali (la perdita o il danneggiamento di uno qualsiasi di essi può causare il fallimento del ripristino), e questi devono essere ripristinati in ordine cronologico, partendo dal backup completo fino a ciascun backup incrementale, il che aumenta notevolmente il tempo di ripristino.
Ad esempio, se abbiamo un database con 20 GB di dati e ogni giorno vengono aggiunti altri 10 MB di dati, eseguire un backup completo ogni giorno comporterebbe un carico elevato per il server. Invece, possiamo fare il backup solo dei nuovi dati aggiunti per ridurre il carico sul server.
Introduzione al Binlog
MySQL non fornisce un metodo diretto per il backup incrementale, ma può realizzare backup incrementali indirettamente attraverso i log binari di MySQL.
Il binlog è abilitato dall'opzione log-bin nel file di configurazione. Il server MySQL creerà due nuovi file, \"XXX-bin.001\" e \"xxx-bin.index\", nella directory principale dei dati. Se nessun nome di file viene specificato nell'opzione di configurazione, MySQL utilizzerà il nome host per denominare questi due file. Il file \".index\" contiene un elenco di tutti i file di log. MySQL registra tutte le modifiche al contenuto e alla struttura del database nei file \"XXX-bin.n\", ma non registra le istruzioni \"SELECT\" e \"UPDATE\" che non aggiornano effettivamente i dati.
Quando il database MySQL si arresta o viene riavviato, il server passa al file di log successivo. MySQL genera un nuovo file binlog al riavvio, con un numero di file crescente. Inoltre, se un file di log supera il limite impostato dalla variabile di sistema "max_binlog_size", viene creato anche un nuovo file di log.
Il backup incrementale si basa sul meccanismo binlog di MySQL. Ogni operazione di inserimento, aggiornamento, eliminazione e query nel database viene registrata nel binlog. Eseguendo prima un backup completo e svuotando contemporaneamente il binlog, tutte le operazioni successive verranno registrate in nuovi file binlog. Per il backup incrementale, è sufficiente eseguire il backup dei file binlog appena generati, consentendo un backup perfetto del database in continua crescita.
L'importanza dei log binari per i backup è la seguente:
I log binari memorizzano tutte le operazioni che aggiornano o potrebbero aggiornare i dati.
I log binari iniziano a registrare dopo l'avvio del server MySQL e creano un nuovo file di log quando viene raggiunta la dimensione massima impostata o quando viene eseguito il comando "flush logs".
Eseguendo periodicamente il comando "flush logs", creando una nuova sequenza di file binari e archiviando questi file in modo sicuro, si completa un backup incrementale nel tempo.
Come eseguire un backup incrementale di MySQL?
Il backup incrementale di MySQL può essere realizzato utilizzando i log di MySQL. I log di MySQL includono tre tipi: log binari, log delle transazioni e log degli errori. Tra questi, i log binari sono i log principali di MySQL, che contengono tutte le operazioni di modifica dei dati in formato binario memorizzato su disco. Eseguendo il backup dei log binari, è possibile implementare il backup incrementale di MySQL.
Passaggi per il backup incrementale di MySQL:
1. Abilita il log binario di MySQL:
Impostare i seguenti parametri nel file di configurazione di MySQL ("my.cnf"):
[mysql]
log-bin=mysql-bin
server-id=1
Il parametro "log-bin" specifica il prefisso del file di log binario, mentre il parametro "server-id" identifica in modo univoco l'istanza di MySQL.
2. Crea un utente di backup:
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your_password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE, PROCESS, SUPER, RELOAD ON *.* TO 'backup'@'localhost';
Qui, "backup" è il nome utente. L'utente deve disporre delle seguenti autorizzazioni: "REPLICATION CLIENT", "REPLICATION SLAVE", "PROCESS", "SUPER" e "RELOAD".
3. Esegui un backup completo:
Utilizzare il comando "mysqldump" per eseguire il backup dell'intero database MySQL in un file:
mysqldump -u root -P 3306 -h 127.0.0.1 -p123456 ks_flask > ks_flask.sql
Qui, "localhost" è l'host di MySQL, "root" è il nome utente e "ks_flask" è il nome del database.
4. Registra la posizione corrente del log binario:
Dopo aver eseguito il primo backup completo, registra il nome del file di log binario corrente e la posizione per i futuri backup incrementali.
SHOW MASTER STATUS;
Questo comando restituisce il file del log binario corrente e la posizione.
5. Eseguire il backup incrementale:
Utilizza il comando "mysqlbinlog" con i parametri "--start-datetime", "--stop-datetime" e "--start-position" per specificare l'intervallo di tempo e la posizione del backup precedente.
mysqlbinlog --start-datetime='2023-10-23 00:00:00'
--stop-datetime='2023-10-23 23:59:59'
--start-position=[previous position]
mysql-bin.000001 > incr_backup_2023-10-23.sql
Qui, “[previous position]” è la posizione del log binario registrata dall'ultimo backup.
6. Registrare la nuova posizione del log binario:
Dopo aver eseguito il backup incrementale, utilizzare "SHOW MASTER STATUS" per registrare il nuovo file del log binario e la posizione per il prossimo backup incrementale.
SHOW MASTER STATUS;
7. Esegui regolarmente il backup incrementale:
Eseguire backup incrementali giornalieri in base alle esigenze, assicurando l'inclusione delle modifiche del giorno precedente. Possono essere utilizzati script di automazione per registrare e aggiornare periodicamente le posizioni dei log binari.
8. Ripristina dati di backup:
Unisci e ripristina backup completi e incrementali in MySQL:
mysql -hlocalhost -uroot -p your_database < ks_flask.sql
mysql -hlocalhost -uroot -p your_database < incr_backup_2023-10-23.sql
Se ci sono diversi backup incrementali, ripristinarli in ordine cronologico.
Come recuperare da un backup incrementale di MySQL?
Esistono tre metodi comuni per il recupero incrementale: recupero generale, recupero basato sulla posizione e recupero punto-tempo.
1. Ripristino generale
Ripristina tutti i contenuti del log binario di backup.
mysqlbinlog [--no-defaults] incremental backup file | mysql -u username -p password
2. Recupero basato sulla posizione
Quando un amministratore di database esegue operazioni, possono verificarsi contemporaneamente operazioni errate e corrette. Il ripristino basato sulla posizione consente un recupero più preciso.
Ripristino a una posizione specifica
mysqlbinlog --stop-position='operation ID' binary log | mysql -u username -p password
Ripristino che inizia da una posizione specifica
mysqlbinlog --start-position='operation ID' binary log | mysql -u username -p password
3. Ripristino a un determinato punto nel tempo
Salta un determinato punto temporale errato per recuperare i dati. Esistono tre metodi:
1) Ripristino dall'inizio del registro fino a un punto temporale specifico
mysqlbinlog [--no-defaults] --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
2) Ripristino da un punto temporale specifico alla fine del log
mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
3) Recupero da un punto temporale a un altro punto temporale
mysqlbinlog [--no-defaults] --start-datetime='YYYY-MM-DD HH:MM:SS' --stop-datetime='YYYY-MM-DD HH:MM:SS' binary log | mysql -u username -p password
Un modo più semplice per creare backup incrementali di MySQL
Come puoi vedere, l'intero processo di esecuzione del backup incrementale e del ripristino di MySQL utilizzando i log binari è complesso e richiede molto tempo. Poiché si tratta di operazioni da riga di comando, non avrai alcuna possibilità di tornare indietro se commetti un errore durante il processo di backup e ripristino.
Fortunatamente, il potente software di backup MySQL Vinchin Backup & Recovery offre un'interfaccia utente intuitiva che semplifica le operazioni.
Vinchin Backup & Recovery offre una soluzione più automatizzata, efficiente e sicura, semplificando i processi di backup di MySQL con gestione centralizzata, ripristino rapido, backup incrementali e strategie di archiviazione flessibili per garantire la disponibilità a lungo termine dei dati. Fornisce inoltre strategie di backup flessibili per Oracle DB, SQL Server, SQL Server, PostgreSQL, Postgres Pro e MariaDB, consentendo backup automatici, backup differenziali e backup del registro delle transazioni.
Per creare attività di backup incrementale del database MySQL, vai alla pagina Physical Backup > Database Backup > Backup (si noti che il primo backup deve essere un backup completo):
1. Seleziona i database che devono essere sottoposti a backup.
2. Seleziona un nodo di backup su cui desideri che i dati di backup vengano elaborati e archiviati.
3. Configura le strategie di backup in base alle tue esigenze.
Qui puoi scegliere il backup incrementale come pianificato.
4. Verifica e conferma le impostazioni.
Fai clic sul pulsante qui sotto per provare la versione di prova gratuita di 60 giorni di Vinchin e scoprire una soluzione efficiente e affidabile per il backup e il ripristino dei dati!
Domande frequenti sul backup incrementale di MySQL
1. Cosa accade se un file di log binario viene eliminato prima del ripristino di un backup incrementale?
Se i file di log binari richiesti sono mancanti, il ripristino potrebbe essere incompleto, causando la perdita di dati. Assicurarsi sempre che i log binari vengano conservati finché non saranno più necessari.
2. Per quanto tempo devo conservare i log binari di MySQL per i backup incrementali?
Dipende dalla tua politica di backup. In genere, i log devono essere conservati fino al completamento e alla verifica di un nuovo backup completo.
Conclusione
Si raccomanda di aggiornare giornalmente i log binari dei backup incrementali. Questo garantisce che la frequenza del backup del database sia sufficientemente elevata da ridurre al minimo il rischio di perdita di dati. Aggiornando giornalmente i backup incrementali, è possibile gestire in modo più efficace le modifiche al database, consentendo il ripristino allo stato più recente quando necessario.
