2

I am having MySQL 5.1 installed on windows box. Now I would like to upgrade to latest MySQL 5.6 version.

I am planning to install MySQL 5.6 in the same server with same port 3306. After installation, I will map the new installation data directory to the older data directory which is used by MySQL 5.1.

After this, I will upgrade using mysql_upgrade.exe.

Will this plan work out or not ? Also let me know if any precautions to be taken care.

Edit: This is production server and the DB is of huge size 100 GB, so we are not able to go with backup and restore mechanism which is time consuming process.

Phanindra
  • 1,007
  • 5
  • 15
  • 31

2 Answers2

0

Actually, this plan is kind of dangerous for the mysql schema. Why ?

You are upgrading two versions up. mysql_upgrade will not respond well to this. I have a post where someone upgraded without addressing mysql.user : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I have old posts on how to upgrade two versions in one shot

Here is an additional post on how to move the grants with pure SQL : Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?. That post is based on Linux.

To do it in Windows, here are the steps

STEP 01 : Run This

set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL% -p%MYSQL_PASS%
set SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
set SQLSTMT=%SQLSTMT% FROM mysql.user WHERE user<>''"
mysql %MYSQL_CONN% -ANe%SQLSTMT% > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql %MYSQL_CONN% -AN < GetGrants.sql >> MySQLUserGrants.sql

STEP 02 : Append a semicolon to every line in MySQLUserGrants.sql

STEP 03 : Load MySQLUserGrants.sql into MySQL 5.6 setup

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

You could achieve this with following steps then

  • Installation of new instance on some new port with latest version.
  • Swap the data directory and swap port in config file.
  • Run mysqlupgrade.
  • Verify the new installation.

To be on safer side you may take old database backup(folder copy or mysqldump or mysqlenterprisebackup before the activity etc)

Hope it helps

Nawaz Sohail
  • 1,480
  • 3
  • 10
  • 25