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

This article is to share with you about MySQL innodb shared table space newly added table space data file method, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article, without saying much, follow the editor to have a look.

When innodb uses shared management tablespaces (innodb_file_per_table=off), there is only one data file ibdata1 and the tablespace is almost full. In this case, additional tablespaces need to be added to expand capacity. The methods are as follows:

1. Stop the mysql service

two。 Backup configuration fil

3. Edit the value of innodb _ data_file_path in my.cnf

Change ibdata1:$size;ibdataN:$size; according to innodb_data_file_path. The table space currently defined by ibdataN:$size; or the default table space cannot be changed, otherwise the startup fails, but additional table spaces can be added, and the ibdataN sequence is incremented according to the current number.

$size customization.

4. Start the mysql service

Examples are as follows:

MySQL [test] > show variables like 'innodb_file_per_table'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_file_per_table | OFF |

+-+ +

1 row in set (0.00 sec)

View the path to the innodb data file:

MySQL [test] > show variables like 'innodb_data%'

+-+

| | Variable_name | Value |

+-+

| | innodb_data_file_path | ibdata1:12M:autoextend |

| | innodb_data_home_dir |

+-+

2 rows in set (0.00 sec)

Data file path:

MySQL [test] > show variables like 'datadir'

+-+ +

| | Variable_name | Value |

+-+ +

| | datadir | / data/mysql/ |

+-+ +

1 row in set (0.00 sec)

Stop the mysql service:

[root@chenfeng mysql] # service mysql stop

Shutting down MySQL. SUCCESS!

Modify / usr/local/mysql/my.cnf, change innodb_file_per_table to shared management tablespace, and add two new data files, ibdata2 and ibdata3, to automatically extend:

Innodb_file_per_table=off

Innodb_data_file_path = ibdata1:12M;ibdata2:100M;ibdata3:300M:autoextend

Start the mysql database:

[root@chenfeng mysql] # service mysql start

Starting MySQL... SUCCESS!

Check the data directory and find that the new data file is successful:

-bash-4.2$ cd / data/mysql

-bash-4.2$ ll ibdata*

-rw-rw----. 1 mysql mysql 12582912 August 25 16:02 ibdata1

-rw-rw----. 1 mysql mysql 104857600 August 25 16:02 ibdata2

-rw-rw----. 1 mysql mysql 314572800 August 25 16:02 ibdata3

MySQL [test] > show variables like 'innodb_data%'

+-+-

| | Variable_name | Value |

+-+-

| | innodb_data_file_path | ibdata1:12M;ibdata2:100M;ibdata3:300M:autoextend |

| | innodb_data_home_dir |

+-+-

2 rows in set (0.00 sec)

The above is what the new tablespace data file method of MySQL innodb shared tablespaces is like. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.