Database storage engine show engines modify engine

Mysql>show engines;+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+| Engine | Support | Comment | Transactions | XA | savepoints |+--------------------+---------+----------------------------------------------------------------+--- -----------+------+------------+| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV Storage Engine | NO | NO | NO | | Blackhole | YES | /dev/NULLStorage Engine (anything youWriteto it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, storedinchMemory, useful forTemporary Tables | NO | NO | NO | | Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive Storage Engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES | | Performance_schema | YES | Performance Schema | NO | NO | NO |+--------------------+---------+----------------------------------------------------------------+----------- ---+------+------------+9RowsinchSet (0.00Sec

To view the storage engine for a table:

Mysql> Show table status from MySQL where name= "user" \g;*************************** 1.           Row *************************** name:user Engine:myisam version:10 row_format:dynamic Rows:10 avg_row_length:117 data_length:1176max_data_length:281474976710655 index_length:2048 Data_fr      ee:0 auto_increment:null create_time:2016-11-30 16:09:58 update_time:2017-05-03 11:26:33 check_time:null  Collation:utf8_bin checksum:null Create_options:Comment:Users and global Privileges1 row in Set (0.00 SEC) Error:no Query specifiedmysql> show CREATE TABLE user\g;*************************** 1.  Row *************************** table:usercreate table:create Table ' user ' (' Host ' char COLLATE utf8_bin not  NULL default ' ', ' User ' char (+) COLLATE utf8_bin not NULL default ' ', ' Password ' char (in a) CHARACTER SET latin1 COLLATE Latin1_bin not NULL DEFAULT ', ' Select_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 NOT null default ' n ', ' Insert_priv ' enum (' n ', ' y ') CHARACTER SET UTF8 not NULL default ' n ', ' Update_priv ' enum (' n ', ' y ') CH Aracter set UTF8 NOT null default ' n ', ' Delete_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 NOT null default ' n ', ' Create_priv ' Enum (' n ', ' y ') CHARACTER set UTF8 NOT null default ' n ', ' Drop_priv ' enum (' n ', ' y ') CHARACTER set UTF8 NOT null default ' n '  ', ' Reload_priv ' enum (' n ', ' y ') CHARACTER set UTF8 not NULL DEFAULT ' n ', ' Shutdown_priv ' enum (' n ', ' y ') CHARACTER set UTF8 Not null default ' n ', ' Process_priv ' enum (' n ', ' y ') CHARACTER SET UTF8 NOT null default ' n ', ' File_priv ' enum (' n ', ' y ') C Haracter set UTF8 NOT null default ' n ', ' Grant_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 NOT null default ' n ', ' References_ Priv ' enum (' n ', ' y ') CHARACTER set UTF8 NOT null the DEFAULT ' n ', ' Index_priv ' enum (' n ', ' y ') CHARACTER set UTF8 NOT null Defau LT ' n ', ' Alter_priv ' enum (' n ', ' y ') CHARACTER set UTF8 not NULL DEFAULT ' n ', ' Show_db_priv ' enum (' n ', ' y ') CHARACTER set U Tf8 not NULL DEFAULT ' N ', ' Super_priv ' enum (' n ', ' y ') CHARACTER SET UTF8 not NULL DEFAULT ' n ', ' Create_tmp_table_priv ' enum (' n ', ' y ') CHARACTER SE T UTF8 NOT null default ' n ', ' Lock_tables_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 NOT null default ' n ', ' Execute_priv ' en Um (' n ', ' y ') CHARACTER set UTF8 NOT null default ' n ', ' Repl_slave_priv ' enum (' n ', ' y ') CHARACTER set UTF8 NOT null default ' n ', ' Repl_client_priv ' enum (' n ', ' y ') CHARACTER SET UTF8 not NULL DEFAULT ' n ', ' Create_view_priv ' enum (' n ', ' y ') characte R set UTF8 NOT null default ' n ', ' Show_view_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 NOT null default ' n ', ' Create_routine _priv ' enum (' n ', ' y ') CHARACTER set UTF8 not NULL DEFAULT ' n ', ' Alter_routine_priv ' enum (' n ', ' y ') CHARACTER set UTF8 not N ULL default ' n ', ' Create_user_priv ' enum (' n ', ' y ') CHARACTER SET UTF8 not NULL DEFAULT ' n ', ' Event_priv ' enum (' n ', ' y ') CH Aracter set UTF8 NOT null default ' n ', ' Trigger_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 NOT null default ' n ', ' Create_tab Lespace_priv ' enum (' N ', 'Y ') CHARACTER set UTF8 not null DEFAULT ' N ', ' Ssl_type ' enum ("', ' any ', ' X509 ', ' SPECIFIED ') CHARACTER set UTF8 NOT null DEF Ault ', ' ssl_cipher ' blob not null, ' X509_issuer ' blob is not null, ' X509_subject ' blob is not null, ' max_questions ' int (11  ) unsigned NOT NULL default ' 0 ', ' max_updates ' int (one) unsigned NOT null default ' 0 ', ' max_connections ' int (one) unsigned Not null default ' 0 ', ' max_user_connections ' int (one) unsigned NOT null default ' 0 ', ' plugin ' char (UP) COLLATE Utf8_bin DEFAULT ', ' authentication_string ' text COLLATE utf8_bin, ' password_expired ' enum (' N ', ' Y ') CHARACTER SET UTF8 not NULL Default ' N ', PRIMARY KEY (' Host ', ' User ') engine=myisam default Charset=utf8 collate=utf8_bin comment= ' Users and global P Rivileges ' 1 row in Set (0.00 sec) Error:no query specified

To modify the engine:

VIM/ETC/MY.CNF[MYSQLD] #skip-grant-tables#datadir=/var/lib/mysqldatadir=/data/db/mysql#socket=/var/lib/mysql/ mysql.socksocket=/data/db/mysql/mysql.sockuser=mysqldefault-storage-engine=innodb# Disabling Symbolic-links is Recommended to prevent assorted security riskssymbolic-links=0 .... Restart mysqld Service

Database storage engine show engines modify engine