[mySQL5.5] 18장. Backup & Recvoery

n  Backup 종류
- Cold/Hot /Warm 백업: online/offline여부

- Logical /Physical : text/binary

- Full / Incremental  : 모든 rows가 백업됨/ 변경된 내용만 백업됨

mysqlbackup : raw(physical) backup
- innodb : ibdata*, *.ibd,ib_logfile*

- mysql디렉토리 : *.MYD,MYI,*.FRM들

 

n  mysqlbackup :MySQL Enterprise Backup
- backup-and-apply-log :  백업
- backup-to-image : 한 개의 image파일로 백업

- copy-back : restore

- image-to-backup-dir : image파일에서 백업 디렉토리로

1. raw 백업을 수행합니다.

[root@EDYDR51P0 bin]# ./mysqlbackup -uroot -poracle --datadir=/var/lib/mysql --backup_dir=/tmp/backup/meb1 backup-and-apply-log

MySQL Enterprise Backup version 3.6.0 [2011/07/01]

Copyright (c) 2003, 2011, Oracle and/or its affiliates. All Rights Reserved.

 

INFO: Starting with following command line ...

 ./mysqlbackup -uroot -poracle --datadir=/var/lib/mysql

        --backup_dir=/tmp/backup/meb1 backup-and-apply-log

 

INFO: Got some server configuration information from running server.

 

IMPORTANT: Please check that mysqlbackup run completes successfully.

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

            prints "mysqlbackup completed OK!".

 

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

                       Server Repository Options:

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

  datadir                          =  /var/lib/mysql/

  innodb_data_home_dir             =

  innodb_data_file_path            =  ibdata1:10M:autoextend

  innodb_log_group_home_dir        =  /var/lib/mysql/

  innodb_log_files_in_group        =  2

  innodb_log_file_size             =  5242880

 

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

                       Backup Config Options:

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

  datadir                          =  /tmp/backup/meb1/datadir

  innodb_data_home_dir             =  /tmp/backup/meb1/datadir

  innodb_data_file_path            =  ibdata1:10M:autoextend

  innodb_log_group_home_dir        =  /tmp/backup/meb1/datadir

  innodb_log_files_in_group        =  2

  innodb_log_file_size             =  5242880

 

 mysqlbackup: INFO: Unique generated backup id for this is 13239809813589760

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.

 mysqlbackup: INFO: System tablespace file format is Antelope.

 mysqlbackup: INFO: Found checkpoint at lsn 444387571.

 mysqlbackup: INFO: Starting log scan from lsn 444387328.

111215 20:29:41 mysqlbackup: INFO: Copying log...

111215 20:29:41 mysqlbackup: INFO: Log copied, lsn 444387571.

          We wait 1 second before starting copying the data files...

111215 20:29:42 mysqlbackup: INFO: Copying /var/lib/mysql/ibdata1 (Antelope file format).

mysqlbackup: Progress in MB: 200

111215 20:29:45 mysqlbackup: INFO: Copying /var/lib/mysql/world_innodb/City_part.ibd (Antelope file format).

111215 20:29:45 mysqlbackup: INFO: Copying /var/lib/mysql/test/my_city.ibd (Antelope file format).

 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.

111215 20:29:45 mysqlbackup: INFO: Starting to lock all the tables....

111215 20:29:45 mysqlbackup: INFO: All tables are locked and flushed to disk

 mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'

111215 20:29:45 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/var/lib/mysql/'

 mysqlbackup: INFO: Backing up the database directory 'mysql'

 mysqlbackup: INFO: Backing up the database directory 'performance_schema'

 mysqlbackup: INFO: Backing up the database directory 'test'

 mysqlbackup: INFO: Backing up the database directory 'world_innodb'

 mysqlbackup: INFO: Copying innodb data and logs during final stage ...

 mysqlbackup: INFO: A copied database page was modified at 444387571.

          (This is the highest lsn found on page)

          Scanned log up to lsn 444389480.

          Was able to parse the log up to lsn 444389480.

          Maximum page number for a log record 7953

111215 20:29:45 mysqlbackup: INFO: All tables unlocked

 mysqlbackup: INFO: All MySQL tables were locked for 0.000 seconds

111215 20:29:45 mysqlbackup: INFO: Full backup completed!

 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000002, position 7358

 mysqlbackup: INFO: Backup created in directory '/tmp/backup/meb1'

111215 20:29:45 mysqlbackup: INFO:  ibbackup_logfile's creation parameters:

          start lsn 444387328, end lsn 444389480,

          start checkpoint 444387571.

InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 5242880

Setting log file size to 0 5242880

 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to

          lsn 444389480.

ibbackup: Last MySQL binlog file position 0 7358, file name ./mysql-bin.000002

 mysqlbackup: INFO: The first data file is '/tmp/backup/meb1/datadir/ibdata1'

          and the new created log files are at '/tmp/backup/meb1/datadir/'

 mysqlbackup: INFO: System tablespace file format is Antelope.

