Convert MySQL database from MyISAM to InnoDB

In MySQL, you have a command to convert a table from MyISAM engine to InnoDB engine. ALTER TABLE table_name ENGINE = InnoDB; However, this command needs to be done for EVERY table, so here’s a way to do it by scripting if you want to do the entire database :) 1) Backup your database 2) Create the script (change DATABASE_NAME by yours): mysql -p -e "show tables in <DATABASE_NAME>;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql 3) Check the file alter_table.sql to see if it looks correct 4) Run the script: mysql --database=<DATABASE_NAME> -p < alter_table.sql 5) Verify: mysql> show table status; ...

How to reduce size of ibdata1 file in MySQL

If you use Innodb engine for your MySQL database, you may have noticed that the /var/lib/mysql/ibdata1 file is the most important and heavy file of your MySQL server. Day by day, this file will increase and can quickly consume all your hard drive. Reducing its size can be a little bit tricky. When "innodb _ file _ per _ table" is set to "0" in your my.cnf file, the ibdata1 file normally stores many classes of information, such as table data, table indexes, MVCC (Multiversioning Concurrency Control) data, table metadata, and so on. You can check this if you want a well explained schema. Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help. Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things: Makes the table's data and ...