반응형

1. 현재의 상태 확인

mysql> use test;

mysql> show tables ;

+----------------+

| Tables_in_test |

+----------------+

| tab1           |

+----------------+

mysql> select * from tab1 ;

+------+--------+

| id   | name   |

+------+--------+

|    1 | mysql  |

|    2 | mysql  |

|    3 | mysql  |

|    4 | oracle |

+------+--------+

 

2. backup

 

[root@MySQL ~]# /engine/mysql-8.0/bin/mysqlbackup --defaults-file=/etc/my.cnf8.0 --user=root --password=root1122 --socket=/data/my8.0.19/mysql.sock --backup-dir=/tmp/backup/backup_2020-06-23 --compress backup

MySQL Enterprise Backup  Ver 8.0.19-commercial for linux-glibc2.12 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 ...

 /engine/mysql-8.0/bin/mysqlbackup --defaults-file=/etc/my.cnf8.0

        --user=root --password=xxxxxxxx --socket=/data/my8.0.19/mysql.sock

        --backup-dir=/tmp/backup/backup_2020-06-23 --compress backup

 

IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup' run mysqlbackup

           prints "mysqlbackup completed OK!".

 

200623 16:04:46 MAIN    INFO: Starting to log actions.

200623 16:04:46 MAIN    INFO: No SSL options specified.

200623 16:04:46 MAIN    INFO: MySQL server version is '8.0.19-commercial'

200623 16:04:46 MAIN    INFO: MySQL server compile os version is 'linux-glibc2.12'

200623 16:04:46 MAIN    INFO: Got some server configuration information from running server.

 

200623 16:04:46 MAIN    INFO: No SSL options specified.

200623 16:04:46 MAIN    INFO: Backup directory created: '/tmp/backup/backup_2020-06-23'

200623 16:04:46 MAIN    INFO: MySQL server version_comment is 'MySQL Enterprise Server - Commercial'

200623 16:04:46 MAIN    INFO: Mysqlbackup component not installed.

200623 16:04:46 MAIN    INFO: Server is not a community server.

200623 16:04:46 MAIN    INFO: KEF target path:'/tmp/backup/backup_2020-06-23/meta/keyring_kef'

200623 16:04:46 MAIN    INFO: TDE Keyring service initialized.

200623 16:04:46 MAIN    INFO: MEB logfile created at /tmp/backup/backup_2020-06-23/meta/MEB_2020-06-23.16-04-46_compress_backup.log

 

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

                       Server Repository Options:

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

  datadir                        = /data/my8.0.19/

  innodb_data_home_dir           =

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = /data/my8.0.19/

  innodb_log_files_in_group      = 2

  innodb_log_file_size           = 50331648

  innodb_undo_directory          = /data/my8.0.19/

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = 16384

  innodb_checksum_algorithm      = crc32

 

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

                       Backup Config Options:

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

  datadir                        = /tmp/backup/backup_2020-06-23/datadir

  innodb_data_home_dir           = /tmp/backup/backup_2020-06-23/datadir

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = /tmp/backup/backup_2020-06-23/datadir

  innodb_log_files_in_group      = 2

  innodb_log_file_size           = 50331648

  innodb_undo_directory          = /tmp/backup/backup_2020-06-23/datadir

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = 16384

  innodb_checksum_algorithm      = crc32

 

200623 16:04:46 MAIN    INFO: Unique generated backup id for this is 15928958867325335

 

200623 16:04:46 MAIN    INFO: Uses LZ4 r109 for data compression.

200623 16:04:46 MAIN    INFO: Copying the server config file '/data/my8.0.19/auto.cnf'

200623 16:04:46 MAIN    INFO: Creating 20 buffers each of size 16794072.

200623 16:04:46 MAIN    INFO: Failed to start redo log archiving...

200623 16:04:46 MAIN    INFO: Found checkpoint at lsn 3694448807.

200623 16:04:46 MAIN    INFO: Starting log scan from lsn = 3694448640 at offset = 20381696 and checkpoint = 3694448807 in file /data/my8.0.19/ib_logfile1.

200623 16:04:46 MAIN    INFO: Compress Backup operation starts with following threads

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

200623 16:04:46 MAIN    INFO: Starting to copy all innodb files...

200623 16:04:46 MAIN    INFO: No SSL options specified.

