大数据备份和恢复应用案例--通过分区表备份和恢复数据-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

大数据备份和恢复应用案例--通过分区表备份和恢复数据

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:46 PM

Partition Tablebackupapplicationrecoverdatadata backupCasepass

大数据备份和恢复应用案例--通过分区表备份和恢复数据海量数据备份和恢复方案对于OLAP的数据库的业务特点，是将批量的数据加载入库，然后对这些数据进行分析处理

大数据备份和恢复应用案例--通过分区表备份和恢复数据

海量数据备份和恢复方案

对于OLAP的数据库的业务特点，是将批量的数据加载入库，然后对这些数据进行分析处理，比如报表或者数据挖掘，最后给业务提供一种决策支持；另外，这类数据库的数据实时性非常高，一旦这些数据处理完毕后，就很少再次使用（有时，也需要对这类数据进行查询）。

对于OLAP数据库的备份和恢复可以考虑这样几种方案：

1、使用分布式数据库

将数据分布到多个库里，当数据库恢复时，只需要恢复单个库的数据，，大大节省恢复时间。

2、结合分区技术，以传输表空间方式进行备份和恢复

案例分析：

1、建立分区表，将分区存储在不同的表空间 [oracle@RH6 ~]$sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 18 17:15:47 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 17:15:47 SYS@ prod >create tablespace tbs1 17:16:03 2 datafile '/dsk1/oradata/prod/tbs1.dbf' size 10m; Tablespace created. 17:17:00 SYS@ prod >create tablespace tbs2 17:17:11 2 datafile '/dsk2/oradata/prod/tbs2.dbf' size 10m; Tablespace created. 17:17:49 SYS@ prod >create tablespace tbs3 17:17:57 2 datafile '/dsk3/oradata/prod/tbs3.dbf' size 10m; Tablespace created. 17:18:35 SYS@ prod >create tablespace tbs1_indx 17:18:49 2 datafile '/dsk1/oradata/prod/tbs1_indx.dbf' size 10m; Tablespace created. 17:19:43 SYS@ prod >create tablespace tbs2_indx 17:19:54 2 datafile '/dsk2/oradata/prod/tbs2_indx.dbf' size 10m; Tablespace created. 17:20:18 SYS@ prod >create tablespace tbs3_indx 17:20:30 2 datafile '/dsk3/oradata/prod/tbs3_indx.dbf' size 10m; Tablespace created. 17:22:12 SYS@ prod >select file_id,file_name,tablespace_name from dba_data_files FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 11 /dsk1/oradata/prod/tbs1.dbf TBS1 12 /dsk2/oradata/prod/tbs2.dbf TBS2 13 /dsk3/oradata/prod/tbs3.dbf TBS3 4 /u01/app/oracle/oradata/prod/users01.dbf USERS 3 /u01/app/oracle/oradata/prod/undotbs01.dbf UNDOTBS1 2 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX 1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM 5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE 6 /u01/app/oracle/oradata/prod/users02.dbf USERS 7 /u01/app/oracle/oradata/prod/catatbs1.dbf CATATBS 8 /u01/app/oracle/oradata/prod/perfertbs1.dbf PERFERTBS 9 /u01/app/oracle/oradata/prod/oggtbs1.dbf OGG_TBS 10 /u01/app/oracle/oradata/prod/test1.dbf TEST1 14 /dsk1/oradata/prod/tbs1_indx.dbf TBS1_INDX 15 /dsk2/oradata/prod/tbs2_indx.dbf TBS2_INDX 16 /dsk3/oradata/prod/tbs3_indx.dbf TBS3_INDX 建立分区表及索引： 17:26:41 SCOTT@ prod >create table t1(id int,name varchar2(1000)) 17:26:57 2 partition by range(id) 17:27:01 3 (partition p1 values less than(1000) tablespace tbs1, 17:27:13 4 partition p2 values less than(2000) tablespace tbs2, 17:27:23 5 partition p3 values less than(maxvalue) tablespace tbs3); Table created. 17:30:33 SCOTT@ prod >create index t1_indx on t1(id) local 2 ( 3 partition p1 tablespace tbs1_indx, 4 partition p2 tablespace tbs2_indx, 5* partition p3 tablespace tbs3_indx ) / 17:30:37 SCOTT@ prod >select partition_name,tablespace_name from user_segments where segment_name='T1'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 TBS1 P2 TBS2 P3 TBS3 17:31:33 SCOTT@ prod >select partition_name,tablespace_name from user_segments where segment_name='T1_INDX'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 TBS1_INDX P2 TBS2_INDX P3 TBS3_INDX 插入数据： 17:34:09 SYS@ prod >begin 17:34:26 2 for i in 1..3 loop 17:34:32 3 insert into scott.t1 select object_id*i,object_name from dba_objects where object_id select count(*) from t1; COUNT(*) ---------- 2826 17:36:52 SCOTT@ prod >select 'p1',count(*) from t1 partition(p1) 17:37:42 2 union 17:37:47 3 select 'p2',count(*) from t1 partition(p2) 17:38:11 4 union 17:38:13 5 select 'p3',count(*) from t1 partition(p3); 'P1' COUNT(*) -------------------------------- ---------- p1 1740 p2 774 p3 312 2、传输表空间 17:35:04 SYS@ prod >alter tablespace tbs1 read only; Tablespace altered. 17:41:02 SYS@ prod >alter tablespace tbs1_indx read only; Tablespace altered. 17:39:14 SYS@ prod >create directory tbs_dir as '/home/oracle/data'; Directory created. 17:40:30 SYS@ prod >grant read,write on directory tbs_dir to scott; Grant succeeded. [oracle@RH6 data]$ expdp system/oracle directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.log Export: Release 11.2.0.1.0 - Production on Tue Nov 18 17:44:25 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.log ORA-39123: Data Pump transportable tablespace job aborted ORA-39187: The transportable set is not self-contained, violation list is ORA-39901: Partitioned table SCOTT.T1 is partially contained in the transportable set. ORA-39921: Default Partition (Table) Tablespace USERS for T1 not contained in transportable set. Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 17:44:49 传输表空间出错，表空间处于非自包含模式： 18:14:47 SYS@ prod >exec dbms_tts.transport_set_check('TBS1',true); PL/SQL procedure successfully completed. 18:17:49 SYS@ prod >select * from transport_set_violations; VIOLATIONS ------------------------------------------------------------------------------------------------------------------------ ORA-39921: Default Partition (Table) Tablespace USERS for T1 not contained in transportable set. . ORA-39901: Partitioned table SCOTT.T1 is partially contained in the transportable set. 解决方法，需要创建一个临时表和一个临时表索引，将分区和分区索引交换到临时表和临时表索引表空间上，然后到处临时表和临时表索引。由于临时表不是分区表，它们呢所在的表空间符合自包含条件。 17:45:37 SCOTT@ prod >create table t1_tmp as select * from t1 where 1=3; Table created. Elapsed: 00:00:00.20 17:45:58 SCOTT@ prod >create index t1_tmp_indx on t1_tmp(id); Index created. 17:46:33 SCOTT@ prod >select segment_name,tablespace_name from user_segments 17:47:18 2 where segment_name in ('T1_TMP','T1_TMP_INDX'); SEGMENT_NAME TABLESPACE_NAME --------------------------------------------------------------------------------- ------------------------------ T1_TMP USERS T1_TMP_INDX USERS 将分区表交换到临时表： 17:48:32 SCOTT@ prod >alter table t1 exchange partition p1 with table t1_tmp including indexes; Table altered. 17:49:02 SCOTT@ prod >select segment_name,tablespace_name from user_segments 17:49:35 2 where segment_name in ('T1_TMP','T1_TMP_INDX'); SEGMENT_NAME TABLESPACE_NAME --------------------------------------------------------------------------------- ------------------------------ T1_TMP TBS1 T1_TMP_INDX TBS1_INDX 17:50:44 SYS@ prod >exec dbms_tts.transport_set_check('TBS1',true); PL/SQL procedure successfully completed. 17:51:59 SYS@ prod >select * from transport_set_violations; no rows selected 已经符合自包含条件 [oracle@RH6 data]$ expdp system/oracle directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.log Export: Release 11.2.0.1.0 - Production on Tue Nov 18 17:52:55 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=tbs_dir dumpfile=p1.dmp transport_tablespaces=tbs1,tbs1_indx logfile=p1.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/data/p1.dmp ****************************************************************************** Datafiles required for transportable tablespace TBS1: /dsk1/oradata/prod/tbs1.dbf Datafiles required for transportable tablespace TBS1_INDX: /dsk1/oradata/prod/tbs1_indx.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:54:17 表空间导出成功！ 17:56:16 SYS@ prod >select file_name,tablespace_name from dba_data_files where tablespace_name in ('TBS1','TBS1_INDX'); FILE_NAME TABLESPACE_NAME -------------------------------------------------- ------------------------------ /dsk1/oradata/prod/tbs1.dbf TBS1 /dsk1/oradata/prod/tbs1_indx.dbf TBS1_INDX [oracle@RH6 ~]$ cp /dsk1/oradata/prod/tbs1* /home/oracle/data [oracle@RH6 ~]$ ls -lh /home/oracle/data total 21M -rw-r----- 1 oracle oinstall 92K Nov 18 17:54 p1.dmp -rw-r--r-- 1 oracle oinstall 1.4K Nov 18 17:54 p1.log -rw-r----- 1 oracle oinstall 11M Nov 18 17:57 tbs1.dbf -rw-r----- 1 oracle oinstall 11M Nov 18 17:57 tbs1_indx.dbf 然后再将表空间的数据文件进行备份，由于表空间传输，只是导出了metadata，所以数据量非常小，速度非常快。 3、数据恢复 17:58:29 SYS@ prod >drop tablespace tbs1 including contents and datafiles; Tablespace dropped. 17:58:55 SYS@ prod >drop tablespace tbs1_indx including contents and datafiles; Tablespace dropped. 17:59:12 SYS@ prod >col segment_name for a20 17:59:42 SYS@ prod >col partition_name for a10 17:59:49 SYS@ prod >col tablespace_name for a10 17:59:59 SYS@ prod >select segment_name,partition_name,tablespace_name from dba_segments 18:00:32 2 where segment_name in ('T1','T1_INDX') order by 2; SEGMENT_NAME PARTITION_ TABLESPACE -------------------- ---------- ---------- T1 P1 USERS T1_INDX P1 USERS T1_INDX P2 TBS2_INDX T1 P2 TBS2 T1_INDX P3 TBS3_INDX T1 P3 TBS3 6 rows selected. 拷贝备份数据文件到数据库下，进行数据导入 [oracle@RH6 oradata]$ cp /home/oracle/data/tbs1*.dbf /u01/app/oracle/oradata/prod/ [oracle@RH6 data]$ impdp system/oracle directory=tbs_dir dumpfile=p1.dmp transport_datafiles='/u01/app/oracle/oradata/prod/tbs1.dbf','/u01/app/oracle/oradata/prod/tbs1_indx.dbf' logfile=imp.log Import: Release 11.2.0.1.0 - Production on Tue Nov 18 18:06:22 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=tbs_dir dumpfile=p1.dmp transport_datafiles=/u01/app/oracle/oradata/prod/tbs1.dbf,/u01/app/oracle/oradata/prod/tbs1_indx.dbf logfile=imp.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:06:37 数据导入成功 18:01:03 SYS@ prod >select segment_name,partition_name,tablespace_name from dba_segments 18:07:37 2 where segment_name in ('T1_TMP','T1_TMP_INDX'); SEGMENT_NAME PARTITION_ TABLESPACE -------------------- ---------- ---------- T1_TMP TBS1 T1_TMP_INDX TBS1_INDX 18:09:40 SCOTT@ prod >alter table t1 exchange partition p1 with table t1_tmp including indexes; Table altered. 18:08:15 SYS@ prod >select segment_name,partition_name,tablespace_name from dba_segments 18:10:46 2 where segment_name in ('T1','T1_INDX') order by 2; SEGMENT_NAME PARTITION_ TABLESPACE -------------------- ---------- ---------- T1 P1 TBS1 T1_INDX P1 TBS1_INDX T1_INDX P2 TBS2_INDX T1 P2 TBS2 T1_INDX P3 TBS3_INDX T1 P3 TBS3 6 rows selected. 访问正常（索引亦导入成功） 18:12:07 SCOTT@ prod >col name for a50 18:12:19 SCOTT@ prod >r 1* select * from t1 where id=4 ID NAME ---------- -------------------------------------------------- 4 C_OBJ# 4 TAB$ Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1229066337 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 1030 | 1 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 2 | 1030 | 1 (0)| 00:00:01 | 1 | 1 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T1 | 2 | 1030 | 1 (0)| 00:00:01 | 1 | 1 | |* 3 | INDEX RANGE SCAN | T1_INDX | 1 | | 1 (0)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"=4) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 524 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed 18:11:05 SYS@ prod >alter tablespace tbs1 read write; Tablespace altered. Elapsed: 00:00:02.10 18:14:34 SYS@ prod >alter tablespace tbs1_indx read write; Tablespace altered.