111215 20:29:45 mysqlbackup: INFO: Full backup prepared for recovery successfully!

 

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

   Parameters Summary

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

   Start LSN                  : 444387328

   End LSN                    : 444389480

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

 

mysqlbackup completed OK!

 

백업한 내용을 확인합니다.

[root@EDYDR51P0 meb1]# ls

backup-my.cnf  datadir  meta

 

n  mysqlbackup : Restore from raw backup

 

1. 먼저 파일을 삭제합니다.
#cd /var/lib/mysql

#rm –rf ibdata1

#rm –rf world_innodb

 

2. 서버를shutdown합니다.

#/etc/init.d/mysql stop

 

3. restore합니다.

##필요에 따라  my.cnf에 innodb_log_files_in_group=2

추가

#cd /opt/mysql/meb-3.5/bin

# ./mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/tmp/backup/meb1 --datadir=/var/lib/mysql copy-back

MySQL Enterprise Backup version 3.6.0 [2011/07/01]

Copyright (c) 2003, 2011, Oracle and/or its affiliates. All Rights Reserved.

 

INFO: Starting with following command line ...

 ./mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/tmp/backup/meb1

        --datadir=/var/lib/mysql copy-back

 

IMPORTANT: Please check that mysqlbackup run completes successfully.

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

            prints "mysqlbackup completed OK!".

 

 mysqlbackup: INFO: Server repository configuration:

  datadir                          =  /var/lib/mysql

  innodb_data_home_dir             =  /var/lib/mysql

  innodb_data_file_path            =  ibdata1:10M:autoextend

  innodb_log_group_home_dir        =  /var/lib/mysql

  innodb_log_files_in_group        =  2

  innodb_log_file_size             =  5M

 

 mysqlbackup: INFO: Backup repository configuration:

  datadir                          =  /tmp/backup/meb1/datadir

  innodb_data_home_dir             =  /tmp/backup/meb1/datadir

  innodb_data_file_path            =  ibdata1:10M:autoextend

  innodb_log_group_home_dir        =  /tmp/backup/meb1/datadir

  innodb_log_files_in_group        =  2

  innodb_log_file_size             =  5242880

 

 mysqlbackup: INFO: Starting to copy back files

 mysqlbackup: INFO: in '/tmp/backup/meb1/datadir' directory

 mysqlbackup: INFO: back to original data directory '/var/lib/mysql'

 mysqlbackup: INFO: Copying back directory '/tmp/backup/meb1/datadir/mysql'

 mysqlbackup: INFO: Copying back directory '/tmp/backup/meb1/datadir/performance_schema'

 mysqlbackup: INFO: Copying back directory '/tmp/backup/meb1/datadir/test'

 mysqlbackup: INFO: Copying back directory '/tmp/backup/meb1/datadir/world_innodb'

 mysqlbackup: INFO: Starting to copy back InnoDB tables and indexes

in '/tmp/backup/meb1' back to original InnoDB data directory: /var/lib/mysql

 mysqlbackup: INFO: Copying back file '/tmp/backup/meb1/datadir/ibdata1'

 mysqlbackup: INFO: Starting to copy back InnoDB log files

in '/tmp/backup/meb1/datadir' back to original InnoDB log directory '/var/lib/mysql'

 mysqlbackup: INFO: Copying back file '/tmp/backup/meb1/datadir/ib_logfile0'

 mysqlbackup: INFO: Copying back file '/tmp/backup/meb1/datadir/ib_logfile1'

 mysqlbackup: INFO: Finished copying backup files.

4. 권한을 mysql:mysql로 변경합니다.

# chown -fR mysql:mysql /var/lib/mysql/

 

5. 서버를 시작합니다.

# /etc/init.d/mysql start

 

 

n  backup-to-image :

 

[root@EDYDR50P0 bin]# ./mysqlbackup -uroot -poracle --backup-image=/tmp/backup/image.mbi --backup-dir=/tmp/backup/backup_image backup-to-image

MySQL Enterprise Backup version 3.6.0 [2011/07/01]

Copyright (c) 2003, 2011, Oracle and/or its affiliates. All Rights Reserved.

 

INFO: Starting with following command line ...

 ./mysqlbackup -uroot -poracle --backup-image=/tmp/backup/image.mbi

        --backup-dir=/tmp/backup/backup_image backup-to-image

 

INFO: Got some server configuration information from running server.

 

IMPORTANT: Please check that mysqlbackup run completes successfully.

            At the end of a successful 'backup-to-image' run mysqlbackup

            prints "mysqlbackup completed OK!".

 

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

                       Server Repository Options:

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

  datadir                          =  /var/lib/mysql/

  innodb_data_home_dir             =

  innodb_data_file_path            =  ibdata1:10M:autoextend

  innodb_log_group_home_dir        =  /var/lib/mysql

  innodb_log_files_in_group        =  2

  innodb_log_file_size             =  5242880

 

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

                       Backup Config Options:

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

  datadir                          =  /tmp/backup/backup_image/datadir

  innodb_data_home_dir             =  /tmp/backup/backup_image/datadir

  innodb_data_file_path            =  ibdata1:10M:autoextend

  innodb_log_group_home_dir        =  /tmp/backup/backup_image/datadir

  innodb_log_files_in_group        =  2

  innodb_log_file_size             =  5242880

 

