Restore MySQL 5.7 Backup with MySQL Enterprise Backup MEB 4.1.0

By | | , ,

In this article, we are going to restore MySQL Server Backup with MySQL Enterprise Backup MEB on Oracle Linux 7.

1. Set UP the envirement:

The steps to install MySQL Server and the tool MySQL Enterprise Backup is defined in this my last article.

Upgrade MySQL Server 5.5 to 5.7 on Oracle Linux 7
Online full backup of MySQL server 5.7 using the MySQL Enterprise Backup

2. Restore MySQL Server Backup

We have the same MySQL Server version as the Version of the Backup server.

a. Step 1: I have cleaned the data directory:

# rm -rf /var/lib/mysql/*
# ll /var/lib/mysql
total 0

b. Step 2: restore The backup

Our MySQL Enterprise Backup is located under /backup-mysql/09-05-2019.
To restore The backup, we are going to use the option copy-back.

# /opt/mysql/meb-4.1/bin/mysqlbackup --backup-dir=/backup-mysql/09-05-2019 --datadir=/var/lib/mysql copy-back

MySQL Enterprise Backup version 4.1.0 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2017/03/01] 
 Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved.
190510 01:14:56 MAIN    INFO: A thread created with Id '140541057181504' 
 190510 01:14:56 MAIN    INFO: Starting with following command line …
  /opt/mysql/meb-4.1/bin/mysqlbackup 
         --backup-dir=/backup-mysql/09-05-2019 --datadir=/var/lib/mysql copy-back 
190510 01:14:56 MAIN    INFO: 
 IMPORTANT: Please check that mysqlbackup run completes successfully.
            At the end of a successful 'copy-back' run mysqlbackup
            prints "mysqlbackup completed OK!". 
190510 01:14:56 MAIN    INFO: MySQL server version is '5.7.26-log'
 190510 01:14:56 MAIN    INFO: Restoring …5.7.26-log version
 190510 01:14:56 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.
 190510 01:14:56 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.
 190510 01:14:56 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
 190510 01:14:56 MAIN    INFO: MEB logfile created at /backup-mysql/09-05-2019/meta/MEB_2019-05-10.01-14-56_copy_back.log 
                       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_log_files_in_group      = 2
   innodb_log_file_size           = 50331648
   innodb_buffer_pool_filename    = ib_buffer_pool
   innodb_page_size               = Null
   innodb_checksum_algorithm      = crc32
                       Backup Config Options:
datadir                        = /backup-mysql/09-05-2019/datadir
   innodb_data_home_dir           = /backup-mysql/09-05-2019/datadir
   innodb_data_file_path          = ibdata1:12M:autoextend
   innodb_log_group_home_dir      = /backup-mysql/09-05-2019/datadir
   innodb_log_files_in_group      = 2
   innodb_log_file_size           = 50331648
   innodb_buffer_pool_filename    = ib_buffer_pool
   innodb_page_size               = 16384
   innodb_checksum_algorithm      = crc32
