MYSQL::Steps to perform mysql enterprise database backup restore #Backup restore #MEB restore

Steps to perform mysql enterprise database backup restore:mysql 8.0.20

Commands used:

mysqlbackup.exe --defaults-file=E:\workspace\data\my.ini --backup-image=E:\dumps\BACKUP\FULL_20211009_123244.img --backup-dir=C:\Temp\TMPDUMP --encrypt-password="DATABASENAME_Encrypti0n@3" copy-back-and-apply-log

Note: Mention the encrypt password which referenced from the production server backup.

======================================================================

C:\Program Files\MySQL\mysql-backup-8.0.20\bin>mysqlbackup.exe --defaults-file=E:\workspace\data\my.ini --backup-image=E:\dumps\BACKUP\FULL_20211009_123244.img --backup-dir=C:\Temp\TMPDUMP --encrypt-password="DATABASENAME_Encrypti0n@3" copy-back-and-apply-log

MySQL Enterprise Backup  Ver 8.0.20-commercial for Win64 on x86_64 (MySQL Enterprise - Commercial)

Copyright (c) 2003, 2020, 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.

Starting with following command line ...

 mysqlbackup.exe --defaults-file=E:\workspace\data\my.ini

        --backup-image=E:\dumps\BACKUP\FULL_20211009_123244.img

        --backup-dir=C:\Temp\TMPDUMP --encrypt-password=xxxxxxxxxxxxxxxxx

        copy-back-and-apply-log

IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'copy-back-and-apply-log' run mysqlbackup

           prints "mysqlbackup completed OK!".

210914 14:48:25 MAIN    INFO: Starting to log actions.

210914 14:48:25 MAIN    INFO: Backup directory exists: 'C:\Temp\TMPDUMP'

210914 14:48:25 MAIN    INFO: Backup Image MEB version string: 8.0.20 [2020-03-31  10:47:11]

210914 14:48:26 MAIN    INFO: MySQL server version is '8.0.20'

210914 14:48:26 MAIN    INFO: Server is not a community server.

210914 14:48:26 MAIN    INFO: KEF source path:'C:/Temp/TMPDUMP/meta/keyring_kef'

210914 14:48:26 MAIN    INFO: KEF target path:'E:/workspace/data/Data/keyring_kef'

210914 14:48:26 MAIN    INFO: TDE Keyring service initialized.

210914 14:48:26 MAIN    INFO: MEB logfile created at C:\Temp\TMPDUMP\meta\MEB_2021-09-14.14-48-26_copy_back_img_to_datadir.log

--------------------------------------------------------------------

                       Server Repository Options:

--------------------------------------------------------------------

  datadir                        = E:\workspace\data\Data

  innodb_data_home_dir           = E:\workspace\data\Data

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = E:\workspace\data\Data

  innodb_log_files_in_group      = 2

  innodb_log_file_size           = 1073741824

  innodb_undo_directory          = E:\workspace\data\Data

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = Null

  innodb_checksum_algorithm      = crc32

--------------------------------------------------------------------

                       Backup Config Options:

--------------------------------------------------------------------

  datadir                        = C:\Temp\TMPDUMP\datadir

  innodb_data_home_dir           = C:\Temp\TMPDUMP\datadir

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = C:\Temp\TMPDUMP\datadir

  innodb_log_files_in_group      = 2

  innodb_log_file_size           = 1073741824

  innodb_undo_directory          = C:\Temp\TMPDUMP\datadir

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = 16384

  innodb_checksum_algorithm      = crc32

210914 14:48:26 MAIN    INFO: Creating 14 buffers each of size 16777216.

210914 14:48:26 MAIN    INFO: Copy-back-and-apply-log operation starts with following threads

                1 read-threads    6 process-threads    1 write-threads

210914 14:48:26 PCR1    INFO: Copying database directory: meta

210914 14:48:26 RDR1    INFO: Copying ibdata1.

210914 14:48:27 RDR1 Progress in MB: 200 400 600 800 1000

210914 14:48:29 RDR1    INFO: Copying undo_002.

210914 14:48:29 RDR1    INFO: Copying undo_001.

210914 14:48:30 RDR1    INFO: Copying sys/sys_config.ibd.

210914 14:50:54 RDR1    INFO: Copying mysql.ibd.

210914 14:50:54 RDR1    INFO: Copying DATABASENAMEDBUAT3-bin.000070.

210914 14:50:54 RDR1 Progress in MB: 39800 40000 40200 40400 40600

210914 14:50:57 RDR1    INFO: Copying DATABASENAMEDBUAT3-bin.000071.

210914 14:50:57 RDR1 Progress in MB: 40800 41000 41200 41400 41600 41800

210914 14:51:00 PCR5    INFO: Copying database directory: #clone

210914 14:51:00 PCR3    INFO: Copying database directory: DATABASENAME

210914 14:51:00 PCR4    INFO: Copying database directory: mysql

210914 14:51:00 PCR6    INFO: Copying database directory: mysql_innodb_cluster_metadata

210914 14:51:00 RDR1    INFO: Binlog Log Index:    'E:/workspace/data/Data/DATABASENAMEDBUAT3-bin.index'

210914 14:51:00 PCR2    INFO: Copying database directory: performance_schema

210914 14:51:00 RDR1    INFO: Copying DATABASENAMEDBUAT3-bin.000072.

