'MYSQL and Maria' 카테고리의 글 목록 (25 Page)

MYSQL and Maria

Xtrabackup 2.4 설치 및 사용법 2017.12.01

Xtrabackup 2.4 설치 및 사용법

2017. 12. 1. 16:53

728x90

0. Percona XtraBackup 2.4 설치

Percona 최신 Repository 설치를 위하여 다음 수행

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

최신 버전의 xtrabackup 확인

yum list | grep percona-xtrabackup

percona-xtrabackup-24.x86_64 2.4.9-1.el7 @percona-release-x86_64

percona-xtrabackup.x86_64 2.3.10-1.el7 percona-release-x86_64

percona-xtrabackup-22.x86_64 2.2.13-1.el7 percona-release-x86_64

percona-xtrabackup-22-debuginfo.x86_64 2.2.13-1.el7 percona-release-x86_64

percona-xtrabackup-24-debuginfo.x86_64 2.4.9-1.el7 percona-release-x86_64

percona-xtrabackup-debuginfo.x86_64 2.3.10-1.el7 percona-release-x86_64

percona-xtrabackup-test.x86_64 2.3.10-1.el7 percona-release-x86_64

percona-xtrabackup-test-22.x86_64 2.2.13-1.el7 percona-release-x86_64

percona-xtrabackup-test-24.x86_64 2.4.9-1.el7 percona-release-x86_64

Xtrabackup 2.4 설치

yum install percona-xtrabackup-24.x86_64 -y

Xtrabackup 2.4 설치 확인

yum list installed | grep percona-xtrabackup

percona-xtrabackup-24.x86_64 2.4.9-1.el7 @percona-release-x86_64

1. xtrabackup을 수행 할 유저 생성

로컬 백업을 위한 유저 생성

CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'xtra' ;

GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost' ;

FLUSH PRIVILEGES ;

원격 백업이 필요할 경우 사용할 유저 생성

CREATE USER 'xtrabackup'@'%' IDENTIFIED BY 'xtra' ;

GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'%' ;

FLUSH PRIVILEGES ;

백업 경로 설정

mkdir /xbackup

mkdir /xbackup/maria5

mkdir /xbackup/maria10

chown -R mysql:mysql /xbackup

xtrabackup은 기본적으로 my.cnf파일의 [mysqld] 와 [xtrabackup] 섹션의 값을 참조 한다.

[mysqld] 섹션에서는 datadir 값을 참조하고 , [xtrabackup] 섹션에서는 백업 경로인 target_dir 값을 참조한다.

각각의 섹션에 저장되지 않은 값은 command-line에서 지정해서 사용할 수 있다.

/etc/my.cnf 파일에 접근이 안 될 경우, mysqld 기동 시 사용된 옵션을 이용한다. (/etc/my.cnf 파일이 없을 경우 basedir 안의 my.cnf 파일을 사용 함)

테스트 환경에서는 한 서버에 Maria10 / Maria5가 설치 되어 있어서 socket 옵션을 주고 수행 시킴.

[root@CMaria maria5.5.58]# ps -ef | grep mysqld

root 30373 30226 0 17:39 pts/1 00:00:00 /bin/sh /usr/local/mariadb5/bin/mysqld_safe --basedir=/usr/local/mariadb5 --datadir=/maria5.5.58 --plugin-dir=/usr/local/mariadb5/lib/plugin --user=mysql --log-error=/maria5.5.58/CMaria.err --open-files-limit=8192 --pid-file=/maria5.5.58/CMaria.pid --socket=/tmp/mysql5.sock --port=3305

mysql 31110 30373 0 17:39 pts/1 00:00:00 /usr/local/mariadb5/bin/mysqld --basedir=/usr/local/mariadb5 --datadir=/maria5.5.58 --plugin-dir=/usr/local/mariadb5/lib/plugin --user=mysql --log-error=/maria5.5.58/CMaria.err --open-files-limit=8192 --pid-file=/maria5.5.58/CMaria.pid --socket=/tmp/mysql5.sock --port=3305

