Thesql upgrading from 5.1 –> 5.6 do I have to do thesqldump before upgrade

MySQLmysql-5.1mysql-5.6mysqldumpupgrade

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?

Best Answer

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

Related Solutions

Mysql – Solutions to 4-byte utf-8 characters in thesql 5.1

Opinion

It's probably better to throw an exception during your applications input checking and not pass the buck to the database.

Workaround

There is a "workaround" but your mileage may vary: http://forge.mysql.com/worklog/task.php?id=3780

Brute Force?

You could convert your front end table VARCHAR field to a BLOB and store as binary data to cure the current problem. ...and of course that invites a host of other problems by using BLOBs instead of VARCHAR.

Upgrading

UTF32 may solve the problem and upgrading to 5.5.x isn't has hard as you'd think. Create a replicated slave (farm), sync up and promote it to master.

Mysql – Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.

I once wrote a post about the home position of any empty binary log:

120 in MySQL 5.6
107 in MySQL 5.5
106 in MySQL 5.1
98 in MySQL 4.1/5.0
MySQL master binlog corruption

Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.

I once put that in an answer (Mar 05, 2013 : MySQL Replication without stopping master). In Step 06 from my answer I wrote this:

CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

I also used position 4 in these other posts

Apr 02, 2014 : How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743
Mar 05, 2014 : Risk of changing clocktime on MySQL server host

Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.

Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.

GIVE IT A TRY AT YOUR OWN RISK !!!

UPDATE 2014-11-18 22:32 EST

Just for official reference, this example CHANGE MASTER TO command

CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.

Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.

UPDATE 2014-11-19 17:47 EST

Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.