Check the tables that use the shared tablespace and the shared tablespace.

Check the tables that use the shared tablespace and the shared tablespace.
Check tables that use the shared tablespace

 

Recently I took over a new database machine and found that some tables in some databases do not have ibd files but frm files, while ibdata1 files have a total of 20 GB +. It is estimated that the shared tablespace is used.

Stored in the ibdata1 File

So I wrote a script to check which databases and tables under the instance use the shared tablespace. The principle is that if there is an frm file and there is no ibd file, it is considered that the shared tablespace is used.

#! /Bin/bash # Written by steven # Name: checktable1_space. sh # Version: v1.0 # Function: Check whether the table results using the shared tablespace are stored in/tmp/checkresult.txt # Create Date: 2016-08-27dbs = "school Report appeal bak zabbix" # enter the database dbpath to be checked = "/data/mysql" # enter the path of the instance>/tmp/checkresult.txt # Back Up for db in $ dbsdo cd $ {dbpath }$ {db} echo -------------- $ {db} --------------->/tmp/checkresult.txt for I in 'ls *. frm 'do p = 'echo $ I | awk-F '. frm'' {prin T $1} ''if [! -S $ p. ibd] then echo $ p>/tmp/checkresult.txt fi done echo>/tmp/checkresult.txt done

 

As we all know, there are many disadvantages for the shared tablespace, such as the inability to use the tablespace transmission, data compression and encryption, and the reorganization of the tablespace.

 

If anything is wrong, you are welcome to make a brick o

This article is copyrighted by the author and cannot be reproduced without the consent of the author.