[mythtv-users] Resetting mysql password for mythtv user

[R. G. Newbury]

On 11/13/2016 12:04 PM, Mike Perkins wrote:
> On 13/11/16 13:24, Simon Waldman wrote:
>> Hi list,
>>
>> I recently reinstalled my MythTV machine (it was easier, with my limited
>> knowledge, than fixing everything that was broken from a number of
>> upgrades
>> over the years).
>>
>> Foolishly, I forgot to write down the database password before I did
>> this -
>> so the new Mythbuntu installation has put a new password in
>> config.xml, and
>> hence when I restored my old db the password was incorrect.
>>
>> https://www.mythtv.org/wiki/Category:MySQL has instructions for resetting
>> the db password. However, I think this is out of date - because the
>> "user"
>> table in my new install doesn't have a "password" field. I've also
>> noticed
>> that somehow my db has two users called "mythtv", which doesn't sound
>> like
>> a good thing:
>>
>> mysql> select User from user;
>> +------------------+
>> | User             |
>> +------------------+
>> | mythtv           |
>> | debian-sys-maint |
>> | mysql.sys        |
>> | mythtv           |
>> | root             |
>> +------------------+
>> 5 rows in set (0.00 sec)
>>
>>
>> So, two questions - and sorry if these are more MySQL questions than
>> MythTV
>> ones:
>>
>> 1. Should I remove one of these users? If so, how? And how to tell which
>> one?
>>
>> 2. How can I reset the password for the user that is left?
>>
>> Thanks for any help
>> SImon.
>>
> You have just selected a single field from the user table with your
> query. Try:
>
> select * from user;
>
> and you will see all the fields in that table. This may help you
> discover what you have done.
>
> If you then need to delete a user, you may find that a combination of
> fields will be needed to do the trick, such as:
>
> delete from user where User="mythtv" and Host="<whatever>";
>
> but CHECK FIRST that you have understood why the user table has ended up
> this way, before deleting anything.

Just to be clear here. You have to log on to mysql as the root user:
DANGER! etc. yadda yadda. YMMV

<you or whatever>$mysql -u root -p
then use the mysql database

MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]>

Then check on your users and hosts with

MariaDB [mysql]>select host,user,password from user;

+-------------------+--------+----------------------+
| host              | user   |  password            |
+-------------------+--------+----------------------+
| localhost         | root   | *B89B6BFC3XXXXXXXXXXX |
| tor1.mandamus.org | root   | *B89B6BFC3XXXXXXXXXXX |
| 127.0.0.1         | root   | *B89B6BFC3XXXXXXXXXXX |
| localhost         | mythtv | *ABCDEFGHIXXXXXXXXXXX |

etc.
Then you can change the password for the mythtv user

mysql>SET PASSWORD FOR user_name = PASSWORD('plaintext_password1')

Note: for user_name is NOT optional in this case. If user_name is not 
specified, the password will be changed for the current user: meaning ROOT

Note: The FOR user_name parameter must be specified in the format of 
user_name at host_name, such as 'techonthenet'@'localhost'.

So mysql>SET PASSWORD FOR 'mythtv'@'localhost' = PASSWORD('mythtv')

(Yes I looked this up, since I can never remember it and it is 
frustrating if not dangerous to get it even a little bit wrong.  And it 
is useful to have the correct stuff on this list. And to be a little 
pedantic about the explanation.)

Geoff