三、备份载入的原介质

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the ACID properties (Atomicity, Consistency, Isolation, Durability).Apr 16, 2025 am 12:20 AM

ACID attributes include atomicity, consistency, isolation and durability, and are the cornerstone of database design. 1. Atomicity ensures that the transaction is either completely successful or completely failed. 2. Consistency ensures that the database remains consistent before and after a transaction. 3. Isolation ensures that transactions do not interfere with each other. 4. Persistence ensures that data is permanently saved after transaction submission.

MySQL: Database Management System vs. Programming LanguageApr 16, 2025 am 12:19 AM

MySQL is not only a database management system (DBMS) but also closely related to programming languages. 1) As a DBMS, MySQL is used to store, organize and retrieve data, and optimizing indexes can improve query performance. 2) Combining SQL with programming languages, embedded in Python, using ORM tools such as SQLAlchemy can simplify operations. 3) Performance optimization includes indexing, querying, caching, library and table division and transaction management.

MySQL: Managing Data with SQL CommandsApr 16, 2025 am 12:19 AM

MySQL uses SQL commands to manage data. 1. Basic commands include SELECT, INSERT, UPDATE and DELETE. 2. Advanced usage involves JOIN, subquery and aggregate functions. 3. Common errors include syntax, logic and performance issues. 4. Optimization tips include using indexes, avoiding SELECT* and using LIMIT.

MySQL's Purpose: Storing and Managing Data EffectivelyApr 16, 2025 am 12:16 AM

MySQL is an efficient relational database management system suitable for storing and managing data. Its advantages include high-performance queries, flexible transaction processing and rich data types. In practical applications, MySQL is often used in e-commerce platforms, social networks and content management systems, but attention should be paid to performance optimization, data security and scalability.

SQL and MySQL: Understanding the RelationshipApr 16, 2025 am 12:14 AM

The relationship between SQL and MySQL is the relationship between standard languages and specific implementations. 1.SQL is a standard language used to manage and operate relational databases, allowing data addition, deletion, modification and query. 2.MySQL is a specific database management system that uses SQL as its operating language and provides efficient data storage and management.

Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

See all articles