[Cialug] Re: updating mysql users after db relocation

[Matthew Nuzum]

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.
-- 
Matthew Nuzum
newz2000 on freenode