Detailed description of InnoDB shared tablespace to independent tablespace and problems encountered

Detailed description of InnoDB shared tablespace to independent tablespace and problems encountered

Zhang Ying posted on

CATEGORY Directory: MySQL

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.

View copy print?

1. Mysql> show variables like '% per_table % ';
2. + ----------------------- + ------- +
3. | Variable_name | value |
4. + ----------------------- + ------- +
5. | Innodb_file_per_table | off |
6. + ----------------------- + ------- +
7. 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.

1. [Root @ localhost TG] # ll
2. Total usage 64
3. -RW ----. 1 MySQL 65 December 30 20:09 dB. Opt
4. -RW ----. 1 MySQL 8658 December 30 23:17 GB. FRM
5. -RW ----. 1 MySQL 8658 December 30 23:19 QR. FRM
6. -RW ----. 1 MySQL 8658 December 30 23:19 QY. FRM
7. -RW ----. 1 MySQL 8658 December 30 23:19 TG. FRM
8. -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.

1. /Etc/init. d/mysqld stop

3. modify the configuration file of my. CNF.

1. InnoDB-file-per-table = 1

4. Back up databases using the InnoDB Engine

1. Mysqldump-u Tg-P Tg>/home/6fan/TG. SQL;

5. Delete InnoDB databases and log files.

View copy print?

1. CD/var/lib/MySQL // database file location
3. Rm-f ib * // delete logs and Space
5. 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

1. /Etc/init. d/mysqld start

7. Import the database

1. Mysql-u root-P

8. Check whether the conversion is complete.

View copy print?

1. // Query after entering MySQL
2. Mysql> show variables like '% per_table % ';
3. + ----------------------- + ------- +
4. | Variable_name | value |
5. + ----------------------- + ------- +
6. | Innodb_file_per_table | on |
7. + ----------------------- + ------- +
8. 1 row in SET (0.00 Sec)
10. // View files in the database directory
11. [Root @ localhost TG] # ll
12. Total usage 544
13. -RW ----. 1 MySQL 65 December 31 22:48 dB. Opt
14. -RW ----. 1 MySQL 8658 December 31 22:49 GB. FRM
15. -RW ----. 1 MySQL 98304 December 31 22:49 GB. IBD
16. -RW ----. 1 MySQL 8658 December 31 22:49 QR. FRM
17. -RW ----. 1 MySQL 98304 December 31 22:49 QR. IBD
18. -RW ----. 1 MySQL 8658 December 31 22:49 QY. FRM
19. -RW ----. 1 MySQL 98304 December 31 22:49 QY. IBD
20. -RW ----. 1 MySQL 8658 December 31 22:49 TG. FRM
21. -RW ----. 1 MySQL 98304 December 31 22:49 TG. IBD
22. -RW ----. 1 MySQL 8658 December 31 22:49 xcy. FRM
23. -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.