Shulou(Shulou.com)06/01 Report--

Mysql, will involve a variety of tablespace concepts, although many aspects of these concepts and Oracle have similarities, but there are also many differences, beginners are easy to be confused by these concepts, thus, confuse the differences and understanding of these concepts, the following, a brief introduction and explanation of these tablespace concepts.

1. System tablespace (System Tablespace)

Innodb system tablespaces contain innodb data dictionaries (metadata for innodb-related objects), while double write buffers (doublewrite buffer), change buffers (change buffer), and undo logs (undo logs) are also stored in the system tablespaces. In addition, the system tablespace

It also contains data such as tables and indexes that users create in this tablespace. Because the system tablespace can store multiple tables, it is a shared tablespace. The system tablespace consists of one or more data files that, by default, contain a system data file called ibdata1, located in mysql

Under the data directory (datadir). The location, size, and number of system tablespace data files are controlled by the innodb_data_home_dir and innodb_data_file_path startup options. Specific examples for different scenarios are as follows:

1) Parameter innodb_data_home_dir is not configured, only parameter innodb_data_file_path is configured:

Innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend

Note: the system tablespace contains two data files, ibdata1 and ibdata2, both of which are located in the datadir directory.

2) parameters innodb_data_home_dir and innodb_data_file_path are configured:

Innodb_data_home_dir = / data

Innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend

Note: the system tablespace contains two data files, ibdata1 and ibdata2, both of which are located in the / data directory.

3) the parameter innodb_data_home_dir position is an empty string, and only parameter innodb_data_file_path is configured:

Innodb_data_home_dir =

Innodb_data_file_path=/d1/ibdata1:1024M;/d2/ibdata2:1024M:autoextend

Note: the system tablespace contains two data files, ibdata1 and ibdata2, ibdata1 is located in / D1 directory, and ibdata2 is located in / D2 directory.

two。 Table file tablespace (File-Per-Table Tablespaces)

A table file tablespace is a single table table space that is created in its own data file, not in the system table space. When the innodb_file_per_table option is turned on, the table is created in the table file tablespace. Otherwise, innodb will be created in the system tablespace.

Each table file tablespace is represented by an .ibd data file, which is created by default in the corresponding database directory. Table file tablespaces support dynamic (DYNAMIC) and compressed (commpressed) row formats.

3. Common tablespaces (General Tablespaces)

Common tablespaces are shared tablespaces created by create tablespace syntax. Common tablespaces can be created in other tablespaces outside the mysql data directory, which can hold multiple tables and support all row formats.

Through create table tab_name... The tablespace [=] tablespace_name or alter table tab_name tablespace [=] tablespace_name syntax adds it to the common tablespace.

4.undo tablespace (undo tablespace)

The undo tablespace consists of one or more files that contain undo logs. The innodb_undo_tablespace configuration option controls the number of undo table spaces. The undo tablespace is created in the location determined by the innodb_undo_directory configuration option, which is typically used to place undo logs in different

On the storage device. If no path is determined by this option, the undo tablespace is created by default under the mysql data directory (datadir).

5. Temporary tablespace (Temporary Tablespace)

User-created and disk-on-disk temporary tables are created in a shared temporary tablespace. The innodb_temp_data_file option determines the relative path, name, size, and attributes of the temporary tablespace data file. If no value is determined by this option, by default, the system will be specified in the

Create an automatically extended data file called ibtmp1 under the directory determined by innodb_data_home_dir, which will be slightly larger than 12m.

When the mysql server shuts down normally or terminates initialization abnormally, the temporary tablespace is removed and the mysql server is recreated each time it starts. When a temporary tablespace is created, it is given a dynamically generated space ID (space ID). If you cannot create a temporary tablespace

Mysql server startup will be rejected. In the case of abnormal termination of the mysql server, the temporary tablespace will not be removed. In this case, DBA can manually remove temporary tablespaces or restart the mysql server, which will automatically remove and recreate temporary tablespaces during server restart.

Temporary tablespaces cannot be stored on bare devices.

Now that we're talking about innodb_data_home_dir, let's talk about this option, which determines the common part of the directory path of the innodb system tablespace data file. When innodb_file_per_table is turned on, this option setting does not affect the location of the table file tablespace. Default value for this option

Is the mysql data directory. If you set this option to an empty string, you can set an absolute path value for innodb_data_file_path. In addition, when you specify a value for innodb_data_home_dir, you need to add a slash to the tail.

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.