210914 14:51:00 PCR2    INFO: Copying database directory: sys

210914 14:51:01 RDR1 Progress in MB: 42000 42200 42400

210914 14:51:03 MAIN    INFO: MySQL server version is '8.0.20-commercial'

210914 14:51:03 MAIN    INFO: Restoring ...8.0.20-commercial version

210914 14:51:03 MAIN WARNING: External plugins list found in meta/backup_content.xml. Please ensure that all plugins are installed in restored server.

210914 14:51:03 MAIN    INFO: Copy-back operation completed successfully.

210914 14:51:03 MAIN    INFO: Source Image Path = E:\dumps\BACKUP\FULL_20211009_123244.img

.

.

.

210914 14:51:03 MAIN    INFO: MySQL server version is '8.0.20-commercial'

210914 14:51:03 MAIN    INFO: Restoring ...8.0.20-commercial version

210914 14:51:03 MAIN    INFO: Creating 14 buffers each of size 65536.

210914 14:51:03 MAIN    INFO: Apply-log operation starts with following threads

                1 read-threads    1 process-threads    6 apply-threads

210914 14:51:03 MAIN    INFO: Using up to 100 MB of memory.

210914 14:51:03 MAIN    INFO: Loading keys from 'C:/Temp/TMPDUMP/meta/keyring_kef'.

210914 14:51:03 MAIN    INFO: Initialized keyring(1 keys) for apply-log.

210914 14:51:03 MAIN    INFO: ibbackup_logfile's creation parameters:

          start lsn 402744199680, end lsn 402744486896,

          start checkpoint 402744200010.

210914 14:51:12 MAIN    INFO: Loading the space id : 3391, space name : E:/workspace/data/Data/mysql/backup_progress.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 3380, space name : E:/workspace/data/Data/mysql_innodb_cluster_metadata/async_cluster_members.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 3379, space name : E:/workspace/data/Data/mysql_innodb_cluster_metadata/async_cluster_views.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 3377, space name : E:/workspace/data/Data/mysql_innodb_cluster_metadata/clusters.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 3378, space name : E:/workspace/data/Data/mysql_innodb_cluster_metadata/instances.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 3382, space name : E:/workspace/data/Data/mysql_innodb_cluster_metadata/router_rest_accounts.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 3381, space name : E:/workspace/data/Data/mysql_innodb_cluster_metadata/routers.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 1121, space name : E:/workspace/data/Data/sys/sys_config.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 4294967294, space name : E:/workspace/data/Data/mysql.ibd.

210914 14:51:12 MAIN    INFO: Loading the space id : 4294967279, space name : E:/workspace/data/Data/undo_001.

210914 14:51:12 MAIN    INFO: Loading the space id : 4294967278, space name : E:/workspace/data/Data/undo_002.

210914 14:51:12 PCR1    INFO: Starting to parse redo log at lsn = 402744199843, whereas checkpoint_lsn = 402744200010.

210914 14:51:12 PCR1    INFO: Doing recovery: scanned up to log sequence number 402744486896.

210914 14:51:12 PCR1    INFO: Starting to apply a batch of log records to the database....

InnoDB: Progress in percent: 0

210914 14:51:14 PCR1    INFO: Updating last checkpoint to 402744486194 in redo log/

210914 14:51:14 PCR1    INFO: Setting 'ib_logfile0' file size to 1073741824

210914 14:51:14 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 10%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 20%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 30%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 40%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 50%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 60%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 70%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 80%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 90%.

210914 14:51:15 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 100%.

210914 14:51:19 PCR1    INFO: Setting 'ib_logfile1' file size to 1073741824

210914 14:51:19 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 10%.

210914 14:51:19 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 20%.

210914 14:51:19 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 30%.

210914 14:51:19 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 40%.

210914 14:51:19 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 50%.

210914 14:51:19 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 60%.

210914 14:51:20 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 70%.

210914 14:51:20 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 80%.

210914 14:51:20 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 90%.

210914 14:51:20 PCR1    INFO: Setting log file  size to 1024 MB. Progress : 100%.

210914 14:51:23 PCR1    INFO: Log file header:

         format = 4

         pad1 = 0

         start lsn = 402744485888

         checkpoint lsn = 402744486194

         checksum = 482103000

         creator = MEB 8.0.20

210914 14:51:23 PCR1    INFO: We were able to parse ibbackup_logfile up to

          lsn 402744486896.

210914 14:51:23 PCR1    INFO: Last MySQL binlog file position 0 -1, file name DATABASENAMEDBUAT3-bin.000072

210914 14:51:23 PCR1    INFO: The first data file is 'E:/workspace/data/Data/ibdata1'

                              and the new created log files are at 'E:\workspace\data\Data'

210914 14:51:23 MAIN    INFO: The Keyring encryption file is restored at 'E:/workspace/data/Data/keyring_kef' from 'C:/Temp/TMPDUMP/meta/keyring_kef'.

210914 14:51:23 MAIN    INFO: Apply-log operation completed successfully.

210914 14:51:23 MAIN    INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 1 warnings

C:\Program Files\MySQL\mysql-backup-8.0.20\bin>

======================================================================

Note: Mysql database backup restore got success if we see mysqlbackup completed OK! in the end of the restore log.