Am I backing up my database correctly? And then load it as INNODB?

Question

mysqldump -hlocalhost -uUSERNAME -pPASSWORD THEDATABASE | gzip > /home/db_backup/abc.sql.gz

tar -xzvf ./abc.sql.gz

mysql THEDATABASE -uroot -pPASSWORD < abc.sql

The important note is that currently, half of my tables are INNODB...and the other half are MYISAM.

If I run these commands...will my newly restored database keep the INNODB/MYISAM?

Answer 1

Look at the .sql file. Following each CREATE TABLE stanza, there should be an Engine= declaration. Those should match whatever engine the tables had when you performed the mysqldump. As long as the target mysql instance supports both storage engines, you should be all set.

Answer 2

Beware of auto-increment integer fields that have a 0 value record -- I got bit by that: http://wiki.xdroop.com/space/MySQL/mysql+backup+and+restore+problem