Everythig from MyISAM to INNODB, how?

[Richard G]

That's indeed an alternative way Wattie. I also encountered that somewhere too on the internet.

But also when doing it like your suggestion, it's better to create a backup first anyway. I felt safer that way.

Thank you anyway, because your tip can always be used too.

 

[wattie]

Always backup. And always on remote resource. That's no discussion here.

 

[BBM]

FYI,
I noticed I had a lot of mixed storage engined tables in certain databases and so I converted a number of them completely to InnoDB, through the ALTER TABLE way and it works nice and smooth.
Just a few clicks to go through a database(s).

 

[Richard G]

Yep thanks. I did that too when I asked this and worked fine.
But I'm glad I did not do it with everythings. Since it seemed applications are better with MyIsam.

 

[anas_xrt]

For future references or for anybody wanting to do this too, this is how I've done it and it worked perfectly.

cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup
mv mysql_backups mysql_backups.`date +%F`
./build mysql_backup
cd mysql_backups
perl -pi -e 's/ENGINE=MyISAM/ENGINE=InnoDB/' *.sql
wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh
chmod 755 restore_sql_files.sh
./restore_sql_files.sh
service mysqld restart

That's all, but take in consideration that the restore can take some time, especially when large databases are present.

The command:

mv mysql_backups mysql_backups.`date +%F`

which you see in between, is to move your backup to a seperate directory with a date because you don't want to work on your original backup.
That's why the backup_mysql command is given a second time. Then you have a new backup which you can adjust.
The restore_sql_files.sh won't restore the mysql.sql file when started like above, without extra command lines. You can better leave the mysql.sql file left alone, so just remove it from your backups directory or create a new backup later on.

Check that everything is working fine. If yes, it would be advisable to create new backups after the changes so you have fresh working backups.

just to confirm that its work great! Thanks for making life easy