[mythtv-users] How do I removed existing mysql database and start over?

[gLaNDix (Jesse Kaufman)]

Mark Knecht wrote:
> My house: 
> Dragonfly - backend and frontend - 192.168.1.55
> Flash - frontend - 192.168.1.29
> 
> My parents house
> Gandalf - backend and frontend - 192.168.1.2
> XBox - frontend - 192.168.1.10

ok, stupid question, but i want to make sure i fully understand ... the 
setup at your house is supposed to be a direct mirror (as far as how 
it's supposed to work) of your parents house, right?  that helps explain 
:) ... oh, and you had a ton of helpful info in this e-mail ;)

> mark at gandalf ~ $ mysql -u mythtv -h 192.168.1.2 -p mythconverg
> Enter password:
> ERROR 1045: Access denied for user: 'mythtv at gandalf' (Using password: YES)
> mark at gandalf ~ $

Re: the off-list e-mail to me, 'mythtv at gandalf' vs 'mythtv at localhost' is 
what i meant when i talked about two mythtv users ... mysql sees (or can 
see) connections from each ip address as a different user, depending on 
how it's setup ... this is helpful, since you can make 'root at localhost' 
have a different password (and different access) than root@% 
[anywhere_else] ...


> So I *THINK* that the problem is not necessarily the mythconverg
> database but just that mysql has stopped allowing me to access
> mythconverg via the network. There are two reasons I can think of:
> 
> 1) mysql is not bound to a network IP. I show below that both machines are:

yes, this part all looks ok


> 2) The mythconverg database does not have proper privleges granted for
> network access. I don't know how to test this fully, but I seem to not
> be able to access the Gandalf databse correctly as root to set them
> again.

sort of ... it's actually the mythtv@% user that doesn't ...

> My setup on Gandlaf (which doesn't work) from /etc/mysql/my.cnf
> 
> # keep secure by default!
> bind-address    = 192.168.1.2
> port            = 3306
> # this can make it even more secure:
> #skip-networking
> 
> My setup from Dragonfly (which does work:
> 
> # keep secure by default!
> bind-address    = 192.168.1.55
> port            = 3306
> # this can make it even more secure:
> #skip-networking
> 
> Dragonfly is listening:
[snip]
> So is Gandalf:
[snip]

exactly ... this is  an authentication problem, not networking problem 
... this is the proof that everything's working in #1 above ...


> Up to this point everything is absolutely identical. I can access
> mythconverge as mythtv on both systems AS LONG AS I do NOT bind it to
> the network IP as I showed at the start of this email. I start
> mythfrontend on Gandalf, and tell it to use the network address
> 192.168.1.2, it fails with messages about not being able to access the
> database:

i'd setup the frontend on gandalf to access 127.0.0.1 regardless ... 
when running on the local machine, it doesn't have the overhead of 
accessing via tcp/ip ...


> mysql> SELECT Host,User,Password FROM user;
> +-------------+--------+------------------+
> | Host        | User   | Password         |
> +-------------+--------+------------------+
> | localhost   | root   | 4849c7517ff2d641 |
> | gandalf     | root   |                  |
> | localhost   |        |                  |
> | gandalf     |        |                  |
> | localhost   | mythtv | 0476fc026afffe24 |
> | 192.168.1.% | mythtv | 0476fc026afffe24 |
> +-------------+--------+------------------+
> 6 rows in set (0.00 sec)

this is pretty close to what i have in my table, but i don't have the 
mythtv at localhost ...


> mysql> grant all on mythconverg.* to mythtv@"192.168.1.%" identified
> by "mythtv";
> ERROR 1047: Unknown command
> mysql>

in phpmyadmin, the command ended up being:

GRANT ALL PRIVILEGES ON mythconverg.* TO 'mythtv'@ '192.168.0.%';

my suggestion is to either install apache on the backend and install 
phpmyadmin or use some other gui tool ... it's much easier to manage 
users that way ... my job is php/mysql programming and we use the heck 
out of phpmyadmin ... it's a great tool, especially when you don't know 
(or in my case, can't remember) CLI mysql user management :P

but first, i'd concentrate on getting root working again, that will open 
the doors for yo u to do whatever you need to get mythtv working again...

-g-