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

Mark Knecht markknecht at gmail.com
Wed Jun 1 04:01:48 UTC 2005


On 5/31/05, gLaNDix (Jesse Kaufman) <glandix at lloydnet.org> wrote:
> Mark Knecht wrote:

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

Good.

> 
> > 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 ...

Makes sense, but being a newbie I thought I'd keep every access from
every machine the same until I learned a bit more. It was a good idea
in the beginning. I hope it's not hurting me now.

> 
> 
> > 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 ...

OK, I figured out the root password on Gandalf and changed it. I can
now get the tables from Both machines. I think I'm getting close. here
goes:

On dragonfly 3 passwords are not set:


mark at dragonfly ~ $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 4.0.24

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE mysql; Reading table information for completion of table
and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT Host,User,Password FROM user; 
+---------------+--------+------------------+
| Host          | User   | Password         |
+---------------+--------+------------------+
| localhost     | root   | 0476fc026afffe24 |
| dragonfly     | root   |                  |
| localhost     |        |                  |
| dragonfly     |        |                  |
| localhost     | mythtv | 0476fc026afffe24 |
| %             | mythtv | 0476fc026afffe24 |
| 192.168.10.%  | mythtv | 0476fc026afffe24 |
| 192.168.10.29 | mythtv | 0476fc026afffe24 |
| dragonfly     | mythtv | 0476fc026afffe24 |
| Dragonfly     | mythtv | 0476fc026afffe24 |
| 192.168.1.%   | mythtv | 0476fc026afffe24 |
+---------------+--------+------------------+
11 rows in set (0.00 sec)

mysql>



On Gandalf all passwords are set. This is different.

gandalf ~ # mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.0.24

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

mysql>
> 
> 
> > 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...
> 

I agree about getting root working. I'm hoping the above info is
helpful. Seems like it should be. In the response to the following
command:

gandalf ~ # mysql -h 192.168.1.2 -u mythtv -p mythconverg
Enter password:
ERROR 1044: Access denied for user: '@gandalf' to database 'mythconverg'
gandalf ~ #

it seems almost critical that the response doesn't list the user
mythtv in the Access denied section. Why does it say '@gandalf' and
not 'mythtv at gandalf'?

On this page:

http://dev.mysql.com/doc/mysql/en/access-denied.html

I found this post:

***************
***************
Posted by [name withheld] on July 31 2003 1:58am	[Delete] [Edit]

when you are simply trying to:
C:\mysql\bin>mysql -uroot -p mysql

and you get:

ERROR 1044: Access denied for user: '@127.0.0.1' to database 'mysql'

Here is what I do. The key is to supply your real ip address for the
-h (host) parameter. On windows, from the command prompt type
'ipconfig' to see your ip address. Once you have that, do the
following:

C:\mysql\bin>mysql -h 192.168.0.1 -u root -p mysql
Enter password: ****************

// then I explicitly add root at 127.0.0.1 to the user table, so after
this I can log in as you would expect

GRANT ALL PRIVILEGES ON *.* TO root at localhost IDENTIFIED BY
'root-password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO root at 127.0.0.1 IDENTIFIED BY
'root-password' WITH GRANT OPTION;

// delete anon accounts

DELETE FROM mysql.user WHERE password='';

FLUSH PRIVILEGES;

It works for me

One other way is when you set the initial password for the root user,
also set it on 127.0.0.1:

SET PASSWORD FOR root at localhost=PASSWORD('new_password');
SET PASSWORD FOR root at 127.0.0.1=PASSWORD('root-password');
FLUSH PRIVILEGES;

******************
******************

Seems like there's good info there but I don't quite understand
exactly what to do. I'm getting worn out this evening so I'm going to
hit this again tomorrow unless I get a magical burst of energy here.

Thanks very much for all your help. I'm learning alot about just
talking to mysql even if I'm not sure yet about what to tell it.

Cheers,
Mark


More information about the mythtv-users mailing list