MySQL Data File description

MySQL Data File description 1. mySQL Data File description first read MySQL DataDirectory file: dave here, mysql is our database name, we are viewing 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 files and ibdata 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 The metadata (meta) information related to the table in the ". frm" file 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 The ". MYD" File ". MYD" 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 The ". MYI" File ". MYI" 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. 2. Change the MySQL data file directory. The default MySQL Data File directory 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. if the cnf configuration file does not exist in the/etc/directory. 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: # 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

 

In the episode 2.7, the following error occurs: The 755 permission granted to the/u01/mysql directory, but the following error is reported:

[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