Mysql storage engine MyISAM FAQ (table corruption, inaccessible, insufficient disk space) _ MySQL

This article mainly introduces the FAQ of Mysql storage engine MyISAM, and solves problems such as table corruption, inaccessible access, and insufficient disk space, interested friends can refer to this article to share with you the solutions to the Mysql storage engine MyISAM FAQ for your reference. the specific content is as follows:

I. dealing with table corruption of the MyISAM storage engine

When using MySQL, the MyISAM storage engine table may be damaged. For example:

. Frm locked and cannot be modified

The. myi File (index file) is not found)

Unexpected End Records

File destroyed

Error nnn obtained from table processor

Solution 1:

Use the myisamchk tool provided by MySQL to fix the problem.
Open the bin directory and you can see the tool

The command is as follows:
Myisamchk-r tablename
R stands for recover
Or
Myisamchk-o tablename
-O parameter represents-safe-recover for more secure repair

Solution 2:
Use the CHECK table and repair table commands of MySQL to fix the vulnerability.
Check table is used to check whether the table is damaged, and repair table is used to repair bad tables.

2. the MyISAM table is too large to be accessed

First, we can use the myisamchk command to view the MyISAM table. For example, view the admin table

Datefile lengthIndicates the current file size.
Keyfile lengthIndicates the size of the index file.
Max datefile lengthMaximum file size
Max keyfile lengthMaximum index size
You can use the following command to expand the data file size:
Alter table table_name MAX_ROWS = 88888888 AVG_ROW_LE = 66666

III. insufficient disk space for data directories

1. for the MyISAM storage engine

You can store data directories and index directories to different disk spaces.

2. InnoDB storage engine

For InnoDB storage engine tables, because data files and index files are stored together. So they cannot be separated. When the disk space is insufficient, you can add a new data file, which is placed on a disk with sufficient space. The specific implementation is to add this file through InnoDB_data_file_path.
Innodb_data_file_path =/home/mysql/data: 10000 M;/user/mysql/data: 10000 M: autoextend
After the parameter is modified, you must restart the server to make the modification take effect.

4. install multiple Mysql instances on the same host

In addition to the installation directory of each Mysql, the port and socket cannot be the same.
Mysql. sock is used for communication between clients and mysql. The socket file can only be used on the local machine. the remote connection must pass through TCP/IP.

The above is the Mysql storage engine MyISAM FAQ analysis, thank you for reading.