I have a MySQL DB with innoDB tables. the DB size is about 30GB. I am using
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; to change all the tables characters set and it takes forever!!
is there a way to speed up the process?
I suggest you to move the data to temporary table prior to ALTERing it. Should there is any foreign key referencing this table, it should be dropped prior to truncation and recreated after the content restored.
Hope this would help.
1- Create a table of the desired structure, but without any indexes.
2- Load the data into the table to build the .MYD file.
3- Create another empty table with the desired structure, this time including indexes. This will create the .frm and .MYI files you need.
4- Flush the tables with a read lock.
5- Rename the second's table's .frm and MYI files, so mySQL uses them for the first table.
6- Release the read lock.
As some have indicated above, importing the data into a new table rather than trying to alter your existing one makes the most sense to me.
Since you are using innodb, you can disable the doublewrite option temporarily. This has worked for me in the past when restoring data.
This configuration option disables "stores all data twice, first to the doublewrite buffer, and then to the actual data files"
Once you've restarted MySQL to update its configuration.
Export the data using mysqldump
mysqldump --single-transaction --default-character-set=utf8 -u user -ppassword mydb nameoftable > /tmp/extracted_table.sql
Then create the new table, load in the data using mysql
mysql --default-character-set=utf8 -u user -ppassword mydb < /tmp/extracted_table.sql
Add your indexes
Remove or comment out the skip-innodb_doublewrite variable and restart mysql.