XtraBackup

XtraBackup e' un tool Open Source sviluppato da Percona per eseguire l'hot backup di MySQL.

XtraBackup consente di eseguire backup online anche sulla versione MySQL community: la funzionalita' di backup online e' infatti presente solo nell'edizione Enterprise di MySQL.

Introduzione

Con il database attivo ed operante xtrabackup esegue un backup fisico, efficiente, e quasi senza impatto sulle applicazioni presenti. xtrabackup e' disponibile per tutte le versioni di MySQL e di MariaDB. xtrabackup opera su tutti gli Engine; le tabelle InnoDB vengono salvate senza alcun lock, le tabelle di altri Engine su cui non e' presente il supporto delle transazioni vengono salvate come ultime cercando di utilizzare un locking minimale.

XtraBackup richiede due passi. Nel primo effettua una copia fisica della base dati, nel secondo le tabelle vengono allineate in modo consistente tra loro.

Installazione

L'installazione dai repository di Percona e' molto semplice:

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm yum install percona-xtrabackup-24

In questo caso installiamo l'ultima versione di xtrabackup, escludendo pero' esplicitamente la 8.0. La versione xtrabackup 8.0 e' infatti compatibile con MySQL 8.0 in cui e' stato cambiato il formato dei binlog e quindi non puo' operare con nessuna delle versioni precedenti.

Gia' fatto! Non serve altro: xtrabackup si puo' utilizzare.

Utilizzo

Il comando per effettuare un backup e':

xtrabackup --backup --user=root --password=xxx --target-dir=/mysql/xtra/bck

xtrabackup trova nel file my.cnf tutti gli estremi di configurazione del DB e si connette per verificare ulteriori configurazioni. Poi effettua il backup copiando localmente i file del DB sulla directory di target. E' ovviamente opportuno lanciare il comando in nohup e salvare l'output...

La durata del backup dipende dalla dimensione del DB, dall'utilizzo del DB (in termini di modifiche) e dalle prestazioni del sistema ospite. In ogni caso e' tipicamente nettamente inferiore alla durata di un backup con mysqldump.
Il backup ha la stessa struttura di directory del database MySQL, la differenza principale e' che al posto dei binlog viene salvato il file xtrabackup_logfile che contiene tutte le transazioni occorse durante il backup. Il dettaglio degli altri file presenti nel backup e' riportato nella documentazione ufficiale.
Lo spazio necessario per il backup e' quindi la dimensione del DB fisico piu' quello di tutti i binlog raccolti durante durata del backup.

