MySQL data file description _ MySQL

MySQL data file description bitsCN.com

MySQL data file description

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:10M: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 stopShutting down MySQL...                                     [  OK  ][root@rac2 lib]# ps -ef|grep mysqlroot     3955  3799  0 20:00 pts/2    00:00:00 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/mysql.socksocket         = /u01/mysql/mysql.sockskip-external-lockingkey_buffer_size = 16M#max_allowed_packet = 1Mmax_allowed_packet=100Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8M

2.5 modify the startup script:

/etc/init.d/myql[root@rac2 mysql]# vi /etc/init.d/mysqldatadir=/u01/mysql

2.6 start mysql

[root@rac2 mysql]# service mysql startStarting MySQL.....                                        [  OK  ]

Started successfully. view the process:

[root@rac2 mysql]# ps -ef|grep mysqlroot    11512     1  2 21:02 pts/2    00:00:00 /bin/sh /usr/bin/mysqld_safe--datadir=/u01/mysql --pid-file=/u01/mysql/rac2.pidmysql   11769 11512  5 21:02 pts/2    00:00:00 /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/mysql.sock --port=3306root    11796  3799  0 21:02 pts/2    00:00:00 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 startStarting MySQL....The server quit withoutupdating PID file[FAILED]ysql/rac2.pid).

View log:

/u01/mysql/rac2.err111130 20:59:34 [Note] /usr/sbin/mysqld:Shutdown complete111130 20:59:34 mysqld_safe mysqld from pidfile /u01/mysql/rac2.pid ended111130 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-rw---- 1 mysql mysql 6 Nov 30 21:02/u01/mysql/rac2.pid

BitsCN.com