MySQL DDL operation--------Specify table data file directory best Practice

1. Background

* in MySQL, create a data table, in its data directory corresponding to the database directory has a corresponding table of. frm files,. frm files are used to hold each data table metadata (meta) information, including the definition of table structure, etc.. frm files are not related to the database storage engine, That is, any data table for the storage engine must have a. frm file, named by the data table name. frm, such as the user.frm. frm file, can be used to restore the table structure when the database crashes.

* mysql files include database files built by MySQL and database files created by MySQL engine.

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

* MyISAM engine files:

* . MyD that is my data, the table file

* . Myi that is my index, indexed files

* . log file.

* InnoDB engine files:

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

* InnoDB Database file (that is, InnoDB file set, Ib-file set).

* ibdata1, IBDATA2, etc.: System tablespace file, when innodb_file_per_table is not turned on, stores InnoDB system Information and user database table data and indexes, all tables are shared.

* . ibd file: When Innodb_file_per_table is turned on, a single table tablespace file, each table uses a tablespace file (file per table), which holds the user database table data and indexes.

* log files: Ib_logfile1, Ib_logfile2.

* The specified directory must be the full path name of the directory, not the 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. MyISAM engine Specifies table data file storage Directory example

* CREATE TABLE data file storage directory

[Email protected] ~]# mkdir-v/test_myisammkdir:created directory '/test_myisam '

* View mysqld running users

[[Email protected] ~]# PS aux | grep mysqld |        Grep-v greproot 1468 0.0 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.P      Idmysql 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 directory belongs to user and group for MySQL running user [This step must]

[Email protected] ~]# chown-v Mysql.mysql/test_myisam changed ownership of '/test_myisam ' to Mysql:mysql

* CREATE TABLE Test_myisam and specify the data file and index file to store the directory

[The table data file in the MyISAM engine is stored separately from the index file, which needs to be specified separately]

CREATE TABLE Test_myisam (-id BIGINT PRIMARY KEY not NULL auto_increment, name VARCHAR (+) NOT NULL-& Gt ) 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 by means of a soft link ]

Mysql> select database (); +------------+| database ()  |+------------+| mytest      |+------------+1 row in set  (0.00 sec) Mysql> system  ls -l /data/mysql_data/mytesttotal 16-rw-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-rw-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. InnoDB engine Specifies table data file storage Directory example

* CREATE TABLE data file storage directory

[Email protected] ~]# mkdir-v/test_innodbmkdir:created directory '/test_innodb '

* View mysqld running users

[[email protected] ~]# ps aux | grep  mysqld | grep -v greproot       1468  0.0   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 directory belongs to user and group for MySQL running user [This step must]

[Email protected] ~]# chown-v mysql.mysql/test_innodbchanged ownership of '/test_innodb ' to Mysql:mysql

* CREATE TABLE Test_innodb and specify the data file to store the directory

[InnoDB engine table data file and index file merge, specify data file to store directory]

Mysql> CREATE TABLE test_innodb (id BIGINT PRIMARY KEY not NULL auto_increment, name VARCHAR NULL-Engine=innodb DATA directory= '/test_innodb ' DEFAULT charset=utf8mb4; Query OK, 0 rows affected (0.06 sec)

* View table data file

[You can see that the <TABLE_NAME>.ISL file is generated in InnoDB, which holds the absolute path for the file record table data file]

Mysql> system ls -l /test_innodbtotal 4drwxr-x--- 2 mysql mysql  4096 jul  5 16:47 mytestmysql> system ls -l /test_innodb /mytesttotal 96-rw-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-rw-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

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1945444

MySQL DDL operation--------Specify table data file directory best Practice