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

I don't know if you have any knowledge about the articles like MySQL that specify the best way to store directories for table data files. Today, I'm here to tell you briefly. If you are interested, let's take a look at the body. I believe you will gain something after reading the best way to specify table data files to store directories in MySQL.

1. Background

* create a data table in MYSQL, and there is a .frm file corresponding to the database directory of its data catalog. The .frm file is used to store the meta information of each data table, including the definition of the table structure, etc., and the .frm file has nothing to do with the database storage engine, that is, the data table of any storage engine must have a .frm file, named as the data table name .frm, such as user.frm. The .frm file can be used to recover the table structure when the database crashes.

* MySQL files include the database files created by MySQL and the database files created by the engine used by MySQL.

* .frm files are independent of the operating system and database engine, and there is a file with the same name as the table.

* files of MyISAM engine:

* .myd is my data, table data file

* .myi is my index, index file

* .log log file.

* files of InnoDB engine:

* use table space (tablespace) to manage data and store table data and indexes

* InnoDB database files (i.e. InnoDB fileset, ib-file set).

* ibdata1, ibdata2, etc.: system tablespace files. When innodb_file_per_table is not enabled, InnoDB system information and user database table data and indexes are stored. All tables are shared.

* .ibd file: when innodb_file_per_table is enabled, a single tablespace file is used for each table. Each table uses a tablespace file (file per table) to store user database table data and indexes.

* Log files: ib_logfile1, ib_logfile2.

* the specified directory must be the full pathname of the directory, not a relative path.

2. MySQL environment

Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > show variables like 'version';+-+-+ | Variable_name | Value | +-+-+ | version | 5.7.18 | +-+-+ 1 row in set (0.01 sec) mysql > show variables like' datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / data/mysql_data/ | +-- -+ 1 row in set (0.04 sec) mysql > show variables like 'innodb_file_per%' +-+-+ | Variable_name | Value | +-+-+ | innodb_file_per_table | ON | +-+-+ 1 row in set (0.02 sec)

3. Example of MyISAM engine specifying table data file storage directory

* create a table data file storage directory

[root@MySQL ~] # mkdir-v / test_myisammkdir: created directory `/ test_myisam'

* View mysqld running users

[root@MySQL ~] # ps aux | grep mysqld | grep-v greproot 1468 0.0 110400 1532? S 16:00 0:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/data/mysql_data-- pid-file=/data/mysql_data/MySQL.pidmysql 1614 0.2 4.9 1309380 194788? Sl 16:00 0:04 / usr/local/mysql/bin/mysqld-basedir=/usr/local/mysql-datadir=/data/mysql_data-plugin-dir=/usr/local/mysql/lib/plugin-user=mysql-log-error=/data/mysql_data/error.log-pid-file=/data/mysql_data/MySQL.pid

* modify the users and groups to which the directory belongs to run users for mysql [this step is required]

[root@MySQL ~] # chown-v mysql.mysql / test_myisam changed ownership of `/ test_myisam' to mysql:mysql

* create table test_myisam and specify the directory where data files and index files are stored

[table data files and index files in MyISAM engine are stored separately, and need to be specified]

CREATE TABLE test_myisam (- > id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> name VARCHAR (64) NOT NULL->) ENGINE=MYISAM DATA DIRECTORY='/test_myisam' INDEX DIRECTORY='/test_myisam' DEFAULT CHARSET=utf8mb4

* View table data files and table structure files

[you can see that in the MyISAM storage engine, the specified table data directory is implemented through soft links]

Mysql > select database () +-+ | database () | +-+ | mytest | +-+ 1 row in set (0.00 sec) mysql > system ls-l / data/mysql_data/mytesttotal 16When Rwashi r-1 mysql mysql 67 Jul 5 16:30 db.opt-rw-r- 1 mysql mysql 8586 Jul 5 16:37 test_myisam.frmlrwxrwxrwx 1 mysql mysql 28 Jul 5 16:37 test_myisam.MYD- > / test_myisam/test_myisam.MYDlrwxrwxrwx 1 mysql mysql 28 Jul 5 16:37 test_myisam.MYI-> / test_myisam/test_myisam.MYImysql > system ls-l / test_myisamtotal 4 Jul r-1 mysql mysql 0 Jul 5 16:37 test_myisam.MYD-rw-r- 1 mysql mysql 1024 Jul 5 16:37 test_myisam.MYI

4. Example of InnoDB engine specifying table data file storage directory

* create a table data file storage directory

[root@MySQL ~] # mkdir-v / test_innodbmkdir: created directory `/ test_innodb'

* View mysqld running users

[root@MySQL ~] # ps aux | grep mysqld | grep-v greproot 1468 0.0 110400 1532? S 16:00 0:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/data/mysql_data-- pid-file=/data/mysql_data/MySQL.pidmysql 1614 0.1 5.0 1309380 196576? Sl 16:00 0:04 / usr/local/mysql/bin/mysqld-basedir=/usr/local/mysql-datadir=/data/mysql_data-plugin-dir=/usr/local/mysql/lib/plugin-user=mysql-log-error=/data/mysql_data/error.log-pid-file=/data/mysql_data/MySQL.pid

* modify the users and groups to which the directory belongs to run users for mysql [this step is required]

[root@MySQL ~] # chown-v mysql.mysql / test_innodbchanged ownership of `/ test_innodb' to mysql:mysql

* create table test_innodb and specify the directory where the data files are stored

[table data file and index file in InnoDB engine are stored together, and you can specify the data file storage directory]

Mysql > CREATE TABLE test_innodb (- > id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> name VARCHAR (64) NOT NULL->) ENGINE=INNODB DATA DIRECTORY='/test_innodb' DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.06 sec)

* View table data files

[you can see that an .isl file is generated in InnoDB, which records the table data file to store the absolute path]

Mysql > system ls-l / test_innodbtotal 4drwxr test_innodb.ibdmysql-2 mysql mysql 4096 Jul 5 16:47 mytestmysql > system ls-l / test_innodb/mytesttotal 96 RWMurray r-1 mysql mysql 98304 Jul 5 16:47 test_innodb.ibdmysql > select database () +-+ | database () | +-+ | mytest | +-+ 1 row in set (0.00 sec) mysql > system ls-l / data/mysql_data/mytesttotal 20 Rwashi r-1 mysql mysql 67 Jul 5 16:30 db.opt-rw-r- 1 mysql mysql 8586 Jul 5 16:47 test_innodb.frm-rw-r -1 mysql mysql 35 Jul 5 16:47 test_innodb.islmysql > system cat / data/mysql_data/mytest/test_innodb.isl/test_innodb/mytest/test_innodb.ibd

5. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

After reading the article in MySQL that specifies the best way to store directories in table data files, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.