systemadmin.es > DBA > xtrabackup / innobackupex: Backups full y incremental

xtrabackup / innobackupex: Backups full y incremental

Podemos utilizar xtrabackup mediante el wrapper innobackupex para realizar backups de MySQL sin la asistencia de LVM de una forma muy similar a hotbackup. Vamos a ver un ejemplo de cómo realizar un full backup, un incremental y otro incremental del incremental anterior

Para realizar un backup simplemente deberemos indicar dónde dejar el backup:

# innobackupex /var/backups/xtrabackup

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

140824 19:04:44  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
140824 19:04:44  innobackupex: Connected to MySQL server
140824 19:04:44  innobackupex: Executing a version check against the server...
140824 19:04:45  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.6.19-67.0-56-log

innobackupex: Created backup directory /var/backups/xtrabackup/2014-08-24_19-04-45

140824 19:04:45  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/var/backups/xtrabackup/2014-08-24_19-04-45 --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=3131) to suspend
innobackupex: Suspend file '/var/backups/xtrabackup/2014-08-24_19-04-45/xtrabackup_suspended_2'

xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (1862778)
[01] Copying ./ibdata1 to /var/backups/xtrabackup/2014-08-24_19-04-45/ibdata1
[01]        ...done
[01] Copying ./jordidb/tbl.ibd to /var/backups/xtrabackup/2014-08-24_19-04-45/jordidb/tbl.ibd
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /var/backups/xtrabackup/2014-08-24_19-04-45/mysql/slave_master_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /var/backups/xtrabackup/2014-08-24_19-04-45/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /var/backups/xtrabackup/2014-08-24_19-04-45/mysql/slave_worker_info.ibd
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /var/backups/xtrabackup/2014-08-24_19-04-45/mysql/slave_relay_log_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /var/backups/xtrabackup/2014-08-24_19-04-45/mysql/innodb_index_stats.ibd
[01]        ...done
>> log scanned up to (1862778)
xtrabackup: Creating suspend file '/var/backups/xtrabackup/2014-08-24_19-04-45/xtrabackup_suspended_2' with pid '3131'

140824 19:04:47  innobackupex: Continuing after ibbackup has suspended
140824 19:04:47  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
140824 19:04:47  innobackupex: All tables locked and flushed to disk

140824 19:04:47  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql'
innobackupex: Backing up file '/var/lib/mysql/jordidb/db.opt'
innobackupex: Backing up file '/var/lib/mysql/jordidb/tbl.frm'
innobackupex: Backing up files '/var/lib/mysql/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> log scanned up to (1862778)
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
140824 19:04:48  innobackupex: Finished backing up non-InnoDB tables and files

140824 19:04:48  innobackupex: Executing FLUSH ENGINE LOGS...
140824 19:04:48  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1862778'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1862778)

xtrabackup: Creating suspend file '/var/backups/xtrabackup/2014-08-24_19-04-45/xtrabackup_log_copied' with pid '3131'
xtrabackup: Transaction log of lsn (1862778) to (1862778) was copied.
140824 19:04:49  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/var/backups/xtrabackup/2014-08-24_19-04-45'
innobackupex: MySQL binlog position: filename 'binlog.000001', position 9309
140824 19:04:49  innobackupex: Connection to database server closed
140824 19:04:49  innobackupex: completed OK!

Ésto creará un directorio con la fecha en el path indicado, en este caso: /var/backups/xtrabackup/2014-08-24_19-04-45. Dentro de dicho fichero tenemos varios ficheros que nos dan información sobre el backup:

# cat /var/backups/xtrabackup/2014-08-24_19-04-45/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1862778
last_lsn = 1862778
compact = 0

Incluso la posición del binlog:

# cat /var/backups/xtrabackup/2014-08-24_19-04-45/xtrabackup_binlog_info
binlog.000001	9309	

