6

I have some questions regarding upgrading from 5.0.88 to the latest version.

  1. Why should I upgrade?
  2. If so, which version should I consider?
  3. What are all the known risks?
  4. Is upgrading step by step suggested? Or is binary replacement suggested?
7

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

  • I paste your script at the command line but I just get errors "You have an error in your SQL syntax; ..." I appreciate the help, but I cannot figure out how to use the information you are providing. Can anyone provide a bit more context for non-dba's ? – SherylHohman Jun 24 at 12:13
  • 1
    @SherylHohman I tested the above statements with MySQL 5.6.33 and they still work. – RolandoMySQLDBA Jun 24 at 17:31
  • Thanks! My first mistake was trying to run this from the mysql prompt. Once I realized that mistake, it still gave me an error, suggesting that I needed to list specific databases, or change the flag to --all-databases. But doing so did not change the error messages. – SherylHohman Jun 24 at 18:41
  • OMG. It turns out that while my host told me that they ran MySQL 5.0, and insisted they had no plans to upgrade to a modern version of MySQL!, It turns out they were actually running MariaDB 10.2.12 - aka MySQL 5.7. That's probably why the script wasn't working for me. In any case, I do not even need it! I also have a whole new reason for moving away from that host ;-) Thank you for your help. Sorry it was such an newbie issue. I learned a lot in the process. – SherylHohman Jun 24 at 18:48
  • (btw, I already upvoted this answer a couple days ago - even before I tried it. Great documentation and links. Thank you.) – SherylHohman Jun 24 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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