200623 16:04:47 RDR1    INFO: Copying /data/my8.0.19/ibdata1.

200623 16:04:47 RDR1    INFO: Starting to copy all undo files...

200623 16:04:47 RLP1    INFO: Starting to parse redo log at lsn = 3694448777, whereas checkpoint_lsn = 3694448807.

200623 16:04:47 RDR1    INFO: Copying /data/my8.0.19/undo_002.

200623 16:04:47 RDR1    INFO: Copying /data/my8.0.19/undo_001.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sys/sys_config.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/actor.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/address.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/category.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/city.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/country.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/customer.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/film.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/film_actor.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/film_category.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/film_text.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_being_deleted.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_being_deleted_cache.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_config.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_deleted.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_deleted_cache.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_00000000000000ab_index_1.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_00000000000000ab_index_2.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_00000000000000ab_index_3.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_00000000000000ab_index_4.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_00000000000000ab_index_5.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/fts_000000000000042c_00000000000000ab_index_6.ibd.

200623 16:04:48 RDR1    INFO: Copying /data/my8.0.19/sakila/inventory.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sakila/language.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sakila/payment.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sakila/rental.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sakila/staff.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sakila/store.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/world/city.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/world/country.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/world/countrylanguage.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/employees/employees.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/employees/departments.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/employees/dept_manager.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/employees/dept_emp.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/employees/titles.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/employees/salaries.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest9.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest11.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest4.ibd.

200623 16:04:49 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest8.ibd.

200623 16:04:50 RDR1 Progress in MB: 200

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest10.ibd.

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest6.ibd.

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest3.ibd.

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest1.ibd.

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest5.ibd.

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest14.ibd.

200623 16:04:50 RDR1 Progress in MB: 400

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest7.ibd.

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest2.ibd.

200623 16:04:50 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest12.ibd.

200623 16:04:51 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest15.ibd.

200623 16:04:51 RDR1    INFO: Copying /data/my8.0.19/sysbench/sbtest13.ibd.

200623 16:04:51 RDR1 Progress in MB: 600

200623 16:04:51 RDR1    INFO: Copying /data/my8.0.19/test/tab1.ibd.

200623 16:04:51 RDR1    INFO: Copying /data/my8.0.19/mysql/backup_progress.ibd.

200623 16:04:51 RDR1    INFO: Copying /data/my8.0.19/mysql.ibd.

200623 16:04:51 RDR1    INFO: Completing the copy of innodb files.

200623 16:04:51 RDR1    INFO: Requesting a dump of the InnoDB buffer pool

200623 16:04:51 RDR1    INFO: Waiting for the dump of the InnoDB buffer pool to complete

200623 16:04:51 RDR1    INFO: The dump of the InnoDB buffer pool completed

200623 16:04:51 RDR1    INFO: Binary Log Basename: '/data/my8.0.19/binlog'

200623 16:04:51 RDR1    INFO: Binary Log Index:    '/data/my8.0.19/binlog.index'

200623 16:04:51 RDR1    INFO: Relay Channel:      'group_replication_applier'

200623 16:04:51 RDR1    INFO: Relay Log Basename: '/data/my8.0.19/MySQL-relay-bin-group_replication_applier'

200623 16:04:51 RDR1    INFO: Relay Log Index:    '/data/my8.0.19/MySQL-relay-bin-group_replication_applier.index'

200623 16:04:51 RDR1    INFO: Relay Channel:      'group_replication_recovery'

200623 16:04:51 RDR1    INFO: Relay Log Basename: '/data/my8.0.19/MySQL-relay-bin-group_replication_applier-group_replication_recovery'

200623 16:04:51 RDR1    INFO: Relay Log Index:    '/data/my8.0.19/MySQL-relay-bin-group_replication_recovery.index'

200623 16:04:51 RDR1    INFO: Starting to copy Binlog files.

200623 16:04:51 RDR1    INFO: Starting to lock instance for backup...

200623 16:04:51 RDR1    INFO: The server instance is locked for backup.

200623 16:04:51 RDR1    INFO: Requesting flush of redo log reading after LSN 3694540407.

200623 16:04:51 RDR1    INFO: Requesting flush of redo log processing after LSN 3694540407.

200623 16:04:51 RDR1    INFO: Waiting to read redo log LSN 3694540407, have 3694540307.

