[mythtv-users] BE3 - Issues with installation and database

[Stephen Worthington]

On Wed, 5 Jun 2019 17:46:48 -0500, you wrote:

>
>Hi Stephen!
>
>(From yesterday’s)
>
>If you want to go ahead and export all the existing recordings from
>BE2 to BE3, then you need to first decide how you are going to move
>all the recording files to BE3.  How many recordings are there?  Do
>you want to move the hard drive(s) with the recording files from BE2
>to BE3?  That would probably be the easiest if there are lots of
>recordings.  Otherwise, you would first need to ensure that BE3 has
>enough space on its recording drives for all of BE2's recordings, plus
>space for any new ones it will be making.  Then you would need to set
>up network access (using SAMBA?) for BE2 to be able to write files to
>BE3's recording drives.  Or you could temporarily install each BE2
>recording drive on BE3 to move the recording files.  Or use an
>external drive mount to do that.  I will need to know how this is
>going to happen in order to do a custom version of mythexport for you.
>Also, does BE2 have multiple recording file locations (multiple
>directories in its recording storage groups)?  Does BE3 record to any
>storage groups other than "Default"?  Does BE3 have multiple recording
>drives, or only one?
>
>
>(From today’s)
>
>Some SQL queries you can run on BE2 and BE3 that can help with all this:
>do_mythconverg.sh
># Number of recordings
>select count(*) from recorded;
># Total size of recordings in bytes
>select sum(filesize) from recorded;
># Total size of recordings in Gbytes
>select sum(filesize)/1000/1000/1000 from recorded;
># List of all partitions in all storage groups
>select * from storagegroup order by groupname,dirname;
>quit
>
>    Glad I procrastinated! The above mythconverg commands makes things a
>    lot easier!!
>
>
>    Run from Backend-3 – the destination box:
>
>
>    barry at Backend-3:~$ do_mythconverg.sh
>
>    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 MariaDB monitor. Commands end with ; or \g.
>
>    Your MariaDB connection id is 4816
>
>    Server version: 10.1.38-MariaDB-0ubuntu0.18.04.2 Ubuntu 18.04
>
>
>    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
>
>
>    Type 'help;' or '\h' for help. Type '\c' to clear the current input
>    statement.
>
>
>
>    MariaDB [mythconverg]> select count(*) from recorded;
>
>    +----------+
>
>    | count(*) |
>
>    +----------+
>
>    | 110 |
>
>    +----------+
>
>    1 row in set (0.00 sec)
>
>
>
>    MariaDB [mythconverg]> select sum(filesize) from recorded;
>
>    +---------------+
>
>    | sum(filesize) |
>
>    +---------------+
>
>    | 335301163476 |
>
>    +---------------+
>
>    1 row in set (0.00 sec)
>
>
>
>    MariaDB [mythconverg]> select sum(filesize)/1000/1000/1000 from
>    recorded;
>
>    +------------------------------+
>
>    | sum(filesize)/1000/1000/1000 |
>
>    +------------------------------+
>
>    | 335.301163476000 |
>
>    +------------------------------+
>
>    1 row in set (0.00 sec)
>
>
>
>    MariaDB [mythconverg]> select * from storagegroup order by
>    groupname,dirname;
>
>    +----+-------------+-----------+---------------------------------+
>
>    | id | groupname | hostname | dirname |
>
>    +----+-------------+-----------+---------------------------------+
>
>    | 9 | Banners | Backend-3 | /home/barry/Mythtv/banners/ |
>
>    | 5 | Coverart | Backend-3 | /home/barry/Mythtv/coverart/ |
>
>    | 2 | DB Backups | Backend-3 | /home/barry/Mythtv/db_backups/ |
>
>    | 14 | Default | Backend-3 | /home/barry/Mythtv/recordings/ |
>
>    | 7 | Fanart | Backend-3 | /home/barry/Mythtv/fanart/ |
>
>    | 1 | LiveTV | Backend-3 | /home/barry/Mythtv/livetv/ |
>
>    | 11 | Music | Backend-3 | /home/barry/Mythtv/music/ |
>
>    | 12 | MusicArt | Backend-3 | /home/barry/Mythtv/music_art/ |
>
>    | 10 | Photographs | Backend-3 | /home/barry/Mythtv/photographs/ |
>
>    | 8 | Screenshots | Backend-3 | /home/barry/Mythtv/screenshots/ |
>
>    | 4 | Trailers | Backend-3 | /home/barry/Mythtv/trailers/ |
>
>    | 3 | Videos | Backend-3 | /home/barry/Mythtv/videos/ |
>
>    +----+-------------+-----------+---------------------------------+
>
>    12 rows in set (0.01 sec)
>
>
>    MariaDB [mythconverg]>
>
>
>
>    Run from BE2 – the source box:
>
>    (Real name is MythBE2)
>
>
>    barry at MythBE2:~$ do_mythconverg.sh
>
>    bash: /usr/local/bin/do_mythconverg.sh: Permission denied
>
>    (I had not rebooted the machine following installation, not sure if
>    the need for sudo shows anything or a special requirment ro run
>    mythimport/mythexport.)
>
>    barry at MythBE2:~$ sudo do_mythconverg.sh
>
>    mysql: [Warning] Using a password on the command line interface can
>    be insecure.
>
>    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 21977
>
>    Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)
>
>
>    Copyright (c) 2000, 2019, 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> select count(*) from recorded;
>
>    +----------+
>
>    | count(*) |
>
>    +----------+
>
>    | 878 |
>
>    +----------+
>
>    1 row in set (0.02 sec)
>
>
>
>    mysql> select sum(filesize) from recorded;
>
>    +---------------+
>
>    | sum(filesize) |
>
>    +---------------+
>
>    | 2513111679220 |
>
>    +---------------+
>
>    1 row in set (0.00 sec)
>
>
>
>    mysql> select sum(filesize)/100/1000/1000 from recorded;
>
>    +-----------------------------+
>
>    | sum(filesize)/100/1000/1000 |
>
>    +-----------------------------+
>
>    | 25131.413409200000 |
>
>    +-----------------------------+
>
>    1 row in set (0.00 sec)
>
>
>
>    mysql> select * from storagegroup order by groupname,dirname;
>
>    +----+-------------+----------+----------------------------+
>
>    | id | groupname | hostname | dirname |
>
>    +----+-------------+----------+----------------------------+
>
>    | 9 | Banners | MythBE2 | /data3/mythtv/banners/ |
>
>    | 6 | Coverart | MythBE2 | /data3/mythtv/coverart/ |
>
>    | 3 | DB Backups | MythBE2 | /data3/mythtv/db_backups/ |
>
>    | 1 | Default | MythBE2 | /data3/mythtv/recordings/ |
>
>    | 7 | Fanart | MythBE2 | /data3/mythtv/fanart/ |
>
>    | 2 | LiveTV | MythBE2 | /data3/mythtv/livetv/ |
>
>    | 11 | Music | MythBE2 | /data3/mythtv/music/ |
>
>    | 12 | MusicArt | MythBE2 | /data3/mythtv/music_art/ |
>
>    | 10 | Photographs | MythBE2 | /data3/mythtv/photographs/ |
>
>    | 8 | Screenshots | MythBE2 | /data3/mythtv/screenshots/ |
>
>    | 13 | Streaming | MythBE2 | /data3/mythtv/streaming/ |
>
>    | 5 | Trailers | MythBE2 | /data3/mythtv/trailers/ |
>
>    | 4 | Videos | MythBE2 | /data3/mythtv/videos/ |
>
>    +----+-------------+----------+----------------------------+
>
>    13 rows in set (0.00 sec)
>
>
>    mysql>
>
>
>
>    BE2 and BE3 record to only /*/recordings.
>
>
>    BE2 has three hard drives:
>
>    /dev/sda1 is /data2 WD WDC20EARS – old shows not watched and to be
>    discarded
>
>    /dev/sdb1 is root (/) WD WDC800BB
>
>    /dev/sdc1 is /data3 WD WD30EFRX - has MythTV’s recordings
>
>
>
>    Samba is available on BE2 but not installed on BE3 – if need will.
>
>
>    BE3 has one drive: root, MythTV, etc.
>
>    /dev/sda2 is root, and everything else.
>
>
>    Think I got all your questions answered. Thanks again for all the help!
>
>    Barry

OK, that looks like I have all the information I need.  BE2 has 2.5
Tbytes of recordings that you want moved to BE3.  There is only one
recording partition each on BE2 and BE3.  So the setup is quite
simple.

I can now write a version of mythexport that will create a .sql file
with all the data for each of the recordings on BE3 and store it in
the same directory as the recording files.  Then we need to get that
directory copied or moved to BE3 and run mythimport from it on BE3. My
plan is to have the special mythexport version run in the recording
directory on BE2, and it will simply take a list of all the recording
files in that directory (*.mpg, *.ts and *.nuv) and for each of those
files, it will run mysqldump with the right commands to export all the
database entries matching that recording (from these tables: recorded,
recordedseek, recordedrating, recordedprogram, recordedmarkup and
recordedcredits).

The only thing really left to decide is that 2.5 Tbytes will take a
very long time to move to a new drive, either directly between drives
or over Ethernet.  The easiest way to get all those recordings onto
BE3 is simply to take that drive out of BE2 and install it on BE3,
then import the data for the recordings into BE3's database.  The
files are never moved, and BE3 then has a lot more space for future
recordings.

If you want to keep using BE2 to record, then the recording files will
need to be copied or moved to BE3's drive, which has enough space for
them all plus what is already there.  Given how much data there is to
be moved, it is going to take a day or more.  So you will likely want
to have BE3 (and maybe also BE2) doing recordings at the same time.
That is a bit more complicated, but I do have code that can detect
when the next recording time is going to be, and stop a copy or move
that is in progress before that time.  So it should be possible to
expand that code a bit so that it will check both BE2 and BE3 for the
next recording time and will stop if either of them need to record.
All of the recording files on BE2 that were being moved would be put
in a special new directory that was in a new storage group on BE2, not
"Default".  That prevents new BE2 recordings from being put into that
directory, and would allow the copy/move software to work without
having to handle the problem of new files appearing.  The copying or
moving would use a network connection to copy or move the BE2 files to
BE3, and as it completed the copy or move of a file, it would move
that recording out of the current BE2 directory to yet another one
that you would create that was also in BE2's new storage group, where
BE2 could see it but the copy/move program would not see it any more.