f



error while moving database to new server

I need to move a mysql database to a new server. 

old-server>>mysqldump --all-databases -u root -p > safe.sql
new-server>>mysql -u root -p < safe.sql

I get an error: 
ERROR 1449 (HY000) at line 1667: There is no 'lars'@'localhost' registered

"lars" was indeed an user on the old installation.

What else do I need to do? What's the best way to move the whole db? Users, 
passwords, views, privilages and all.


-- 
Rahul
0
nospam59 (11089)
5/28/2009 8:52:12 PM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

13 Replies
1075 Views

Similar Articles

[PageSpeed] 59

Rahul <nospam@nospam.invalid> wrote in
news:Xns9C19A16CADAEB6650A1FC0D7811DDBC81@85.214.105.209: 

> 
> What else do I need to do? What's the best way to move the whole db?
> Users, passwords, views, privilages and all.
> 
> 

Tried to dig deeper:

use information_schema;
SELECT * FROM USER_PREVILAGES;

The table seems different from the old server. Doesn't mysqldump transfer 
those too?


-- 
Rahul
0
nospam59 (11089)
5/28/2009 10:44:54 PM
Rahul <nospam@nospam.invalid> wrote in 
news:Xns9C19A16CADAEB6650A1FC0D7811DDBC81@85.214.105.209:

> I need to move a mysql database to a new server. 
> 
> old-server>>mysqldump --all-databases -u root -p > safe.sql
> new-server>>mysql -u root -p < safe.sql
> 

Another hunch I have is that the old machine was 32 bit and the new 64 bit. 
Would that matter?

They are both running the exact same mysql version though: 5.0.45.


-- 
Rahul
0
nospam59 (11089)
5/28/2009 10:49:02 PM
>I need to move a mysql database to a new server. 
>
>old-server>>mysqldump --all-databases -u root -p > safe.sql
>new-server>>mysql -u root -p < safe.sql
>
>I get an error: 
>ERROR 1449 (HY000) at line 1667: There is no 'lars'@'localhost' registered

This is probably due to the creator of a function, view, or procedure
which is still referenced on the old server.

>"lars" was indeed an user on the old installation.

*WAS* or *STILL IS*?  Is this user to be copied to the new server?

>What else do I need to do? What's the best way to move the whole db? Users, 
>passwords, views, privilages and all.

Do you really want to copy over all the users and privileges from
the old server to the new server?  (and wipe out all the users present
only on the new server, if any?)  If so, you may need to copy the
database `mysql` *first* so the users are defined when you load the
privileges, functions, views, and procedures.

If not, you may need to change the DEFINER for functions, views, and
procedures to a user that exists on the new server before copying them over.
You can change the DEFINER for views with ALTER VIEW, I think, at least
if you have administrative privileges.  For functions and procedures
I think you have to drop the function or procedure and re-create it
with the new user that will own it.

0
5/29/2009 3:56:12 AM
gordonb.f2onp@burditt.org (Gordon Burditt) wrote in
news:CYKdnfkVe4RBwYLXnZ2dnUVZ_jednZ2d@posted.internetamerica: 

> *WAS* or *STILL IS*?  Is this user to be copied to the new server?

Thanks Gordon. He was and is. He was on the old server and I want to retain 
him on the new.

> 
> Do you really want to copy over all the users and privileges from
> the old server to the new server?

Yes I do. 
  (and wipe out all the users present
> only on the new server, if any?)  

The new server had no mysql running on it previously. Hence no worries 
about overwrites. I want to replicate the db and users and everthing else 
mysql from oldserver to newserver.

>If so, you may need to copy the
> database `mysql` *first* so the users are defined when you load the
> privileges, functions, views, and procedures.

Ah! Thanks!

I thought, when one uses mysqldump with the --al-databases options it ought 
to copy *everything*. Maybe I was wrong.



