[Xtrabackup] Backup 방법2 (추가 full backup)

지난 innobackupex 명령어를 단순히 따라 하는 것에서 벗어나

하나씩 저희 회사에 맞게 변경을 하고 있습니다.

이게 진정 회사를 위한 공부겠죠..?

각설하고...

지난 명령어는 아래와 같습니다.

[root@DBTEST02:/root]# innobackupex --user=DB유저명 --password=비밀번호--socket=/tmp/mysql_8003.sock  /home/backup/game/

나름 저희 회사에 맞게 변경한다고 한 것이지만 여기서 빼놓고 간 부분이 있습니다.

위와 같이 진해을 하면 Default DB 의 위치를 backup 한 다는 것을..

잠시 /etc/my.cnf 를 확인해 보면

[mysqld]

user            = mysql

basedir         = /usr/local/mysql

datadir         = /home/DATABASE/Default_DB

tmpdir          = /usr/local/mysql/DB/tmp

datadir 가 Default DB 위치다.

그래서 위의 innobackupex 를 하면 port 가 8003 인 곳의 datadir 가 아닌 Default 위치의 데이터가 백업 되는 것을 확인할 수 있다.

그렇다면, 어떻게 진행해야 될까?

아래와 같이 defaults-file 를 선언해 주면 된다.

[root@DBTEST02:/root]#innobackupex --defaults-file=/home/DATABASE/InnoDB/Game2/ --user=root --password= --socket=/tmp/mysql_8003.sock /home/backup/game/

여기서 --defaults-file 은 앞쪽에 위치해야 한다. 혹시나 해당 변수를 뒤에다 선언하면 에러가 발생한다.

xtrabackup: Error: --defaults-file must be specified first on the command line

제대로 백업이 되는지는 로그만 봐도 알 수 있다.

아래 보면 해당 위치의 DB까지 모두 백업 되는 것을 확인할 수 있다.

[root@DBTEST02:/home/backup/game]# innobackupex --defaults-file=/home/DATABASE/InnoDB/Game2/ --user=root --password= --socket=/tmp/mysql_8003.sock --databases=DARKEDEN /home/backup/game/

160920 10:52:00 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

           At the end of a successful backup run innobackupex

           prints "completed OK!".

160920 10:52:00  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql_8003.sock' as 'root'  (using password: NO).

160920 10:52:00  version_check Connected to MySQL server

160920 10:52:00  version_check Executing a version check against the server...

160920 10:52:00  version_check Done.

160920 10:52:00 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /tmp/mysql_8003.sock

Using server version 5.6.32-debug-log

innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /home/DATABASE/InnoDB/Game2/

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup:   innodb_data_home_dir = .

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

160920 10:52:00 >> log scanned up to (21174002)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 442 for DARKEDEN/ShoulderArmorInfo, old maximum was 0

160920 10:52:01 >> log scanned up to (21174002)

160920 10:52:02 >> log scanned up to (21174002)

160920 10:52:03 >> log scanned up to (21174002)

160920 10:52:04 [01] Copying ./ibdata1 to /home/backup/game/2016-09-20_10-52-00/ibdata1

160920 10:52:04 >> log scanned up to (21174002)

160920 10:52:05 >> log scanned up to (21174002)

160920 10:52:05 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/ContributeDegree.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/ContributeDegree.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/OustersWristletObject.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/OustersWristletObject.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/SubInventoryInfo.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/SubInventoryInfo.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/HighEnchantLog.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/HighEnchantLog.ibd

...

160920 10:52:10 [01] Copying ./DARKEDEN/VampireETCInfo.frm to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/VampireETCInfo.frm

160920 10:52:10 [01]        ...done

160920 10:52:10 Finished backing up non-InnoDB tables and files

160920 10:52:10 [00] Writing xtrabackup_binlog_info

160920 10:52:10 [00]        ...done

160920 10:52:10 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '21174002'

xtrabackup: Stopping log copying thread.

.160920 10:52:10 >> log scanned up to (21174002)

160920 10:52:10 Executing UNLOCK TABLES

160920 10:52:10 All tables unlocked

160920 10:52:10 Backup created in directory '/home/backup/game/2016-09-20_10-52-00'

MySQL binlog position: filename 'Bin_Log.000051', position '120'

160920 10:52:10 [00] Writing backup-my.cnf

160920 10:52:10 [00]        ...done

160920 10:52:10 [00] Writing xtrabackup_info

160920 10:52:10 [00]        ...done

xtrabackup: Transaction log of lsn (21174002) to (21174002) was copied.

160920 10:52:11 completed OK!

incremental 백업도 비슷하게 진행하면 될 것 같다.