200623 16:04:51 RDR1    INFO: Completed flush of redo log reading after LSN 3694540407.

200623 16:04:51 RDR1    INFO: Waiting to process redo log LSN 3694540407, have 3694540307.

200623 16:04:51 RDR1    INFO: Completed flush of redo log processing after LSN 3694540407.

200623 16:04:53 RDR1    INFO: Starting to read-lock tables...

200623 16:04:53 RDR1    INFO: No tables to read-lock.

200623 16:04:53 RDR1    INFO: Opening backup source directory '/data/my8.0.19'

200623 16:04:53 RDR1    INFO: Starting to copy non-innodb files in subdirs of '/data/my8.0.19'

200623 16:04:53 RDR1    INFO: Copying the database directory 'employees'

200623 16:04:53 RDR1    INFO: Copying the database directory 'mysql'

200623 16:04:53 RDR1    INFO: Copying the database directory 'performance_schema'

200623 16:04:53 RDR1    INFO: Copying the database directory 'sakila'

200623 16:04:53 RDR1    INFO: Copying the database directory 'sys'

200623 16:04:53 RDR1    INFO: Copying the database directory 'sysbench'

200623 16:04:53 RDR1    INFO: Copying the database directory 'test'

200623 16:04:53 RDR1    INFO: Copying the database directory 'world'

200623 16:04:53 RDR1    INFO: Completing the copy of all non-innodb files.

200623 16:04:53 RDR1    INFO: Requesting consistency information...

200623 16:04:53 RDR1    INFO: Locked the consistency point for 11666 microseconds.

200623 16:04:53 RDR1    INFO: Consistency point server_uuid '995c9064-5776-11ea-93fc-080027323367'.

200623 16:04:53 RDR1    INFO: Consistency point gtid_executed ''.

200623 16:04:53 RDR1    INFO: Consistency point binary_log_file 'binlog.000014'.

200623 16:04:53 RDR1    INFO: Consistency point binary_log_position 155.

200623 16:04:53 RDR1    INFO: Consistency point InnoDB lsn 3694545284.

200623 16:04:53 RDR1    INFO: Consistency point InnoDB lsn_checkpoint 3694540417.

200623 16:04:53 RDR1    INFO: Requesting completion of redo log copy after LSN 3694545284.

200623 16:04:53 RLR1    INFO: Redo log reader waited 135 times for a total of 770.00 ms for logs to generate.

200623 16:04:53 RLW1    INFO: A copied database page was modified at 3694540211. (This is the highest lsn found on a page)

200623 16:04:53 RLW1    INFO: Scanned log up to lsn 3694545284.

200623 16:04:53 RLW1    INFO: Was able to parse the log up to lsn 3694545284.

200623 16:04:53 RDR1    INFO: Copying /data/my8.0.19/binlog.000014.

200623 16:04:53 RDR1    INFO: Completed the copy of binlog files...

200623 16:04:53 RDR1    INFO: The server instance is unlocked after 1.939 seconds.

200623 16:04:53 RDR1    INFO: Reading all global variables from the server.

200623 16:04:53 RDR1    INFO: Completed reading of all 555 global variables from the server.

200623 16:04:53 RDR1    INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.19-commercial' in '/tmp/backup/backup_2020-06-23'.

200623 16:04:53 MAIN    INFO: 634 MB of data files compressed to 326619 kbytes (compression 49.69%).

200623 16:04:53 MAIN    INFO: Compress Backup operation completed successfully.

200623 16:04:53 MAIN    INFO: Backup created in directory '/tmp/backup/backup_2020-06-23'

200623 16:04:53 MAIN    INFO: MySQL binlog position: filename binlog.000014, position 155

 

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

   Parameters Summary        

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

   Start LSN                  : 3694448640

   End LSN                    : 3694545284

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

 

mysqlbackup completed OK!

백업 시점의 MySQL 로그 확인

 

[root@MySQL meta]# cat /tmp/backup/backup_2020-06-23/meta/backup_variables.txt

#

# This file is auto generated by mysqlbackup.

#

 

[backup_variables]

apply_log_done=0

binlog_index=binlog.index

binlog_position=binlog.000014:155

consistency_time_utc=1592895893150975

end_lsn=3694545284

end_time_utc=1592895893300158

has_tde_tables=0

is_compressed=1

is_incremental=0

