[mythtv-users] How do I removed existing mysql database and start
over?
Mark Knecht
markknecht at gmail.com
Wed Jun 1 02:45:39 UTC 2005
This is very, very long. I'm sorry. It's not that much writing. More a
comparison of a working and failing environment.
On 5/31/05, Greg Mitchell <greg at nodecam.com> wrote:
> > if it was a problem bindging to a port, you wouldn't need mysql root
> > access, only root access to the machine itself ... you might be able to
> > run the following to see if it is setup to only bind locally:
> >
> > grep 'skip-networking' /etc/rc.*
> >
> > there are probably other ways to make it only bind locally, but the
> > --skip-networking option is the one i use ... to make it only bind
> > locally, you would have had to edit files to make it happen, so if you
> > never did that, it's probably not a binding issue :)
>
> I think Mark is using Gentoo, and at least in my Gentoo install, the
> skip-networking setting was on by default in /etc/mysql/my.cnf (I have
> that line commented out, as well as the bind-address=127.0.0.1 line from
> the default)
>
> Could that be at the root of at least some of the problems?
>
> Greg
Hi Greg,
Possibly. Below both backends are Gentoo, as is Flash. The XBox is
Xebian. I'm now testing both environments in parallel.
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
Here's the best example I can show of the differences between the
two environments. I am sitting on both backends - Dragonfly & Gandalf.
Dragonfly allows user mythtv with a password of mythtv to connect.
Gandalf does not. In both examples I use the password mythtv, just
like the Mythtv frontend would do:
mark at dragonfly ~ $ mysql -u mythtv -h 192.168.1.55 -p mythconverg
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 615 to server version: 4.0.24
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>quit
Bye
mark at dragonfly
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 ~ $
About 3 days ago Gandalf allowed this login. Both servers are Gentoo.
Both are running the same versions of MythTV and Mysql. Both are
(supposedly) configured the same in terms of allowing network access
to mysql.
Gandalf still allows access to the mythconverg database for user
mythtv but not over the network:
mark at gandalf ~ $ mysql -u mythtv -p mythconverg
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48 to server version: 4.0.24
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye
mark at gandalf ~ $
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:
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.
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:
dragonfly ~ # netstat -apn | grep mysql
tcp 0 0 192.168.1.55:3306 0.0.0.0:*
LISTEN 22111/mysqld
unix 2 [ ACC ] STREAM LISTENING 36166 22111/mysqld
/var/run/mysqld/mysqld.sock
unix 3 [ ] STREAM CONNECTED 2277042 22111/mysqld
/var/run/mysqld/mysqld.sock
unix 3 [ ] STREAM CONNECTED 2103327 22111/mysqld
/var/run/mysqld/mysqld.sock
unix 3 [ ] STREAM CONNECTED 2081497 22111/mysqld
/var/run/mysqld/mysqld.sock
dragonfly ~ #
So is Gandalf:
gandalf ~ # netstat -apn | grep mysql
tcp 0 0 192.168.1.2:3306 0.0.0.0:*
LISTEN 9779/mysqld
unix 2 [ ACC ] STREAM LISTENING 13791 9779/mysqld
/var/run/mysqld/mysqld.sock
unix 3 [ ] STREAM CONNECTED 13883 9779/mysqld
/var/run/mysqld/mysqld.sock
unix 3 [ ] STREAM CONNECTED 13850 9779/mysqld
/var/run/mysqld/mysqld.sock
unix 3 [ ] STREAM CONNECTED 13848 9779/mysqld
/var/run/mysqld/mysqld.sock
unix 3 [ ] STREAM CONNECTED 13846 9779/mysqld
/var/run/mysqld/mysqld.sock
gandalf ~ #
mark at dragonfly ~ $ mysql -u root -h 192.168.1.55 -p mythconverg
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 617 to server version: 4.0.24
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant all on mythconverg.* to mythtv@"192.168.1.%" identified
by "mythtv";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
mark at dragonfly ~ $
mark at gandalf ~ $ mysql -u root -h 192.168.1.2 -p mythconverg
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50 to server version: 4.0.24
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant all on mythconverg.* to mythtv@"192.168.1.%" identified
by "mythtv";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
mark at gandalf ~ $
mark at dragonfly ~ $ su -
Password:
dragonfly ~ # /etc/init.d/mysql restart
* Stopping mysqld ... [ ok ]
* Starting mysqld ... [ ok ]
dragonfly ~ #
gandalf ~ # /etc/init.d/mysql restart
* Stopping mysqld ... [ ok ]
* Starting mysqld ... [ ok ]
gandalf ~ #
mark at dragonfly ~ $ mysql -u mythtv -p mythconverg
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.24
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mark at gandalf ~ $ mysql -u mythtv -p mythconverg
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.24
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
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:
mark at gandalf ~ $ mythfrontend
2005-05-31 19:16:47.585 New DB connection, total: 1
2005-05-31 19:16:47.588 Unable to connect to database!
2005-05-31 19:16:47.588 Driver error was [1/1045]:
QMYSQL3: Unable to connect
Database error was:
Access denied for user: 'mythtv at gandalf' (Using password: YES)
2005-05-31 19:16:47.589 Unable to connect to database!
2005-05-31 19:16:47.590 Driver error was [1/1045]:
QMYSQL3: Unable to connect
Database error was:
Access denied for user: 'mythtv at gandalf' (Using password: YES)
Doing the same thing on Dragonfly works:
mark at dragonfly ~ $ mythfrontend
X Error: BadAtom (invalid Atom parameter) 5
Major opcode: 20
Minor opcode: 0
Resource id: 0x134
X Error: BadWindow (invalid Window parameter) 3
Major opcode: 2
Minor opcode: 0
Resource id: 0x40
X Error: BadAtom (invalid Atom parameter) 5
Major opcode: 18
Minor opcode: 0
Resource id: 0x1b6
Xlib: extension "XInputExtension" missing on display "localhost:10.0".
Failed to get list of devices
And up comes the gui.
mark at dragonfly ~ $ 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 |
| 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>
ark at gandalf ~ $ mysql -u root -p mythconverg
Enter password:
ERROR 1045: Access denied for user: 'root at localhost' (Using password: YES)
mark at gandalf ~ $
LOCKED OUT! But I can get in using the safe version:
gandalf ~ # /usr/bin/mysqld_safe --skip-grant-tables --user=root &
[1] 10499
gandalf ~ # Starting mysqld daemon with databases from /var/lib/mysql
gandalf ~ # exit
logout
mark at gandalf ~ $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 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 | 4849c7517ff2d641 |
| gandalf | root | |
| localhost | | |
| gandalf | | |
| localhost | mythtv | 0476fc026afffe24 |
| 192.168.1.% | mythtv | 0476fc026afffe24 |
+-------------+--------+------------------+
6 rows in set (0.00 sec)
mysql> grant all on mythconverg.* to mythtv@"192.168.1.%" identified
by "mythtv";
ERROR 1047: Unknown command
mysql>
The root at localhost password is different so I'd like to reset that if
I could. However the mythtv password is the same on both systems.
However the safe version won't let me set the network access
privileges.
I'm stumped!!!
- Mark
More information about the mythtv-users
mailing list