Nerd Alert Ultra-specific nerdery.

Had to upgrade mass amounts of data from outdated MySQL 3.23 to 5. Couldn't find any good guides so I am attempting to make one. This is currently a work in progress. I will add more as I get farther.

Backup existing data:
mysqldump --all-databases --quote-names -u root -p > backup.sql

I built a virtual Redhat Enterprise Linux server using VirtualBox to test out the upgrade path before I did it on the production server. This process is pretty easy and well documented. Install the RPM for MySQL server and client.

While trying to re-import the SQL, it failed several times. As I was migrating a little more than 100 databases, I found it handy to have a script that removes all the databases when the import failed. Here's what i did:
grep 'CREATE DATABASE' backup.sql > bobby_droptables.sql
vi bobby_droptables.sql
:% s/CREATE/DROP/g
** Remove the line: DROP DATABASE mysql; **

If the import fails, you can run:
cat bobby_droptables.sql | mysql -u root -p

Found out there is a bug in "--quote-names". It does not quote database names, only tables. This is fixed in version 4 which does me no good. I need to escape all my database names in quotes because Ensim created database names as SITENAME_-_PROJECTNAME.

Did this instead:
vi backup.sql
:% s/CREATE\sDATABASE\s\/\*\!32312\sIF\sNOT\sEXISTS\*\/\s\([a-zA-z0-9\_\-]*\);/CREATE DATABASE `\1\`;/g
The above RegEx puts back ticks around all database names.

My backup.sql had create statements such as this:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ some_database_name_here;
If your statement is different you may need to tweak the RegEx above.

Also, I added:
DROP DATABASE mysql;
... immediately before the CREATE DATABASE mysql; statement. As of now, I don't know if this will work or not. I hope that removing the mysql database momentarily will not cause any catastrophes. I'm hoping that replacing the database will restore all the existing usernames and passwords for the databases. It worked!

Now after all this, I was finally able to import the sql:
cat backup.sql | mysql -u root -p --verbose
--verbose is nice because while importing about 300mb of SQL, I kept thinking that the thing was gonna stall out and then would manually run:
mysql -u root -p
show databases;
but with --verbose you get to see all the SQL whiz past your screen.