I passi del backup sono diversi: reperimento delle informazioni, connessione al DB, scan dei binlog e salvataggio dei file delle tabelle InnoDB, lock e quindi salvataggio delle tabelle di altri Engine, compilazione dei file riassuntivi e termine backup.
Ecco un estratto dell'output (con evidenziati i punti salienti):

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=69 --tmpdir=/mysql/data/tmp/ --innodb_buffer_pool_size=128G 
            --innodb_log_file_size=1G --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=16M --log_bin=mysql-bin 
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/mysql/xtra/bck 
190401 07:46:37  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
190401 07:46:37  version_check Connected to MySQL server
190401 07:46:37  version_check Executing a version check against the server...
190401 07:46:37  version_check Done.
190401 07:46:37 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.23-log
xtrabackup version 2.4.15 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 544842a)
...
190401 07:46:46 >> log scanned up to (20800623372815)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 139455 for acme_invoicing/Plan, old maximum was 0
190401 07:46:47 >> log scanned up to (20800624278919)
...
190401 07:47:10 >> log scanned up to (20800646078252)
190401 07:47:10 [01] Copying ./ibdata1 to /mysql/xtra/bck/ibdata1
190401 07:47:11 >> log scanned up to (20800647079221)
...
190401 07:47:11 >> log scanned up to (20800647079221)
190401 07:48:20 [01] Copying ./mysql/time_zone_transition.ibd to /mysql/xtra/bck/mysql/time_zone_transition.ibd
190401 07:48:21 [01]        ...done
190401 07:48:21 [01] Copying ./mysql/innodb_table_stats.ibd to /mysql/xtra/bck/mysql/innodb_table_stats.ibd
190401 07:48:21 [01]        ...done
190401 07:48:21 [01] Copying ./mysql/plugin.ibd to /mysql/xtra/bck/mysql/plugin.ibd
190401 07:48:21 [01]        ...done
190401 07:48:21 [01] Copying ./mysql/time_zone_transition_type.ibd to /mysql/xtra/bck/mysql/time_zone_transition_type.ibd
190401 07:48:21 >> log scanned up to (20800676254590)
190401 07:48:21 [01]        ...done
190401 07:48:21 [01] Copying ./mysql/servers.ibd to /mysql/xtra/bck/mysql/servers.ibd
...
190401 21:12:57 >> log scanned up to (40899430531673)
190401 21:12:58 >> log scanned up to (40899438852648)
190401 21:12:59 >> log scanned up to (40899438852648)
190401 21:13:00 Starting to backup non-InnoDB tables and files
190401 21:13:00 [01] Copying ./acme_invoicing/db.opt to /mysql/xtra/bck/acme_invoicing/db.opt
190401 21:13:00 [01]        ...done
190401 21:13:00 [01] Copying ./acme_invoicing/results_view.frm to /mysql/xtra/bck/acme_invoicing/results_view.frm
190401 21:13:00 [01]        ...done
190401 21:13:00 [01] Copying ./acme_invoicing/Plan.frm to /mysql/xtra/bck/acme_invoicing/Plan.frm
190401 21:13:00 [01]        ...done
...
190401 21:22:52 [01] Copying ./ppm/confl_pmh.frm to /mysql/xtra/bck/ppm/confl_pmh.frm
190401 21:22:52 [01]        ...done
190401 21:22:52 Finished backing up non-InnoDB tables and files
190401 21:22:52 [00] Writing /mysql/xtra/bck/xtrabackup_binlog_info
190401 21:22:52 [00]        ...done
190401 21:22:52 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
...
xtrabackup: The latest check point (for incremental): '40899450737590'
xtrabackup: Stopping log copying thread.
190401 21:22:52 >> log scanned up to (40899450737599)
...
190401 21:22:52 Executing UNLOCK TABLES
190401 21:22:52 All tables unlocked
190401 21:22:52 [00] Copying ib_buffer_pool to /mysql/xtra/bck/ib_buffer_pool
190401 21:22:52 [00]        ...done
190401 21:22:53 Backup created in directory '/mysql/xtra/bck/'
MySQL binlog position: filename 'mysql-bin.006969', position '205351313'
190401 21:22:53 [00] Writing /mysql/xtra/bck/backup-my.cnf
190401 21:22:53 [00]        ...done
190401 21:22:53 [00] Writing /mysql/xtra/bck/xtrabackup_info
190401 21:22:53 [00]        ...done
xtrabackup: Transaction log of lsn (40888248351115) to (40899450737599) was copied.
190401 21:22:53 completed OK!

Poiche' il backup e' stato eseguito a database aperto le tabelle sono disallineate tra loro, come se fosse avvenuto un crash del server. E' quindi necessaria una seconda fase, chiamata prepare, in cui viene eseguito un normale recovery applicando il binlog raccolto durante il backup:

xtrabackup --prepare --user=root --password=xxx --target-dir=/mysql/xtra/bck

La fase di prepare non richiede la connessione al DB poiche' tutte le informazioni necessarie sono gia' state raccolte. Puo' anche essere eseguita su un sistema diverso (e potenzialmente anche su una versione diversa di MySQL/MariaDB purche' compatibile).

I passi sono simili a quelli di recovery InnoDB che prevede un serie di fasi di avvio e shutdown. Al termine vengono generati alcuni file aggiuntivi: ad esempio xtrabackup_binlog_info che contiene le informazioni necessarie per configurare uno slave in replica. Ecco un estratto dell'output (con evidenziati i punti salienti):

xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend
            --innodb_log_files_in_group=2 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512
            --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=70 --redo-log-version=1
