http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [194]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2888]
PostgreSQL [103]
기타정보 [150]
운영체제 [577]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Work Book :: MySQL

 회원가입

mysqlbackup [ backup / recovery ]
최길호 [LIST]   2020-07-02 23:39:15, 조회 : 287

https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.backup.html
https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.verify.html
https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.restore.html
https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/mysqlbackup.incremental.html

증분백업 [ 관련 정보는 mysql.backup_history 에 저장된다. ]
SELECT start_time,end_time,start_lsn,end_lsn,backup_type from mysql.backup_history WHERE last_error_code=0 order by 1;
delete from mysql.backup_history; -- 정보를 삭제하면 , 증분 백업 되던게 에러난다.
--
$ mysqlbackup -uroot -prootpwd --incremental --incremental-base=history:last_backup ....
... 에러 메세지
200703 17:19:49 MAIN   ERROR: Could not find end_lsn of last successful backup in backup_history table.
Provide base backup destination using '--incremental-base=dir:' or start_lsn using '--start-lsn'.
-- 백업 정보가 없으니 증분 백업이 안된다.
-- Full 백업후 증분 백업 가능하다.


폴더에 파일이 하나라도 있으면 안된다.
200703 17:38:55 MAIN    INFO: Backup directory: '/data/backup/full'
200703 17:38:55 MAIN   ERROR: The backup directory does already exist and is not empty. Remove or clear it and retry.
[root@node2 backup]# ls -alrt /data/backup/full
total 8
drwxr-xr-x. 7 root root 4096 Jul  3 16:46 ..
-rw-r--r--. 1 root root    0 Jul  3 17:38 0.mybk.log
drwxr-xr-x. 2 root root 4096 Jul  3 17:38 .
[root@node2 backup]#



