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

Source: Internet
Author: User
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
  2. Rm-f ib * // delete logs and Space
  3. 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)
  9. // View files in the database directory
  10. [Root @ localhost TG] # ll
  11. Total usage 544
  12. -RW ----. 1 MySQL 65 December 31 22:48 dB. Opt
  13. -RW ----. 1 MySQL 8658 December 31 22:49 GB. FRM
  14. -RW ----. 1 MySQL 98304 December 31 22:49 GB. IBD
  15. -RW ----. 1 MySQL 8658 December 31 22:49 QR. FRM
  16. -RW ----. 1 MySQL 98304 December 31 22:49 QR. IBD
  17. -RW ----. 1 MySQL 8658 December 31 22:49 QY. FRM
  18. -RW ----. 1 MySQL 98304 December 31 22:49 QY. IBD
  19. -RW ----. 1 MySQL 8658 December 31 22:49 TG. FRM
  20. -RW ----. 1 MySQL 98304 December 31 22:49 TG. IBD
  21. -RW ----. 1 MySQL 8658 December 31 22:49 xcy. FRM
  22. -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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.