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.
No comments:
Post a Comment