Loading...

Applying the Database Dumps Manually for XtraBackup Utility

While performing a restore operation, you can apply the database dumps to the MySQL Server in the following cases:

  • If the MySQL Server is offline, clear the Recover check box, and then perform the restore operation. After the MySQL Server is online, you can apply the database dumps to the MySQL Server.
  • If you want to replicate offline data from one server to another server, clear the Recover check box, and then perform the restore operation. Later you can apply the database dumps to the MySQL Server.

Before You Begin

Stage database dumps to a destination folder. For detailed information, see Restoring Data and Transaction Logs without Recovery for XtraBackup Utility.

Procedure

Use the following steps to restore databases from the files generated from XtraBackup restore without selecting the recover option:

  1. To create database and tables using the .sql file, run the following command:

    mysql -uroot -p < /staging/xtrabackupdbs/Dbname.sql

    where, /staging/xtrabackupdbs is the destination folder.

  2. To extract the .ibd files from the compressed xbstream, run the following commands:

    mkdir /staging/xtrabackupdbs/extract

    xbstream -x -C "/staging/xtrabackupdbs/extract/" < /staging/xtrabackupdbs/Dbname

  3. To prepare tables for consistency, run the following command:

    xtrabackup --prepare --target-dir="/staging/xtrabackups/extract/Dbname"

  4. To discard tablespaces of all the tables in the database, in the MySQL console, run the following commands:

    ALTER TABLE Table1 DISCARD TABLESPACE;

    ALTER TABLE Table2 DISCARD TABLESPACE;

  5. To copy the extrated .ibd files to the data directory of the MySQL Server, and to set the permissions for the MySQL user to access the newly copied .ibd files, run the following commands:

    cp -R /staging/xtrabackupdbs/extract/Dbname /var/lib/mysql/

    chown -R mysql:mysql /var/lib/mysql//Dbname/

  6. To import table spaces from the copied .ibd files, run the following commands:

    ALTER TABLE Table1 IMPORT TABLESPACE;

    ALTER TABLE Table2 IMPORT TABLESPACE;

Last modified: 7/25/2019 6:20:41 AM