Revisions to MySQL Upgrade on Windows

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

Source Link

edited Apr 13, 2017 at 12:42

1

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

Apr 11, 2013 : MySQL upgrade 5.0.88 to the latest version MySQL upgrade 5.0.88 to the latest version (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release Restoring an old backup to latest MySQL release

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

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

Apr 11, 2013 : MySQL upgrade 5.0.88 to the latest version (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

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

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

Apr 11, 2013 : MySQL upgrade 5.0.88 to the latest version (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

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

deleted 49 characters in body

Source Link

edited Jun 17, 2015 at 16:56

RolandoMySQLDBA

184.3k
33
323
531

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

Apr 11, 2013 : MySQL upgrade 5.0.88 to the latest version (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

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 : You run MySQLUserGrants.sql in the new MySQL 5.6 setupMySQLUserGrants.sql

STEP 0403 : Load mysqldump of MySQL 5.1 dataMySQLUserGrants.sql into MySQL 5.6 setup

Please do not mysqldump the mysql schema.

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

Apr 11, 2013 : MySQL upgrade 5.0.88 to the latest version (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

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),';') 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 : You run MySQLUserGrants.sql in the new MySQL 5.6 setup

STEP 04 : Load mysqldump of MySQL 5.1 data into MySQL 5.6 setup

Please do not mysqldump the mysql schema.

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

Apr 11, 2013 : MySQL upgrade 5.0.88 to the latest version (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

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

Post Undeleted by RolandoMySQLDBA

occurred Jun 17, 2015 at 16:51

Post Deleted by RolandoMySQLDBA

occurred Jun 17, 2015 at 16:11

Post Undeleted by RolandoMySQLDBA

occurred Jun 17, 2015 at 16:09

Post Deleted by RolandoMySQLDBA

occurred Jun 17, 2015 at 16:01

Source Link

created Jun 17, 2015 at 15:25

RolandoMySQLDBA

184.3k
33
323
531

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

Apr 11, 2013 : MySQL upgrade 5.0.88 to the latest version (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

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),';') 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 : You run MySQLUserGrants.sql in the new MySQL 5.6 setup

STEP 04 : Load mysqldump of MySQL 5.1 data into MySQL 5.6 setup

Please do not mysqldump the mysql schema.

Stack Exchange Network

Return to Answer