DBA의 역할중 가장 중요한 건 Data 백업과 튜닝, 점검시 downtime 최소화..등등..
DB의 데이터가 쌓여..100G가 이상 되면 슬슬 mysqldump로는 한계를 만나게 된다.
이때 다른 백업을 찾게 되는데..
여러 오픈소스중 성능이 우수한 Xtrabackup 을 설치하고 테스트한다.
환경 : OS CentOS 6.9
1) 설치파일 확인
-- xtrabacup 설치하기 위해 기본적인 RPM 이 설치되어있는지 확인하고 없으면 설치해야한다.
|
#su권한
[root@~]# rpm -qa | grep perl-DBD
perl-DBD-MySQL-4.013-3.el6.x86_64
[root@~]# rpm -qa | grep perl-Time
perl-Time-HiRes-1.9721-144.el6.x86_64
[[root@~]# rpm -qa | grep libev
libevent-1.4.13-4.el6.x86_64
-->libev 없다. 찾아서 설치한다. |
2) xtrabackup 설치(rpm 설치)
#설치 파일은 2개
percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
percona-xtrabackup-24-debuginfo-2.4.9-1.el6.x86_64.rpm
|
-->libev 없는거 먼저 설치
[root@~]# rpm -Uvh libev-4.03-3.el6.x86_64.rpm
경고: libev-4.03-3.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
준비 중... ########################################### [100%]
1:libev ########################################### [100%]
확인) rpm -qa | grep libev
libev-4.03-3.el6.x86_64
설치)
[root@~]# rpm -Uvh percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
경고: percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
준비 중... ########################################### [100%]
1:percona-xtrabackup-24 ########################################### [100%]
[root@~]# rpm -Uvh percona-xtrabackup-24-debuginfo-2.4.9-1.el6.x86_64.rpm
경고: percona-xtrabackup-24-debuginfo-2.4.9-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
준비 중... ########################################### [100%]
1:percona-xtrabackup-24-d########################################### [100%]
[root@~]#
설치 끝 ~~~!!
어디에 설치된거야?? RPM 설치의 단점..
실행파일은 여기에 있다.
/usr/bin/innobackupex |
3) 백업
-- 쉘 스크립트로 만들어 Crontab 에 등록해서 편하게 쓴다.
쉘은 구글링~~!!
|
vi xtrabackup.sh
----------------------------------------------------------------------------------
/usr/bin/innobackupex \
--host=127.0.0.1 \
--user=root \
--password='DB패스워드' \
--ibbackup=xtrabackup \
--no-lock \
--no-timestamp \
${BACKUP_LOCATED}/${NOW_DATE} \
1> $PWD/logs/innobackupex_${NOW_DATE}.err 2> $PWD/logs/innobackupex_${NOW_DATE}.log
-->스크립트 내 xtrabackup 옵션들 중에 가장 중요한 것은 실시간 CRUD가 일어나는 DB는 mysql 엔진을 innodb를 쓰기 때문에 핫백업을 하려면 ibbackup 과 no-lock 옵션이 가장 중요하다. |
4) 재생성
4-1) 현재 사용중인 DB 중단
service mysqld stop
4-2) 디스크 비우고 백업
mv mysqldb_data mysqldb_data_bak
mkdir mysqldb_data
chown -Rf mysql:mysql mysqldb_data
4-3) xtrabacup apply 로그 복구
|
[root@~]# innobackupex --defaults-file=/etc/my.cnf --datadir=/home/mysql/mysqldb_data --apply-log /backup/innobackup/20190906_06/
190906 16:04:06 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /home/mysql/backup/innobackup/20190906_06/
xtrabackup: This target seems to be already prepared.
InnoDB: Number of pools: 1
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 4
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 4
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 2347797778603
InnoDB: xtrabackup: Last MySQL binlog file position 7587824, file name ./mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2347797779605
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 4
xtrabackup: innodb_log_file_size = 268435456
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 2347797779605
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2347797780598
190906 16:04:11 completed OK! |
4-4) xtrabackup 데이터복구
|
[root@~]# innobackupex --defaults-file=/etc/my.cnf --datadir=/home/mysql/mysqldb_data --copy-back /backup/innobackup/20190906_06/
...
190906 16:34:27 [01] Copying ./WWW/TB_COM_CD_DTL.frm to /home/mysql/mysqldb_data/WWW/TB_COM_CD_DTL.frm
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_CM_WWW_CTGR_SORT.frm to /home/mysql/mysqldb_data/WWW/TB_CM_WWW_CTGR_SORT.frm
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_COM_WWW_CTGR.frm to /home/mysql/mysqldb_data/WWW/TB_COM_WWW_CTGR.frm
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_PM_WWW_010PAY_RESERVE.frm to /home/mysql/mysqldb_data/WWW/TB_PM_WWW_010PAY_RESERVE.frm
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_SM_WWW_INFO.ibd to /home/mysql/mysqldb_data/WWW/TB_SM_WWW_INFO.ibd
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_CM_WWW_CTGR_SORT.ibd to /home/mysql/mysqldb_data/WWW/TB_CM_WWW_CTGR_SORT.ibd
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_PM_WWW_AUTOPAY.frm to /home/mysql/mysqldb_data/WWW/TB_PM_WWW_AUTOPAY.frm
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_WWW_ACCT_RCVM_HIST.ibd to /home/mysql/mysqldb_data/WWW/TB_WWW_ACCT_RCVM_HIST.ibd
190906 16:34:27 [01] ...done
190906 16:34:27 [01] Copying ./WWW/TB_WWW_ACCT_RCVM_DRW_HIST.frm to /home/mysql/mysqldb_data/WWW/TB_WWW_ACCT_RCVM_DRW_HIST.frm
190906 16:34:27 [01] ...done
190906 16:34:27 completed OK!
[root@~]#
|
4-5) DB 시작
|
[root@~]# service mysqld start
Starting MySQL.190906 17:35:42 mysqld_safe Logging to '/home/mysql/mariadb_logs/error/mysql.err'.
190906 17:35:42 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mariadb_data
. [실패]
190906 17:35:43 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode'
190906 17:35:43 [ERROR] InnoDB: The system tablespace must be writable!
190906 17:35:43 [ERROR] Plugin 'InnoDB' init function returned error.
190906 17:35:43 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
190906 17:35:43 [Note] Plugin 'FEEDBACK' is disabled.
190906 17:35:43 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
190906 17:35:43 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
190906 17:35:43 [Note] Server socket created on IP: '::'.
190906 17:35:43 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
190906 17:35:43 mysqld_safe mysqld from pid file /home/mysql/mariadb_data/test.pid ended'
--> 권한 오류 확인
/mariadb_data 아래 파일이 root 권한으로 인해 발생한 문제로 권한을 mysql로 수정한다.
[root@~]# chown -Rf mysql:mysql mysqldb_data
[root@~]# chown -Rf mysql:mysql mariadb_logs
[mysql@~]# service mysqld start
Starting MySQL.190906 17:52:46 mysqld_safe Logging to '/home/mysql/mariadb_logs/error/mysql.err'.
190906 17:52:46 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mariadb_data
.. [ OK ]
끝~~!! |
