MySQL database convert!
Posted by tetrahost, 11-14-2012, 03:25 AM Hello all, i need a help from you. i need to convert a database of my user from innodb to myisam. The thing is, his db is pretty big and has many tables, so it will take a lot of time to convert them from phpmyadmin one by one! i need to save my time? any sql query or mysql linux command which will do this in a single step will be helpful for me. I am on linux, cPanel server. Will wait for your response.
Posted by SajanP, 11-14-2012, 03:51 AM A quick PHP script would do the trick for you. 1. Fetch all tables 2. Loop through tables with command "ALTER TABLE table_name ENGINE=MYISAM"
Posted by KMyers, 11-14-2012, 09:32 AM You could also just run "ALTER TABLE table_name ENGINE=MYISAM" via command line or PHPMyAdmin for each table.
Posted by SajanP, 11-14-2012, 10:17 AM He said there are many tables. I'd hate to have to do each one manually if it was more than 20 or so.
Posted by Herasil, 11-14-2012, 11:07 AM Before converting to MyISAM, you might need to check if any foreign keys were created, and if so, drop all of those. Otherwise some of the tables can give you errors when you try to convert from InnoDB to MyISAM, since MyISAM doesn't support foreign keys.
Posted by JayWard_HSW, 11-15-2012, 01:10 PM Here's a commandline one-liner you can mess around with too (assuming bash): mysql -h HOST -u USER -p DATABASE -e 'SHOW TABLES\G' | grep -v '*' | awk '{ print "ALTER TABLE "$2" ENGINE=MYISAM;" }' | xargs -L1 mysql -h HOST -u USER --password=PASS DATABASE -e I haven't tested it fully, so you may have to work with the xargs part at the end, or just copy & paste the list of alter statements into mysql's prompt if you would rather do it that way. Herasil brought up a good point though, you want to make sure your tables are ready to be converted to MyISAM. You can use the line above to do sweeping alterations to the tables to drop foreign key constraints pretty easily.