Mysql – Upgrading the thesql server from 5.1 to 5.6

MySQLmysql-5.1mysql-5.6replicationupgrade

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.

Best Answer

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

Related Question