-- Full 백업
mkdir /data/backup/full/
rm -fr /data/backup/full/*
mysqlbackup -uroot -prootpwd \
--backup-image=/data/backup/full/0.mybk \
--backup-dir=/data/backup/full/ backup-to-image

-- 증분 백업 [ 같은 곳에 백업이 안되니 삭제 하고 백업 해야 한다.]
mkdir /data/backup/inc/
rm -fr /data/backup/inc/*
mysqlbackup -uroot -prootpwd \
  --incremental --incremental-base=history:last_backup \
  --backup-dir=/data/backup/inc \
  --backup-image=/data/backup/inc/1.mybk \
   backup-to-image

du -sh /data/backup/*
748M    /data/backup/200702.mybk
748M    /data/backup/full
2.1M    /data/backup/inc




-- 백업
mysqlbackup -uroot -prootpwd \
--backup-image=/data/backup/`date +%y%m%d`.mybk \
--backup-dir=/data/backup/ backup-to-image

-- 백업파일 검증
mysqlbackup --backup-image=/data/backup/`date +%y%m%d`.mybk list-image
mysqlbackup --backup-image=/data/backup/`date +%y%m%d`.mybk validate

-- 복구 [ datadir, tmp 폴더 만들고 / 복구 / 권한 변경 / 서비스 시작 ]
mkdir /data/mysql /data/backup/tmp
mysqlbackup --datadir=/data/mysql \
  --backup-image=/data/backup/`date +%y%m%d`.mybk \
  --backup-dir=/data/backup/tmp \
    copy-back-and-apply-log
chown -R mysql:mysql /data/mysql
systemctl start mysqld



-- 이하 진행중 에러 메모
CREATE TABLE `backup_history` (
  `backup_id` bigint NOT NULL,
  `tool_name` varchar(4096) COLLATE utf8_bin NOT NULL,
  `start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `binlog_pos` bigint NOT NULL,
  `binlog_file` varchar(255) COLLATE utf8_bin NOT NULL,
  `compression_level` int NOT NULL,
  `engines` varchar(100) COLLATE utf8_bin NOT NULL,
  `innodb_data_file_path` varchar(2048) COLLATE utf8_bin NOT NULL,
  `start_lsn` bigint NOT NULL,
  `end_lsn` bigint NOT NULL,
  `backup_type` varchar(50) COLLATE utf8_bin NOT NULL,
  `backup_format` varchar(50) COLLATE utf8_bin NOT NULL,
  `mysql_data_dir` varchar(2048) COLLATE utf8_bin NOT NULL,
  `innodb_data_home_dir` varchar(2048) COLLATE utf8_bin NOT NULL,
  `innodb_log_group_home_dir` varchar(2048) COLLATE utf8_bin NOT NULL,
  `innodb_log_files_in_group` varchar(100) COLLATE utf8_bin NOT NULL,
  `innodb_log_file_size` varchar(100) COLLATE utf8_bin NOT NULL,
  `backup_destination` varchar(4096) COLLATE utf8_bin NOT NULL,
  `lock_time` double(7,3) NOT NULL,
  `exit_state` varchar(10) COLLATE utf8_bin NOT NULL,
  `last_error` varchar(4096) COLLATE utf8_bin NOT NULL,
  `last_error_code` int NOT NULL,
  `start_time_utc` bigint NOT NULL,
  `end_time_utc` bigint NOT NULL,
  `consistency_time_utc` bigint NOT NULL,
  `meb_version` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '0.0.0',
  `server_uuid` varchar(36) COLLATE utf8_bin NOT NULL,
  `binlog_master_key_seq_num` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`backup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

systemctl stop mysqld
mv /data/mysql /data/mysql.old
mkdir /data/mysql
chown mysql:mysql /data/mysql

mysqlbackup --datadir=/data/mysql \
  --backup-image=/data/backup/`date +%y%m%d`.mybk \
  --backup-dir=/data/backup/ \
    copy-back-and-apply-log

200702 23:22:02 MAIN    INFO: Starting to log actions.
200702 23:22:02 MAIN    INFO: Backup directory: '/data/backup/'
200702 23:22:02 MAIN   ERROR: The backup directory does already exist and is not empty. Remove or clear it and retry.


mkdir /data/backup/tmp
  --backup-dir=/data/backup/tmp \

....
200702 23:24:26 PCR1    INFO: We were able to parse ibbackup_logfile up to
          lsn 523000947.
200702 23:24:26 PCR1    INFO: Last MySQL binlog file position 0 504347733, file name binlog.000005
200702 23:24:26 PCR1    INFO: The first data file is '/data/mysql/ibdata1'
                              and the new created log files are at '/data/mysql'
200702 23:24:26 MAIN    INFO: Apply-log operation completed successfully.
200702 23:24:26 MAIN    INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 3 warnings
[root@node2 mysql]#


[root@node2 mysql]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@node2 mysql]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Thu 2020-07-02 23:25:34 KST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 13633 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 13601 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 13633 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"
    Error: 13 (Permission denied)

Jul 02 23:25:32 node2 systemd[1]: Starting MySQL Server...
Jul 02 23:25:34 node2 systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jul 02 23:25:34 node2 systemd[1]: Failed to start MySQL Server.
Jul 02 23:25:34 node2 systemd[1]: Unit mysqld.service entered failed state.
Jul 02 23:25:34 node2 systemd[1]: mysqld.service failed.
[root@node2 mysql]#


grep log /etc/my.cnf
cat /var/log/mysqld.log
2020-07-02T14:18:54.622150Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-commercial)  MySQL Enterprise Server - Commercial.
mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
2020-07-02T14:25:34.422002Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20-commercial) starting as process 13633
2020-07-02T14:25:34.427622Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-02T14:25:34.427954Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-commercial)  MySQL Enterprise Server - Commercial.


[root@node2 mysql]# find /data/mysql -name binlog.index
/data/mysql/binlog.index
[root@node2 mysql]# ls -al /data/mysql/binlog.index
-rw-r-----. 1 root root 80 Jul  2 23:24 /data/mysql/binlog.index
[root@node2 mysql]# ls -al /data/mysql
total 654416
drwxr-xr-x. 6 mysql mysql      4096 Jul  2 23:30 .
drwxr-xr-x. 7 root  root       4096 Jul  2 23:19 ..
-rw-r-----. 1 root  root         56 Jul  2 23:24 backup-auto.cnf
-rw-r--r--. 1 root  root        741 Jul  2 23:24 backup_variables.txt
-rw-r-----. 1 root  root       1771 Jul  2 23:24 binlog.000001
-rw-r-----. 1 root  root       1362 Jul  2 23:24 binlog.000002
-rw-r-----. 1 root  root       3557 Jul  2 23:24 binlog.000003
-rw-r-----. 1 root  root        179 Jul  2 23:24 binlog.000004
-rw-r-----. 1 root  root  504347733 Jul  2 23:24 binlog.000005
-rw-r-----. 1 root  root         80 Jul  2 23:24 binlog.index
-rw-r-----. 1 root  root      17213 Jul  2 23:24 ib_buffer_pool
-rw-r-----. 1 root  root   12582912 Jul  2 23:24 ibdata1
-rw-r-----. 1 root  root   50331648 Jul  2 23:24 ib_logfile0
-rw-r-----. 1 root  root   50331648 Jul  2 23:24 ib_logfile1
drwxr-x---. 2 root  root       4096 Jul  2 23:24 mysql
-rw-r-----. 1 root  root   27262976 Jul  2 23:24 mysql.ibd
drwxr-x---. 2 root  root       4096 Jul  2 23:24 performance_schema
-rw-r-----. 1 root  root      17791 Jul  2 23:24 server-all.cnf
-rw-r-----. 1 root  root        856 Jul  2 23:24 server-my.cnf
drwxr-x---. 2 root  root       4096 Jul  2 23:24 sys
-rw-r-----. 1 root  root   12582912 Jul  2 23:24 undo_001
-rw-r-----. 1 root  root   12582912 Jul  2 23:24 undo_002
drwxr-x---. 2 root  root       4096 Jul  2 23:24 webdb
[root@node2 mysql]# chown -R mysql:mysql /data/mysql
[root@node2 mysql]# systemctl start mysqld
[root@node2 mysql]#


  LIST

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
2008/12/15
MySQL이 안좋은건가? 장애도 쉽게 나고,...
by 최길호
최근 게시물
06/29
[ORACLE]
DDE: Problem Key 'ORA 600 [133....
by 최길호
06/25
[ORACLE]
Replication may not be valid i....
by 최길호
06/22
[운영체제]
CentOS7 홈페이지 Migration 메모.
by 최길호