Loading...

Applying the Logs Manually for XtraBackup Utility

While performing a restore operation, you can apply the logs 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 logs 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 logs to the MySQL Server.

Before You Begin

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

Procedure

Use the following steps to apply logs to the MySQL Server:

  1. Identify the master log file number and master log position from the database dump file saved at the staging location:

    Example: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000104', MASTER_LOG_POS=107

  2. To apply the master log file to the MySQL Server, run the following commands:

    mysqlbinlog <master logfile> --stop-datetime=<PIT> -d <databasename> --result-file=<result file name> --start-position=<master log position>

    mysql -u <username> -p -D <database name> -e "source <result file name>"

    Example:

    mysqlbinlog mysql-bin.000104 --stop-datetime="2015-10-6 15:36:0" -d mydatabase2 --result-file=new.sql --start-position=107

    mysql -u root -p -D mydatabase2 -e "source new.sql"

  3. After applying the master log file, you can apply a log file with the next sequential log file number.

    For example, if the master log file number is mysql-bin.000104, the next log file that you can apply should have mysql-bin.000105 as its log file number. Then apply the second log file with log file number mysql-bin.000106, and so on.

    Run the following commands for each log file that you want to apply to the MySQL Server:

    mysqlbinlog <logfile> --stop-datetime=<PIT> -d <databasename> --result-file=<result file name>

    mysql -u <username> -p -D <database name> -e "source <result file name>"

    Example for applying log file number mysql-bin.000105:

    mysqlbinlog mysql-bin.000105 --stop-datetime="2015-10-6 15:36:0" -d mydatabase2 --result-file=new.sql

    mysql -u root -p -D mydatabase2 -e "source new.sql"

    Example for applying log file number mysql-bin.000106:

    mysqlbinlog mysql-bin.000106 --stop-datetime="2015-10-6 15:36:0" -d mydatabase2 --result-file=new.sql

    mysql -u root -p -D mydatabase2 -e "source new.sql"

    Similarly, you can run these commands sequentially for each log file that you want to apply to the MySQL Server.

Last modified: 7/4/2019 5:20:27 AM