[Cialug] Re: updating mysql users after db relocation

[Dave J. Hala Jr.]

If you have a copy of your /var/lib/mysql/mysql/host* files (that's the
path on redhat), you could overwrite the current ones and get back to
where you started.



On Thu, 2008-02-28 at 10:40 -0600, Matthew Nuzum wrote:
> On Thu, Feb 28, 2008 at 9:49 AM, Tom Pohl <tom at tcpconsulting.com> wrote:
> > Actually, if any of the hosts were localhost or %, you just updated
> >  them too.  Some mysql clients like PHP tend to use localhost and
> >  explicitly setting it to your 192.x.x.x ip would make them grumpy :)
> >
> >  -Tom
> 
> Here's a sample of the before and after
> 
> I dumped the database on localhost, restored it to remote computer, then did:
> select Host,User,Password from mysql.user;
> 
> +--------------+------------------+-------------------------------------------+
> | Host         | User             | Password                                  |
> +--------------+------------------+-------------------------------------------+
> | localhost    | root             |                                           |
> | matts-laptop | root             |                                           |
> | 127.0.0.1    | root             |                                           |
> | localhost    | debian-sys-maint | *765BB6FEBA352D87EA19501B7ED995DEB0F14D31 |
> | localhost    | training         | *071F0E52D5A97B50BE78FA78FF5BA45ADCE01F97 |
> | localhost    | wp               | *8C2FB6D9F381D4D1442EF41C174D50EFE9B0E80D |
> | localhost    | drupal           | *7AFEAE5774E672996251E09B946CB3953FC67656 |
> | localhost    | rails            | *85B6336B08E24D6213F8F72A3B305EFBD56B21E1 |
> | localhost    | art              | *A4B6157319038724E3560894F7F932C8886EBFCF |
> | localhost    | moodle           | *1ECDE898A7068C6422AF1220D301EBA14F576CF9 |
> 
> After I did the update it looks like this:
> +---------------+------------------+-------------------------------------------+
> | Host          | User             | Password                                  |
> +---------------+------------------+-------------------------------------------+
> | localhost     | root             |
>         |
> | matts-laptop  | root             |
>         |
> | 127.0.0.1     | root             |
>         |
> | localhost     | debian-sys-maint |
> *765BB6FEBA352D87EA19501B7ED995DEB0F14D31 |
> | 192.168.1.103 | training         |
> *071F0E52D5A97B50BE78FA78FF5BA45ADCE01F97 |
> | 192.168.1.103 | wp               |
> *8C2FB6D9F381D4D1442EF41C174D50EFE9B0E80D |
> | 192.168.1.103 | drupal           |
> *7AFEAE5774E672996251E09B946CB3953FC67656 |
> | 192.168.1.103 | rails            |
> *85B6336B08E24D6213F8F72A3B305EFBD56B21E1 |
> | 192.168.1.103 | art              |
> *A4B6157319038724E3560894F7F932C8886EBFCF |
> | 192.168.1.103 | moodle           |
> *1ECDE898A7068C6422AF1220D301EBA14F576CF9 |
> 
> I think debian-sys-maint is used for doing things like
> starting/stopping the database from /etc/init.d/mysql
> 
> I now get errors when I try to restart or connect to the database as
> anything but root.
> error: 'Access denied for user 'debian-sys-maint'@'localhost' (using
> password: YES)'
> 
> Likewise I'm not able to connect from 192.168.1.103. I created a user
> who should be able to connect from any host (I tried % and *).
> 
> >  > Did you do a "flush privileges" after you did the update?
> >  >
> >  >
> >  > :) Dave
> 
> Yes, unfortunately.