Upgrade to MySQL 4.1 from 4.0

------=_NextPart_000_000F_01C520A9.03D01EA0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Env: Windows XP

This procedure was followed to upgrade to MySQL 4.1
1. Uninstall MySQL 4.0 (c:\mysql\), data directory (c:\mysql\data)
2. Install MySQL 4.1 (c:\program files\MYSQL\MySQL Server 4.1\)
3. The new data directory is (c:\program files\mysql\MySql Server
4.1\Data)

How is the MySQL 4.0 data (c:\mysql\data\) transferred in MySQL 4.1 ?
The problem I am having is with the users
Users are lost when a database is imported



------=_NextPart_000_000F_01C520A9.03D01EA0--

Re: Upgrade to MySQL 4.1 from 4.0

4.1 uses different size password field and the PASSWORD() function (used
for authentication) returns a different hash than it did. You coulod fix
this by doing an update user set password= PASSWORD('the old password
that I happen to know') for each user if that is feasible , or you can
start the ddatabase with --old-password flag to have the function return
short hashes.

You can read more here:
http://dev.mysql.com/doc/mysql/en/upgrading-from-4.0.html

Hope that helps.

-Corey

Dave Kennedy wrote:

>Env: Windows XP
>
>This procedure was followed to upgrade to MySQL 4.1
>1. Uninstall MySQL 4.0 (c:\mysql\), data directory (c:\mysql\data)
>2. Install MySQL 4.1 (c:\program files\MYSQL\MySQL Server 4.1\)
>3. The new data directory is (c:\program files\mysql\MySql Server
>4.1\Data)
>
>How is the MySQL 4.0 data (c:\mysql\data\) transferred in MySQL 4.1 ?
>The problem I am having is with the users
>Users are lost when a database is imported
>
>
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Upgrade to MySQL 4.1 from 4.0

Corey,
I did follow the procedure in the reference manual, but how is MySQL 4.1
configured to use c:\mysql\data (4.0 data)?

-----Original Message-----
From: Corey Tisdale [mailto:corey@bbqguys.com]
Sent: Friday, March 04, 2005 12:51 PM
To: Dave Kennedy
Cc: win32@lists.mysql.com
Subject: Re: Upgrade to MySQL 4.1 from 4.0

4.1 uses different size password field and the PASSWORD() function (used

for authentication) returns a different hash than it did. You coulod fix

this by doing an update user set password= PASSWORD('the old password
that I happen to know') for each user if that is feasible , or you can
start the ddatabase with --old-password flag to have the function return

short hashes.

You can read more here:
http://dev.mysql.com/doc/mysql/en/upgrading-from-4.0.html

Hope that helps.

-Corey

Dave Kennedy wrote:

>Env: Windows XP
>
>This procedure was followed to upgrade to MySQL 4.1
>1. Uninstall MySQL 4.0 (c:\mysql\), data directory (c:\mysql\data)
>2. Install MySQL 4.1 (c:\program files\MYSQL\MySQL Server 4.1\)
>3. The new data directory is (c:\program files\mysql\MySql Server
>4.1\Data)
>
>How is the MySQL 4.0 data (c:\mysql\data\) transferred in MySQL 4.1 ?
>The problem I am having is with the users
>Users are lost when a database is imported
>
>
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=brian@tek-tools.com




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Upgrade to MySQL 4.1 from 4.0

--=_alternative 0076EAB085256FBA_=
Content-Type: text/plain; charset="US-ASCII"

Dave,

I think you probably want to mysqldump just the data from your old 4.0
users table (mysql.user) and re-import into into your 4.1 server. You will
keep the shorter hashes and all of the new fields will get their
table-defined default values.

I would do it like this:

A) Create the dump of your old accounts:

(in a command shell)
mysqldump -h 4.0.server.address -u root -p -t -q -Q -r accts.sql mysql
user;

B) Create a dupe of your current user table (for disaster recovery and to
save any newly created accounts)

(within the mysql command-line client. everything from here down is
performed there)
use mysql;

CREATE TABLE tblUserBackup
SELECT *
FROM user;

C) Clear out your current user data

TRUNCATE TABLE user;

D) Load in your old accounts

source accts.sql;

E) Restore any "new" accounts (Accounts not on your old 4.0 server) :

INSERT user
SELECT tub.*
FROM tblUserBackup tub
LEFT JOIN user u
ON u.user = tub.user
AND u.host = u.host
WHERE u.user is null;

F) Restore the passwords for any already upgraded accounts

UPDATE tblUserBackup tub
INNER JOIN user u
ON u.user = tub.user
AND u.host = u.host
SET u.password = tub.password;

G) Check to see that everything looks right

SELECT * from user;

H) Activate the updated accounts

FLUSH PRIVILEGES;

I) Get rid of your backup data when you are sure you no longer need it

DROP TABLE tblUserBackup;

If you need to UNDO all of this (and you haven't deleted your backup
account information (step I))

TRUNCATE TABLE user;

INSERT user
SELECT *
FROM tblUserBackup;

