Putting mySQL 5.0.33 into production

So, I'm playing with 5.0.33 and there are a few gotcha:

If you use innodb and like to backup your databases using ibbackup, make sure your license is for ibackup-3.0. 2.0 will report that the iblog files are corrupt.


Otherwise it's a straight forward upgrade for me, so far.

I have a mysql 4.0 database that was about 200GB in size, most of that data was removed yet the ibdata file is still 200GB. So, what I did was

mysqldump --single-transaction -uroot -p --all-databases --master-data=1 > all_databases.sql


once backed up I trashed the old datafile and rebuilt it into an empty 5.0 datafile.

Next I added the option innodb_file_per_table so, I can shrink data on a table basis in the future.

mysql -uroot < all_databases.sql

wala done.


In a few hours I turned that 200 GB datafile into 27 GB data, and reclaimed a bunch of resources.



So, why upgrade? Well I wanted to try the innodb scalability patch in production, and 4.1.23 is not in bitkeeper yet (last time I checked). Also there is a cool feature in mysql 5, that turns simple SQL-OR statements internally into UNIONS to get the most out of indexes. I'd like to see how this will help me from day to day uses.