Mysql Commercial Mysqlbackup:How to take full backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition

 How to take backup and resotre using mysqlbackup utility in mysql 8.0.36 enterprise edition

Step.1.First We need go to oracle website and download mysql enterprise ediation.

https://edelivery.oracle.com/osdc/faces/Home.jspx

step.2.Download and install mysql enterprise edition.


step.3.unzaip file.


step.4.In Our system mysql enterprise ediation mysql8.0.36 already install and we are going install only 

mysql-commercial-backup.


step.5.Finally we are going to check mysql enterprise version and mysqlbackup version also.

step..6.First we need to create one directory for store backup file.



step.7.Now we are going  taking full backup using mysqlbackup utility.

 mysqlbackup --user='username'--password --host='serverip' --backup-image=/Enterprise_Bakup/my.mbi  --backup-dir=/Enterprise_Bakup/Enterprise_Bakup-tmp backup-to-image  



After that insert password and continue.....




Finall we have taking bkup successfully and backup file are listed on mention directory /Enterprise_Bakup.



step.8.Now we going to restore  full database process.Firstly we need to rename datadir 

mv mysql mysql_today


step.9. now we need to check mysql process and stop mysql service.


step.10.Before restore backup need to verify backup

Run below command to verify backup



step.11.After that we are going to restore backup using below command

mysqlbackup --user=db_user --backup-image=/Enterprise_Bakup/my.mbi  --backup-dir=/Enterprise_Bakup/resore-tmp --datadir=/var/lib/mysql copy-back-and-apply-log

[root@MYSQL-IDC-01 /]# mysqlbackup --user=db_user --backup-image=/Enterprise_Bakup/my.mbi  --backup-dir=/Enterprise_Bakup/resore-tmp --datadir=/var/lib/mysql copy-back-and-apply-log

MySQL Enterprise Backup  Ver 8.0.36-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)

Copyright (c) 2003, 2024, Oracle and/or its affiliates.


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

--user=db_user

--backup-image=/Enterprise_Bakup/my.mbi

--backup-dir=/Enterprise_Bakup/resore-tmp

--datadir=/var/lib/mysql

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!".


240718 18:39:37 MAIN     INFO: Backup Image MEB version string: 8.0.36

240718 18:39:37 MAIN     INFO: MySQL server version is '8.0.36'

240718 18:39:37 MAIN     INFO: Backup directory created: '/Enterprise_Bakup/resore-tmp'

240718 18:39:37 MAIN  WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration.

240718 18:39:37 MAIN     INFO: MEB logfile created at /Enterprise_Bakup/resore-tmp/meta/MEB_2024-07-18.18-39-37_copy-back-and-apply-log.log


240718 18:39:37 MAIN     INFO: The backup image has no keyring.

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

                       Server Repository Options:

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

  datadir                        = /var/lib/mysql

  innodb_data_home_dir           = /var/lib/mysql

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = /var/lib/mysql

  innodb_undo_directory          = /var/lib/mysql

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = Null

  innodb_checksum_algorithm      = crc32


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

                       Backup Config Options:

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

  datadir                        = /Enterprise_Bakup/resore-tmp/datadir

  innodb_data_home_dir           = /Enterprise_Bakup/resore-tmp/datadir

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = /Enterprise_Bakup/resore-tmp/datadir

  innodb_undo_directory          = /Enterprise_Bakup/resore-tmp/datadir

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = 16384

  innodb_checksum_algorithm      = crc32


240718 18:39:37 MAIN     INFO: Creating 14 buffers each of size 16777216.

240718 18:39:37 MAIN     INFO: Copy-back-and-apply-log from image operation starts with following threads

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

240718 18:39:37 PCR1     INFO: Copying database directory: meta

240718 18:39:37 RDR1     INFO: Copying ibdata1.

240718 18:39:37 RDR1     INFO: Copying undo_002.

240718 18:39:37 RDR1     INFO: Copying undo_001.