is_incremental_with_redo_log_only=0

is_onlyinnodb=0

is_partial=0

is_skip_binlog=0

is_skip_relaylog=0

is_skip_unused_pages=0

last_checkpoint=3694540417

log_bin_index_name=binlog

log_bin_name=binlog

meb_version=8.0.19

mysql_version=8.0.19-commercial

mysql_version_comment=MySQL Enterprise Server - Commercial

start_lsn=3694448640

start_time_utc=159289588665723

 

3. DB 새로운 트랜잭션 발생

 

Insert 수행

mysql> insert into tab1 select * from tab1 ;

mysql> insert into tab1 select * from tab1 ;

mysql> insert into tab1 select * from tab1 ;

.......

mysql> insert into tab1 select * from tab1 ;

mysql> select count(*) from tab1 ;

+----------+

| count(*) |

+----------+

|  1048576 |

+----------+

1 row in set (0.07 sec)

 

4. DB 장애 발생 (시나리오)

DB 종료

최신의 데이터까지 완전 복구를 하기 위해서 binlog.000014이후의 binary log 파일을 다른 곳으로 copy

 

현재 시나리오에서는 로그가 binlog.000014 있으므로 해당 파일만 copy

 

cp /data/my8.0.19/binlog.000014 /tmp/backup/

 

 

 

5. 백업 파일을 이용한 복구

 

BACKUP 받은 파일에 백업 받는 시점에 발생했던 트랜잭션 로그 적용

 

[root@MySQL backup]# /engine/mysql-8.0/bin/mysqlbackup --defaults-file=/etc/my.cnf8.0 --backup-dir=/tmp/backup/backup_2020-06-23 --uncompress apply-log

MySQL Enterprise Backup  Ver 8.0.19-commercial for linux-glibc2.12 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 ...

 /engine/mysql-8.0/bin/mysqlbackup --defaults-file=/etc/my.cnf8.0

        --backup-dir=/tmp/backup/backup_2020-06-23 --uncompress apply-log

 

IMPORTANT: Please check that mysqlbackup run completes successfully.

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

           prints "mysqlbackup completed OK!".

 

200623 16:17:50 MAIN    INFO: Starting to log actions.

200623 16:17:50 MAIN    INFO: MySQL server version is '8.0.19-commercial'

200623 16:17:50 MAIN    INFO: Restoring ...8.0.19-commercial version

200623 16:17:50 MAIN    INFO: Server is not a community server.

200623 16:17:50 MAIN    INFO: KEF source path:'/tmp/backup/backup_2020-06-23/meta'

200623 16:17:50 MAIN    INFO: KEF target path:'/tmp/backup/backup_2020-06-23/meta'

200623 16:17:50 MAIN    INFO: TDE Keyring service initialized.

200623 16:17:50 MAIN    INFO: MEB logfile created at /tmp/backup/backup_2020-06-23/meta/MEB_2020-06-23.16-17-50_uncompress_apply_log.log

 

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

                       Backup Config Options:

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

  datadir                        = /tmp/backup/backup_2020-06-23/datadir

  innodb_data_home_dir           = /tmp/backup/backup_2020-06-23/datadir

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = /tmp/backup/backup_2020-06-23/datadir

  innodb_log_files_in_group      = 2

  innodb_log_file_size           = 50331648

  innodb_undo_directory          = /tmp/backup/backup_2020-06-23/datadir

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = 16384

  innodb_checksum_algorithm      = crc32

 

200623 16:17:50 MAIN    INFO: Creating 20 buffers each of size 16794072.

200623 16:17:50 MAIN    INFO: Uncompress operation starts with following threads

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

200623 16:17:50 MAIN    INFO: Connection to server is not available for query 'SHOW ENGINE INNODB STATUS'

200623 16:17:50 MAIN    INFO: Cannot get redo log flush status

200623 16:17:50 MAIN    INFO: Starting to copy all innodb files...

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/ibdata1.ibz.

