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

This article mainly explains the "MySQL disk space is not enough to do", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL disk space is not enough how to do" it!

Most databases have the ability to store data file extensions, ORACLE can expand your tablespace, SQL SERVER can build a few more FILEGROUP, and PostgreSQL can build similar extensions. These are to deal with the current lack of storage space to store data files across physical locations.

MYSQL itself has supported related extensions since 5.6. in fact, very few people use them. Today, let's talk about the related extensions. The version is based on the professional name of MYSQL 5.7 and MySQL called external tables.

The first use of a technology is purposeful, and external tables that use MYSQL are mainly for the following reasons

1 there is not enough storage space. A large table needs more storage space.

2 the storage media is updated, and some tables need to be stored in faster storage media

Let's take a look at what to do. At present, the mysql directory and the mysql_extend directory are mounted under the data directory. At present, the data is stored in the mysql directory. We need to store all the newly created tables in the employees library not in the mysql directory, but in the new location / data/mysql_extend/employees directory.

Here are several ways

1, only for the data of the new table, stored in the new physical location

CREATE TABLE `d_ extend` (

-> `dept_ no` char (4) NOT NULL

-> `dept_ name` varchar (40) NOT NULL

-> PRIMARY KEY (`dept_ no`)

-> UNIQUE KEY `dept_ name` (`dept_ name`)

->) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DATA DIRECTORY ='/ data/mysql_extend'

We can see that the physical file has reached the specified location

An isl file will be generated in the original directory

The isl file actually contains only the storage location of the actual ibd file.

In practical applications, there may be such a situation, that is, we have replaced the SSD disk.

But how to safely migrate the physical files of the table to the new SSD disk.

Let's move on, for example, we want to migrate the emplyees table to the SSD disk environment, and the corresponding directory of the SSD disk environment is / data/mysql_extend

1 We create a tablespace and specify it under / data/mysql_extend

Create tablespace employees add datafile'/ data/mysql_extend/employees/employees.ibd' engine = innodb

Migrate data to a new tablespace between 2

In the above two figures, you can see that the ibd file has been migrated from the original directory to the new location, but the FRM file remains in the original directory.

In addition, it should be noted that if it is MGR, replication, etc., make sure that the directories on other clusters are the same and are doing relevant operations.

In addition, we can find two problems by looking at the tablespace.

Select * from INNODB_SYS_TABLESPACES

The file format of the newly established table space is any row_format is also any and space_type is genernal, indicating that the two problems 1 this table space is not per_table, is a common storage space for multiple tables, which is consistent with other databases.

In addition, according to official documents, if multiple tables are stored in one table space, one table space relative to one table has the following problems

1 does not support the way to move tables quickly as mentioned on Tuesday, that is, table discard

2. The partition table misuses this method.

3 official documents show that because multiple tables share a table space, it will be better than a single form file in metadata reading, but it does not provide specific data and related experimental proof.

Thank you for your reading, the above is the "MySQL disk space is not enough to do" content, after the study of this article, I believe you have a deeper understanding of the MySQL disk space is not enough to do this problem, the specific use of the need for you to practice and verify. 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.