root 31141 30226 0 17:39 pts/1 00:00:00 /bin/sh /usr/local/mariadb10/bin/mysqld_safe --basedir=/usr/local/mariadb10 --datadir=/maria10.2.10 --plugin-dir=/usr/local/mariadb10/lib/plugin --user=mysql --log-error=/maria10.2.10/CMaria.err --pid-file=/maria10.2.10/CMaria.pid --socket=/tmp/mysql10.sock --port=3310

mysql 31287 31141 0 17:39 pts/1 00:00:00 /usr/local/mariadb10/bin/mysqld --basedir=/usr/local/mariadb10 --datadir=/maria10.2.10 --plugin-dir=/usr/local/mariadb10/lib/plugin --user=mysql --log-error=/maria10.2.10/CMaria.err --pid-file=/maria10.2.10/CMaria.pid --socket=/tmp/mysql10.sock --port=3310

2. Xtrabackup 실행 (Maria5.x 버전 백업 수행)

FULL backup 수행

/usr/bin/innobackupex --defaults-file=/usr/local/mariadb5/my.cnf --user xtrabackup --password xtra --socket=/tmp/mysql5.sock /xbackup/maria5

중략.....

...... /xbackup/maria5/2017-12-01_16-15-32/.....

171130 17:44:10 completed OK!

백업 중 변경된 내용을 반영하기 위하여 로그 파일을 읽어서 백업 파일에 반영

/usr/bin/innobackupex --defaults-file=/usr/local/mariadb5/my.cnf --apply-log /xbackup/maria5/2017-12-01_16-15-32

로그 까지 반영이 끝난 후의 백업 된 디렉토리 내용.

[root@CMaria ~]# ls -al /xbackup/maria5/2017-12-01_16-15-32

total 825372

drwxr-x--- 6 root root 323 Dec 1 16:16 .

drwxr-xr-x 8 mysql mysql 168 Dec 1 16:15 ..

-rw-r----- 1 root root 419 Dec 1 16:15 backup-my.cnf

-rw-r----- 1 root root 18874368 Dec 1 16:16 ibdata1

-rw-r----- 1 root root 268435456 Dec 1 16:16 ib_logfile0

-rw-r----- 1 root root 268435456 Dec 1 16:16 ib_logfile1

-rw-r----- 1 root root 268435456 Dec 1 16:16 ib_logfile2

-rw-r----- 1 root root 12582912 Dec 1 16:16 ibtmp1

drwxr-x--- 2 root root 41 Dec 1 16:15 maria5

drwxr-x--- 2 root root 4096 Dec 1 16:15 mysql

drwxr-x--- 2 root root 4096 Dec 1 16:15 performance_schema

drwxr-x--- 2 root root 20 Dec 1 16:15 test

-rw-r----- 1 root root 21 Dec 1 16:15 xtrabackup_binlog_info

-rw-r--r-- 1 root root 23 Dec 1 16:16 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root 113 Dec 1 16:16 xtrabackup_checkpoints

-rw-r----- 1 root root 555 Dec 1 16:15 xtrabackup_info

-rw-r----- 1 root root 8388608 Dec 1 16:16 xtrabackup_logfile

3. 복구 테스트를 위하여 데이터 삭제

[root@CMaria ~]# mysql5 -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 16

Server version: 5.5.58-MariaDB Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| maria5 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

MariaDB [(none)]> use maria5 ;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [maria5]> show tables ;

+------------------+

| Tables_in_maria5 |

+------------------+

| test_enum |

+------------------+

1 row in set (0.01 sec)

MariaDB [maria5]> select * from test_enum ;

+----+-----------+

| id | type_enum |

+----+-----------+

| 1 | c |

| 2 | a |

| 3 | a |

