IMHO the best option would be to Convert First to InnoDB then perform the mysql upgrade. I prefer this because performing a mysql upgrade mostly involves alter the grant tables. (This especially includes the mysql.user table since MySQL 5.0's mysql.user tables has 37 columns while MySQL 5.5's mysql.user tables has 42 columns) I would not want to mess with connectivity or SQL Grants issues first.
Given the following facts:
- your database is 74G
- your RAM is 4GB
- assuming you did not optimize or configure InnoDB ahead of time
Here is how to go about doing this InnoDB conversion:
Step 01) Create the script to convert all MyISAM tables into InnoDB
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
The script looks for all tables in all databases can converts them in size order starting with the smallest.
Step 02) service mysql stop
Step 03) rm -f /var/lib/mysql/ib_logfile* /var/lib/mysql/ibdata1
Step 04) Add the following to /etc/my.cnf
[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=2G
innodb_log_file_size=512M
innodb_log_buffer_size=64M
innodb_open_files=4096
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=65536
key_buffer_size=8M
CAVEATS
- innodb_log_file_size should be 25% of innodb_buffer_pool_size
- key_buffer_size can now be scaled back
Step 05) service mysql start
This will take awhile (5 min) because mysqld will create ib_logfile0, format it, create ib_logfile1, format it.
Step 06) Login to mysql and load the conversion script
mysql> source /root/ConvertMyISAMToInnoDB.sql
This will take the longest
CAVEAT : you do not have to run it all. You could cut and paste a few lines at a time into mysql and convert a few tables at a time.
If you would like to reverse InnoDB tables back to MyISAM, just run the process backwards:
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToMyISAM.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') MyISAMConversionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" > /root/ConvertInnoDBToMyISAM.sql
mysql -u... -p... -A < /root/ConvertInnoDBToMyISAM.sql
Step 07) As a precaution, dump the MySQL Grants to a text file
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > /root/MySQLGrants.sql
Step 08) Perform mysql upgrade
Step 09) Test all mysql users' connectivity. If it fails, run this:
mysql> source /root/MySQLGrants.sql