mariadb partition table 다른 서버로 partition 이관(복원)

2022. 7. 11. 10:25

- 그림설명

* server 1

old.nct ( old partition table)

* server 2

backup.nct_tmp( new temp table)

backup.nct ( new partition table)

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

- source(백업) DB

root@localhost:(none) >FLUSH TABLES old.nct for export;
Query OK, 0 rows affected (12.634 sec)

★ source DB에 flush table lock 먼저 걸어서 cfg 파일이 생성된 상태에서 백업(복사) 진행, 해당 세션이 종료되지 않게 유지

root@server 1:>\!  ls -al /mariadb/DATA/old/nct*
drwx------  2 mysql mysql          4096 Jun 27 16:08.
drwxr-xr-x 11 mysql mysql          4096 Jun 27 13:11 ..
-rw-rw----  1 mysql mysql            67 Jun 23 14:33 db.opt
-rw-rw----  1 mysql mysql          4503 Jun 23 16:20 nct.frm
-rw-rw----  1 mysql mysql           356 Jun 23 16:20 nct.par
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202201.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202201.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202202.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202202.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202203.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202203.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202204.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202204.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202205.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202205.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202206.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202206.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202207.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202207.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202208.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202208.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202209.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202209.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202210.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202210.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202211.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202211.ibd
-rw-rw----  1 mysql mysql          1797 Jun 27 16:08 nct#P#P202212.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202212.ibd
-rw-rw----  1 mysql mysql          1794 Jun 27 16:08 nct#P#pmax.cfg
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#pmax.ibd

=> 유지 상태로 복사 진행
=> 완료되면 unlock table; 풀어줘야함 아니면 세션 아웃하면 됨

--------------------------------------------------------------------------------------------------------------------------------
★.백업할 파일을 복원할 DB 임시 경로로 복사 (ibd파일 과 cfg 파일 두 개다 복사 진행)

# 복원할 파일 임시 DB 경로 복사
root@server 1 # scp /mariadb/DATA/old/nct#P#P202201.cfg root@server2:/data2/temp/
root@server 1 # scp /mariadb/DATA/old/nct#P#P202201.ibd root@server2:/data2/temp/

... 그외 다른 파티션도 동일한 방법으로 전송

★ 주의사항 백업 시 lock(cfg파일 생성됨) 걸지 않은 상태에서 백업을 하면 second index을 삭제하지 않으면 import가 되지 않음

(pk까지 허용됨)---------------------------------------------------------

root@localhost:(none) > alter table test.nct_bak import tablespace;
ERROR 1815 (HY000): Internal error: Drop all secondary indexes before importing table test/nct_tmp when. cfg file is missing.
Warning (Code 1810): IO Read error: (2, No such file or directory) Error opening './test/nct_tmp.cfg', will attempt to import without schema verification
Error (Code 1815): Internal error: Drop all secondary indexes before importing table test/nct_tmp when .cfg file is missing.
Warning (Code 1816): ALTER TABLE '`test`.`nct_tmp`' IMPORT TABLESPACE failed with error 11 : 'Generic error'
Error (Code 1030): Got error 168 "Unknown (generic) error from engine" from storage engine InnoDB
root@localhost:(none) 12:59:16>
root@localhost:(none ) 12:59:16> drop index test. 인덱스명;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

- target(복원) DB
root@localhost:(none) > create database backup;
Query OK, 1 row affected (0.005 sec)

root@localhost:(none) >show databases;
+--------------------+
| Database           |
+--------------------+
| backup             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

