MySQL upgrade 5.0.88 to the latest version

MySQLmysql-5.0upgrade

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

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

Best Answer

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

Sep 12, 2011 : Possible to make MySQL use more than one core?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

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

Related Solutions

MySQL – Which Version to Use

I tend to follow the following rule for existing code-base:

If it's working fine, there is no need to upgrade unless there's a specific feature you are missing.

InnoDB does get some nice performance boosts in 5.5, but if your current use-case is not seeing any issues with your install, why upgrade?

If you ran some performance tests on a development server that indicates you are getting near your limit, you could try 5.5 to see if that helps any.

For future projects, I would definitely recommend 5.5, however.

Mysql – Steps to upgrade MySQL from version 5.0 to version 5.5

Step 01 : Run this script to Dump Everything from MySQL 5.0

cd /root
MYSQL_CONN="-uroot -p..."
SQLSTMT="SELECT schema_name FROM information_schema.schemata WHERE"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
MYSQL_OPTIONS="--skip-column-names -A"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > /tmp/dblist.txt
DB_OPTIONS="--databases"
for DB in `cat /tmp/dblist.txt` ; do DB_OPTIONS="${DB_OPTIONS} ${DB}"
MYSQLDUMP_OPTIONS="--routines --triggers ${DB_OPTIONS}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''"
SQLSTMT="${SQLSTMT}@''',host,''';') FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > GetGrants.sql
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < /root/GetGrants.sql | sed 's/$/;/g' > Grants.sql
rm -f GetGrants.sql

Step 02 : Backup config file

cp /etc/my.cnf /etc/my.cnf.50

Step 03 : Backup /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql50

Step 04 : Uninstall MySQL 5.0

Step 05 : Install MySQL 5.5

Step 06 : service mysql start

Step 07 : Login to mysql

mysql -uroot

Step 08 : At the mysql prompt enter the following:

mysql> source /root/Grants.sql
mysql> source /root/MySQLData.sql

That's it.

Give it a Try !!!

I have suggested this before : MySQL upgrade 5.0.88 to latest

Best Answer

Related Solutions

MySQL – Which Version to Use

Mysql – Steps to upgrade MySQL from version 5.0 to version 5.5

Related Question