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.
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.
Google+
Facebook
