Migrating from MyISAM to XtraDB

Question

Just a few questions that I just can't find anywhere about migrating to XtraDB.

My group has been using MyISAM dbs for production and was wondering how hard is it to migrate to Percona's XtraDB and how would you go about doing so?

Would I have to migrate MyISAM to InnoDB first or can I go straight to XtraDB?

I installed Percona Server with XtraDB package on my Fedora machine but the documentation isn't very helpful as to how to use it so I was wondering does Percona just piggyback on a standard MySQL installation or is it a separate entity?

Links to documentation on how to solve my questions would be fantastic.

Answer 1

You could convert either before or after. I prefer before because the data will be ready once Percona Server is installed, up and running. I say this because there are some hairy things you have to address with regards to 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.

Here is is the script to perform the InnoDB conversion:

Step 01) Run these commands

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) Run the script

mysql ${MYSQL_CONN} -A < /root/ConvertMyISAMToInnoDB.sql

Step 03) Run this query

SELECT SUM(data_length+index_length)/POWER(1024,2) RecommendedInnoDBBufferPoolSize
FROM information_schema.tables WHERE engine='InnoDB';

This will give you the suggested (ideal, perfect world) value for innodb_buffer_pool_size. If this number exceeds75% of server RAM, then use 75% of RAM.

Step 04) service mysql stop

Step 05) rm -f /var/lib/mysql/ib_logfile* /var/lib/mysql/ibdata1

Step 06) Add the following to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=<SizeFromStep03>M
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=5000
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 07) service mysql start

This will take awhile (2-4 min) because mysqld will creates

• ib_logfile0
• format it
• create ib_logfile1
• format it.

Step 08) Perform Percona Upgrade

Answer 2

XtraDB is a drop-in replacement for InnoDB. Existing MyISAM tables are unaffected. To actually gain any benefit from XtraDB, you will have to convert them to InnoDB. You can do this either before or after you install XtraDB.