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 |
+----------+