200623 16:17:50 RDR1    INFO: Starting to copy all undo files...

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/undo_002.uz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/undo_001.uz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/mysql.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/employees/departments.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/employees/dept_emp.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/employees/dept_manager.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/employees/employees.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/employees/salaries.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/employees/titles.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/mysql/backup_progress.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/actor.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/address.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/category.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/city.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/country.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/customer.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/film.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/film_actor.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/film_category.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/film_text.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_1.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_2.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_3.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_4.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_5.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_6.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_being_deleted.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_being_deleted_cache.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_config.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_deleted.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_deleted_cache.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/inventory.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/language.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/payment.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/rental.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/staff.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sakila/store.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sys/sys_config.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest1.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest10.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest11.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest12.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest13.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest14.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest15.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest2.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest3.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest4.ibz.

200623 16:17:50 RDR1 Progress in MB: 200

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest5.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest6.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest7.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest8.ibz.

200623 16:17:50 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest9.ibz.

200623 16:17:51 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/test/tab1.ibz.

200623 16:17:51 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/world/city.ibz.

200623 16:17:51 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/world/country.ibz.

200623 16:17:51 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/world/countrylanguage.ibz.

200623 16:17:51 RDR1    INFO: Binary Log Basename: ''

200623 16:17:51 RDR1    INFO: Binary Log Index:    '/tmp/backup/backup_2020-06-23/datadir/binlog.index'

200623 16:17:51 RDR1    INFO: Starting to copy Binlog files.

200623 16:17:51 RDR1    INFO: Uncompressing /tmp/backup/backup_2020-06-23/datadir/binlog.000014.bz.

200623 16:17:51 RDR1    INFO: Completed the copy of binlog files...

200623 16:17:51 WTR1    INFO: Compressed files removed successfully

200623 16:17:51 MAIN    INFO: Uncompress operation completed successfully.

 

 

200623 16:17:51 MAIN    INFO: Creating 14 buffers each of size 65680.

200623 16:17:51 MAIN    INFO: Apply-log operation starts with following threads

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

200623 16:17:51 MAIN    INFO: Using up to 400 MB of memory.

200623 16:17:51 MAIN    INFO: ibbackup_logfile's creation parameters:

          start lsn 3694448640, end lsn 3694545284,

          start checkpoint 3694448807.

200623 16:17:51 MAIN    INFO: Loading the space id : 0, space name : /tmp/backup/backup_2020-06-23/datadir/ibdata1.

200623 16:17:51 MAIN    INFO: Apply log: Loading the undo space id : 4294967278, space name : /tmp/backup/backup_2020-06-23/datadir/undo_002.

200623 16:17:51 MAIN    INFO: Loading the space id : 4294967278, space name : /tmp/backup/backup_2020-06-23/datadir/undo_002.

200623 16:17:51 MAIN    INFO: Apply log: Loading the undo space id : 4294967279, space name : /tmp/backup/backup_2020-06-23/datadir/undo_001.

200623 16:17:51 MAIN    INFO: Loading the space id : 4294967279, space name : /tmp/backup/backup_2020-06-23/datadir/undo_001.

200623 16:17:51 MAIN    INFO: Loading the space id : 33, space name : /tmp/backup/backup_2020-06-23/datadir/employees/departments.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 35, space name : /tmp/backup/backup_2020-06-23/datadir/employees/dept_emp.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 34, space name : /tmp/backup/backup_2020-06-23/datadir/employees/dept_manager.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 32, space name : /tmp/backup/backup_2020-06-23/datadir/employees/employees.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 37, space name : /tmp/backup/backup_2020-06-23/datadir/employees/salaries.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 36, space name : /tmp/backup/backup_2020-06-23/datadir/employees/titles.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 84, space name : /tmp/backup/backup_2020-06-23/datadir/mysql/backup_progress.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 2, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/actor.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 3, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/address.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 4, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/category.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 5, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/city.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 6, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/country.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 7, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/customer.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 8, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/film.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 9, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/film_actor.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 10, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/film_category.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 11, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/film_text.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 17, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_1.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 18, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_2.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 19, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_3.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 20, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_4.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 21, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_5.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 22, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_6.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 12, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_being_deleted.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 13, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_being_deleted_cache.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 14, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_config.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 15, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_deleted.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 16, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_deleted_cache.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 23, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/inventory.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 24, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/language.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 25, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/payment.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 26, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/rental.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 27, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/staff.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 28, space name : /tmp/backup/backup_2020-06-23/datadir/sakila/store.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 1, space name : /tmp/backup/backup_2020-06-23/datadir/sys/sys_config.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 73, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest1.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 70, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest10.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 67, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest11.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 78, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest12.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 80, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest13.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 75, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest14.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 79, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest15.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 77, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest2.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 72, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest3.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 68, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest4.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 74, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest5.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 71, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest6.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 76, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest7.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 69, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest8.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 66, space name : /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest9.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 83, space name : /tmp/backup/backup_2020-06-23/datadir/test/tab1.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 29, space name : /tmp/backup/backup_2020-06-23/datadir/world/city.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 30, space name : /tmp/backup/backup_2020-06-23/datadir/world/country.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 31, space name : /tmp/backup/backup_2020-06-23/datadir/world/countrylanguage.ibd.

