MySQL backups y sincronización mediante Percona XtraBackup

Utilizar Percona Xtrabackup para dumpear nuestras bases de datos comporta una serie de ventajas, las mas destacables son, poder hacerlo en caliente sin necesidad de filtrar el tráfico, una mayor velocidad que un dump tradicional ya que la copia de los ficheros es física(no lógica), posibilidad de compresión, envío de backups remotos a través de SSH.

Si estamos corriendo MySQL8 necesitaremos la versión inestable de xtrabackup:

dev-db/percona-xtrabackup
 Available versions: 2.4.15 ~8.0.7

En caso contrario al realizar el backup nos mostrará el siguiente error:

Error: MySQL 8.0 and Percona Server 8.0 are not supported by Percona Xtrabackup 2.4.x series. Please use Percona Xtrabackup 8.0.x for backups and restores.

Desenmascaramos el ebuild:

vi /etc/portage/package.accept_keywords/percona-xtrabackup

dev-db/percona-xtrabackup ~amd64

Compilamos e instalamos el software:

emerge -av dev-db/percona-xtrabackup

Configuramos la replicación tal y como se indica en este artículo anterior .

Nos aseguramos de disponer de suficiente espacio antes de sacar el backup:

df -h

Creamos el directorio donde guardaremos el backup:

mkdir -p /var/backup/xtra

Aumentamos el límite de ficheros para evitar problemas en medio del proceso de backup:

ulimit -n 90000

Dependiendo de si sacamos el backup de un Master o un Slave y del modo de replicación que utilicemos seguiremos unos pasos u otros.

MASTER - LEGACY MODE
MASTER - GTID MODE
SLAVE - LEGACY MODE
SLAVE - GTID MODE
SSH BACKUPS

MASTER - LEGACY MODE

Sacamos el backup:

xtrabackup --backup --target-dir=/var/backup/xtra -uroot -p

El resto de comandos se ejecutarán todos en el Slave.

Creamos el directorio donde copiaremos el backup:

mkdir -p /var/backup/xtra

Paramos MySQL y eliminamos cualquier fichero previo:

/etc/init.d/mysql stop
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*

Copiamos el backup del Master al Slave.

Este backup es una copia de los datos en crudo, pueden haber transacciones no commiteadas que deben ser revertidas y transacciones en los logs por aplicar, realizamos todas estas acciones antes de arrancar MySQL:

xtrabackup --prepare --target-dir=/var/backup/xtra

Restauramos los datos, podemos copiarlos o moverlos:

xtrabackup --copy-back --target-dir=/var/backup/xtra
xtrabackup --move-back --target-dir=/var/backup/xtra

Arrancamos MySQL:

chown -R mysql:mysql /var/lib/mysql/
/etc/init.d/mysql start

Sacamos la posición en la que se encontraba el Master cuando se extrajo el backup:

cat /var/backup/xtra/xtrabackup_binlog_info

mysql-bin.000002 155

Reseteamos cualquier configuración anterior:

mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;

Le indicamos cual será su Master, el usuario de replicación, el password, el fichero de binlog y la posición donde debe enganchar:

mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='rep_user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=155;
mysql> START SLAVE;

Comprobamos que no haya ningún problema:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_IP
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 155
               Relay_Log_File: kr0mtest2-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 155
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: f7f291ff-7976-11ea-b70b-00163ea83ac0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

Para comprobar que funciona correctament creamos en el Master una base de datos llamada kr0m:

mysql> CREATE DATABASE kr0m;

Comprobamos que se ha replicado en el Slave:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kr0m               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

MASTER - GTID MODE

Sacamos el backup:

xtrabackup --backup --target-dir=/var/backup/xtra -uroot -p

El resto de comandos se ejecutarán todos en el Slave.

Creamos el directorio donde copiaremos el backup:

mkdir -p /var/backup/xtra

Paramos MySQL y eliminamos cualquier fichero previo:

/etc/init.d/mysql stop
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*

Copiamos el backup del Master al Slave.

xtrabackup --prepare --target-dir=/var/backup/xtra

Restauramos los datos, podemos copiarlos o moverlos:

xtrabackup --copy-back --target-dir=/var/backup/xtra
xtrabackup --move-back --target-dir=/var/backup/xtra

Arrancamos MySQL:

chown -R mysql:mysql /var/lib/mysql/
/etc/init.d/mysql start

Obtenemos el GTID:

cat /var/backup/xtra/xtrabackup_binlog_info

mysql-bin.000010 195 509b989d-797d-11ea-b209-00163ea83ac0:1-5

NOTA: Si el Master es un servidor virgen, el GTID saldrá vacío, creamos una base de datos de prueba vacía, la eliminamos y redumpeamos. El GTID puede ser único o varios separados por comas.

Eliminamos cualquier configuración previa:

mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;

Asignamos el GTID, el servidor Master, el usuario de replicación y su password, en esta ocasión al tratarse de GTID no es necesario indicar posición de binlog:

