Mysql Data File description

 

I read MySQL Log classification in a previous blog, and read MySQL data files.

MySQL Log File description

Http://blog.csdn.net/tianlesoftware/article/details/7028304

 

1. Mysql Data File description

First look at the file under MySQL datadirectory:

Here Dave and MySQL are our database names. We can view them in the MySQL database:

 

In MySQL, each database has a folder named after the database in the defined (or default) data directory to store various table data files in the database. Different MySQL storage engines have different data files and different storage locations.

Data Files of most storage engines are stored in the same directory as MyISAM data files, but the extensions of each data file are different. For example, MyISAM uses ". MYD" as the extension, InnoDB uses ". IBD", archive uses ". Arc", and CSV uses ". CSV.

 

1.1 ". IBD" and ibdata files

Both types of files are files that store InnoDB data. They are used to store InnoDB data (including indexes ), the reason is that the InnoDB data storage method can be configured to decide whether to use the shared tablespace to store the stored data or the exclusive tablespace to store the stored data. The exclusive tablespace storage method uses the ". IBD" file to store data, and each table has a ". IBD" file, which is stored in the same location as MyISAM data.

If you use a shared storage tablespace to store data, ibdata files are used for storage. One or more ibdata files can be used for all tables. The ibdata file can be configured using two parameters: innodb_data_home_dir and innodb_data_file_path. innodb_data_home_dir configures the directory where data is stored, while innodb_data_file_path configures the name of each file. Of course, the absolute path can be used for configuration directly when the innodb_data_file_path parameter is configured without innodb_data_home_dir.

Mysql> showvariables like 'innodb _ DATA % ';

+ ----------------------- + ------------------------ +

| Variable_name | value |

+ ----------------------- + ------------------------ +

| Innodb_data_file_path | ibdata1: 10 m: autoextend |

| Innodb_data_home_dir |

+ ----------------------- + ------------------------ +

2 rows in SET (0.01 Sec)

 

Multiple ibdata files can be configured at a time in innodb_data_file_path. The file size can be specified or automatically expanded, but InnoDB limits that only the last ibdata file can be configured as the automatic extension type. When you need to add a new ibdata file, you can only add it at the end of the innodb_data_file_path configuration, and you must restart MySQL to add ibdata.

 

1.2 ". frm" File

Table-related metadata (Meta) information is stored in the ". frm" file, including the definition information of the table structure. No matter what storage engine, each table will have a ". frm" file named after the table name. All ". frm" files are stored in the folder of the database.

 

1.3 ". MYD" File

The ". MYD" file is dedicated to the MyISAM storage engine and stores data in the MyISAM table. Each MyISAM table corresponds to a ". MYD" file, which is also stored in the folder of the database to which it belongs and is stored together with the ". frm" file.

 

1.4 ". myi" File

The ". myi" file is also designed for the MyISAM storage engine and mainly stores the index information of the MyISAM table. For MyISAM storage, the content that can be cached is mainly from the ". myi" file. Each MyISAM table corresponds to a ". myi" file, which is stored in the same location as ". frm" and ". MYD.

 

Summary:

MyISAM storage engine tables are stored in the database. Each table is stored as three physical files (FRM, MYD, and myi) named after the table name ). Each table has only three such files for the storage of MyISAM tables. That is to say, no matter how many indexes the table has, it is stored in the same. myi file.

This is clear at the beginning.

 

 

Ii. Change the MySQL data file directory

The default data file storage directory of MySQL is/var/lib/MySQL. The following shows how to modify the default data directory of MySQL.

 

2.1 create a new directory.

[Root @ rac2/] # mkdir-P/u01/MySQL

[Root @ rac2 lib] # chown mysql. MySQL/u01/MySQL

[Root @ rac2 lib] # chmod-r 777/u01/MySQL

 

2.2 stop MySQL

[Root @ rac2 lib] # service MySQL stop

Shutting down MySQL... [OK]

[Root @ rac2 lib] # ps-Ef | grep MySQL

Root 3955 3799 0 00:00:00 pts/2 grep MySQL

 

2.3 move directory

[Root @ rac2 lib] # mv/var/lib/MySQL/u01/

 

2.4 modify my. CNF configuration file

If my. in the CNF configuration file, go to/usr/share/MySQL/and find *. copy one of the CNF files to/etc/and change it to my. CNF. The command is as follows:
[Root @ rac2 MySQL] # cp/usr/share/MySQL/my-medium.cnf/etc/My. CNF

 

VI: Modify the sock file location of my. CNF:

# The MySQL Server

[Mysqld]

Port = 3306

# Socket =/var/lib/MySQL. Sock

Socket =/u01/MySQL. Sock

Skip-external-locking

Key_buffer_size = 16 m

# Max_allowed_packet = 1 m

Max_allowed_packet = 100 m

Table_open_cache = 64

Sort_buffer_size = 512 K

Net_buffer_length = 8 K

Read_buffer_size = 256 K

Read_rnd_buffer_size = 512 K

Myisam_sort_buffer_size = 8 m

 

2.5 modify the Startup Script:/etc/init. d/myql

[Root @ rac2 MySQL] # vi/etc/init. d/MySQL

Datadir =/u01/MySQL

 

2.6 start MySQL

[Root @ rac2 MySQL] # service MySQL start

Starting MySQL... [OK]

 

Started successfully. view the process:

[Root @ rac2 MySQL] # ps-Ef | grep MySQL

Root 11512 1 2 00:00:00 pts/2/bin/sh/usr/bin/mysqld_safe -- datadir =/u01/MySQL -- PID-file =/u01/MySQL/rac2.pid

MySQL 11769 11512 5 00:00:00 pts/2/usr/sbin/mysqld -- basedir =/usr -- datadir =/u01/MySQL -- Plugin-Dir =/usr/lib64/MySQL/plugin -- user = MySQL -- log-error =/u01/MySQL/rac2.err -- PID-file =/u01/MySQL/rac2.pid -- socket =/u01/MySQL. sock -- Ports = 3306

Root 11796 3799 0 00:00:00 pts/2 grep MySQL

 

2.7 minor episodes:

 

Previously assigned the 755 permission to the/u01/MySQL directory, but the following error is returned:

[Root @ rac2 MySQL] # service MySQL start

Starting MySQL... the server quit withoutupdating PID file [failed] ysql/rac2.pid ).

 

View log:/u01/MySQL/rac2.err

111130 20:59:34 [note]/usr/sbin/mysqld: shutdown complete

 

111130 20:59:34 mysqld_safe mysqld from pidfile/u01/MySQL/rac2.pid ended

111130 21:02:25 mysqld_safe starting mysqlddaemon with databases from/u01/MySQL

/Usr/sbin/mysqld: file'./MySQL-bin .~ REC ~ 'Not found (errcode: 13)

111130 21:02:25 [Error] mysql_bin_log: open_purge_index_file failed to open register file.

111130 21:02:25 [Error] mysql_bin_log: open_index_file failed to sync the index file.

111130 21:02:25 [Error] aborting

 

The permission should be insufficient. The PID file cannot be created. After the permission is granted to 777, it is normal:

Chmod-r 777/u01/MySQL

 

[Root @ rac2 MySQL] # ll/u01/MySQL/rac2.pid

-RW ---- 1 MySQL 6 Nov 30 21:02/u01/MySQL/rac2.pid

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 dba8 group: 102954821