# 복원할 DB에 임시 일반 테이블과 파티션 테이블 생성
CREATE TABLE backup.nct_tmp (
  `sid` int(11) NOT NULL,
  `read_dt` bigint(14) unsigned NOT NULL,
  `f1` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `test` varchar(20) DEFAULT NULL,
  `test1` int(12) DEFAULT NULL,
  PRIMARY KEY (`sid`,`read_dt`),
  KEY `INNN` (`f1`,`test`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (`read_dt`)
(PARTITION `P202201` VALUES LESS THAN (20220201000000) ENGINE = InnoDB,
PARTITION `P202202` VALUES LESS THAN (20220301000000) ENGINE = InnoDB,
PARTITION `P202203` VALUES LESS THAN (20220401000000) ENGINE = InnoDB,
PARTITION `P202204` VALUES LESS THAN (20220501000000) ENGINE = InnoDB,
PARTITION `P202205` VALUES LESS THAN (20220601000000) ENGINE = InnoDB,
PARTITION `P202206` VALUES LESS THAN (20220701000000) ENGINE = InnoDB,
PARTITION `P202207` VALUES LESS THAN (20220801000000) ENGINE = InnoDB,
PARTITION `P202208` VALUES LESS THAN (20220901000000) ENGINE = InnoDB,
PARTITION `P202209` VALUES LESS THAN (20221001000000) ENGINE = InnoDB,
PARTITION `P202210` VALUES LESS THAN (20221101000000) ENGINE = InnoDB,
PARTITION `P202211` VALUES LESS THAN (20221201000000) ENGINE = InnoDB,
PARTITION `P202212` VALUES LESS THAN (20230101000000) ENGINE = InnoDB,
PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

CREATE TABLE backup.nct (
  `sid` int(11) NOT NULL,
  `read_dt` bigint(14) unsigned NOT NULL,
  `f1` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `test` varchar(20) DEFAULT NULL,
  `test1` int(12) DEFAULT NULL,
  PRIMARY KEY (`sid`,`read_dt`),
  KEY `INNN` (`f1`,`test`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (`read_dt`)
(PARTITION `P202201` VALUES LESS THAN (20220201000000) ENGINE = InnoDB,
PARTITION `P202202` VALUES LESS THAN (20220301000000) ENGINE = InnoDB,
PARTITION `P202203` VALUES LESS THAN (20220401000000) ENGINE = InnoDB,
PARTITION `P202204` VALUES LESS THAN (20220501000000) ENGINE = InnoDB,
PARTITION `P202205` VALUES LESS THAN (20220601000000) ENGINE = InnoDB,
PARTITION `P202206` VALUES LESS THAN (20220701000000) ENGINE = InnoDB,
PARTITION `P202207` VALUES LESS THAN (20220801000000) ENGINE = InnoDB,
PARTITION `P202208` VALUES LESS THAN (20220901000000) ENGINE = InnoDB,
PARTITION `P202209` VALUES LESS THAN (20221001000000) ENGINE = InnoDB,
PARTITION `P202210` VALUES LESS THAN (20221101000000) ENGINE = InnoDB,
PARTITION `P202211` VALUES LESS THAN (20221201000000) ENGINE = InnoDB,
PARTITION `P202212` VALUES LESS THAN (20230101000000) ENGINE = InnoDB,
PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

# 임시 테이블 파티션 여부 삭제
root@localhost:(none) > alter table backup.nct_tmp remove partitioning;
Query OK, 0 rows affected (0.118 sec)

# tablespace 삭제전 확인
root@localhost:(none) > \! ls -al /data1/backup;
drwx------  2 mysql mysql   4096  6월 28 10:15.
drwxr-xr-x 13 mysql mysql   4096  6월 28 10:12 ..
-rw-rw----  1 mysql mysql     61  6월 28 10:12 db.opt

-rw-rw----  1 mysql mysql          4503 Jun 23 16:20 nct.frm
-rw-rw----  1 mysql mysql           356 Jun 23 16:20 nct.par
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202201.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202202.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202203.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202204.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202205.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202206.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202207.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202208.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202209.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202210.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202211.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#P202212.ibd
-rw-rw----  1 mysql mysql        180224 Jun 24 13:30 nct#P#pmax.ibd
-rw-rw----  1 mysql mysql   4553  6월 28 10:15 nct_tmp.frm
-rw-rw----  1 mysql mysql 163840  6월 28 10:15 nct_tmp.ibd

# tablespace 삭제
root@localhost:(none) > alter table backup.nct_tmp discard tablespace;
Query OK, 0 rows affected (0.007 sec)

# tablespace 삭제 확인
root@localhost:(none) > \! ls -al /data1/backup;
drwx------  2 mysql mysql 4096  6월 28 10:17.
drwxr-xr-x 13 mysql mysql 4096  6월 28 10:12 ..
-rw-rw----  1 mysql mysql   61  6월 28 10:12 db.opt

root@localhost:(none) 15:17:36>\q
Bye

# file move 및 rename

root@server 2 # mv /data2/temp/nct#P#P202201.cfg /data1/backup/nct_tmp.cfg
root@server 2 # mv /data2/temp/nct#P#P202201.ibd /data1/backup/nct_tmp.ibd

# 권한 변경( server 2)
root@server 2 # chown mysql:mysql /data1/backup/nct_tmp*

# tablepsace import
root@localhost:backup > alter table backup.nct_tmp import tablespace;
Query OK, 0 rows affected, 1 warning (0.609 sec)

# 파티션 테이블로 전환
root@localhost:backup > alter table backup.nct exchange partition P202201 with table backup.nct_bak;

# 그 뒤 파일은 이어서 진행
root@localhost:backup > alter table backup.nct_tmp discard tablespace;
root@localhost:backup >\! mv /data2/temp/nct#P#P202202.cfg /data1/backup/nct_tmp.cfg
root@localhost:backup >\! mv /data2/temp/nct#P#P202202.ibd /data1/backup/nct_tmp.ibd
root@localhost:backup >\! chown mysql:mysql /data1/backup/nct_tmp*
root@localhost:backup >alter table nct_tmp import tablespace;
root@localhost:backup >alter table backup.nct exchange partition P202202 with table nct_tmp;

root@localhost:backup > alter table backup.nct_tmp discard tablespace;
root@localhost:backup >\! mv /data2/temp/nct#P#P202203.cfg /data1/backup/nct_tmp.cfg
root@localhost:backup >\! mv /data2/temp/nct#P#P202203.ibd /data1/backup/nct_tmp.ibd
root@localhost:backup >\! chown mysql:mysql /data1/backup/nct_tmp*
root@localhost:backup >alter table nct_tmp import tablespace;
root@localhost:backup >alter table backup.nct exchange partition P202203 with table nct_tmp;

...

root@localhost:backup > alter table backup.nct_tmp discard tablespace;
root@localhost:backup >\! mv /data2/temp/nct#P#P202212.cfg /data1/backup/nct_tmp.cfg
root@localhost:backup >\! mv /data2/temp/nct#P#P202212.ibd /data1/backup/nct_tmp.ibd
root@localhost:backup >\! chown mysql:mysql /data1/backup/nct_tmp*
root@localhost:backup >alter table nct_tmp import tablespace;
root@localhost:backup >alter table backup.nct exchange partition P202212 with table nct_tmp;

완료

저작자표시 (새창열림)

'DBMS > MySQL&Mariadb' 카테고리의 다른 글

mariadb client 설치(application 서버) (0)	2023.02.20
mariadb HA 구성도별 설명 (0)	2022.07.11
mariadb partition exchange(같은 서버 내 DB(schema)명만 변경) (0)	2022.06.30
mariadb,mysql partition table 조회 (0)	2022.06.16
replication 깨질때 skip 방법 (0)	2022.06.16

itexit

mariadb partition table 다른 서버로 partition 이관(복원)

'DBMS > MySQL&Mariadb' 카테고리의 다른 글

+ Recent posts

티스토리툴바