[gentoo-user] putting mysql databases from one system to another

[gentoo-user] putting mysql databases from one system to another

[covici]

I am trying to copy my databases from one system to another and since
one is 32-bit and the other is 64-bit, I was told that I could not copy
the binary  databases   directly, but I had to  do mysqldump and then
put that source file into the new system.  What I am getting is that the
passwords seem not to have gotten through -- the user names seem to be
there, but I cannot login with the passwords the user had in the old
system.

Can anyone tell me why this is so and what I can do to fix?

Thanks in advance for any ideas.

-- 
Your life is like a penny.  You're going to lose it.  The question is:
How do
you spend it?

         John Covici
         covici@ccs.covici.com

Re: [gentoo-user] putting mysql databases from one system to another

[Josh]

[-- Attachment #1: Type: text/plain, Size: 1579 bytes --]

On Tue, Apr 5, 2011 at 11:59 AM, <covici@ccs.covici.com> wrote:

> I am trying to copy my databases from one system to another and since
> one is 32-bit and the other is 64-bit, I was told that I could not copy
> the binary  databases   directly, but I had to  do mysqldump and then
> put that source file into the new system.  What I am getting is that the
> passwords seem not to have gotten through -- the user names seem to be
> there, but I cannot login with the passwords the user had in the old
> system.
>
> Can anyone tell me why this is so and what I can do to fix?
>
> Thanks in advance for any ideas.
>
> --
> Your life is like a penny.  You're going to lose it.  The question is:
> How do
> you spend it?
>
>         John Covici
>         covici@ccs.covici.com
>
> Which two MySQL versions are in use here?
     Older versions of mysql used a different format for the passwords and
there is a flag you need to pass to mysqld to get it to use old passwords (I
believe)

What is the connection string you are using? Specifically are you connecting
via the mysql socket, using a hostname etc?
     Say the old server was called "foo.stuff.net" and the connection was
made via the external interface e.g. "mysql -h foo.stuff.net", the user may
have been setup to allow connections from "foo.stuff.net" only, as where now
you may be connectin from "bar.stuff.net" or "localhost".

SELECT user,host FROM mysql.user ORDER BY user;

May shed some light on the situation for you.


-- 
()  The ASCII Ribbon Campaign - against HTML Email,
/\  vCards, and proprietary formats.

[-- Attachment #2: Type: text/html, Size: 2266 bytes --]

Re: [gentoo-user] putting mysql databases from one system to another

[covici]

Josh <korthrun@gmail.com> wrote:

> On Tue, Apr 5, 2011 at 11:59 AM, <covici@ccs.covici.com> wrote:
> 
> > I am trying to copy my databases from one system to another and since
> > one is 32-bit and the other is 64-bit, I was told that I could not copy
> > the binary  databases   directly, but I had to  do mysqldump and then
> > put that source file into the new system.  What I am getting is that the
> > passwords seem not to have gotten through -- the user names seem to be
> > there, but I cannot login with the passwords the user had in the old
> > system.
> >
> > Can anyone tell me why this is so and what I can do to fix?
> >
> > Thanks in advance for any ideas.
> >
> > --
> > Your life is like a penny.  You're going to lose it.  The question is:
> > How do
> > you spend it?
> >
> >         John Covici
> >         covici@ccs.covici.com
> >
> > Which two MySQL versions are in use here?
>      Older versions of mysql used a different format for the passwords and
> there is a flag you need to pass to mysqld to get it to use old passwords (I
> believe)
> 
> What is the connection string you are using? Specifically are you connecting
> via the mysql socket, using a hostname etc?
>      Say the old server was called "foo.stuff.net" and the connection was
> made via the external interface e.g. "mysql -h foo.stuff.net", the user may
> have been setup to allow connections from "foo.stuff.net" only, as where now
> you may be connectin from "bar.stuff.net" or "localhost".
> 
> SELECT user,host FROM mysql.user ORDER BY user;
> 
> May shed some light on the situation for you.
It should be localhost in all cases.  The mysql versions are  5.1.53 in
both cases.  I am trying to login with the mysql client and I can do it
on the old box, but not the new one --same host name, etc.

Now I can login with the root password on the new box, maybe that is
stored somewher else.


-- 
Your life is like a penny.  You're going to lose it.  The question is:
How do
you spend it?

         John Covici
         covici@ccs.covici.com

Re: [gentoo-user] putting mysql databases from one system to another

[Mark Shields]

[-- Attachment #1: Type: text/plain, Size: 2497 bytes --]

On Tue, Apr 5, 2011 at 2:37 PM, <covici@ccs.covici.com> wrote:

>
> Josh <korthrun@gmail.com> wrote:
>
> > On Tue, Apr 5, 2011 at 11:59 AM, <covici@ccs.covici.com> wrote:
> >
> > > I am trying to copy my databases from one system to another and since
> > > one is 32-bit and the other is 64-bit, I was told that I could not copy
> > > the binary  databases   directly, but I had to  do mysqldump and then
> > > put that source file into the new system.  What I am getting is that
> the
> > > passwords seem not to have gotten through -- the user names seem to be
> > > there, but I cannot login with the passwords the user had in the old
> > > system.
> > >
> > > Can anyone tell me why this is so and what I can do to fix?
> > >
> > > Thanks in advance for any ideas.
> > >
> > > --
> > > Your life is like a penny.  You're going to lose it.  The question is:
> > > How do
> > > you spend it?
> > >
> > >         John Covici
> > >         covici@ccs.covici.com
> > >
> > > Which two MySQL versions are in use here?
> >      Older versions of mysql used a different format for the passwords
> and
> > there is a flag you need to pass to mysqld to get it to use old passwords
> (I
> > believe)
> >
> > What is the connection string you are using? Specifically are you
> connecting
> > via the mysql socket, using a hostname etc?
> >      Say the old server was called "foo.stuff.net" and the connection
> was
> > made via the external interface e.g. "mysql -h foo.stuff.net", the user
> may
> > have been setup to allow connections from "foo.stuff.net" only, as where
> now
> > you may be connectin from "bar.stuff.net" or "localhost".
> >
> > SELECT user,host FROM mysql.user ORDER BY user;
> >
> > May shed some light on the situation for you.
> It should be localhost in all cases.  The mysql versions are  5.1.53 in
> both cases.  I am trying to login with the mysql client and I can do it
> on the old box, but not the new one --same host name, etc.
>
> Now I can login with the root password on the new box, maybe that is
> stored somewher else.
>
>
> --
> Your life is like a penny.  You're going to lose it.  The question is:
> How do
> you spend it?
>
>         John Covici
>         covici@ccs.covici.com
>
> I hate to ask the obvious, but are you passing "-u <username> and -p" to
mysqldump?  the "-p" by itself will prompt for a password, which you will
then enter. The format should be "mysqldump databasename -u username -p >
file", then enter the password at the "Password:" prompt.

[-- Attachment #2: Type: text/html, Size: 3668 bytes --]

Re: [gentoo-user] putting mysql databases from one system to another

[Gregory Fontenele]

[-- Attachment #1: Type: text/plain, Size: 2765 bytes --]

want to leave this list but I can not, can someone erase me from this list?

On Tue, Apr 5, 2011 at 23:08, Mark Shields <laebshade@gmail.com> wrote:

> On Tue, Apr 5, 2011 at 2:37 PM, <covici@ccs.covici.com> wrote:
>
>>
>> Josh <korthrun@gmail.com> wrote:
>>
>> > On Tue, Apr 5, 2011 at 11:59 AM, <covici@ccs.covici.com> wrote:
>> >
>> > > I am trying to copy my databases from one system to another and since
>> > > one is 32-bit and the other is 64-bit, I was told that I could not
>> copy
>> > > the binary  databases   directly, but I had to  do mysqldump and then
>> > > put that source file into the new system.  What I am getting is that
>> the
>> > > passwords seem not to have gotten through -- the user names seem to be
>> > > there, but I cannot login with the passwords the user had in the old
>> > > system.
>> > >
>> > > Can anyone tell me why this is so and what I can do to fix?
>> > >
>> > > Thanks in advance for any ideas.
>> > >
>> > > --
>> > > Your life is like a penny.  You're going to lose it.  The question is:
>> > > How do
>> > > you spend it?
>> > >
>> > >         John Covici
>> > >         covici@ccs.covici.com
>> > >
>> > > Which two MySQL versions are in use here?
>> >      Older versions of mysql used a different format for the passwords
>> and
>> > there is a flag you need to pass to mysqld to get it to use old
>> passwords (I
>> > believe)
>> >
>> > What is the connection string you are using? Specifically are you
>> connecting
>> > via the mysql socket, using a hostname etc?
>> >      Say the old server was called "foo.stuff.net" and the connection
>> was
>> > made via the external interface e.g. "mysql -h foo.stuff.net", the user
>> may
>> > have been setup to allow connections from "foo.stuff.net" only, as
>> where now
>> > you may be connectin from "bar.stuff.net" or "localhost".
>> >
>> > SELECT user,host FROM mysql.user ORDER BY user;
>> >
>> > May shed some light on the situation for you.
>> It should be localhost in all cases.  The mysql versions are  5.1.53 in
>> both cases.  I am trying to login with the mysql client and I can do it
>> on the old box, but not the new one --same host name, etc.
>>
>> Now I can login with the root password on the new box, maybe that is
>> stored somewher else.
>>
>>
>> --
>> Your life is like a penny.  You're going to lose it.  The question is:
>> How do
>> you spend it?
>>
>>         John Covici
>>         covici@ccs.covici.com
>>
>> I hate to ask the obvious, but are you passing "-u <username> and -p" to
> mysqldump?  the "-p" by itself will prompt for a password, which you will
> then enter. The format should be "mysqldump databasename -u username -p >
> file", then enter the password at the "Password:" prompt.




-- 
Atenciosamente,
Gregory Fontenele

[-- Attachment #2: Type: text/html, Size: 4303 bytes --]

Re: [gentoo-user] putting mysql databases from one system to another

[kashani]

On 4/5/2011 11:59 AM, covici@ccs.covici.com wrote:
> I am trying to copy my databases from one system to another and since
> one is 32-bit and the other is 64-bit, I was told that I could not copy
> the binary  databases   directly, but I had to  do mysqldump and then
> put that source file into the new system.  What I am getting is that the
> passwords seem not to have gotten through -- the user names seem to be
> there, but I cannot login with the passwords the user had in the old
> system.
>
> Can anyone tell me why this is so and what I can do to fix?
>
> Thanks in advance for any ideas.
>

	On Linux there is no difference between the on disk format so rsync 
away assuming you're keeping roughly the same Mysql version. You can 
have issues on Windows for some reason. However mysqldump is always 
considered safer for a number of other reasons.

	After you imported your fresh new mysqldump you ran flush privileges; 
for the mysql.user table to take effect?

kashani

Re: [gentoo-user] putting mysql databases from one system to another

[Alex Schuster]

kashani writes:

> On 4/5/2011 11:59 AM, covici@ccs.covici.com wrote:
> > I am trying to copy my databases from one system to another and since
> > one is 32-bit and the other is 64-bit, I was told that I could not copy
> > the binary  databases   directly, but I had to  do mysqldump and then
> > put that source file into the new system.  What I am getting is that
> > the passwords seem not to have gotten through -- the user names seem
> > to be there, but I cannot login with the passwords the user had in the
> > old system.
> > 
> > Can anyone tell me why this is so and what I can do to fix?

I can't.

> 	On Linux there is no difference between the on disk format so rsync
> away assuming you're keeping roughly the same Mysql version.

Um, but only when the architecture is identical. I'm pretty sure binary data 
is stored in different format on 32bit and 64bit systems.

	Wonko

Re: [gentoo-user] putting mysql databases from one system to another

[kashani]

On 4/6/2011 3:47 PM, Alex Schuster wrote:
>> 	On Linux there is no difference between the on disk format so rsync
>> away assuming you're keeping roughly the same Mysql version.
>
> Um, but only when the architecture is identical. I'm pretty sure binary data
> is stored in different format on 32bit and 64bit systems.
>
> 	Wonko
>

	I had done it myself in the past a number of times without issue, but 
here's the documentation to back it up.

kashani

http://wikis.sun.com/display/WebStack/MySQL64bitARC

"It should be noted that, when switching between 32bit and 64bit server 
using the same data-files, all the current major storage engines
(with one exception) are architecture neutral, both in endian-ness and 
bit size. You should be  able to copy a 64-bit or 32-bit DB either way,
and even between platforms without problems for MyISAM, InnoDB and NDB.
For other  engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and 
FEDERATED) either the engine doesn't have a disk storage format or
the format they use is text based (CSV) or based on MyISAM (MERGE; and 
therefore not an issue). The only exception is Falcon, which is only 
available in MySQL 6.0.

It is generally recommended from MySQL that a dump and reload of
data for absolute compatibility for any engine and major migration."

Re: [gentoo-user] putting mysql databases from one system to another

[Alex Schuster]

kashani wrote:

> On 4/6/2011 3:47 PM, Alex Schuster wrote:
> >> 	On Linux there is no difference between the on disk format so rsync
> >> away assuming you're keeping roughly the same Mysql version.
> > 
> > Um, but only when the architecture is identical. I'm pretty sure binary
> > data is stored in different format on 32bit and 64bit systems.

> 	I had done it myself in the past a number of times without issue, but
> here's the documentation to back it up.
> 
> kashani
> 
> http://wikis.sun.com/display/WebStack/MySQL64bitARC
> 
> "It should be noted that, when switching between 32bit and 64bit server
> using the same data-files, all the current major storage engines
> (with one exception) are architecture neutral, both in endian-ness and
> bit size. You should be  able to copy a 64-bit or 32-bit DB either way,
> and even between platforms without problems for MyISAM, InnoDB and NDB.
> For other  engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and
> FEDERATED) either the engine doesn't have a disk storage format or
> the format they use is text based (CSV) or based on MyISAM (MERGE; and
> therefore not an issue). The only exception is Falcon, which is only
> available in MySQL 6.0.
> 
> It is generally recommended from MySQL that a dump and reload of
> data for absolute compatibility for any engine and major migration."

Interesting. I searched a little, and while there are many warnings that 
binary data will not survive when moved to another architecture, some people 
say this definitely is NOT a problem. I stand corrected.

	Wonko