Overview

As we already discussed, after backup is taken, the last step is to prepare the backup. In this situation, we don't have to recover our backup since it is already recovered. Let's test our scenario of a complete server lost.

[root@mysqlmaster backup]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
[root@mysqlmaster backup]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mysqldata                                        <- Our data directory
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=1
log-bin=mysql-bin
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time                = 5
slow_query_log
log_slow_admin_statements=on

innodb_flush_log_at_trx_commit=1
sync_binlog=1

Restore

According our configuration, the data directory is located in /mysqldata and our backup is at /backup The utility will simply copy back the already recovered backup as follows:

[root@mysqlmaster backup]# innobackupex --copy-back /backup/2018-06-08_14-27-54/
IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
180608 14:37:25 [01] Copying ib_logfile0 to /mysqldata/ib_logfile0
180608 14:37:25 [01]        ...done
180608 14:37:27 [01] Copying ib_logfile1 to /mysqldata/ib_logfile1
****************************
[root@mysqlmaster backup]# chown -R mysql:mysql /mysqldata/*
[root@mysqlmaster backup]# ls -lart /mysqldata/
total 122924
dr-xr-xr-x. 19 root  root       275 Jun  4 08:30 ..
-rw-r-----   1 mysql mysql 50331648 Jun  8 14:37 ib_logfile0
-rw-r-----   1 mysql mysql 50331648 Jun  8 14:37 ib_logfile1
-rw-r-----   1 mysql mysql 12582912 Jun  8 14:37 ibdata1
drwxr-x---   2 mysql mysql    12288 Jun  8 14:37 sys
drwxr-x---   2 mysql mysql     4096 Jun  8 14:37 newDB
-rw-r-----   1 mysql mysql      403 Jun  8 14:37 ib_buffer_pool
drwxr-x---   2 mysql mysql     4096 Jun  8 14:37 performance_schema
-rw-r-----   1 mysql mysql 12582912 Jun  8 14:37 ibtmp1
-rw-r-----   1 mysql mysql      474 Jun  8 14:37 xtrabackup_info
drwxr-x---   2 mysql mysql     4096 Jun  8 14:37 backup
-rw-r-----   1 mysql mysql       23 Jun  8 14:37 xtrabackup_binlog_pos_innodb
drwxr-xr-x   7 mysql mysql     4096 Jun  8 14:37 .
drwxr-x---   2 mysql mysql     4096 Jun  8 14:38 mysql
[root@mysqlmaster backup]# service mysqld start
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2018-06-08 14:38:41 EDT; 9s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1747 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1730 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1751 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1751 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jun 08 14:38:39 mysqlmaster systemd[1]: Starting MySQL Server...
Jun 08 14:38:41 mysqlmaster systemd[1]: Started MySQL Server.
[root@mysqlmaster backup]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Congrats, your MySQL database as restored to the time of the backup :)