Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Very interesting and informative blog..

I have a question - i have a requirement of upgrading from 5.0.88 to latest version..

  1. Why should i upgrade?
  2. if so, which version should i consider?
  3. What are all the known risks
  4. upgrading step by step is suggested? or binary replacement suggested?

Please do guide me

Thanks Devi

share|improve this question

1 Answer 1

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.