Want to change default mysql storage engine type

[alexsu]

I don't want to use Innodb which is a default system of mysql in directadmin. Is there anyone who knows how to change Innodb to Myisam in default?

Please kindly help me with instructions details. Thank you so much

 

[zEitEr]

That's not true. Anyway, update /etc/my.cnf to fit your needs, and you might disable Innodb there. Read MySQL documentation for your MySQL version.

 

[alexsu]

already tried but the problem came up that error in establishing connection. So I think I need to change some core functions which will be fit with DA .

 

[zEitEr]

Nobody but you I guess knows what you did and what error you got. So if you want to help here, post your /etc/my.cnf before modification and after it. Post here last 10-20 lines from MySQL error log as soon as error occurs.

Otherwise you should read carefully documentation, or order a commerce service (I'm available for example as well as some other guys here on these forums).

 

[alexsu]

120226 18:57:35 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
120226 18:57:35 [Note] Plugin 'FEDERATED' is disabled.
120226 18:57:35 InnoDB: The InnoDB memory heap is disabled
120226 18:57:35 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
120226 18:57:35 InnoDB: Compressed tables use zlib 1.2.3
120226 18:57:35 InnoDB: Using Linux native AIO
120226 18:57:35 InnoDB: Initializing buffer pool, size = 128.0M
120226 18:57:35 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
120226 18:57:35 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
120226 18:57:35 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120226 18:57:35 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
120226 18:57:35 InnoDB: Waiting for the background threads to start
120226 18:57:36 InnoDB: 1.1.5 started; log sequence number 0
120226 18:57:36 [Note] Event Scheduler: Loaded 0 events
120226 18:57:36 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.9' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
120227 16:21:33 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

120227 16:21:33 [Note] Event Scheduler: Purging the queue. 0 events
120227 16:21:33 InnoDB: Starting shutdown...
120227 16:21:34 InnoDB: Shutdown completed; log sequence number 5687701
120227 16:21:34 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

120227 16:21:34 mysqld_safe mysqld from pid file /usr/local/mysql/data/server.pid ended

this is error log

................................

[mysqld]
local-infile=0

/etc/my.cnf before editing

..................................

[mysqld]
local-infile=0
--skip-innodb


.................................

Thanks for helping. As I am learning to use all, so I will not hire any third party service. Again, Thanks.

 

[zEitEr]

http://www.directadmin.com/forum/showthread.php?t=40169&p=216865#post216865
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#option_mysqld_ignore-builtin-innodb

 

[alexsu]

I tried to solve according to your suggestion.

ignore_builtin_innodb

It's not enough information. Because DA make "Innodb" to be a default one. That's why we got errors when we skipped or ignored innodb.

Finally I added another line in my.cnf.

default-storage-engine=MyISAM

...

So, this gonna be answer.

ignore_builtin_innodb
default-storage-engine=MyISAM


put these lines in my.cnf and restart mysql. The problem has been solved. Thank you so much for your help.

 

[zEitEr]

Just a remark, as I already mentioned it, but probably not that clear, as I'd need to do it. But directadmin by itself has nothing to do with default MySQL engine. Directadmin by itself does not use MySQL for storing any data. It even does not do anything with /etc/my.cnf

So that should clarify some moments for you:

Trends in Storage Engine Usage

In the first years of MySQL growth, early web-based applications didn't push the limits of concurrency and availability. In 2010, hard drive and memory capacity and the performance/price ratio have all gone through the roof. Users pushing the performance boundaries of MySQL care a lot about reliability and crash recovery. MySQL databases are big, busy, robust, distributed, and important.

InnoDB hits the sweet spot of these top user priorities. The trend of storage engine usage has shifted in favor of the more efficient InnoDB. With MySQL 5.5, the time is right to make InnoDB the default storage engine.

Consequences of InnoDB as Default MySQL Storage Engine

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB. This change applies to newly created tables that don't specify a storage engine with a clause such as ENGINE=MyISAM. (Given this change of default behavior, MySQL 5.5 might be a logical point to evaluate whether your tables that do use MyISAM could benefit from switching to InnoDB.)

http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html