Well, first off, if you are upgrading from 5.0 to 5.5, you really need to do this on a test server prior to making the upgrade in a production environment. This will get you comfortable with the steps of upgrading and let you figure out any trouble areas beforehand, which will dramatically reduce your downtime of upgrading.
So to upgrade, I would upgrade from 5.0 to 5.1, then 5.1 to 5.5 as the manual suggests. There will be downtime, but if you do the upgrade steps on a test setup first, you will reduce your downtime dramatically. Make sure you have all the installers downloaded beforehand, don't wait to download them while you're in downtime.
You could do the replication setup as Rolando points out, but please don't do the upgrade on production without testing out the steps first.
Why should I upgrade ?
For MySQL 5.1.37 and prior, InnoDB does not take advantage of multiple CPUs/multiple cores. No amount of scaling up of hardware will ever make InnoDB go any faster. MySQL 5.1.38 first introduced the InnoDB Plugin which has many aspects of tuning now available for multiple CPU/multiple core engagement. This is now fully available as native InnoDB in MySQL 5.5./5.6. Here are my past posts on this subject
if so, which version should i consider?
Although it was released 2 months ago, I trust MySQL 5.6.10 thus far. I already gave a client that version from MySQL 5.0.51a. Check out this post from Jan 19, 2012 :
Which version of MySQL should I use? . The client's production server is replicating to the new MySQL 5.6 Cluster and waiting for the Client to cutover.
What are all the known risks
As with any release of MySQL, you should always read the Release Notes. The only thing to worry about is not doing an upgrade correctly. Another risk is forgetting to run mysql_upgrade script to fix the mysql.user table's column layout.
upgrading step by step is suggested? or binary replacement suggested?
Personally, I am uncomfortable with a binary replacement. I have a method to upgrade from MySQL 5.0.88 straight to MySQL 5.6.10.
STEP 01 : mysqldump all databases except the mysql schema
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > Data.sql
STEP 02 : dump the grants as pure SQL commands
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql
I have recommended this technique before : importing myisam 5.0 database into a 5.5 innodb server
STEP 03 load each file into a new MySQL 5.6 Instance
Before you can do anything, you have so set a password for root@localhost in MySQL 5.6
The temporary password is located in ~/.mysql_secret. Login as root@localhost using that password. Then, change the password with
SET PASSWORD=PASSWORD('whateverIwantasthenewpassword');
While in the MySQL Client, simply run
source Data.sql
source Grants.sql
That's all that's needed.
Give it a Try !!!
UPDATE 2019-06-24 13:30 EDT
For the benefit of Linux users and in response to the comment from @SherylHohman, I tested the above statements from STEP 01 and STEP 02 without MYSQL_CONN defined (left blank on purpose) in a Vagrant environment:
root@vt-mysql:~# mkdir DBAStackExchange
root@vt-mysql:~# cd DBAStackExchange
root@vt-mysql:~/DBAStackExchange# ls -l
total 0
root@vt-mysql:~/DBAStackExchange# SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
root@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
root@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
root@vt-mysql:~/DBAStackExchange# DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
root@vt-mysql:~/DBAStackExchange# MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
root@vt-mysql:~/DBAStackExchange# mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > Data.sql
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange# SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sqlroot@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
root@vt-mysql:~/DBAStackExchange# mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange# ls -l
total 4056
-rw-r--r-- 1 root root 4146556 Jun 24 10:22 Data.sql
-rw-r--r-- 1 root root 1326 Jun 24 10:23 Grants.sql
root@vt-mysql:~/DBAStackExchange# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
The statements I posted here 6 years ago still work in Linux. I ran these in MySQL 5.6.33
Best Answer
Create "main script" which detects current DB version and shells only scripts proper for it. For example, it can look like
Don't forget error handlers (no script file or access error, error during script execution, etc.).