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)