InnoDB modifies the table shared space to an independent Space

Recently, the mysqlinnodb storage engine has been optimized to convert the shared tablespace to an independent tablespace. I didn't think so much at the beginning, so I had to promote it in a short time, So I optimized it,

Recently, the mysql innodb Storage engine was optimized to convert the shared tablespace into an independent tablespace. I didn't think so much at the beginning, so I had to promote it in a short time, So I optimized it,

Recently, the mysql innodb Storage engine was optimized to convert the shared tablespace into an independent tablespace. I didn't think so much at the beginning, so I had to promote it in a short time. So I want to optimize it and see how it works. Describes a conversion process.

1. Check whether the table space is shared or independent.

Mysql> show variables like '% per_table % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Innodb_file_per_table | OFF |
+ ----------------------- + ------- +
1 row in set (0.00 sec)
If it is OFF, it is definitely not an independent tablespace. If it is ON, it is not necessarily an independent tablespace. The most direct method is to view the files on the hard disk, independent tablespace, and each table corresponds to a space.

[Root @ localhost tg] # ll
Total usage 64
-Rw ----. 1 mysql 65 December 30 20:09 db. opt
-Rw ----. 1 mysql 8658 December 30 23:17 gb. frm
-Rw ----. 1 mysql 8658 December 30 23:19 qr. frm
-Rw ----. 1 mysql 8658 December 30 23:19 qy. frm
-Rw ----. 1 mysql 8658 December 30 23:19 tg. frm
-Rw ----. 1 mysql 8658 December 30 23:19 xcy. frm
Tg is a database name, which contains innodb. In this case, the table space is shared.

2. Stop mysql.

/Etc/init. d/mysqld stop
3. modify the configuration file of my. cnf.

Innodb-file-per-table = 1
4. Back up databases using the innodb Engine

Mysqldump-u tg-p tg>/home/6fan/tg. SQL;
5. Delete innodb databases and log files.

Cd/var/lib/mysql // database file location
Rm-f ib * // delete logs and Space
Rm-rf tg // Delete the database folder using the innodb Engine
If the innodb database folder is not deleted, the innodb engine cannot be started. I checked the error log. As follows:

111231 20:54:44 InnoDB: Log file./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file./ib_logfile0 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
111231 20:54:50 InnoDB: Log file./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file./ib_logfile1 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
InnoDB: Cannot initialize created log files because
InnoDB: data files are created upt, or new data files were
InnoDB: created when the database was started previous
InnoDB: time but the database was not shut down
InnoDB: normally after that.
111231 20:54:55 [ERROR] Plugin 'innodb' init function returned error.
111231 20:54:55 [ERROR] Plugin 'innodb' registry as a storage engine failed.
111231 20:54:55 [Note] Event schedents: Loaded 0 events

6. Start mysql

/Etc/init. d/mysqld start
7. Import the database

Mysql-u root-p 8. Check whether the conversion is complete.

// Query after entering mysql
Mysql> show variables like '% per_table % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Innodb_file_per_table | ON |
+ ----------------------- + ------- +
1 row in set (0.00 sec)
// View files in the database directory
[Root @ localhost tg] # ll
Total usage 544
-Rw ----. 1 mysql 65 December 31 22:48 db. opt
-Rw ----. 1 mysql 8658 December 31 22:49 gb. frm
-Rw ----. 1 mysql 98304 December 31 22:49 gb. ibd
-Rw ----. 1 mysql 8658 December 31 22:49 qr. frm
-Rw ----. 1 mysql 98304 December 31 22:49 qr. ibd
-Rw ----. 1 mysql 8658 December 31 22:49 qy. frm
-Rw ----. 1 mysql 98304 December 31 22:49 qy. ibd
-Rw ----. 1 mysql 8658 December 31 22:49 tg. frm
-Rw ----. 1 mysql 98304 December 31 22:49 tg. ibd
-Rw ----. 1 mysql 8658 December 31 22:49 xcy. frm
-Rw ----. 1 mysql 98304 December 31 22:49 xcy. ibd
From this we can see that each table has a. ibd file, and the root shared tablespace is different. The configuration is complete here.

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