Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL Innodb independent table space configuration and its advantages and disadvantages", the article explains the content is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "MySQL Innodb independent table space configuration and its advantages and disadvantages" bar!

Configuration of Innodb independent tablespaces

Environment introduction:

MySQL version: 5.5.40

1. Check to see if the independent tablespace is on

Mysql > show variables like'% per_table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_file_per_table | OFF |

+-+ +

1 row in set (0.00 sec)

Description: OFF stands for mysql is a shared tablespace

two。 Stop the mysql server:

[root@localhost ~] # / etc/init.d/mysqld stop

3. Modify the my.cnf file:

[mysqld]

Innodb-file-per-table=1

4. Start mysql

[root@localhost ~] # / etc/init.d/mysqld start

5. Verify that the function is turned on

Mysql > show variables like'% per_table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_file_per_table | ON |

+-+ +

1 row in set (0.00 sec)

How do I migrate to a separate tablespace?

1. If the online server needs to save data, add 2 more steps

a. Backing up data using mysqldump

b. Use mysql to recover the corresponding data

Reference link: http://blog.csdn.net/jacson_bai/article/details/44781033

two。 When deleting old files, delete both the ibdata*, and the corresponding log file ib_logfile*,. Otherwise, when you start MySQL, you will be prompted that the mysql.pid file is missing abnormally.

Reference link: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

Summary of independent tablespace learning

Advantages:

1. Each table in the database has its own independent tablespace, and the data and indexes of each table will exist in its own tablespace.

two。 It is possible to move a single table in different databases.

3. Quickly reclaim tablespaces

Shortcoming

1. Increase the number of system open_files

two。 The database file capacity of a single table is large, which leads to difficult storage planning and so on.

Note:

1. After innodb_file_per_table=1 is enabled, the innodb_open_files size must be adjusted reasonably. The minimum value of this parameter is 10, and the default is 300. it is a global parameter and cannot be adjusted dynamically.

two。 Adjust whether or not to use independent tablespaces according to your own business

Thank you for your reading, the above is the content of "MySQL Innodb independent tablespace configuration and its advantages and disadvantages". After the study of this article, I believe you have a deeper understanding of the configuration and advantages and disadvantages of MySQL Innodb independent tablespace, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.