mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

Question

I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway around this?

Answer 1

I have addressed something like this already

• Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
• Jan 31, 2014 : Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?
• Apr 11, 2013 : MySQL upgrade 5.0.88 to latest
• Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
• Jul 26, 2011 : Restoring an old backup to latest MySQL release

Leaping two versions is risky if you do not want to run mysql_upgrade twice. My Oct 17, 2014 post mentions running mysql_upgrade twice, the right way.

In your case, you should do this:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="set group_concat_max_len = 1048576;"
SQL="${SQL} select group_concat(schema_name) from information_schema.schemata"
SQL="${SQL} where schema_name not in"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} > MySQLData.sql
SQL="SELECT CONCAT('SHOW GRANTS FOR ',"
SQL="${SQL} QUOTE(user),'@',QUOTE(host),';') "
SQL="${SQL} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLGrants.sql
rm -f GetGrants.sql

Now just load MySQLGrants.sql and MySQLData.sql into the MySQL 5.6 instance.

GIVE IT A TRY !!!