2

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?

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Chris Muench
  • 701
  • 5
  • 14
  • 34
  • 1
    Skipping a major version is not supported. It might work. The backups are to be made ​​in any case, even if you don't plan to upgrade. There are too many things that can go wrong. Here a recent blog post of mysql employee about upgrade http://www.tocker.ca/2015/03/12/making-the-case-to-support-2-version-upgrades.html – Giovanni Mar 25 '15 at 22:23
  • Thanks for the article. It sounds like going from one major version to the next is supported. Follow up: if I create a dump file from 5.1 and then test in 5.6; and it works; is this safe to do? – Chris Muench Mar 25 '15 at 22:38
  • That's not quite the same. Can you load the dump into a 5.1 on another system, then upgrade to 5.6 on there? – Rick James Mar 25 '15 at 23:43
  • In any case, be sure to run mysql_upgrade. – Rick James Mar 25 '15 at 23:44

1 Answers1

1

I have addressed something like this already

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

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Can you point me to a document from mysql team that says you can do a dump from 5.1 --> 5.6x and not have issues? A lot of the upgrade docs don't talk about versions that are this far apart. – Chris Muench Apr 30 '15 at 19:06
  • If I knew where such documentation were, I would not have written all these posts. – RolandoMySQLDBA Apr 30 '15 at 20:20