Everythig from MyISAM to INNODB, how?

Richard G

Verified User
Joined
Jul 6, 2008
Messages
5,287
Location
Maastricht
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.
 

BBM

Verified User
Joined
Jun 8, 2013
Messages
349
Location
Dutch Mountains
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

Verified User
Joined
Jul 6, 2008
Messages
5,287
Location
Maastricht
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

Verified User
Joined
Aug 5, 2017
Messages
11
For future references or for anybody wanting to do this too, this is how I've done it and it worked perfectly.

Code:
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:
Code:
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 ;)
 
Top