Configuration of MySQL Innodb independent tablespace and its advantages and disadvantages
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!
