Backup MySQL · 9 min read · Dec 10, 2025

Backup Incrementale MySQL - Ripristino e Backup Point In Time di Database InnoDB e MyIsam

Eseguire backup incrementali è un requisito importante per grandi database di produzione. Senza un backup incrementale sicuro, non puoi dirti di avere un database di produzione affidabile. Perché devi avere abbastanza dati per recuperare il tuo database in casi di emergenza. Dopo alcune ricerche su Internet, non sono riuscito a trovare alcun strumento che possa eseguire un backup incrementale completo per MyISAM e InnodB in un ambiente misto in cui le applicazioni utilizzano entrambi i motori di database simultaneamente (forse non sono un esperto cercatore su Google e Internet). Così ho deciso di scrivere questo, ma per evitare di perdere tempo e beneficiare di altre soluzioni open-source, ho preferito aggiungere questa funzionalità allo script -automysqlbackup- che è il miglior script per backup completo in semplicità e uso diffuso.

Meccanismo

Utilizziamo la funzionalità Post- e Pre di automysqlbackup per eseguire un backup incrementale. Prima di avviare un backup completo, mysql-backup-pre esegue una query per bloccare l’intero database durante il processo di backup perché dobbiamo congelare il binlog per evitare qualsiasi modifica mentre il backup è in esecuzione. Il nome e la posizione del binlog non possono cambiare durante il backup. La posizione del log binario è molto cruciale nel successivo processo di backup incrementale e sarà utilizzata come punto di partenza per iniziare il prossimo backup incrementale. Dopo aver terminato il backup completo, mysql-backup-post rimuove il blocco del database.

Query di Blocco: FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)

Trova Query di Blocco: mysql -u[username] -p[pass] -e “show processlist” | grep “SELECT SLEEP(86400)” | awk ‘{print $1}’

Requisiti

  • privilegi di root per installare il pacchetto e aggiornare mysql.conf
  • pacchetto mysql-community-client
  • installazione automysqlbackup e mysql-incremental

Installazione

Installa il pacchetto mysql-community-client per la tua distribuzione.

Nota: dopo l’installazione di MySQL devi avere il comando ‘mysqlshow’.

Installa automysqlbackup:

download the package from https://sourceforge.net/projects/automysqlbackup/
tar -xzf [PathYouSavedTarFile] -C /tmp/
cd /tmp/
./install.sh

Durante l’installazione di automysqlbackup, ti verrà chiesto il percorso di automysqlbackup.conf e il suo binario, puoi lasciare i valori predefiniti senza alcuna modifica.

rm /etc/automysqlbackup/myserver.conf

Installa il mysql-incremental: Scarica il pacchetto da https://sourceforge.net/projects/mysqlincrementalbackup/

cd /tmp  
wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz  
tar xfz mysql-incremental.tar.gz
cp mysql-incremental /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-incremental
cp mysql-backup-post /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-backup-post
cp mysql-backup-pre /etc/automysqlbackup/
chmod 755 /etc/automysqlbackup/mysql-backup-pre

Aggiorna il automysqlbackup.conf:

Trova i seguenti parametri, decommentali e cambiali:

        CONFIG_mysql_dump_username='Nome utente Mysql. Deve avere privilegi per ottenere Lock'
    CONFIG_mysql_dump_password='Password'
    CONFIG_backup_dir='La directory di backup in cui desideri memorizzare il backup completo e incrementale'
    CONFIG_db_names=('databaseName1' 'databaseName2' )
    CONFIG_db_month_names=('databaseName1' 'databaseName2' )
    CONFIG_mysql_dump_master_data=2
    CONFIG_prebackup="/etc/automysqlbackup/mysql-backup-pre"
    CONFIG_postbackup="/etc/automysqlbackup/mysql-backup-post"

Aggiorna my.cnf:

Modifica il file di configurazione di MySQL:

nano /etc/mysql/my.cnf

1- Formato BinLog

A causa di alcune limitazioni sul formato STATEMENT, la mia raccomandazione è di impostare il formato basato su ROW. Per ulteriori informazioni, consulta la sezione ‘risoluzione dei problemi’ in questo howto. Puoi controllare il tipo di formato del log binario eseguendo la query “ select @@binlog_format; “. Per modificare il formato logbin, devi aggiungere binlog_format = ROW a mysql.conf o my.cnf.

2- binlog_do_db

