By Chris Muench


2015-03-25 21:30:50 8 Comments

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?

1 comments

@RolandoMySQLDBA 2015-03-26 19:11:14

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

@Chris Muench 2015-04-30 19:06:44

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.

@RolandoMySQLDBA 2015-04-30 20:20:36

If I knew where such documentation were, I would not have written all these posts.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema

2 Answered Questions

2 Answered Questions

3 Answered Questions

[SOLVED] Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

1 Answered Questions

[SOLVED] Solutions to 4-byte utf-8 characters in mysql 5.1

Sponsored Content