3

I have a VPS running CentOs 6.5, PHP-FPM, Mysql database and Nginx server.

I installed and used mysql by default, which I can see now that it is using InnoDB as the default engine. So, after realizing that my VPS was running out of its 512 RAM usage, I started to optimize Nginx and PHP-FPM. And now, I have learned that InnoDB uses more memory (at-least while reading) than MyISAM engine, so I am trying to change the default engine of mysql to use MyISAM.

The problem is that since I am new at this, I am worried that amidst the process of changing I may corrupt the database, as I barely had done this before.

So, the first thing I have done, is to save the entire Mysql data just to be on the safe side
cp –Rp /var/lib/mysql/*.* /backup

Now according to this answer the easiest way to change engines is

ALTER TABLE table_name ENGINE = MyISAM;

So, now my question is, is it safe, and if it that's all it takes to change directly the engine for my databases, which in turn will result in memory optimization ? Or is there another way.

Community
  • 1
  • 1
robue-a7119895
  • 816
  • 2
  • 11
  • 31
  • Possible duplicate of [How to convert all tables from MyISAM into InnoDB?](http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb) – T.Todua Oct 22 '15 at 20:05

1 Answers1

2

Mostly InnoDB is better choice than a MyISAM. You can read a bit more about it here. Everything depends on your tables. If you want to read from them (with not many inserts/updates/deletes) then you can think about MyISAM. In other cases if there is many write operation i will recommend you to stay on InnoDB.

Going back to your question: ALTER TABLE table_name ENGINE = MyISAM; is safe. It can take a while (on big tables) but your data should not been broken after this operation. If there will be any problem, MySQL will inform you about it and conversion will be not performed. In worst case you have backup and you can restore it in any time.

Community
  • 1
  • 1
Kasyx
  • 3,170
  • 21
  • 32
  • Thanks, this is helpful. Is there anyway to change the engine from database-level? as opposed to having change all the tables individually ? OR maybe change all tables in database ? – robue-a7119895 Jul 17 '14 at 12:57
  • You can set default engine (new tables will be created with this type if another is not spefied). But im afraid i dont know how to change engine of all existing tables with one query. I think you have to change it for every one seperately. – Kasyx Jul 17 '14 at 13:12