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.

Improve this question
3
  • 1
    the better plan would be install new server on new port...take backup from old one restore to new one..run mysqlupgrade and then swap ports...any reservations on this let us know
    – Nawaz Sohail
    Commented Jun 17, 2015 at 14:27
  • This is the better way, but I am having DB of huge size and taking backup and restoring backup will take time. But we are not having much downtime. So wanted to have faster approach.
    – Phanindra
    Commented Jun 17, 2015 at 15:18
  • You should take you comment (dba.stackexchange.com/questions/104312/…) and put it into the question.
    – RolandoMySQLDBA
    Commented Jun 17, 2015 at 16:00

2 Answers 2

Reset to default
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

Improve this answer
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

Improve this answer
1
  • Nawaz, my plan is the same what you mentioned, but I need if any precautions to be taken care other than mysql users.
    – Phanindra
    Commented Jun 17, 2015 at 16:29

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.