240718 18:39:37 RDR1     INFO: Copying sys/sys_config.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql/firewall_whitelist.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql/firewall_group_allowlist.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql/firewall_users.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql/firewall_groups.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql/firewall_membership.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql/backup_progress.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql/backup_history.ibd.

240718 18:39:37 RDR1     INFO: Copying Test/emp.ibd.

240718 18:39:37 RDR1     INFO: Copying Test/dep.ibd.

240718 18:39:37 RDR1     INFO: Copying mysql.ibd.

240718 18:39:37 RDR1     INFO: log location mysql-bin

240718 18:39:37 RDR1     INFO: relay log location mysql-relay-log

240718 18:39:37 RDR1     INFO: Copying mysql-bin.000001.

240718 18:39:37 PCR6     INFO: Copying database directory: .config

240718 18:39:37 PCR6     INFO: Copying database directory: Test

240718 18:39:37 PCR6     INFO: Copying database directory: mysql

240718 18:39:37 PCR6     INFO: Copying database directory: performance_schema

240718 18:39:37 PCR5     INFO: Copying database directory: sys

240718 18:39:37 RDR1     INFO: Copying mysql-bin.000002.

240718 18:39:37 MAIN     INFO: read_backup_variables_txt_file: '/Enterprise_Bakup/resore-tmp/meta/backup_variables.txt'

240718 18:39:37 MAIN     INFO: backup variable mysql_version=8.0.36-commercial

240718 18:39:37 MAIN     INFO: MySQL server version is '8.0.36-commercial'

240718 18:39:37 MAIN     INFO: Restoring ...8.0.36-commercial version

240718 18:39:37 MAIN     INFO: backup variable meb_version=8.0.36

240718 18:39:37 MAIN     INFO: backup variable start_lsn=21035008

240718 18:39:37 MAIN     INFO: backup variable last_checkpoint=21035213

240718 18:39:37 MAIN     INFO: backup variable end_lsn=21037398

240718 18:39:37 MAIN     INFO: backup variable apply_log_done=0

240718 18:39:37 MAIN     INFO: backup variable is_incremental=0

240718 18:39:37 MAIN     INFO: backup variable is_incremental_with_redo_log_only=0

240718 18:39:37 MAIN     INFO: backup variable is_partial=0

240718 18:39:37 MAIN     INFO: backup variable is_compressed=0

240718 18:39:37 MAIN     INFO: backup variable is_skip_binlog=0

240718 18:39:37 MAIN     INFO: backup variable is_skip_relaylog=0

240718 18:39:37 MAIN     INFO: backup variable is_skip_unused_pages=0

240718 18:39:37 MAIN     INFO: backup variable is_onlyinnodb=0

240718 18:39:37 MAIN     INFO: backup variable binlog_position=mysql-bin.000002:157

240718 18:39:37 MAIN     INFO: backup variable binlog_index=mysql-bin.index

240718 18:39:37 MAIN     INFO: backup variable has_tde_tables=0

240718 18:39:37 MAIN     INFO: backup variable has_external_plugins=1

240718 18:39:37 MAIN     INFO: backup variable start_time_utc=1721306266198117

240718 18:39:37 MAIN     INFO: backup variable end_time_utc=1721306316000254

240718 18:39:37 MAIN     INFO: backup variable consistency_time_utc=1721306315920695

240718 18:39:37 MAIN     INFO: backup variable mysql_version_comment=MySQL Enterprise Server - Commercial

240718 18:39:37 MAIN     INFO: backup variable log_bin_name=mysql-bin

240718 18:39:37 MAIN     INFO: backup variable log_bin_index_name=mysql-bin

240718 18:39:37 MAIN     INFO: backup variable innodb_undo_files_count=2

240718 18:39:37 MAIN  WARNING: External plugins list found in meta/backup_content.xml. Please ensure that all plugins are installed in restored server.

240718 18:39:37 MAIN     INFO: Copy-back operation completed successfully.

240718 18:39:37 MAIN     INFO: Source Image Path = /Enterprise_Bakup/my.mbi



