systemadmin.es > DBA > xtrabackup / innobackupex: Backup y restore con Galera

xtrabackup / innobackupex: Backup y restore con Galera

Para realizar backups en Galera, podemos usar xtrabackup.

La única diferéncia es que deberemos conservar la posición del backup, mediante la opción –galera-info se guarda automáticamente:

# innobackupex --galera-info /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.

(...)

140826 16:57:33  innobackupex: Connection to database server closed
140826 16:57:33  innobackupex: completed OK!

Dentro del backup podemos encontrar el fichero xtrabackup_binlog_info:

# ls /var/backups/xtrabackup/2014-08-26_16-57-30/
backup-my.cnf           ibdata1                 mysql/                  test/                   xtrabackup_checkpoints  xtrabackup_info
binlog.000008           jordidb/                performance_schema/     xtrabackup_binlog_info  xtrabackup_galera_info  xtrabackup_logfile

Con la posición del backup (ID de transacción):

# cat /var/backups/xtrabackup/2014-08-26_16-57-30/xtrabackup_galera_info 
31df6dfc-29f0-11e4-a2c1-8bc065959a42:70

Para restaurar el backup, primero aplicamos el log:

# innobackupex --apply-log /var/backups/xtrabackup/2014-08-26_16-57-30/

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

(...)
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1865750
140826 16:58:59  innobackupex: completed OK!

A continuación eliminamos el datadir:

# mv /var/lib/mysql /var/lib/mysql.old
# mkdir /var/lib/mysql
# chown mysql. /var/lib/mysql

Y copiamos los datafiles de vuelta:

# innobackupex --copy-back /var/backups/xtrabackup/2014-08-26_16-57-30/

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

(...)

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/var/backups/xtrabackup/2014-08-26_16-57-30'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying '/var/backups/xtrabackup/2014-08-26_16-57-30/ib_logfile1' to '/var/lib/mysql/ib_logfile1'
innobackupex: Copying '/var/backups/xtrabackup/2014-08-26_16-57-30/ib_logfile0' to '/var/lib/mysql/ib_logfile0'
innobackupex: Finished copying back files.

140826 17:00:21  innobackupex: completed OK!

Volvemos a dejar los permisos para mysql:

# chown mysql. /var/lib/mysql -R

Y buscamos la posición de Galera:

# cat /var/backups/xtrabackup/FULL/xtrabackup_galera_info 
9e01d73c-2d32-11e4-bc72-47b3381eaa94:4

A continuación, arrancamos un nodo como bootstrap con la posición que hemos obtenido:

# /etc/init.d/mysql start --wsrep-cluster-address='gcomm://' --wsrep-recover '--wsrep_start_position=9e01d73c-2d32-11e4-bc72-47b3381eaa94:4'
 ERROR! MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists
Starting MySQL (Percona XtraDB Cluster)... ERROR! The server quit without updating PID file (/var/lib/mysql/galera1.lolcathost.pid).
 ERROR! MySQL (Percona XtraDB Cluster) server startup failed!

El arranque fallarà porque esta haciendo el recovery, en los logs podremos encontrar:

140826 17:04:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140826 17:04:12 mysqld_safe Skipping wsrep-recover for empty datadir: /var/lib/mysql
140826 17:04:12 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position
2014-08-26 17:04:12 0 [Note] WSREP: wsrep_start_position var submitted: '31df6dfc-29f0-11e4-a2c1-8bc065959a42:70'
2014-08-26 17:04:12 0 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'
2014-08-26 17:04:12 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-08-26 17:04:12 9284 [Note] Plugin 'FEDERATED' is disabled.
2014-08-26 17:04:12 9284 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-08-26 17:04:12 9284 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-26 17:04:12 9284 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-08-26 17:04:12 9284 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-26 17:04:12 9284 [Note] InnoDB: Using Linux native AIO
2014-08-26 17:04:12 9284 [Note] InnoDB: Not using CPU crc32 instructions
2014-08-26 17:04:12 9284 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-08-26 17:04:12 9284 [Note] InnoDB: Completed initialization of buffer pool
2014-08-26 17:04:12 9284 [Note] InnoDB: Highest supported file format is Barracuda.
2014-08-26 17:04:12 9284 [Note] InnoDB: 128 rollback segment(s) are active.
2014-08-26 17:04:12 9284 [Note] InnoDB: Waiting for purge to start
2014-08-26 17:04:12 9284 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.19-67.0 started; log sequence number 1865750
2014-08-26 17:04:12 9284 [Warning] InnoDB: Skipping buffer pool dump/restore during wsrep recovery.
2014-08-26 17:04:12 9284 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3d69e075-2d32-11e4-aee8-080027ed4eff.
2014-08-26 17:04:13 9284 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2014-08-26 17:04:13 9284 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2014-08-26 17:04:13 9284 [Note] Server hostname (bind-address): '*'; port: 3306
2014-08-26 17:04:13 9284 [Note] IPv6 is available.
2014-08-26 17:04:13 9284 [Note]   - '::' resolves to '::';
2014-08-26 17:04:13 9284 [Note] Server socket created on IP: '::'.
2014-08-26 17:04:13 9284 [Note] WSREP: Recovered position: 31df6dfc-29f0-11e4-a2c1-8bc065959a42:70
2014-08-26 17:04:13 9284 [Note] Binlog end
(...)
2014-08-26 17:04:15 9284 [Note] /usr/sbin/mysqld: Shutdown complete

Volvemos a arrancar el nodo en bootstrap sin indicar la posición inicial:

# /etc/init.d/mysql start --wsrep-cluster-address='gcomm://' 
 ERROR! MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists
Starting MySQL (Percona XtraDB Cluster). SUCCESS! 

Finalmente ya podremos arrancar el resto de nodos, que recibirán automáticamente una cópia del datadir:

# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)....State transfer in progress, setting sleep higher
.. SUCCESS! 

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>