A continuación podemos crear un backup incremental en base a éste backup mediante la opción –incremental y –incremental-basedir para indicar el path del backup base, por ejemplo:

# innobackupex --incremental /var/backups/xtrabackup/ --incremental-basedir=/var/backups/xtrabackup/2014-08-24_19-04-45/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

140824 19:13:55  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
140824 19:13:55  innobackupex: Connected to MySQL server
140824 19:13:55  innobackupex: Executing a version check against the server...
140824 19:13:55  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.6.19-67.0-56-log

innobackupex: Created backup directory /var/backups/xtrabackup/2014-08-24_19-13-55

140824 19:13:55  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/var/backups/xtrabackup/2014-08-24_19-13-55 --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/var/backups/xtrabackup/2014-08-24_19-04-45/'
innobackupex: Waiting for ibbackup (pid=3297) to suspend
innobackupex: Suspend file '/var/backups/xtrabackup/2014-08-24_19-13-55/xtrabackup_suspended_2'

xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )
incremental backup from 1862778 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (1863213)
xtrabackup: using the full scan for incremental backup
[01] Copying ./ibdata1 to /var/backups/xtrabackup/2014-08-24_19-13-55/ibdata1.delta
[01]        ...done
[01] Copying ./jordidb/tbl.ibd to /var/backups/xtrabackup/2014-08-24_19-13-55/jordidb/tbl.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /var/backups/xtrabackup/2014-08-24_19-13-55/mysql/slave_master_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /var/backups/xtrabackup/2014-08-24_19-13-55/mysql/innodb_table_stats.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /var/backups/xtrabackup/2014-08-24_19-13-55/mysql/slave_worker_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /var/backups/xtrabackup/2014-08-24_19-13-55/mysql/slave_relay_log_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /var/backups/xtrabackup/2014-08-24_19-13-55/mysql/innodb_index_stats.ibd.delta
[01]        ...done
>> log scanned up to (1863213)
xtrabackup: Creating suspend file '/var/backups/xtrabackup/2014-08-24_19-13-55/xtrabackup_suspended_2' with pid '3297'

140824 19:13:57  innobackupex: Continuing after ibbackup has suspended
140824 19:13:57  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
140824 19:13:57  innobackupex: All tables locked and flushed to disk

140824 19:13:57  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql'
innobackupex: Backing up file '/var/lib/mysql/jordidb/db.opt'
innobackupex: Backing up file '/var/lib/mysql/jordidb/tbl.frm'
>> log scanned up to (1863213)
innobackupex: Backing up files '/var/lib/mysql/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
140824 19:13:58  innobackupex: Finished backing up non-InnoDB tables and files

140824 19:13:58  innobackupex: Executing FLUSH ENGINE LOGS...
140824 19:13:58  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1863213'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1863213)

xtrabackup: Creating suspend file '/var/backups/xtrabackup/2014-08-24_19-13-55/xtrabackup_log_copied' with pid '3297'
xtrabackup: Transaction log of lsn (1863213) to (1863213) was copied.
140824 19:13:59  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/var/backups/xtrabackup/2014-08-24_19-13-55'
innobackupex: MySQL binlog position: filename 'binlog.000001', position 9394
140824 19:13:59  innobackupex: Connection to database server closed
140824 19:13:59  innobackupex: completed OK!

Si volvemos a mirar el fichero xtrabackup_checkpoints veremos que se trata de un incremental:

# cat /var/backups/xtrabackup/2014-08-24_19-13-55/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1862778
to_lsn = 1863213
last_lsn = 1863213
compact = 0

Si comparamos los backups podremos ver que efectivamente el incremental tiene un tamaño mucho menor que el full:

# du -hs *
15M	2014-08-24_19-04-45
2.0M	2014-08-24_19-13-55

También podemos hacer un incremental cogiendo de base otro incremental, simplemente indicando con –incremental-basedir el incremental:

# innobackupex --incremental /var/backups/xtrabackup/ --incremental-basedir=/var/backups/xtrabackup/2014-08-24_19-13-55/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