240718 18:39:37 MAIN     INFO: read_backup_variables_txt_file: '/Enterprise_Bakup/resore-tmp/meta/backup_variables.txt'

240718 18:39:37 MAIN     INFO: backup variable mysql_version=8.0.36-commercial

240718 18:39:37 MAIN     INFO: MySQL server version is '8.0.36-commercial'

240718 18:39:37 MAIN     INFO: Restoring ...8.0.36-commercial version

240718 18:39:37 MAIN     INFO: backup variable meb_version=8.0.36

240718 18:39:37 MAIN     INFO: backup variable start_lsn=21035008

240718 18:39:37 MAIN     INFO: backup variable last_checkpoint=21035213

240718 18:39:37 MAIN     INFO: backup variable end_lsn=21037398

240718 18:39:37 MAIN     INFO: backup variable apply_log_done=0

240718 18:39:37 MAIN     INFO: backup variable is_incremental=0

240718 18:39:37 MAIN     INFO: backup variable is_incremental_with_redo_log_only=0

240718 18:39:37 MAIN     INFO: backup variable is_partial=0

240718 18:39:37 MAIN     INFO: backup variable is_compressed=0

240718 18:39:37 MAIN     INFO: backup variable is_skip_binlog=0

240718 18:39:37 MAIN     INFO: backup variable is_skip_relaylog=0

240718 18:39:37 MAIN     INFO: backup variable is_skip_unused_pages=0

240718 18:39:37 MAIN     INFO: backup variable is_onlyinnodb=0

240718 18:39:37 MAIN     INFO: backup variable binlog_position=mysql-bin.000002:157

240718 18:39:37 MAIN     INFO: backup variable binlog_index=mysql-bin.index

240718 18:39:37 MAIN     INFO: backup variable has_tde_tables=0

240718 18:39:37 MAIN     INFO: backup variable has_external_plugins=1

240718 18:39:37 MAIN     INFO: backup variable start_time_utc=1721306266198117

240718 18:39:37 MAIN     INFO: backup variable end_time_utc=1721306316000254

240718 18:39:37 MAIN     INFO: backup variable consistency_time_utc=1721306315920695

240718 18:39:37 MAIN     INFO: backup variable mysql_version_comment=MySQL Enterprise Server - Commercial

240718 18:39:37 MAIN     INFO: backup variable log_bin_name=mysql-bin

240718 18:39:37 MAIN     INFO: backup variable log_bin_index_name=mysql-bin

240718 18:39:37 MAIN     INFO: backup variable innodb_undo_files_count=2

240718 18:39:37 MAIN     INFO: Creating 14 buffers each of size 65536.

240718 18:39:37 MAIN     INFO: Apply-log operation starts with following threads

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

240718 18:39:37 MAIN     INFO: Using up to 100 MB of memory.

240718 18:39:37 MAIN     INFO: ibbackup_logfile's creation parameters:

          start lsn 21035008, end lsn 21037398,

          start checkpoint 21035213.

240718 18:39:37 MAIN     INFO: Loading the space id : 0, space name : /var/lib/mysql/ibdata1.

240718 18:39:37 MAIN     INFO: Loading the space id 10 name '/var/lib/mysql/Test/dep.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 9 name '/var/lib/mysql/Test/emp.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 8 name '/var/lib/mysql/mysql/backup_history.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 7 name '/var/lib/mysql/mysql/backup_progress.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 3 name '/var/lib/mysql/mysql/firewall_group_allowlist.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 5 name '/var/lib/mysql/mysql/firewall_groups.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 6 name '/var/lib/mysql/mysql/firewall_membership.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 4 name '/var/lib/mysql/mysql/firewall_users.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 2 name '/var/lib/mysql/mysql/firewall_whitelist.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 1 name '/var/lib/mysql/sys/sys_config.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 4294967294 name '/var/lib/mysql/mysql.ibd'.

240718 18:39:37 MAIN     INFO: Loading the space id 4294967279 name '/var/lib/mysql/undo_001'.