190510 01:14:56 MAIN    INFO: Creating 14 buffers each of size 16777216.
 190510 01:14:56 MAIN    INFO: Copy-back operation starts with following threads
         1 read-threads    1 write-threads
 190510 01:14:56 MAIN    INFO: Starting to copy all innodb files…
 190510 01:14:56 RDR1    INFO: A thread created with Id '140540692936448' 
 190510 01:14:56 WTR1    INFO: A thread created with Id '140540684543744' 
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/ibdata1.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/engine_cost.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/gtid_executed.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/help_category.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/help_keyword.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/help_relation.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/help_topic.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/innodb_index_stats.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/innodb_table_stats.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/plugin.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/server_cost.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/servers.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/slave_master_info.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/slave_relay_log_info.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/slave_worker_info.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/time_zone.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/time_zone_leap_second.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/time_zone_name.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/time_zone_transition.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql/time_zone_transition_type.ibd.
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/sys/sys_config.ibd.
 190510 01:14:56 RDR1    INFO: Starting to copy Binlog files…
 190510 01:14:56 RDR1    INFO: Copying /backup-mysql/09-05-2019/datadir/mysql-bin.000001.
 190510 01:14:56 RDR1    INFO: Completed the copy of binlog files…
 190510 01:14:56 RDR1    INFO: Copying the database directory 'mysql'
 190510 01:14:56 RDR1    INFO: Copying the database directory 'performance_schema'
 190510 01:14:56 RDR1    INFO: Copying the database directory 'sys'
 190510 01:14:56 RDR1    INFO: Copying the database directory 'test'
 190510 01:14:56 RDR1    INFO: Copying the database directory 'world_innodb'
 190510 01:14:56 RDR1    INFO: Completing the copy of all non-innodb files.
 190510 01:14:56 RDR1    INFO: Copying the log file 'ib_logfile0'
 190510 01:14:56 RDR1    INFO: Copying the log file 'ib_logfile1'
 190510 01:14:57 MAIN    INFO: MySQL server version is '5.7.26-log'
 190510 01:14:57 MAIN    INFO: MySQL server compile os version is 'Linux'
 190510 01:14:57 MAIN    INFO: Writing config file for server '5.7.26-log'.
 190510 01:14:57 MAIN    INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql
 190510 01:14:57 MAIN    INFO: Variable 'datadir'='/var/lib/mysql/'->'/var/lib/mysql' option 'datadir' type 0
 190510 01:14:57 MAIN    INFO: Variable 'datadir'='/var/lib/mysql/'->'/var/lib/mysql' option 'datadir' type 0
 190510 01:14:57 MAIN    INFO: Variable 'innodb_buffer_pool_filename'='ib_buffer_pool'->'ib_buffer_pool' option 'innodb_buffer_pool_filename' type 9
 190510 01:14:57 MAIN    INFO: Variable 'innodb_buffer_pool_filename'='ib_buffer_pool'->'ib_buffer_pool' option 'innodb_buffer_pool_filename' type 9
 190510 01:14:57 MAIN    INFO: Variable 'innodb_checksum_algorithm'='crc32'->'crc32' option 'innodb_checksum_algorithm' type 11
 190510 01:14:57 MAIN    INFO: Variable 'innodb_checksum_algorithm'='crc32'->'crc32' option 'innodb_checksum_algorithm' type 11
 190510 01:14:57 MAIN    INFO: Variable 'innodb_checksums'='ON'->'ON' option 'innodb_checksums' type 12
 190510 01:14:57 MAIN    INFO: Variable 'innodb_checksums'='ON'->'ON' option 'innodb_checksums' type 12
 190510 01:14:57 MAIN    INFO: Variable 'innodb_data_file_path'='ibdata1:12M:autoextend'->'ibdata1:12M:autoextend' option 'innodb_data_file_path' type 2
 190510 01:14:57 MAIN    INFO: Variable 'innodb_data_file_path'='ibdata1:12M:autoextend'->'ibdata1:12M:autoextend' option 'innodb_data_file_path' type 2
 190510 01:14:57 MAIN    INFO: Variable 'innodb_data_home_dir'=''->'/var/lib/mysql' option 'innodb_data_home_dir' type 1
 190510 01:14:57 MAIN    INFO: Variable 'innodb_log_file_size'='50331648'->'50331648' option 'innodb_log_file_size' type 5
 190510 01:14:57 MAIN    INFO: Variable 'innodb_log_file_size'='50331648'->'50331648' option 'innodb_log_file_size' type 5
 190510 01:14:57 MAIN    INFO: Variable 'innodb_log_files_in_group'='2'->'2' option 'innodb_log_files_in_group' type 4
 190510 01:14:57 MAIN    INFO: Variable 'innodb_log_files_in_group'='2'->'2' option 'innodb_log_files_in_group' type 4
 190510 01:14:57 MAIN    INFO: Variable 'innodb_log_group_home_dir'='./'->'/var/lib/mysql' option 'innodb_log_group_home_dir' type 3
 190510 01:14:57 MAIN    INFO: Variable 'innodb_log_group_home_dir'='./'->'/var/lib/mysql' option 'innodb_log_group_home_dir' type 3
 190510 01:14:57 MAIN    INFO: Variable 'innodb_page_size'='16384'->'16384' option 'innodb_page_size' type 10
 190510 01:14:57 MAIN    INFO: Variable 'innodb_page_size'='16384'->'16384' option 'innodb_page_size' type 10
 190510 01:14:57 MAIN    INFO: Variable 'innodb_undo_directory'='./'->'/var/lib/mysql' option 'innodb_undo_directory' type 6
 190510 01:14:57 MAIN    INFO: Variable 'innodb_undo_directory'='./'->'/var/lib/mysql' option 'innodb_undo_directory' type 6
 190510 01:14:57 MAIN    INFO: Variable 'innodb_undo_logs'='128'->'128' option 'innodb_undo_logs' type 8
 190510 01:14:57 MAIN    INFO: Variable 'innodb_undo_logs'='128'->'128' option 'innodb_undo_logs' type 8
 190510 01:14:57 MAIN    INFO: Variable 'innodb_undo_tablespaces'='0'->'0' option 'innodb_undo_tablespaces' type 7
 190510 01:14:57 MAIN    INFO: Variable 'innodb_undo_tablespaces'='0'->'0' option 'innodb_undo_tablespaces' type 7
 190510 01:14:57 MAIN    INFO: Copy-back operation completed successfully.
 190510 01:14:57 MAIN    INFO: Finished copying backup files to '/var/lib/mysql'
mysqlbackup completed OK! with 3 warnings

c. Step 3: Analyse the warnings:

Note we have 3 warning:
In the restore log, we find:

  • Warning 1:
190510 01:14:56 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.
  • Warning 2:
190510 01:14:56 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.
  • Warning 3:
190510 01:14:56 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.

Note: It is the dafault configuration as a note to check Il you changed the configuration.

d. Step 4: Change the Owner of MySQL directories and files

ll /var/lib/mysql
total 116804
 -rw-r--r-- 1 root root      468 May 10 01:14 backup_variables.txt
 -rw-r--r-- 1 root root      572 May 10 01:14 ib_buffer_pool
 -rw-r--r-- 1 root root 18874368 May 10 01:14 ibdata1
 -rw-r--r-- 1 root root 50331648 May 10 01:14 ib_logfile0
 -rw-r--r-- 1 root root 50331648 May 10 01:14 ib_logfile1
 drwxr-x--- 2 root root     4096 May 10 01:14 mysql
 -rw-r--r-- 1 root root      154 May 10 01:14 mysql-bin.000001
 -rw-r--r-- 1 root root       19 May 10 01:14 mysql-bin.index
 drwxr-x--- 2 root root     8192 May 10 01:14 performance_schema
 -rw-r--r-- 1 root root    15341 May 10 01:14 server-all.cnf
 -rw-r--r-- 1 root root     4257 May 10 01:14 server-my.cnf
 drwxr-x--- 2 root root     8192 May 10 01:14 sys
 drwxr-x--- 2 root root        6 May 10 01:14 test
 drwxr-x--- 2 root root       82 May 10 01:14 world_innodb
# chown -R mysql:mysql /var/lib/mysql

e. Step 5: Start MySQL Server

# systemctl start mysqld

3. Conclusion:

MySQL Enterprise Backup can make an online Backup and Restore with redo bin log anly also.

Bookmark the permalink.
Loading Facebook Comments ...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.