-- 
Rahul
0
nospam59 (11089)
5/31/2009 8:00:15 PM
Rahul wrote:
> gordonb.f2onp@burditt.org (Gordon Burditt) wrote in
> news:CYKdnfkVe4RBwYLXnZ2dnUVZ_jednZ2d@posted.internetamerica: 
> 
>> *WAS* or *STILL IS*?  Is this user to be copied to the new server?
> 
> Thanks Gordon. He was and is. He was on the old server and I want to retain 
> him on the new.
> 
>> Do you really want to copy over all the users and privileges from
>> the old server to the new server?
> 
> Yes I do. 
>   (and wipe out all the users present
>> only on the new server, if any?)  
> 
> The new server had no mysql running on it previously. Hence no worries 
> about overwrites. I want to replicate the db and users and everthing else 
> mysql from oldserver to newserver.
> 
>> If so, you may need to copy the
>> database `mysql` *first* so the users are defined when you load the
>> privileges, functions, views, and procedures.
> 
> Ah! Thanks!
> 
> I thought, when one uses mysqldump with the --al-databases options it ought 
> to copy *everything*. Maybe I was wrong.
> 
> 
> 
If using ISAM just copy everything under /var/lib/mysql.

0
tnp (2409)
5/31/2009 10:17:36 PM
>>If so, you may need to copy the
>> database `mysql` *first* so the users are defined when you load the
>> privileges, functions, views, and procedures.
>
>Ah! Thanks!
>
>I thought, when one uses mysqldump with the --al-databases options it ought 
>to copy *everything*. Maybe I was wrong.

It does not necessarily copy everything *in the correct order*.
Given the possibilities for foreign key relationships and views
(base tables have to be defined before views), it is less than clear
that there always *is* a correct order.  You may be able to get the
output of mysqldump to load on a freshly-installed server by loading
it with the "mysql -f" (keep going on sql error) *TWICE*.

mysqldump keeps sprouting new options to dump new stuff that previously
didn't exist to dump.  For example, --events, --routines, and --triggers .
0
gordon13 (233)
6/1/2009 12:43:43 AM
The Natural Philosopher <tnp@invalid.invalid> wrote in news:gvuvm0$o8l$6
@news.albasani.net:

> If using ISAM just copy everything under /var/lib/mysql.
> 

I have some InnoDBs too. I was told that copying a "file" to replicate a 
database was abad idea. Maybe not?

-- 
Rahul
0
nospam59 (11089)
6/1/2009 9:52:19 PM
gordon@hammy.burditt.org (Gordon Burditt) wrote in
news:0dednS4eub6iub7XnZ2dnUVZ_oydnZ2d@posted.internetamerica: 

  You may be able to get the
> output of mysqldump to load on a freshly-installed server by loading
> it with the "mysql -f" (keep going on sql error) *TWICE*.

Thanks Gordon. I think that worked. It seems to be working fine now. I wish 
there was a way to test the integrity though. To make sure I got 
*everything* from the last db. I don't want to wake up 3 months later and 
discover "oh half the data from table foo was corrupted"
 
> mysqldump keeps sprouting new options to dump new stuff that
> previously didn't exist to dump.  For example, --events, --routines,
> and --triggers . 

I didn't use those but my procedures seem to have made it anyhow. I don't 
have any events or triggers. Not sure if "routines" mean procedures? I have 
those.


-- 
Rahul
0
nospam59 (11089)
6/1/2009 9:54:31 PM
Rahul wrote:
> The Natural Philosopher <tnp@invalid.invalid> wrote in news:gvuvm0$o8l$6
> @news.albasani.net:
> 
>> If using ISAM just copy everything under /var/lib/mysql.
>>
> 
> I have some InnoDBs too. I was told that copying a "file" to replicate a 
> database was abad idea. Maybe not?
> 
About InnodB I know little. Do not take that statement as valid for innodb.
0
tnp (2409)
6/1/2009 10:06:53 PM
Rahul wrote:
> The Natural Philosopher <tnp@invalid.invalid> wrote in news:gvuvm0$o8l$6
> @news.albasani.net:
> 
>> If using ISAM just copy everything under /var/lib/mysql.
>>
> 
> I have some InnoDBs too. I was told that copying a "file" to replicate a 
> database was abad idea. Maybe not?
> 

