My server have CentOS6. Currently I'm are using the Percona 5.1.47 version. Would like to upgrade to 5.6.

What is the best way to do, and what are the prerequisites to keep in mind before doing that.

Added this server is master-master replication topology.

  • Master-Master has problems, especially if you are trying to upgrade that far.
    – Rick James
    Aug 22, 2015 at 6:02
  • The topology is master master. But the application pointed on only one side. How about it in this case ?
    – s_ramesh
    Aug 22, 2015 at 11:56

2 Answers 2


I have a more direct way to upgrade from MySQL 5.1 to 5.6

The idea is to do the following:

  • Dump the mysql schema from MySQL 5.1 as pure SQL into a grants file
  • Dump the data from MySQL 5.1 without the mysql schema into a data file
  • Uninstall MySQL 5.1
  • Install MySQL 5.6
  • Load grants file into MySQL 5.6
  • Load data file into MySQL 5.6


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 ${MYSQL_CONN} --databases ${DBLIST} > MySQLData.sql


Here is my personal emulation of pt-show-grants to capture SQL Grants

SQL="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQL="${SQL} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}"|mysql ${MYSQL_CONN} -AN|sed 's/$/;/g' > MySQLGrants.sql

Here are my posts where I discussed these things before



Caveat: There are many details left out of these steps. Read the docs; find more forum entries; etc.

Assuming you are starting with A<->B, meaning replication is established in both directions. Also assuming you are currently writing to only A.

Plan A, just 2 servers

  1. Change to A->B: That is, STOP SLAVE on A.
  2. Upgrade in place B to 5.5. (Keep the binlogs turned on.)
  3. Upgrade in place B to 5.6.
  4. Failover. B is now the writable one. A is unused. (You are now vulnerable. You needed 3 servers to avoid the vulnerability.)
  5. STOP SLAVE on B.
  6. Upgrade A to 5.5 to 5.6
  7. START SLAVE on A and B.

Plan B, add new server

Better would be to throw a third machine, C, in to the mix.

  1. 5.6 on C.
  2. Clone C from A.
  3. Make C a Slave of A: B <-> A -> C(5.6)
  4. Clone C onto B and make it a slave of C: A -> C(5.6) -> B(5.6)
  5. Toss A; establish dual master: A(unused); C(5.6) <-> B(5.6)

Plan C mysqldump

  1. Stop writes
  2. mysqldump
  3. Stop B, load 5.6, load data, start writes.
  4. ditto on A. (Watchout for binlogs, etc)

Footnote: Percona Xtradbdump is better in many respects, and the steps will be different.

  • Step-by-step upgrade from 5.1 -> 5.5 -> 5.6 takes several process. How this could be like, Create a new instance with 5.6 version. Later took mysqldump from the current version 5.1. If that is the case, I know we have the depreciated parameter problems, and then cause datatype issues. Any other issues that lead trouble if I follow this method?
    – s_ramesh
    Aug 24, 2015 at 4:16
  • Yeah, that is a viable approach. There are likely to be a few VARIABLEs and datatypes that give minor problems. You do need to stop writing to the system starting when you start the mysqldump through until 5.6 is online.
    – Rick James
    Aug 24, 2015 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.