Upgrading the mysql server from 5.1 to 5.6?

Question

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.

Answer 1

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
  • Use pt-show-grants
  • Emulate pt-show-grants
• 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

CAPTURE DATA

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

CAPTURE GRANTS

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

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
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

• Mar 26, 2015 : mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?
• Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

GIVE IT A TRY !!!

Answer 2

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.