Yes, it is a bad idea, even with MyISAM files.

Don't worry about Natural Philosopher - he's well known for his 
inability to provide any accurate information.  He's not even a 
programmer - just a wanna be.  He'd be funny if he weren't so pathetic.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
0
jstucklex (14659)
6/1/2009 11:36:29 PM
Jerry Stuckle wrote:
> Rahul wrote:
>> The Natural Philosopher <tnp@invalid.invalid> wrote in news:gvuvm0$o8l$6
>> @news.albasani.net:
>>
>>> If using ISAM just copy everything under /var/lib/mysql.
>>>
>>
>> I have some InnoDBs too. I was told that copying a "file" to replicate 
>> a database was abad idea. Maybe not?
>>
> 
> Yes, it is a bad idea, even with MyISAM files.
> 
> Don't worry about Natural Philosopher - he's well known for his 
> inability to provide any accurate information.  He's not even a 
> programmer - just a wanna be.  He'd be funny if he weren't so pathetic.
> 
*shrug*. If you shut down the daemon, before doing it, it works. How 
could it not? its the only place database info is stored with ISAM files.

And no, Im not just a programmer. I've done a lot more in my life than 
that., .

Unlike some.
0
tnp (2409)
6/2/2009 5:44:59 PM
The Natural Philosopher wrote:
> Jerry Stuckle wrote:
>> Rahul wrote:
>>> The Natural Philosopher <tnp@invalid.invalid> wrote in news:gvuvm0$o8l$6
>>> @news.albasani.net:
>>>
>>>> If using ISAM just copy everything under /var/lib/mysql.
>>>>
>>>
>>> I have some InnoDBs too. I was told that copying a "file" to 
>>> replicate a database was abad idea. Maybe not?
>>>
>>
>> Yes, it is a bad idea, even with MyISAM files.
>>
>> Don't worry about Natural Philosopher - he's well known for his 
>> inability to provide any accurate information.  He's not even a 
>> programmer - just a wanna be.  He'd be funny if he weren't so pathetic.
>>
> *shrug*. If you shut down the daemon, before doing it, it works. How 
> could it not? its the only place database info is stored with ISAM files.
> 
> And no, Im not just a programmer. I've done a lot more in my life than 
> that., .
> 
> Unlike some.

No, you're not EVEN a programmer.  And I know you've done a lot more in 
your live - and still can't find anything you can do right.

Have you tried digging ditches?  Naw, you'd probably do that wrong, too.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
0
jstucklex (14659)
6/2/2009 7:18:30 PM
"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message 
news:h03tun$v9u$1@news.eternal-september.org...
> The Natural Philosopher wrote:
>> Jerry Stuckle wrote:
>>> Rahul wrote:
>>>> The Natural Philosopher <tnp@invalid.invalid> wrote in 
>>>> news:gvuvm0$o8l$6
>>>> @news.albasani.net:
>>>>
>>>>> If using ISAM just copy everything under /var/lib/mysql.
>>>>>
>>>>
>>>> I have some InnoDBs too. I was told that copying a "file" to 
>>>> replicate a database was abad idea. Maybe not?
>>>>
>>>
>>> Yes, it is a bad idea, even with MyISAM files.
>>>
>>> Don't worry about Natural Philosopher - he's well known for his 
>>> inability to provide any accurate information.  He's not even a 
>>> programmer - just a wanna be.  He'd be funny if he weren't so 
>>> pathetic.
>>>
>> *shrug*. If you shut down the daemon, before doing it, it works. 
>> How could it not? its the only place database info is stored with 
>> ISAM files.
>>
>> And no, Im not just a programmer. I've done a lot more in my life 
>> than that., .
>>
>> Unlike some.
>
> No, you're not EVEN a programmer.  And I know you've done a lot more 
> in your live - and still can't find anything you can do right.
>
> Have you tried digging ditches?  Naw, you'd probably do that wrong, 
> too.
>
>
> -- 
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

*plonk*


0
Richard
6/2/2009 7:22:57 PM
Reply: