2.7.13. Types of storages and their change
MySQL supports two main types of storage:
- MyISAM - has a simpler architecture, tables of this type take up less disk space than InnoDB.
- InnoDB - Supports transactions and foreign keys, but has several significant drawbacks: the space occupied by tables can only increase and never decrease, tables are difficult or impossible to recover in case of failure.
We recommend using MyISAM by default, and using InnoDB only in cases where you need features that are not available in MyISAM (for example, transactions).
Changing the storage type
Attention!Before taking action create a database backup.
There are several ways to change the storage type for individual MySQL tables.
Using a SQL query
Through the console
- Connect to hosting via SSH.
- To convert all tables to InnoDB run the command:
for n in `mysql -h host -u login -pPASSWORD name_БД -B -N -e "show tables;"`;do mysql -h host -u login -pPASSWORD name_БД -B -N -e "ALTER TABLE $n ENGINE=innodb;";done
In the command, specify data to connect to the database (everything except the storage type is specified in two places):
host— database server.
login— the name of the database user.
PASSWORD— database user password.
name_БД— the name of the database.
innodb— storage type.
When you run the command, the output might look like this:
mysql: [Warning] Using a password on the command line interface can be insecure.
Do not be intimidated by such a message. This notice only indicates that the use of a password in a command is unsafe. If there is no other data, then the command was executed correctly.
With PHP script
PHP script for converting storage type is presented here.