[mythtv-users] db restore

[Stephen Worthington]

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.