Devi specificare i database di cui intendi avere le modifiche correlate nel log binario. Si prega di notare che se non specifichi alcun database, qualsiasi modifica su qualsiasi database sarà registrata nel log binario. In questo caso, se scegli il formato STATEMENT, potresti avere alcuni problemi durante il ripristino dal backup incrementale e dai file binlog. Puoi aggiungere database a questa opzione:

binlog_do_db = DATABASENAME1
binlog_do_db = DATABASENAME2

3- expire_logs_days

Per avere file di log binari per un periodo di tempo più lungo, puoi aumentare questo parametro a un valore più alto. La mia raccomandazione è 60 giorni. Quindi devi aggiungere o cambiarlo in “ expire_logs_days = 60 “.

4- log-bin

La directory in cui saranno memorizzati i log binari. Nelle vecchie versioni di MySQL, mysql-incremental potrebbe non essere in grado di trovare il percorso corretto. Quindi, se ricevi un errore a riguardo dopo aver eseguito mysql-incremental, devi aggiornare lo script mysql-incremental e impostare il percorso del log binario.

5- log_slave_updates

Se stai impostando il backup mysql-incrementale su un server secondario, devi abilitare questa opzione. Normalmente, un secondario non registra aggiornamenti nel proprio log binario poiché sono stati ricevuti da un server principale. Questa opzione dice al secondario di registrare gli aggiornamenti eseguiti dai suoi thread SQL nel proprio log binario. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

Esegui automysqlbackup

Esegui automysqlbackup manualmente per avere almeno un backup completo dai tuoi database specificati.

automysqlbackup

Dopo aver eseguito il comando con successo, controlla il file /[BackupDirInAutomysqlbackup]/status/backup_info per le nuove informazioni aggiunte sul backup giornaliero. Per dettagli sugli errori, controlla /var/log/Backup_Post_Pre_log. Il file di backup sarà memorizzato nella directory /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/.

Esegui mysql-incremental

Esegui mysql-incremental manualmente ora per avere almeno un backup orario.

mysql-incremental

In caso di errore, i dettagli sono registrati nel file “ /var/log/Backup_Incremental_Log “. I file di backup incrementale saranno memorizzati nella directory /[BackupDirInAutomysqlbackup]/IncrementalBackup/.

Modifica il crontab di root

Puoi pianificare mysql-incremental per più di un’ora. Puoi trovare il tempo totale del backup completo da backup_status e quindi, in base a quel valore, impostare un orario di pianificazione accurato. Naturalmente, il backup mysql-incremental ha un meccanismo per trovare eventuali backup completi in esecuzione prima di iniziare, quindi non c’è preoccupazione per conflitti tra backup incrementali e completi.

crontab -e
5 00 * * * root /usr/local/bin/automysqlbackup
25 *  * * * root  /etc/automysqlbackup/mysql-incremental

Ripristina Database

Per ripristinare fino a un momento specifico (recupero point in time), prima devi ripristinare un backup completo giornaliero e poi ripristinare sequenzialmente i file di backup incrementali correlati. Per chiarire ulteriormente, ecco i passaggi per recuperare il database testDB. In uno scenario di esempio intendiamo recuperare i nostri dati fino al 2015-5-01 alle 2 AM. abbiamo impostato /backup come nostra directory di backup principale e testDB come nostro database target:

1- mysql -u root -p DatabaseName < /backup/daily/testDB/daily_DatabaseName_2015-05-16_00h05m_Saturday.sql.gz
2- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_00h25m.1
3- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_01h25m.2
4- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_02h25m.3

Note Importanti e Risoluzione dei Problemi

MySQL supporta diversi formati per il log binario. Alcune versioni di Mysql utilizzano ‘statement-based’ come formato binlog e questo tipo di binlog ha alcune limitazioni a cui dobbiamo prestare particolare attenzione quando intendiamo utilizzarlo nella procedura di backup incrementale. Quando mysql è impostato sul formato basato su statement, non è in grado di filtrare correttamente in base ai database. Se imposti ‘USE o \u’ per cambiare database e poi aggiorni un altro database che non è incluso in binlog-do-db, la dichiarazione sarà registrata nel file binlog che non è uno stato desiderabile! e causerà alcuni problemi quando si ripristina in base a un database specifico e anche se cambi a un altro database che non è incluso in binlog-do-db e aggiorni un database che è incluso in binlog-do-db, la dichiarazione non sarà registrata nel file binlog. Il nostro scopo nell’aggiungere database a binlog-do-db è filtrare in base al database, ma non funziona come previsto. Se USE o \u non viene eseguito prima di eseguire le query, mysqlbinlog non può estrarre ‘query di aggiornamento’ relative a un database. Spiegheremo ulteriormente questo problema con gli scenari seguenti:

