Database upgrades

After several months of waffling, watching and just general procrastinating, I finally took the plunge and upgraded the database server on my Sparc20 from MySQL 3.23.58 to 4.0.18. It wasn't exactly trouble free, but wasn't as much work or as difficult as I thought it might be.

Step 1: Create an SQL dump of all the databases: mysqldump -A -u user -p > dbdump.sql
Step 2: Shut down MySQL
Step 3: Run mysql_install_db from the MySQL4 installation
Step 4: Start up MySQL4
Step 5: Use the database dump created in Step 1 to restore the databases: mysql -u root < dbdump.sql
Step 6: Change password on MySQL root user and recreate database users.

Went pretty smoothly with a 30 MB dump file. There are probably easier ways, but this method seemed more straight forward.

Ended up losing a couple of tables because of column names conflicting with MySQL4 reserved words. Fairly easy to fix though. Just took time to go through all the databases and compare the tables to the dbdump.

So, next task is to learn about all the new things in MySQL 4. Then the next big thing will be to repeat the upgrade process on my main production server. I figure by the time I get around to that, MySQL 5 will be the production release :).