I am a new DBA focusing mostly on Oracle, but I also have a MySQL 3.23 database that has become my responsibility. They want me to upgrade to 5.0, but I understand that I have to upgrade from 3.23 to 4.0, then from 4.0 to 4.1, then to 5.0. UNIX is our O/S. I have five databases including the mysql database. The main one is a Mantis bug tracker database. Its version is 1.0.5. I am fairly familiar with SQL at this point and can get around the databases fine, but I don't know how to prepare for the upgrade properly. My UNIX admin is the keeper of the root passwords, so when I'm done with my end, he'll do the actual software upgrade portion. I just don't know how to do the backup and data import. Somebody told me that I wouldn't be able to just do a mysqldump because of the changes from version 3.23 to 4.0. I need help getting started here.

Thank you in advance for any advice.

-Jacki

caveat: i am not a DBA, i don't do backups and such

do you have a front end like phpmyadmin? this program has an "export" feature which will generate a file called xxxx.sql which contains the CREATE TABLE and INSERT statements that allow you to export the data

then you just execute the .sql file into the new (higher version) database

In addition to what r937 has suggested, you should test this backup old version/restore to new version off line on a test server before you commit to upgrading the live server. There is usually a problem with the length of hashed passwords that you need to overcome. Check out the mysql manual sections on the upgrade and version differences.

If you don't have an existing application such as phpmyadmin, the mysql administrator program also has a backup and restore feature that creates the .sql file.

Edit: Here is an additional thought on this. In general, you can have multiple versions of mysql running on the same computer. There are probably some operating system/version combination restrictions, but I am not aware of any case where this won't work.

Configure the second mysql server to listen on a different port (for example port:3307 instead of the standard 3306.) You can then copy/backup the existing database and get it working under the new version of mysql. You can even test existing applications by using a copy of them that connects to the new port number.

Once you have everything working, you can switch from the old version to the new version by changing the port numbers around so that the new version is listening on the standard port 3306. This will allow a backward path should you find something that does not work on the new version. Once you are completely satisfied everything is working, you can remove the old version.

Thanks for the suggestions! I am running MySQL 3.23 on both the production server and a test server. They are separate UNIX boxes and the test is a recent clone of the production (one of the UNIX admins set that up for me). I do have phpmyadmin on there as a database. I can go about figuring out how to use the export function and so forth with that. I downloaded the mysql administrator program, but it was for version 5.0 and doesn't work with 3.23. I didn't really know what I had with phpmyadmin, so I'll look into that a lot more and let you know if I have further difficulty with it. Thank you so much for the help!! I haven't had any clue where to go with this for weeks.