200623 16:17:51 MAIN    INFO: Loading the space id : 4294967294, space name : /tmp/backup/backup_2020-06-23/datadir/mysql.ibd.

200623 16:17:51 PCR1    INFO: Starting to parse redo log at lsn = 3694448777, whereas checkpoint_lsn = 3694448807.

200623 16:17:51 PCR1    INFO: Doing recovery: scanned up to log sequence number 3694545284.

200623 16:17:51 PCR1    INFO: Starting to apply a batch of log records to the database....

InnoDB: Progress in percent: 1 2 4 5 6

200623 16:17:51 PCR1    INFO: Updating last checkpoint to 3694540417 in redo log/

200623 16:17:51 PCR1    INFO: Setting 'ib_logfile0' file size to 50331648

200623 16:17:51 PCR1    INFO: Setting 'ib_logfile1' file size to 50331648

200623 16:17:51 PCR1    INFO: Log file header:

 format = 3

 pad1 = 0

 start lsn = 3694540288

 checkpoint lsn = 3694540417

 checksum = 3688586621

 creator = MEB 8.0.19

200623 16:17:51 PCR1    INFO: We were able to parse ibbackup_logfile up to

          lsn 3694545284.

200623 16:17:51 PCR1    INFO: Last MySQL binlog file position 0 155, file name binlog.000014

200623 16:17:51 PCR1    INFO: The first data file is '/tmp/backup/backup_2020-06-23/datadir/ibdata1'

                              and the new created log files are at '/tmp/backup/backup_2020-06-23/datadir'

200623 16:17:51 MAIN    INFO: Apply-log operation completed successfully.

200623 16:17:51 MAIN    INFO: Full backup prepared for recovery successfully.

 

mysqlbackup completed OK!

datadir 디렉터리 내용 삭제

 

[root@MySQL backup]# cd /data/my8.0.19

[root@MySQL my8.0.19]# ls

auto.cnf       binlog.index  ca.pem           client-key.pem  error.log       ibdata1      ib_logfile1   mysql      performance_schema  public_key.pem  server-cert.pem  sys       test      undo_002

binlog.000014  ca-key.pem    client-cert.pem  employees       ib_buffer_pool  ib_logfile0  #innodb_temp  mysql.ibd  private_key.pem     sakila          server-key.pem   sysbench  undo_001  world

[root@MySQL my8.0.19]# rm -rf *

백업 파일 copy back

 

[root@MySQL my8.0.19]# /engine/mysql-8.0/bin/mysqlbackup --defaults-file=/etc/my.cnf8.0 --backup-dir=/tmp/backup/backup_2020-06-23 copy-back

MySQL Enterprise Backup  Ver 8.0.19-commercial for linux-glibc2.12 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 ...

 /engine/mysql-8.0/bin/mysqlbackup --defaults-file=/etc/my.cnf8.0

        --backup-dir=/tmp/backup/backup_2020-06-23 copy-back

 

IMPORTANT: Please check that mysqlbackup run completes successfully.

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

           prints "mysqlbackup completed OK!".

 

200623 16:21:24 MAIN    INFO: Starting to log actions.

200623 16:21:24 MAIN    INFO: MySQL server version is '8.0.19-commercial'

200623 16:21:24 MAIN    INFO: Restoring ...8.0.19-commercial version

200623 16:21:24 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.

200623 16:21:24 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.

200623 16:21:24 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.

200623 16:21:24 MAIN    INFO: Server is not a community server.

200623 16:21:24 MAIN    INFO: KEF source path:'/tmp/backup/backup_2020-06-23/meta'

200623 16:21:24 MAIN    INFO: KEF target path:'/data/my8.0.19'

200623 16:21:24 MAIN    INFO: TDE Keyring service initialized.

