[mythtv-users] db restore

[Stephen Worthington]

On Mon, 6 Aug 2018 07:30:00 -0400, you wrote:

>On Sun, Aug 5, 2018 at 1:44 PM, Stephen Worthington <
>stephen_agent at jsw.gen.nz> wrote:
>
>> On Sun, 5 Aug 2018 11:30:14 -0400, you wrote:
>>
>> >On Sun, Aug 5, 2018 at 11:06 AM, Stephen Worthington <
>> >stephen_agent at jsw.gen.nz> wrote:
>> >
>> >> On Sun, 5 Aug 2018 10:48:01 -0400, you wrote:
>> >>
>> >>
>> >> >I'm still getting nowhere:
>> >> > daryl at trieli:~$ ./mythconverg_restore.pl --create_database
>> --directory ~
>> >> >--filename mythconverg-1344-20180804112717.sql.gz --verbose
>> >> >
>> >> >Configuring environment:
>> >> >  -    username: daryl
>> >> >  -        HOME: /home/daryl
>> >> >  - MYTHCONFDIR: /home/daryl/.mythtv
>> >> >
>> >> >Parsing configuration files:
>> >> >  - checking: /home/daryl/.mythtv/config.xml
>> >> >  - checking: /usr/local/share/mythtv/mysql.txt
>> >> >  - checking: /usr/share/mythtv/mysql.txt
>> >> >  - checking: /usr/local/etc/mythtv/mysql.txt
>> >> >  - checking: /etc/mythtv/mysql.txt
>> >> >  - checking: /home/daryl/.mythtv/mysql.txt
>> >> >  - checking: mysql.txt
>> >> >  - checking: /home/daryl/.mythtv/mysql.txt
>> >> >  - checking: /home/daryl/.mythtv/backuprc
>> >> >
>> >> >Applying command-line arguments.
>> >> >
>> >> >Checking configuration.
>> >> >
>> >> >WARNING: DBName not specified. Using mythconverg
>> >> >
>> >> >WARNING: DBHostName not specified.
>> >> >         Assuming it is specified in the MySQL options file.
>> >> >
>> >> >WARNING: DBUserName not specified.
>> >> >         Assuming it is specified in the MySQL options file.
>> >> >
>> >> >WARNING: DBPassword not specified.
>> >> >         Assuming it is specified in the MySQL options file.
>> >> >
>> >> >Database Information:
>> >> >         DBHostName:
>> >> >             DBPort: -1
>> >> >         DBUserName:
>> >> >         DBPassword:
>> >> >             DBName: mythconverg
>> >> >        DBSchemaVer:
>> >> >  DBBackupDirectory: /home/daryl
>> >> >   DBBackupFilename: mythconverg-1344-20180804112717.sql.gz
>> >> >      drop_database: no
>> >> >    create_database: yes
>> >> >
>> >> >Executables:
>> >> >       mysql_client: mysql
>> >> >         uncompress: gzip -d
>> >> >
>> >> >Miscellaneous:
>> >> >    partial_restore: no
>> >> >   restore_xmltvids: no
>> >> >    change_hostname: no
>> >> >
>> >> >Checking database.
>> >> >DBI connect('host=:database=mythconverg','',...) failed: Access denied
>> >> for
>> >> >user 'daryl'@'localhost' (using password: NO) at ./
>> mythconverg_restore.pl
>> >> >line 933.
>> >> >
>> >> >Preparing initial database.
>> >> >DBI connect('host=','',...) failed: Access denied for user 'daryl'@
>> >> 'localhost'
>> >> >(using password: NO) at ./mythconverg_restore.pl line 933.
>> >> >
>> >> >Unable to connect to database.
>> >> >           database: mythconverg
>> >> >               host:
>> >> >           username:
>> >> >           password:
>> >> >
>> >> >Please check your configuration files to verify the database connection
>> >> >information is correct.  The files that are used to retrieve connection
>> >> >information are prefixed with "parsing" in the "Parsing configuration
>> >> files"
>> >> >section of the --verbose output.
>> >> >
>> >> >Also note that any [client] or [mysql] password specified in the MySQL
>> >> >options
>> >> >file (/etc/my.cnf or /etc/mysql/my.cnf or ~/.my.cnf) will take
>> precedence
>> >> >over
>> >> >the password specified in the MythTV configuration files.
>> >> >
>> >> >ERROR: Unable to connect to database.
>> >>
>> >> It looks like you have restored the /etc/mythtv/config.xml file, which
>> >> contains the password to access the mythconverg database.  But the
>> >> MariaDB or MySQL database system you are restoring to is clean - it
>> >> does not have the user/password that was in your old database to allow
>> >> access to the mythconverg database.  The user/password data is not
>> >> stored in the mythconverg database - it is stored in a separate system
>> >> maintained database, and can not be restored by
>> >> mythconverg_restore.pl.
>> >>
>> >> So what you need to do is to do the SQL commands needed to add the
>> >> user/password from /etc/mythtv/config.xml into the MySQL
>> >> authentication system.  See this page for how:
>> >>
>> >> https://www.mythtv.org/wiki/Category:MySQL
>> >>
>> >> Search for "Access denied for user".
>> >>
>> >
>> >I don't think this went as it should have:
>> >
>> >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 42
>> >Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
>> >
>> >Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights
>> reserved.
>> >
>> >Oracle is a registered trademark of Oracle Corporation and/or its
>> >affiliates. Other names may be trademarks of their respective
>> >owners.
>> >
>> >Type 'help;' or '\h' for help. Type '\c' to clear the current input
>> >statement.
>> >
>> >mysql> GRANT ALL PRIVILEGES ON *.* TO 'mythtv'@'localhost' IDENTIFIED BY
>> >'mythtv' WITH GRANT OPTION;
>> >Query OK, 0 rows affected, 1 warning (0.02 sec)
>> >
>> >mysql> UPDATE user SET Password=h2TGgzwD('mythtv') WHERE user='mythtv';
>> >ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
>> >mysql> FLUSH PRIVELIGES
>>
>> You have misinterpreted the syntax of the GRANT command.  PASSWORD()
>> is a function that encrypts a plain-text password for storage into the
>> database.  So the command should be:
>>
>> UPDATE user SET Password=PASSWORD('h2TGgzwD') where user='mythtv';
>>
>> But as you have posted the actual password publicly here, it would be
>> a good idea to run the apg command (install with "apt install apg" if
>> necessary) and generate a new password to use, and put that in
>> /etc/mythtv/config.xml and in the above command.
>>
>
>With proper syntax (copy&paste) it still looks unproductive:
>daryl at trieli:~$ mysql -u root -p mysql
>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 12
>Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
>
>Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
>
>Oracle is a registered trademark of Oracle Corporation and/or its
>affiliates. Other names may be trademarks of their respective
>owners.
>
>Type 'help;' or '\h' for help. Type '\c' to clear the current input
>statement.
>
>mysql> GRANT ALL PRIVILEGES ON *.* TO 'mythtv'@'localhost' IDENTIFIED BY
>'mythtv' WITH GRANT OPTION;
>Query OK, 0 rows affected, 1 warning (0.01 sec)
>
>mysql> UPDATE user SET Password=PASSWORD('mythtv') WHERE user='mythtv';
>ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
>mysql> FLUSH PRIVILEGES;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> quit
>Bye

I have done some web searching and it seems that things have changed
with the latest version of MySQL (5.7).  MariaDB still works with the
UPDATE command as above, but MySQL does not.  So try this command
instead:

SET PASSWORD for 'mythtv' = PASSWORD('your new password');