Follow us on:

How to Convert MyISAM to InnoDB on DirectAdmin

tsteki - -
How to Convert MyISAM to InnoDB on DirectAdmin

It is important to make sure that you shift from MyISAM to InnoDB if database is set for using MariaDB system while you are using DirectAdmin panel for VPS, Dedicated, VDS or cloud hosting.

By default, after installing DirectAdmin on server the database tables use MyISAM even if you chose MariaDB, this issue is not persisted for managed hosting as they already configure for INNODB to give user the maximum performance.

If your hosting is still using MyISAM database tables, then you have to contact your hosting providers for the changes to stay updated with current system.

Database table system might be mix of MyISAM to InnoDB not only from first install, but it might happen if you migrate web data from one hosting to another with different system.

MyISAM Vs InnoDB

MyISAM is a default MySQL engine version, while InnoDB is the modern version with a lot of advantages as follows:

  • InnoDB supports transactions whereas MyISAM does not.
  • InnoDB is more complex while MyISAM is simpler.
  • InnoDB is more complex while MyISAM is simpler.
  • InnoDB provides support for foreign keys whereas MyISAM does not.
  • InnoDB is suitable for large databases because it supports transaction, volume whereas MyISAM is suitable for small projects.
  • Performance speed of MyISAM is much higher than InnoDB
  • InnoDB supports row level locking which means inserting and updating is much faster as compared with MyISAM.
  • InnoDB supports ACID properties whereas MyISAM does not.

Advantages of InnoDB

  • InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
  • Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  • Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
  • Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
  • No full-text indexing.

Advantages of MyISAM

  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  • Full-text indexing.
  • Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

  • No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  • Doesn’t support transactions which is essential in critical data applications such as that of banking.
  • Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

Before converting from MyISAM to InnoDB, then you have to make sure that the database engine is set for new system and some tables displays old system by checking via root or some database tools like Litespeed cache.

If you find old table system in your database tables, then backup your data firstly, then run the following codes for the changes:

cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup
mv mysql_backups mysql_backups.`date +%F`
./build mysql_backup
cd mysql_backups
perl -pi -e 's/ENGINE=MyISAM/ENGINE=InnoDB/' *.sql
wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh
chmod 755 restore_sql_files.sh
./restore_sql_files.sh
service mysqld restart

Tags:
Leave a Comment