200623 16:21:24 MAIN    INFO: MEB logfile created at /tmp/backup/backup_2020-06-23/meta/MEB_2020-06-23.16-21-24_copy_back.log

 

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

                       Server Repository Options:

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

  datadir                        = /data/my8.0.19

  innodb_data_home_dir           = /data/my8.0.19

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = /data/my8.0.19

  innodb_log_files_in_group      = 2

  innodb_log_file_size           = 50331648

  innodb_undo_directory          = /data/my8.0.19

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = Null

  innodb_checksum_algorithm      = crc32

 

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

                       Backup Config Options:

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

  datadir                        = /tmp/backup/backup_2020-06-23/datadir

  innodb_data_home_dir           = /tmp/backup/backup_2020-06-23/datadir

  innodb_data_file_path          = ibdata1:12M:autoextend

  innodb_log_group_home_dir      = /tmp/backup/backup_2020-06-23/datadir

  innodb_log_files_in_group      = 2

  innodb_log_file_size           = 50331648

  innodb_undo_directory          = /tmp/backup/backup_2020-06-23/datadir

  innodb_undo_tablespaces        = 2

  innodb_buffer_pool_filename    = ib_buffer_pool

  innodb_page_size               = 16384

  innodb_checksum_algorithm      = crc32

 

200623 16:21:24 MAIN    INFO: Creating 14 buffers each of size 16777216.

200623 16:21:24 MAIN    INFO: Copy-back operation starts with following threads

1 read-threads    1 write-threads

200623 16:21:24 MAIN    INFO: Connection to server is not available for query 'SHOW ENGINE INNODB STATUS'

200623 16:21:24 MAIN    INFO: Cannot get redo log flush status

200623 16:21:24 MAIN    INFO: Starting to copy all innodb files...

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/ibdata1.

200623 16:21:24 RDR1    INFO: Starting to copy all undo files...

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/undo_002.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/undo_001.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/mysql.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/employees/departments.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/employees/dept_emp.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/employees/dept_manager.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/employees/employees.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/employees/salaries.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/employees/titles.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/mysql/backup_progress.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/actor.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/address.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/category.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/city.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/country.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/customer.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/film.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/film_actor.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/film_category.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/film_text.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_1.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_2.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_3.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_4.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_5.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_00000000000000ab_index_6.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_being_deleted.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_being_deleted_cache.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_config.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_deleted.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/fts_000000000000042c_deleted_cache.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/inventory.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/language.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/payment.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/rental.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/staff.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sakila/store.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sys/sys_config.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest1.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest10.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest11.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest12.ibd.

200623 16:21:24 RDR1 Progress in MB: 200

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest13.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest14.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest15.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest2.ibd.

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest3.ibd.

200623 16:21:24 RDR1 Progress in MB: 400

200623 16:21:24 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest4.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest5.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest6.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest7.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest8.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/sysbench/sbtest9.ibd.

200623 16:21:25 RDR1 Progress in MB: 600

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/test/tab1.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/world/city.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/world/country.ibd.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/world/countrylanguage.ibd.

200623 16:21:25 RDR1    INFO: Binary Log Basename: ''

200623 16:21:25 RDR1    INFO: Binary Log Index:    '/tmp/backup/backup_2020-06-23/datadir/binlog.index'

200623 16:21:25 RDR1    INFO: Starting to copy Binlog files.

200623 16:21:25 RDR1    INFO: Copying /tmp/backup/backup_2020-06-23/datadir/binlog.000014.

200623 16:21:25 RDR1    INFO: Copying the database directory 'employees'

200623 16:21:25 RDR1    INFO: Copying the database directory 'mysql'

200623 16:21:25 RDR1    INFO: Copying the database directory 'performance_schema'

200623 16:21:25 RDR1    INFO: Copying the database directory 'sakila'

200623 16:21:25 RDR1    INFO: Copying the database directory 'sys'

200623 16:21:25 RDR1    INFO: Copying the database directory 'sysbench'

200623 16:21:25 RDR1    INFO: Copying the database directory 'test'

200623 16:21:25 RDR1    INFO: Copying the database directory 'world'

200623 16:21:25 RDR1    INFO: Completing the copy of all non-innodb files.

200623 16:21:25 RDR1    INFO: Completed the copy of binlog files...