140824 19:19:46  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
140824 19:19:46  innobackupex: Connected to MySQL server
140824 19:19:46  innobackupex: Executing a version check against the server...
140824 19:19:46  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.6.19-67.0-56-log

innobackupex: Created backup directory /var/backups/xtrabackup/2014-08-24_19-19-46

140824 19:19:46  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/var/backups/xtrabackup/2014-08-24_19-19-46 --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/var/backups/xtrabackup/2014-08-24_19-13-55/'
innobackupex: Waiting for ibbackup (pid=3463) to suspend
innobackupex: Suspend file '/var/backups/xtrabackup/2014-08-24_19-19-46/xtrabackup_suspended_2'

xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )
incremental backup from 1863213 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (1864381)
xtrabackup: using the full scan for incremental backup
[01] Copying ./ibdata1 to /var/backups/xtrabackup/2014-08-24_19-19-46/ibdata1.delta
[01]        ...done
[01] Copying ./jordidb/tbl.ibd to /var/backups/xtrabackup/2014-08-24_19-19-46/jordidb/tbl.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /var/backups/xtrabackup/2014-08-24_19-19-46/mysql/slave_master_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /var/backups/xtrabackup/2014-08-24_19-19-46/mysql/innodb_table_stats.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /var/backups/xtrabackup/2014-08-24_19-19-46/mysql/slave_worker_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /var/backups/xtrabackup/2014-08-24_19-19-46/mysql/slave_relay_log_info.ibd.delta
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /var/backups/xtrabackup/2014-08-24_19-19-46/mysql/innodb_index_stats.ibd.delta
[01]        ...done
>> log scanned up to (1864381)
xtrabackup: Creating suspend file '/var/backups/xtrabackup/2014-08-24_19-19-46/xtrabackup_suspended_2' with pid '3463'

140824 19:19:48  innobackupex: Continuing after ibbackup has suspended
140824 19:19:48  innobackupex: Executing FLUSH TABLES WITH READ LOCK...
140824 19:19:48  innobackupex: All tables locked and flushed to disk

140824 19:19:48  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql'
innobackupex: Backing up file '/var/lib/mysql/jordidb/db.opt'
>> log scanned up to (1864381)
innobackupex: Backing up file '/var/lib/mysql/jordidb/tbl.frm'
innobackupex: Backing up files '/var/lib/mysql/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
140824 19:19:48  innobackupex: Finished backing up non-InnoDB tables and files

140824 19:19:48  innobackupex: Executing FLUSH ENGINE LOGS...
140824 19:19:48  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1864381'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1864381)

xtrabackup: Creating suspend file '/var/backups/xtrabackup/2014-08-24_19-19-46/xtrabackup_log_copied' with pid '3463'
xtrabackup: Transaction log of lsn (1864381) to (1864381) was copied.
140824 19:19:49  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/var/backups/xtrabackup/2014-08-24_19-19-46'
innobackupex: MySQL binlog position: filename 'binlog.000001', position 9479
140824 19:19:49  innobackupex: Connection to database server closed
140824 19:19:49  innobackupex: completed OK!

Volviendo a mirar el fichero xtrabackup_checkpoints veremos que no hay diferencia respecto un incremental de un full:

# cat /var/backups/xtrabackup/2014-08-24_19-19-46/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1863213
to_lsn = 1864381
last_lsn = 1864381
compact = 0

Podemos apreciar cómo el número de LSN tiene un from si es un incremental, en el caso de un full es cero:

# cat /var/backups/xtrabackup/2014-08-24_19-*/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1862778
last_lsn = 1862778
compact = 0
backup_type = incremental
from_lsn = 1862778
to_lsn = 1863213
last_lsn = 1863213
compact = 0
backup_type = incremental
from_lsn = 1863213
to_lsn = 1864381
last_lsn = 1864381
compact = 0

También:

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>