You did not state what OS you are using and if you have full access to the server. For Windows servers I use http://www.webyog.com/en/downloads.php - use option: Backup Database as SQL dump. http://www.phpmyadmin.net/home_page/index.php is another great tool and can be used for both Windows and Linux servers and in cases where you don't have full server access (shell or desktop).
In any case you should first experiment with the process of backing up, deleting and restoring the database. Don't just assume that it will work - make sure that you can repeat the process many times.
For Windows users I suggest the following technique:
create a directory C:\Scripts
create a directory where MySQL data will be located (in the example below it will be D:\MySQL_Backup)
create this script (name it dailymysqldump.bat for example)
set Task scheduler to run this task every day for example at 1:00 am
@ECHO OFF
rem Daily MySQL backup of all databases - J Tylek 04-16-2008
FOR /f "tokens=2,3,4 delims=-/. " %%F IN ('DATE /T') DO SET FN=%%F%%G%%H
"D:\MySQL\bin\mysqldump" --user=useyourusername --all-databases --password=yourpassword >D:\MySQL_Backup\%FN%.sql"
SET FN=
rem *****************************************************
rem Utility to clean MySQL dump files older than 30 days using purger.exe
c:\scripts\purger -wd 30 D:\MySQL_Backup\*.sql
this script will make a dump of all databases and name it:
11172008.sql
11182008.sql and so on...
To prevent accumulation of excessive number of backups the purger utlity is needed (I don't know any other way how to do it in Windows). You can download it from here: http://www.freedownloadscenter.com/Utilities/File_Cleanup_Utilities/EServ_Purger.html
Put it into scripts directory as well. Of course set proper username, password and directory names that correspond to your environment.
As for upgrade process don't delete anything - just overwrite the old installation with the new one. And always, always, always: backup, backup, backup!