mysql> SET GLOBAL gtid_purged="509b989d-797d-11ea-b209-00163ea83ac0:1-5";
mysql> CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="rep_user", MASTER_PASSWORD="PASSWORD", MASTER_AUTO_POSITION = 1;

Arrancamos la sincronización y comprobamos su estado:

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_IP
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 195
               Relay_Log_File: kr0mtest2-relay-bin.000002
                Relay_Log_Pos: 369
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 195
              Relay_Log_Space: 581
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 509b989d-797d-11ea-b209-00163ea83ac0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 509b989d-797d-11ea-b209-00163ea83ac0:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

Para comprobar que funciona correctament creamos en el Master una base de datos llamada kr0m:

mysql> CREATE DATABASE kr0m;

Comprobamos que se ha replicado en el Slave:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kr0m               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

SLAVE - LEGACY MODE

Sacamos el backup:

xtrabackup --backup --slave-info --safe-slave-backup --target-dir=/var/backup/xtra -uroot -p

Todos los comandos a partir de aquí se ejecutarán en el Slave nuevo.

Creamos el directorio donde copiar el backup:

mkdir -p /var/backup/xtra

Paramos MySQL y eliminamos cualquier fichero previo:

/etc/init.d/mysql stop
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*

Copiamos el backup del Slave al Slave2.

xtrabackup --prepare --target-dir=/var/backup/xtra

Restauramos los datos, podemos copiarlos o moverlos:

xtrabackup --copy-back --target-dir=/var/backup/xtra
xtrabackup --move-back --target-dir=/var/backup/xtra

Arrancamos MySQL:

chown -R mysql:mysql /var/lib/mysql/
/etc/init.d/mysql start

Obtenemos la posición del binlog:

cat /var/backup/xtra/xtrabackup_slave_info

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=340;

Eliminamos cualquier configuración previa:

mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;

Enganchamos el Slave al Master indicando la ip de este, el usuario de replicación, el password, el fichero de binlog y la posición:

mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='rep_user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=340;

Arrancamos la sincronización:

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_IP
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 340
               Relay_Log_File: kr0mtest3-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 340
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: f7f291ff-7976-11ea-b70b-00163ea83ac0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

Para comprobar que funciona correctament creamos en el Master una base de datos llamada kr0m:

mysql> CREATE DATABASE kr0m;

Comprobamos que se ha replicado en el Slave:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kr0m               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

SLAVE - GTID MODE

Sacamos el backup:

xtrabackup --backup --slave-info --safe-slave-backup --target-dir=/var/backup/xtra -uroot -p

Todos los comandos a partir de aquí se ejecutarán en el Slave nuevo.

Creamos el directorio donde copiar el backup:

mkdir -p /var/backup/xtra

Paramos MySQL y eliminamos cualquier fichero previo:

/etc/init.d/mysql stop
rm -rf /var/lib/mysql/*
rm -rf /var/lib/mysql/.*

Copiamos el backup del Slave al Slave2.

xtrabackup --prepare --target-dir=/var/backup/xtra

Restauramos los datos, podemos copiarlos o moverlos:

xtrabackup --copy-back --target-dir=/var/backup/xtra
xtrabackup --move-back --target-dir=/var/backup/xtra

Arrancamos MySQL:

chown -R mysql:mysql /var/lib/mysql/
/etc/init.d/mysql start

Obtenemos el GTID:

cat /var/backup/xtra/xtrabackup_binlog_info

mysql-bin.000002 195 509b989d-797d-11ea-b209-00163ea83ac0:1-6

NOTA: Si el master es un servidor virgen, el GTID saldrá vacío, creamos una base de datos de prueba vacía, la eliminamos y redumpeamos. El GTID puede ser único o varios separados por comas.

Eliminamos cualquier configuración previa:

mysql> RESET SLAVE;
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;

Asignamos el GTID, el servidor Master, el usuario de replicación y su password, en esta ocasión al tratarse de GTID no es necesario indicar posición de binlog:

mysql> SET GLOBAL gtid_purged="509b989d-797d-11ea-b209-00163ea83ac0:1-6";
mysql> CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="rep_user", MASTER_PASSWORD="PASSWORD", MASTER_AUTO_POSITION = 1;

Arrancamos la sincronización:

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_IP
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 380
               Relay_Log_File: kr0mtest3-relay-bin.000002
                Relay_Log_Pos: 416
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 380
              Relay_Log_Space: 628
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 509b989d-797d-11ea-b209-00163ea83ac0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 509b989d-797d-11ea-b209-00163ea83ac0:1-6
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

Para comprobar que funciona correctament creamos en el Master una base de datos llamada kr0m:

mysql> CREATE DATABASE kr0m;

Comprobamos que se ha replicado en el Slave:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kr0m               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

SSH BACKUPS

Es tan sencillo como “empipar” la salida por SSH utilizando xbstream(tar ya no es soportado):

xtrabackup --stream=xbstream --backup --target-dir=/var/backup/xtra -uroot -p | ssh root@SLAVE_IP “xbstream -x -C /var/backup/xtra”

El resto es exactamente igual que si se hubiese sacado el backup en el propio servidor ya sea con replicación legacy como con GTID.