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
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 :)