2.6.1.13. Storage types and their change

MySQL supports two main storage types:

• InnoDB — a more modern and efficient data storage engine that is stable, efficient, and easy to configure; it supports transactions and foreign keys. Its main drawback is that the space occupied by tables can only increase and never decrease.
• MyISAM — a simpler data storage mechanism; tables of this type take up less disk space than InnoDB. Its main drawbacks are that it is not well-suited for large amounts of data, and the entire table is locked when data is added.

There are several ways to change the storage engine in individual MySQL tables.

1. Connect to the database using phpMyAdmin or any other MySQL client.
2. For the relevant tables, execute an SQL query like this:

   ALTER TABLE table_name ENGINE=InnoDB;

   Replace table_name with the desired table name, and replace InnoDB with the desired storage engine type.

1. Connect to the hosting via SSH.
2. To convert all tables to InnoDB, run the following command:

   for n in `mysql -h host -u login -pPASSWORD db_name -B -N -e "show tables;"`;do mysql -h host -u login -pPASSWORD db_name -B -N -e "ALTER TABLE $n ENGINE=innodb;";done

   In the command, specify the credentials for connecting to the database (everything except the storage type is specified in two places):

   • host — database server.
   • login — database user name.
   • PASSWORD — database user password.
   • db_name — database name.
   • 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. </code>This is just a warning that using a password in the command is unsafe. If there is no other data, the command executed successfully.

A PHP script for converting the storage engine is available here.