| 4 | e |

| 5 | a |

| 6 | NULL |

| 7 | |

| 8 | z |

| 9 | a |

| 10 | A |

| 11 | AbCd |

| 12 | AbCd |

| 13 | AbCd |

+----+-----------+

13 rows in set (0.00 sec)

MariaDB [maria5]> delete from test_enum where id > 10 ;

Query OK, 3 rows affected (0.08 sec)

MariaDB [maria5]> commit ;

Query OK, 0 rows affected (0.00 sec)

MariaDB [maria5]> select * from test_enum ;

+----+-----------+

| id | type_enum |

+----+-----------+

| 1 | c |

| 2 | a |

| 3 | a |

| 4 | e |

| 5 | a |

| 6 | NULL |

| 7 | |

| 8 | z |

| 9 | a |

| 10 | A |

+----+-----------+

10 rows in set (0.00 sec)

MariaDB [maria5]> exit

Bye

4. 복구

백업 이후에 변경된 로그를 적용해서 마지막 데이터 까지 살리고자 한다면, --copy-back 전에 복구에 사용할 백업 디렉토리에 --apply-log 옵션을 이용하여 데이터를

최신의 데이터로 반영해 주고 --copy-back 옵션을 주어서 복구를 하면 된다.

그러나 여기 시나리오에서는 지워진 테이블의 데이터를 삭제 이전으로 돌리기 위한 시나리오 였으므로 -- apply-log를 적용하지 않고 백업 시점으로 DB를 되돌린다.

/usr/bin/innobackupex --defaults-file=/usr/local/mariadb5/my.cnf --copy-back /xbackup/maria5/2017-12-01_16-15-32

171201 16:24:08 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

prints "completed OK!".

/usr/bin/innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

Error: datadir must be specified.

/usr/local/mariadb5/my.cnf 파일에 datadir 값이 지정되지 않았음. 그래서 옵션으로 지정해 줌

/usr/bin/innobackupex --defaults-file=/usr/local/mariadb5/my.cnf --datadir=/maria5.5.58 --copy-back /xbackup/maria5/2017-12-01_16-15-32

171201 16:26:13 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

prints "completed OK!".

/usr/bin/innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

Original data directory /maria5.5.58 is not empty!

datadir에 파일이 하나도 없어야 함.

[root@CMaria ~]# cd /maria5.5.58

[root@CMaria maria5.5.58]# ls

aria_log.00000001 CMaria.err ibdata1 ib_logfile1 maria5 mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.000009 mysql-bin.000011 performance_schema

aria_log_control CMaria-slow.log ib_logfile0 ib_logfile2 mysql mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 mysql-bin.000010 mysql-bin.index test

[root@CMaria maria5.5.58]# rm -rf *

[root@CMaria maria5.5.58]# ls

[root@CMaria maria5.5.58]# pwd

/maria5.5.58

/usr/bin/innobackupex --defaults-file=/usr/local/mariadb5/my.cnf --datadir=/maria5.5.58 --copy-back /xbackup/maria5/2017-12-01_16-15-32

171201 16:28:18 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

prints "completed OK!".

/usr/bin/innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

171201 16:28:18 [01] Copying ib_logfile0 to /maria5.5.58/ib_logfile0

171201 16:28:19 [01] ...done

중략...

171201 16:28:29 completed OK!

5. 디비 기동

xtrabackup 명령을 root로 수행하고, 복구도 root로 하니 datadir에 copy된 데이터들이 모두 root 권한으로 되어 있어서 DB가 기동 되지 않음.

datadir의 모든 파일들의 권한을 변경 시켜 줘야 함.

[root@CMaria maria5.5.58]# cd /maria5.5.58

[root@CMaria maria5.5.58]# ls -al

total 817188

drwxr-xr-x 6 mysql mysql 306 Dec 1 16:29 .

dr-xr-xr-x. 20 root root 4096 Nov 30 16:43 ..