Backup Image Path= /tmp/backup/image.mbi

 mysqlbackup: INFO: Unique generated backup id for this is 13354724267317450

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.

 mysqlbackup: INFO: System tablespace file format is Antelope.

 mysqlbackup: INFO: Found checkpoint at lsn 4129317.

 mysqlbackup: INFO: Starting log scan from lsn 4129280.

120426 20:33:46 mysqlbackup: INFO: Copying log...

120426 20:33:46 mysqlbackup: INFO: Log copied, lsn 4129317.

          We wait 1 second before starting copying the data files...

120426 20:33:47 mysqlbackup: INFO: Copying /var/lib/mysql/ibdata1 (Antelope file format).

 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.

120426 20:33:47 mysqlbackup: INFO: Starting to lock all the tables....

120426 20:33:47 mysqlbackup: INFO: All tables are locked and flushed to disk

 mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'

120426 20:33:47 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/var/lib/mysql/'

 mysqlbackup: INFO: Backing up the database directory 'mysql'

 mysqlbackup: INFO: Backing up the database directory 'performance_schema'

 mysqlbackup: INFO: Backing up the database directory 'test'

 mysqlbackup: INFO: Backing up the database directory 'world_innodb'

 mysqlbackup: INFO: Copying innodb data and logs during final stage ...

 mysqlbackup: INFO: A copied database page was modified at 4129317.

          (This is the highest lsn found on page)

          Scanned log up to lsn 4131155.

          Was able to parse the log up to lsn 4131155.

          Maximum page number for a log record 524

120426 20:33:47 mysqlbackup: INFO: All tables unlocked

 mysqlbackup: INFO: All MySQL tables were locked for 0.000 seconds

120426 20:33:47 mysqlbackup: INFO: Full backup completed!

 mysqlbackup: WARNING: backup-image already closed

 mysqlbackup: INFO: Backup image created successfully.:

           Image Path: '/tmp/backup/image.mbi'

 

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

   Parameters Summary

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

   Start LSN                  : 4129280

   End LSN                    : 4131155

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

 

mysqlbackup completed OK!

n  image-to-backup-dir

 

[root@EDYDR50P0 bin]# ./mysqlbackup -uroot -poracle --backup-image=/tmp/backup/image.mbi --backup-dir=/tmp/backup/meb1 image-to-backup-dir

MySQL Enterprise Backup version 3.6.0 [2011/07/01]

Copyright (c) 2003, 2011, Oracle and/or its affiliates. All Rights Reserved.

 

INFO: Starting with following command line ...

 ./mysqlbackup -uroot -poracle --backup-image=/tmp/backup/image.mbi

        --backup-dir=/tmp/backup/meb1 image-to-backup-dir

 

IMPORTANT: Please check that mysqlbackup run completes successfully.

            At the end of a successful 'image-to-backup-dir' run mysqlbackup

            prints "mysqlbackup completed OK!".

 

 mysqlbackup: INFO: Backup Image MEB version string: 3.6.0 [2011/07/01]

 mysqlbackup: INFO:   Total files as specified in image: 126

 mysqlbackup: INFO:  Backup Image contents extracted successfully into backup-dir.

     Backup directory: /tmp/backup/meb1

mysqlbackup completed OK!

n  mysqldump : logical backup

 

1.  백업을 합니다.

# mysqldump --user=root --password=oracle --tab=/tmp/backup --opt world_innodb

 

# ls /tmp/backup

CityLanguage.sql  City_part.txt  CountryLanguage3.sql  Country.txt

…

2.백업을 확인합니다.

# more City.sql

-- MySQL dump 10.13  Distrib 5.5.8, for Linux (i686)

--

-- Host: localhost    Database: world_innodb

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

-- Server version       5.5.8-log

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `City`

--

 

DROP TABLE IF EXISTS `City`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `City` (

  `ID` int(11) NOT NULL AUTO_INCREMENT,

  `Name` char(35) NOT NULL DEFAULT '',

  `CountryCode` char(3) NOT NULL DEFAULT '',

  `District` char(20) NOT NULL DEFAULT '',

  `Population` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`),

 

[root@EDYDR51P0 backup]# more City.txt

1       Kabul   AFG     Kabol   1780000

2       Qandahar        AFG     Qandahar        237500

3       Herat   AFG     Herat   186800

4       Mazar-e-Sharif  AFG     Balkh   127800

 

3. Restore합니다.

cd /tmp/backup

mysql –uroot –poracle world2 < Country.sql