xtrabackup: recognized client arguments: --user=root --password=* --prepare=1 --target-dir=/mysql/xtra/bck
xtrabackup version 2.4.15 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 544842a)
xtrabackup: cd to /mysql/xtra/bck/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=12602638336, start_lsn=(40888248351115)
xtrabackup: using the following InnoDB configuration for recovery:
...
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 40888248351115
InnoDB: Doing recovery: scanned up to log sequence number 40888253593600 (0%)
...
InnoDB: Doing recovery: scanned up to log sequence number 40899447142400 (99%)
InnoDB: Doing recovery: scanned up to log sequence number 40899450737599 (100%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Tablespace 221187 was not found at './test/ds_68604804_test.ibd', but there were no modifications either.
InnoDB: Tablespace 221188 was not found at './test/ds_2178016204_test.ibd', but there were no modifications either.
InnoDB: Doing recovery: scanned up to log sequence number 40888249989120 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 40888255232000 (0%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
        50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
...
InnoDB: Doing recovery: scanned up to log sequence number 40899448780800 (99%)
InnoDB: Doing recovery: scanned up to log sequence number 40899450737599 (100%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
        50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: xtrabackup: Last MySQL binlog file position 205358366, file name mysql-bin.006886
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 40899450737599
InnoDB: page_cleaner: 1000ms intended loop took 941199ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: xtrabackup: Last MySQL binlog file position 205358366, file name mysql-bin.006886

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 40899450781069
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 1073741824
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 1024 MB
InnoDB: Progress in MB:
 100 200 300 400 500 600 700 800 900 1000
InnoDB: Setting log file ./ib_logfile1 size to 1024 MB
InnoDB: Progress in MB:
 100 200 300 400 500 600 700 800 900 1000
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=40899450781069
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 40899450781196
InnoDB: Doing recovery: scanned up to log sequence number 40899450781205 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 205358366, file name mysql-bin.006886
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 40899450781205
InnoDB: page_cleaner: 1000ms intended loop took 19966ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 40899450781224
190401 23:14:69 completed OK!

Ora il backup e' pronto e puo' essere ripristinato o salvato per un eventuale successivo utilizzo.
La fase di prepare non e' strettamente necessaria se il backup non deve essere subito utilizzato, ma generalmente si preferisce eseguirla comunque per controllarne l'esito finale.

Restore

La fase di restore e' molto semplice poiche' la struttura generata da XtraBackup e' quella di una data directory MySQL.
L'unica avvertenza, importante, e' che la base dati da restorare non deve essere attiva.

E' possibile utilizzare un qualsiasi comando come cp mv scp rsync con le relative caratteristiche e limitazioni (eg. -p chown). Il comando xtrabackup ha anche le opzioni --copy-back oppure --move-back dall'evidente significato!

Privilegi

Nell'esempio di utilizzo e' stato impiegato l'utente root... ovviamente e' sconsigliabile (cosi' come e' sconsigliabile passare la password in linea).

Ecco i GRANT da concedere:

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'xxx'; mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';

Ovviamente non usate xxx come password!

Replica

xtrabackup puo' essere utilmente impiegato per creare una copia del DB ed impostare la replica MySQL. Nel file xtrabackup_binlog_info sono riportati il MASTER_LOG_FILE ed il MASTER_LOG_POS da specificare nel comando CHANGE MASTER TO.

Il backup puo' essere eseguito anche su uno slave gia' configurato in replica. Dal punto di vista tecnico non vi sono particolari differenze... l'unica avvertenza e' quella di utilizzare il parametro --slave-info che fa generare un ulteriore file xtrabackup_slave_info da cui ricavare i parametri per la replica.

XtraBackup supporta anche la replica GTID [NdA anche quella di MariaDB che e' implementata in modo differente].

Incremental backup

XtraBackup supporta i backup incrementali.
Come primo passo e' necessario effettuare un normale backup online:

xtrabackup --backup --user=bkpuser --password=xxx --target-dir=/mysql/xtra/bck

Il backup incrementale viene eseguito con:

xtrabackup --backup --user=bkpuser --password=xxx --target-dir=/mysql/xtra/inc1 --incremental-basedir=/mysql/xtra/bck

I successivi backup incrementali possono essere eseguiti indicando come incremental-basedir l'incrementale precedente.

Per eseguire i backup incrementali XtraBackup utilizza gli LSN (log sequence number) dell'Engine InnoDB.
In ogni backup viene generato il file xtrabackup_checkpoints che riporta gli estremi necessari per l'eventuale backup incrementale successivo:

backup_type = full-backuped
from_lsn = 1313
to_lsn = 6969
last_lsn = 6969
compact = 0
recover_binlog_info = 1

Nella fase di prepare dei backup incrementali deve essere poi esclusa la fase di rollback delle pending transactions con --apply-log-only [NdA perche' verrano poi probabilmente committate nel backup incrementale successivo]. In pratica vanno lanciati i comandi seguenti:

xtrabackup --prepare --apply-log-only --target-dir=/mysql/xtra/bck xtrabackup --prepare --apply-log-only --target-dir=/mysql/xtra/bck --incremental-dir=/mysql/xtra/inc1 xtrabackup --prepare --apply-log-only --target-dir=/mysql/xtra/bck --incremental-dir=/mysql/xtra/inc2 ... xtrabackup --prepare --target-dir=/mysql/xtra/bck --incremental-dir=//mysql/xtra/incN

A questo punto la datadir e' pronta puo' essere attivata completando cosi' il restore.

Common problems and workaround

Possono verificarsi errori... vediamo i piu' comuni e come risolverli:

xtrabackup: Can't create/write to file '/mysql/xtra/bck/xtrabackup_logfile' (Errcode: 17 - File exists) xtrabackup: error: failed to open the target stream for 'xtrabackup_logfile'.
XtraBackup richiede che la directory di destinazione del backup non esista o sia vuota. Basta rinominarla, liberarla o cambiare il target.
InnoDB: Operating system error number 24 in a file operation. InnoDB: Error number 24 means 'Too many open files' InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html InnoDB: File ./test/test.ibd: 'open' returned OS error 124. Cannot continue operation InnoDB: Cannot continue operation.
xtrabackup esegue in parallelo la copia dei file, se si utilizza la modalita' file-per-table (che e' il default nella 5.7) il numero di file aperti potrebbe superare il limite impostato sul sistema operativo. Una stima di massima sul numero di file aperti durante il backup si ha con:
find /var/lib/mysql/ -name "*.ibd" | wc -l
Aggiungendo 1024 al valore ottenuto si e' certi che l'impostazione sara' sufficiente. Basta alzare il limite con il comando:
ulimit -n 8192
InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet. PXB will not be able to make a consistent backup. Retry the backup operation
Con la versione 5.7 sono state ottimizzate alcune DDL implementandole in modo differente ma che rendono incosistente il backup. Sono possibili diverse opzioni di xtrabackup per utilizzare un lock in questi casi e consentire un backup consistente (eg. --lock-ddl, --lock-ddl-timeout, --lock-ddl-per-table); pero' viene richiesto un lock... Naturalmente e' possibile rilanciare il backup in un orario in cui non vengono eseguite DDL; pero' a volte le applicazioni sono imprevedibili... Una soluzione e' quella di far operare MySQL "alla vecchia maniera" [NdA in questo modo MySQL utilizza una tabella temporanea scambiandola al termine dell'operazione di ALTER] con il parametro:
set global old_alter_table=on;
Avviando uno Slave sul DB restorato con XtraBackup: Slave failed to initialize relay log info structure from the repository
Le ragioni dell'errore possono essere molte!
Una prima ragione e' che abbiate copiato male i dati dal file xtrabackup_binlog_info!
Un'altra possibile ragione e' che sia stato eseguito il backup da uno Slave per aggiungere un nuovo Slave. In questo caso il backup va lanciato con l'opzione --slave-info ed i parametri per la configurazione dello slave vanno raccolte dal file xtrabackup_slave_info.
Con un restore fisico non vengono mantenute le tabelle di repository. Nel caso fossero presenti nel my.cnf vanno commentate e riavviato lo Slave:
```
# relay_log_info_repository = TABLE
# master_info_repository = TABLE
```
Sono presenti diversi altri casi (bug, disallineamenti di versioni, crash, parametri non ACID, ...) per cui l'IO Thread puo' non ripartire correttamente... Con i comandi: reset slave; change master to...; start slave; stop slave; reset slave; start slave;... insomma con un po' di insistenza di solito si risolve!
Non basta? Riavviate anche MySQL! Dalla 5.6 in poi il reset completo si effettua con reset slave all;
???
State usando la versione corretta? Avete installato l'ultima versione di XtraBackup? Avete aggiornato all'ultima versione MySQL/MariaDB?
Fatelo e riprovate!

Varie ed eventuali

Quando si ha la necessita' di un backup logico il tool di elezione e' mysqldump che e' ricco di opzioni e disponibile su tutte le versioni ed i fork di MySQL.

Titolo: XtraBackup
Livello: Esperto (4/5)
Data: 1 Aprile 2019
Versione: 1.0.1 - 31 Ottobre 2019 🎃 Halloween
Autori: mail [AT] meo.bogliolo.name, Umberto Signori