-rw-rw---- 1 mysql mysql 8192 Dec 1 16:29 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Dec 1 16:29 aria_log_control

-rw-rw---- 1 mysql mysql 951 Dec 1 16:29 CMaria.err

-rw-r----- 1 root root 18874368 Dec 1 16:28 ibdata1

-rw-r----- 1 root root 268435456 Dec 1 16:28 ib_logfile0

-rw-r----- 1 root root 268435456 Dec 1 16:28 ib_logfile1

-rw-r----- 1 root root 268435456 Dec 1 16:28 ib_logfile2

-rw-r----- 1 root root 12582912 Dec 1 16:28 ibtmp1

drwxr-x--- 2 root root 41 Dec 1 16:28 maria5

drwxr-x--- 2 root root 4096 Dec 1 16:28 mysql

-rw-rw---- 1 mysql mysql 0 Dec 1 16:29 mysql-bin.index

drwxr-x--- 2 root root 4096 Dec 1 16:28 performance_schema

drwxr-x--- 2 root root 20 Dec 1 16:28 test

-rw-r----- 1 root root 23 Dec 1 16:28 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root 555 Dec 1 16:28 xtrabackup_info

[root@CMaria maria5.5.58]# chown -R mysql:mysql /maria5.5.58

[root@CMaria maria5.5.58]# ls -al

total 817188

drwxr-xr-x 6 mysql mysql 306 Dec 1 16:29 .

dr-xr-xr-x. 20 root root 4096 Nov 30 16:43 ..

-rw-rw---- 1 mysql mysql 8192 Dec 1 16:29 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Dec 1 16:29 aria_log_control

-rw-rw---- 1 mysql mysql 951 Dec 1 16:29 CMaria.err

-rw-r----- 1 mysql mysql 18874368 Dec 1 16:28 ibdata1

-rw-r----- 1 mysql mysql 268435456 Dec 1 16:28 ib_logfile0

-rw-r----- 1 mysql mysql 268435456 Dec 1 16:28 ib_logfile1

-rw-r----- 1 mysql mysql 268435456 Dec 1 16:28 ib_logfile2

-rw-r----- 1 mysql mysql 12582912 Dec 1 16:28 ibtmp1

drwxr-x--- 2 mysql mysql 41 Dec 1 16:28 maria5

drwxr-x--- 2 mysql mysql 4096 Dec 1 16:28 mysql

-rw-rw---- 1 mysql mysql 0 Dec 1 16:29 mysql-bin.index

drwxr-x--- 2 mysql mysql 4096 Dec 1 16:28 performance_schema

drwxr-x--- 2 mysql mysql 20 Dec 1 16:28 test

-rw-r----- 1 mysql mysql 23 Dec 1 16:28 xtrabackup_binlog_pos_innodb

-rw-r----- 1 mysql mysql 555 Dec 1 16:28 xtrabackup_info

[root@CMaria maria5.5.58]# mysql5 -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.58-MariaDB Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| maria5 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

MariaDB [(none)]> use maria5 ;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [maria5]> show tables ;

+------------------+

| Tables_in_maria5 |

+------------------+

| test_enum |

+------------------+

1 row in set (0.00 sec)

MariaDB [maria5]> select * from test_enum ;

+----+-----------+

| id | type_enum |

+----+-----------+

| 1 | c |

| 2 | a |

| 3 | a |

| 4 | e |

| 5 | a |

| 6 | NULL |

| 7 | |

| 8 | z |

| 9 | a |

| 10 | A |

| 11 | AbCd |

| 12 | AbCd |

| 13 | AbCd |

+----+-----------+

13 rows in set (0.01 sec)

PREV 1 ···22 23 24 25 26 27 28 ···59 NEXT

멋지게 놀아라

MYSQL and Maria

Xtrabackup 2.4 설치 및 사용법

+ Recent posts

티스토리툴바