[go: up one dir, main page]
Scribd
Upload
11 views2 pages

MySQL Tablespaces POC

This document provides a proof of concept (POC) for MySQL Tablespaces, focusing on their types and functionalities, particularly the Undo Tablespace used for rollback operations. It includes examples of how to check and shrink tablespaces, as well as interview questions related to the topic. The document is aimed at users ranging from beginner to advanced DBA levels and is applicable to MySQL versions 5.7 and 8.x.

Uploaded by

ljana6601
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
11 views2 pages

MySQL Tablespaces POC

This document provides a proof of concept (POC) for MySQL Tablespaces, focusing on their types and functionalities, particularly the Undo Tablespace used for rollback operations. It includes examples of how to check and shrink tablespaces, as well as interview questions related to the topic. The document is aimed at users ranging from beginner to advanced DBA levels and is applicable to MySQL versions 5.7 and 8.x.

Uploaded by

ljana6601
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

MySQL Tablespaces with Undo Logs and Rollback - POC Documentation

MySQL Tablespaces with Undo Logs and Rollback - Full POC Documentation

Version: MySQL 5.7 / 8.x

DBA Level: Beginner to Advanced

Prepared by: Alien Thoughts

1. Introduction

Tablespaces are physical storage units in MySQL that store data and logs based on their

functionality.

2. Types of Tablespaces

- System Tablespace: Stores metadata and dictionary data.

- Undo Tablespace: Used for rollback operations.

- General Tablespace: Stores user tables.

- Temporary Tablespace: Used for sorting and JOIN queries.

- Redo Log Tablespace: Used for crash recovery.

3. Real-Time Scenario (Undo Tablespace)

1. Start Transaction

2. Update Salary

3. Old Data is stored in Undo Tablespace

4. If Rollback is issued, Old Data will be restored.

4. How to Check Tablespaces

Example Query:
SELECT tablespace_name, file_name, file_size

FROM information_schema.files;

5. How to Shrink Undo Tablespace

Example Command:

SET GLOBAL innodb_undo_log_truncate = ON;

6. Interview Questions

1. What is Undo Tablespace?

It stores old data for rollback operations.

2. How Many Undo Tablespaces are there in MySQL?

2 by default.

3. Which Tablespace Stores Temporary Data?

Temporary Tablespace.

You might also like