0

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 '15 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 '15 at 11:56
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
  • 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

GIVE IT A TRY !!!

0

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 '15 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 '15 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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