200623 16:21:25 RDR1    INFO: Copying the log file 'ib_logfile0'

200623 16:21:25 RDR1    INFO: Copying the log file 'ib_logfile1'

200623 16:21:25 MAIN    INFO: No Keyring file to process.

200623 16:21:25 MAIN    INFO: Copying the server config file '/tmp/backup/backup_2020-06-23/datadir/backup-auto.cnf'

200623 16:21:25 MAIN    INFO: Copy-back operation completed successfully.

200623 16:21:25 MAIN    INFO: Finished copying backup files to '/data/my8.0.19'

 

mysqlbackup completed OK! with 3 warnings

 

6. datadir 디렉터리 권한 변경

 

[root@MySQL my8.0.19]# cd /data/my8.0.19

[root@MySQL my8.0.19]# ls -al

total 168000

drwxr-xr-x 10 mysql mysql     4096 Jun 23 16:21 .

drwxr-xr-x  7 mysql mysql       86 Mar 18 11:09 ..

-rw-r-----  1 root  root        56 Jun 23 16:21 backup-auto.cnf

-rw-r--r--  1 root  root       721 Jun 23 16:21 backup_variables.txt

-rw-r-----  1 root  root       155 Jun 23 16:21 binlog.000014

-rw-r-----  1 root  root        16 Jun 23 16:21 binlog.index

drwxr-x---  2 root  root       132 Jun 23 16:21 employees

-rw-r-----  1 root  root      3853 Jun 23 16:21 ib_buffer_pool

-rw-r-----  1 root  root  12582912 Jun 23 16:21 ibdata1

-rw-r-----  1 root  root  50331648 Jun 23 16:21 ib_logfile0

-rw-r-----  1 root  root  50331648 Jun 23 16:21 ib_logfile1

drwxr-x---  2 root  root       170 Jun 23 16:21 mysql

-rw-r-----  1 root  root  25165824 Jun 23 16:21 mysql.ibd

drwxr-x---  2 root  root      8192 Jun 23 16:21 performance_schema

drwxr-x---  2 root  root      4096 Jun 23 16:21 sakila

-rw-r-----  1 root  root     17179 Jun 23 16:21 server-all.cnf

-rw-r-----  1 root  root       813 Jun 23 16:21 server-my.cnf

drwxr-x---  2 root  root        28 Jun 23 16:21 sys

drwxr-x---  2 root  root       297 Jun 23 16:21 sysbench

drwxr-x---  2 root  root        22 Jun 23 16:21 test

-rw-r-----  1 root  root  16777216 Jun 23 16:21 undo_001

-rw-r-----  1 root  root  16777216 Jun 23 16:21 undo_002

drwxr-x---  2 root  root        68 Jun 23 16:21 world

 

권한이 모두 root 되어 있으니 mysql 유저로 변경

 

chown -R mysql:mysql /data/my8.0.19

 

7. DB기동 데이터 확인

 

DB 기동 test DB 확인

mysql> use test ;

mysql> show tables ;

+----------------+

| Tables_in_test |

+----------------+

| tab1           |

+----------------+

 

mysql> select * from tab1 ;

+------+--------+

| id   | name   |

+------+--------+

|    1 | mysql  |

|    2 | mysql  |

|    3 | mysql  |

|    4 | oracle |

+------+--------+

 

백업 받는 시점의 데이터로만 복구 됨.

 

8. 완전 복구 수행

 

완전복구를 하기 위해서 copy 놓았던 binlog.000014 파일을 이용해서 복구 진행  binlog.000014에서 포지션은 155 부터만 적용하면 되고 만약에 binlog.000015 있으면 전체 내용을 복구 하면 되므로 start-position 필요 없음

SQL 추출

/engine/mysql-8.0/bin/mysqlbinlog --start-position=155 /tmp/backup/binlog.000014 > /tmp/backup/backup.sql

 

추출하면서 바로 적용

 

/engine/mysql-8.0/bin/mysqlbinlog --start-position=155 /tmp/backup/binlog.000014 | /engine/mysql-8.0/bin/mysql -h 127.0.0.1 -P 3380 -u root -p

 

 

9. 복구 데이터 확인

데이터 확인

 

mysql> select count(*) from tab1 ;

+----------+

| count(*) |

+----------+

|  1048576 |

+----------+

 

 

반응형

+ Recent posts