MySQL 5.6 default storage engine is MyISAM instead InnoDB

Question

I'm new here and I hope someone will help me out with MySQL issue.

Here I have weird situation that InnoDB isn't the default storage engine on:

• MySQL version 14.14 Distrib 5.6.39, for Linux (x86_64) using EditLine wrapper.
• linux distribution: CentOS Linux release 7.5.1804 (Core)

I have tried almost every single solution to make InnoDB as default engine but it did work for me.

Here are few things I've tried:

STEP 1

files my.cnf and my.ini. has been edited in /root/my.cnf and /root/my.ini and /etc/my.cnf as:

[mysqld]
character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=2GB
transaction-isolation=READ-COMMITTED
binlog_format=row

STEP 2 after files was saved I did restart the MySQL in few ways:

Following commands DID NOT work:

# /etc/init.d/mysqld stop:  No such file or directory  
# /etc/init.d/mysql stop:  No such file or directory 
# service mysqld stop
Redirecting to /bin/systemctl start mysql.service
Failed to start mysqld.service: Unit not found.

Following commands DID work:

# service mysql stop
Redirecting to /bin/systemctl start mysql.service
# service mysql status (was Active: inactive (dead))
# service mysql start
Redirecting to /bin/systemctl start mysql.service
# service mysql status (was Active: active (running))

STEP 3

 mysql> SHOW ENGINES;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        
    | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | FEDERATED          | NO      | Federated MySQL storage engine                                 
    | NULL         | NULL | NULL       |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          
    | NO           | NO   | NO         |
    | MyISAM             | DEFAULT | MyISAM storage engine                                          
    | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             
    | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         
    | NO           | NO   | NO         |
    | InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             
    | NO           | NO   | NO         |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

    mysql>

STEP 4 I have try the following command and I finally get InnoDB as DEFAULT but still didn't work cos once you reset mysqld it comes back to MyISAM is DEFAULT

SET default_storage_engine=INNODB;

This is really frustrating, your help will be highly appreciated

Answer 1

It is possible by doing these steps: navigate to > phpmyadmin -> Home-> variables -> storage engine and change it MyISAM to InnoDB

see the picture