FLUSH PRIVILEGES;


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Dave Kennedy" wrote on 03/04/2005 04:09:59 PM:

> Corey,
> I did follow the procedure in the reference manual, but how is MySQL 4.1
> configured to use c:\mysql\data (4.0 data)?
>
> -----Original Message-----
> From: Corey Tisdale [mailto:corey@bbqguys.com]
> Sent: Friday, March 04, 2005 12:51 PM
> To: Dave Kennedy
> Cc: win32@lists.mysql.com
> Subject: Re: Upgrade to MySQL 4.1 from 4.0
>
> 4.1 uses different size password field and the PASSWORD() function (used
>
> for authentication) returns a different hash than it did. You coulod fix
>
> this by doing an update user set password= PASSWORD('the old password
> that I happen to know') for each user if that is feasible , or you can
> start the ddatabase with --old-password flag to have the function return
>
> short hashes.
>
> You can read more here:
> http://dev.mysql.com/doc/mysql/en/upgrading-from-4.0.html
>
> Hope that helps.
>
> -Corey
>
> Dave Kennedy wrote:
>
> >Env: Windows XP
> >
> >This procedure was followed to upgrade to MySQL 4.1
> >1. Uninstall MySQL 4.0 (c:\mysql\), data directory (c:\mysql\data)
> >2. Install MySQL 4.1 (c:\program files\MYSQL\MySQL Server 4.1\)
> >3. The new data directory is (c:\program files\mysql\MySql Server
> >4.1\Data)
> >
> >How is the MySQL 4.0 data (c:\mysql\data\) transferred in MySQL 4.1 ?
> >The problem I am having is with the users
> >Users are lost when a database is imported
> >
> >
> >
> >
> >
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=brian@tek-tools.com
>
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 0076EAB085256FBA_=--

RE: Upgrade to MySQL 4.1 from 4.0

--=_alternative 007783F685256FBA_=
Content-Type: text/plain; charset="US-ASCII"

I don't like to reply to myself but message wrapping ruined the command in
step A). That's supposed to be entered all on one line, no breaks...

SGreen@unimin.com wrote on 03/04/2005 04:36:01 PM:

> Dave,

> A) Create the dump of your old accounts:
>
> (in a command shell)
> mysqldump -h 4.0.server.address -u root -p -t -q -Q -r accts.sql mysql
> user;
>


No line breaks the term "user" is
supposed to be right after "accts.sql mysql".

ALSO! I had a typo. Mysqldump does not end it's commands with a ";".
"user" will be the last part of that command.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 007783F685256FBA_=--

Re: Upgrade to MySQL 4.1 from 4.0

In your configuration file you can specify the data directory, or you
can move the data directory post install to wherever you want the data
to be read from now. That is kind of risky because of the password
issue with the mysql database and a few other table incompatabilities,
so I would recommend the first one.Anyway, the configuration file thing
goes like this:

[mysqld]
basedir=C:/wherever_your_new_installation_is
datadir=C:/mysql/data

-Corey

Dave Kennedy wrote:

>Corey,
>I did follow the procedure in the reference manual, but how is MySQL 4.1
>configured to use c:\mysql\data (4.0 data)?
>
>-----Original Message-----
>From: Corey Tisdale [mailto:corey@bbqguys.com]
>Sent: Friday, March 04, 2005 12:51 PM
>To: Dave Kennedy
>Cc: win32@lists.mysql.com
>Subject: Re: Upgrade to MySQL 4.1 from 4.0
>
>4.1 uses different size password field and the PASSWORD() function (used
>
>for authentication) returns a different hash than it did. You coulod fix
>
>this by doing an update user set password= PASSWORD('the old password
>that I happen to know') for each user if that is feasible , or you can
>start the ddatabase with --old-password flag to have the function return
>
>short hashes.
>
>You can read more here:
>http://dev.mysql.com/doc/mysql/en/upgrading-from-4.0.html
>
>Hope that helps.
>
>-Corey
>
>Dave Kennedy wrote:
>
>
>
>>Env: Windows XP
>>
>>This procedure was followed to upgrade to MySQL 4.1
>>1. Uninstall MySQL 4.0 (c:\mysql\), data directory (c:\mysql\data)
>>2. Install MySQL 4.1 (c:\program files\MYSQL\MySQL Server 4.1\)
>>3. The new data directory is (c:\program files\mysql\MySql Server
>>4.1\Data)
>>
>>How is the MySQL 4.0 data (c:\mysql\data\) transferred in MySQL 4.1 ?
>>The problem I am having is with the users
>>Users are lost when a database is imported
>>
>>
>>
>>
>>
>>
>>
>
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Upgrade to MySQL 4.1 from 4.0

--=======AVGMAIL-4229180B616E=======
Content-Type: text/plain; x-avg=cert; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.6.1 - Release Date: 3/4/2005


--=======AVGMAIL-4229180B616E=======
Content-Type: text/plain; charset=us-ascii

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
--=======AVGMAIL-4229180B616E=======--