database: 
 - binlog
     - person (tabella) 
  - binlog2
     - person (tabella)

 binlog-do-db=binlog2 (si suppone che solo le modifiche di questo database siano registrate nel file binlog)
--------Scenario 1---------
\u binlog2
insert into person (data) values ('17') ---> registrato nel binlog  *stato desiderato*
insert into binlog.person (data) values ('25'); ---> registrato nel binlog (il database target è 'binlog' ) *stato indesiderato*
--------Scenario 2---------
\u binlog
insert into person (data) values ('17') ---> non è registrato nel binlog  *stato desiderato*
insert into binlog2.person (data) values ('25'); ---> non è registrato nel binlog (il database target è 'binlog2' ) *stato indesiderato* perché il database binlog2
è in fase di modifica, quindi vogliamo avere questa modifica, ma non sarà registrata nel file logbin
--------Scenario 3---------
se ti connetti semplicemente al database senza alcuna dichiarazione USE o \u, tutti gli aggiornamenti su qualsiasi database saranno registrati, ma mysqlbinlog non sarà in grado di filtrare
in base a un database specifico, quindi non è uno stato desiderabile per il nostro scopo nel backup incrementale. Utilizzare USE o \u prima di eseguire le query di aggiornamento è molto
importante. Perché mysqlbinlog trova le query di aggiornamento in base alla dichiarazione USE nel file binlog.

Soluzione alternativa per il problema menzionato

  1. Definendo utenti sui database in modo che ogni utente abbia accesso solo a un database da aggiornare (utente dell’applicazione) e quando ci si connette al database, deve essere specificato il nome del database. Naturalmente, la maggior parte delle applicazioni ha un file di configurazione in cui sono impostate le credenziali e il nome del database, quindi in quel caso non avrai accesso incrociato sui database e non ci sarà preoccupazione nell’usare “\USE o \u”.

  2. Se utilizzi il formato binlog basato su righe, tutti i problemi menzionati scompariranno. In altre parole, il formato basato su righe è un metodo molto più appropriato per il binlog. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

File di Log

Ho cercato di registrare tutto in un file di log in modo da poter trovare informazioni sufficienti nei log:

/var/log/Backup_Post_Pre_log
/var/log/Backup_Incremental_Log
/[SpecifiedBackupDirInAutomysqlbackup.conf]/status/backup_info

Il file “ backup_info “ contiene informazioni dettagliate sul backup e quando il backup è stato completato (I tempi sono in formato Unix Time). Contiene il nome del binlog e la posizione del punto temporale in cui è iniziato il backup, il tipo di backup, il numero di backup dall’ultimo backup completo e la durata del backup.

Esempio di backup_info:

1431043501,mysql-bin.000026,120,Daily,2015-05-08,0,24
1431044701,mysql-bin.000026,120,Hourly,2015-05-08,1,1

Ecco la descrizione dei diversi valori:

 1°) 1431043501 : indica il momento in cui il backup è stato completato. Puoi eseguire il comando date --date @1431043501 sul server in cui è stato eseguito il backup per visualizzarlo in formato leggibile dall'uomo.
 2°) Mysql-bin.000026 : indica il nome del log binario su cui è stato eseguito il backup fino a questo file.
 3°) 120 : indica la posizione del binlog su cui è stato eseguito il backup fino a questa posizione nel log binario.
 4°) Daily/Hourly: indica il tipo di backup. Daily significa il backup completo eseguito dallo script automysqlbackup e Hourly è eseguito dallo script mysql-incremental.
 5°) 2015-05-08: La data in cui è stato eseguito il backup. Questa data sarà utilizzata nella creazione della directory per il backup incrementale e anche come base per ripristinare i backup orari. Nella procedura di ripristino, prima viene ripristinato un backup completo e poi sequenzialmente vengono ripristinati altri backup incrementali.
 6°) 0 : indica il numero di backup dall'ultimo backup completo. 0 significa che il backup è completo e altri significano orari. Questo numero è molto importante nella procedura di ripristino.
 7°) 24: La durata del backup in secondi.

Segnalazione Bug

Puoi segnalare bug o dare i tuoi suggerimenti e recensioni su https://sourceforge.net/projects/mysqlincrementalbackup.

Share: X/Twitter LinkedIn
#Backup MySQL

Ricevi i nuovi post nella tua casella di posta.

Nessuno spam. Disiscriviti in qualsiasi momento.