Replace tablespaces in InnoDB of MySQL

Today, when migrating the zabbix database, a shared tablespace cannot be released. because mysql uses the innodb Storage engine by default, when a large data volume is deleted, because of the Force interruption, the shared tablespace cannot be released, and the database to be deleted cannot be deleted. As a result, the shared tablespace still contains information about the database that has not been completely deleted. As a result, the tablespace cannot be released.

The correct steps are as follows:
Reconfigure the shared tablespace
1. view the existing database

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Test |
| Zhujiangtao |
+ -------------------- +
4 rows in set (0.00 sec)

2. Back up the databases whose storage engines are innodb. Of course, if there are other databases, you also need to back up the databases. This article only applies to the innodb Storage engine.

[Root @ zhu2 var] # mysqldump zhujiangtao>/tmp/zhu. SQL # use specific parameters for mysqldump as needed

3. Close the mysql database

[Root @ zhu2 ~] # Service mysqld stop
Shutting down MySQL... [OK]

4. Delete the shared tablespace and log files of the innodb Storage engine and the. frm files of tables using the innodb Storage engine.

[Root @ zhu2 var] # ll/opt/mysql/var/
Total 45880
-Rw ---- 1 mysql 35651584 10-18 ibdata1
-Rw ---- 1 mysql 5242880 10-18 ib_logfile0
-Rw ---- 1 mysql 5242880 10-18 ib_logfile1
[Root @ zhu2 var] # rm-rf ibdata1 # delete a tablespace
[Root @ zhu2 var] # rm-rf ib_logfile * # delete a log file
[Root @ zhu2 var] # rm-rf zhujaingtao/student. frm # Delete. frm of the innodb table
File

5. reconfigure the shared tablespace

Innodb_data_file_path = ibdata1: 100 M: autoextend

For more details, please continue to read the highlights on the next page:

Recommended reading:

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

1 2 Next Page