240718 18:39:37 MAIN     INFO: Loading the space id 4294967278 name '/var/lib/mysql/undo_002'.

240718 18:39:37 PCR1     INFO: Starting to parse redo log at lsn = 21035067, whereas checkpoint_lsn = 21035213 and start_lsn = 21035008.

240718 18:39:37 PCR1     INFO: Doing recovery: scanned up to log sequence number 21037398.

240718 18:39:37 PCR1     INFO: Starting to apply a batch of log records to the database....

InnoDB: Progress in percent: 12 25 37 50

240718 18:39:38 PCR1     INFO: Create redo log files. target start_lsn 0 last_checkpoint 0 end_lsn 0

240718 18:39:38 PCR1     INFO: Create redo log files. source start_lsn 21035008 last_checkpoint 21035213 end_lsn 21037398

240718 18:39:38 PCR1     INFO: Updating last checkpoint to 21035213 in redo log

240718 18:39:38 PCR1     INFO: We were able to parse ibbackup_logfile up to lsn 21037398.

240718 18:39:38 PCR1     INFO: Last MySQL binlog file position 0 157, file name mysql-bin.000002

240718 18:39:38 PCR1     INFO: The first data file is '/var/lib/mysql/ibdata1'

                               and the new created log files are at '/var/lib/mysql'

240718 18:39:38 MAIN     INFO: Apply-log operation completed successfully.

240718 18:39:38 MAIN     INFO: Full Backup has been restored successfully.


mysqlbackup completed OK! with 2 warnings


 step.11. Now we are going to change  user and group  for  /var/lib/mysql/


step.12. Finally we going to restart mysql service.




step.13. Finally we are going to login mysql and check databses.



Comments

Post a Comment

Popular posts from this blog

MySQL Point in Time Recovery: How To Configure And How Does it Work?

Image
 MySQL Point in Time Recovery: How To Configure And How Does it Work? The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key components: Full backup: This serves as the foundation for recovery, providing the starting state of the database. Binary logs: These binary log files record all changes made to the database, allowing you to replay those changes to a desired point. If you don’t have a full backup or binary logs enabled, you cannot carry the point-in-time recovery. We’ll illustrate how to perform the point-in-time recovery in MySQL to recover a database to a specific point in time. Checking binar log status:- We need login to mysql server and run given below command:- show global variables like 'log_bin' ; Show output for enable mysql-bin log. Creating a new sample database & taking a full backup:- We are going to login in mysql databse and creating new database and table.given
Read more

MySQL Replication Switchover: Step-by-Step Guide

Image
 MySQL Replication Switchover: Step-by-Step Guide MySQL replication is a powerful feature that allows you to create redundant copies of your database for improved availability and scalability. In certain scenarios, it becomes necessary to switch the roles of the master and slave servers in a replication setup. This article will guide you through the process of performing a MySQL replication switchover, ensuring a smooth transition without any data loss. Let’s dive into the step-by-step instructions. I have tow node one is master node and another is slave node.Given below details:- Current Master: 192.168.70.10 Current Slave : 192.168.70.11 Verifying the Replication:- Before initiating the switchover, it’s important to verify that replication is functioning correctly on the current Slave server (192.168.70.11). Execute the following queries to ensure the replication status: mysql> show slave status\G This command displays the slave’s status, including information such as replication
Read more

Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition

Image
Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition Incremental vs Differential Backup:- The main difference between incremental and differential backups is how they save space and time by storing only changed files. However, the effectiveness of incremental backups is very different from differential backups. Before we dive into the differences between Incremental and Differential backups let’s review Full Backups.  Are you still using Full Backups? In its simplest form, a full backup is the starting point for both Incremental and Differential backup strategies. A full backup, of course, backs up all the data on a partition or disk by copying all disk sectors with data to the backup image file.  A full backup is time-consuming and requires a lot of space, it’s usually used as part of a backup plan with long intervals, like once a week or a month. During this time, if anything goes wrong, a
Read more