[mythtv-users] Database backup failing
rcs at malibyte.net
Fri Jul 20 23:53:33 UTC 2018
> On Wed, 11 Jul 2018 23:22:00 -0700, you wrote:
>> Hi all -
>> I have been having issues with Mysql since the latest update. First,
>> the update itself failed and I had to ask Dr. Google how to MacGyver a
>> fix for that (yes, it worked, got it installed). However, since then
>> have not been able to get mythconverg_backup.pl to work. If fails as
>> rcs at vader: ~]$ /usr/share/mythtv/mythconverg_backup.pl -verbose
>> Configuring environment:
>> - username: rcs
>> - HOME: /home/rcs
>> - MYTHCONFDIR: /home/rcs/.mythtv
>> Parsing configuration files:
>> - checking: /home/rcs/.mythtv/config.xml
>> parsing: /home/rcs/.mythtv/config.xml
>> - checking: /home/rcs/.mythtv/backuprc
>> parsing: /home/rcs/.mythtv/backuprc
>> Applying command-line arguments.
>> Checking configuration.
>> No DBSchemaVer specified, querying database.
>> Found DBSchemaVer: 1344.
>> Database Information:
>> DBHostName: 192.168.1.9
>> DBPort: 3306
>> DBUserName: mythtv
>> DBPassword: XXX
>> DBName: mythconverg
>> DBSchemaVer: 1344
>> DBBackupDirectory: /misc/dbbackup
>> DBBackupFilename: mythconverg-1344-20180711231352.sql
>> mysqldump: mysqldump
>> compress: gzip
>> Attempting to use supplied password for mysqldump.
>> Any [client] or [mysqldump] password specified in the MySQL options
>> take precedence.
>> Executing command:
>> '/usr/bin/mysqldump' --defaults-extra-file='/tmp/aVusmdWS2l'
>> --host='192.168.1.9' --port='3306' --user='mythtv' --add-drop-table
>> --add-locks --allow-keywords --complete-insert --extended-insert
>> --lock-tables --no-create-db --quick --add-drop-table 'mythconverg'
>> mysqldump exited with status: 2
>> mysqldump output:
>> mysqldump: Got error: 1812: Tablespace is missing for table
>> `mythconverg`.`music_albumart`. when using LOCK TABLES
>> Running 0.28.x-fixes (via mythbuntu repo) on Ubuntu 16.04 LTS, all
>> currently updated.
>> I'm not even using mythmusic (not a fan of the interface...I use vlc
>> Rhythmbox to play music files on this machine).
>> Is there an easy way to fix this? I am by no means a Mysql guru.
>> Thanks in advance! I definitely am not happy with not like not having
>> current DB backup (though I back up the whole system every couple of
> Before trying any fixes, it is best to make a manual backup of the
> database files, just in case. To do that, first shut down mythbackend
> and MySQL/MariaDB (sudo or root required):
> systemctl stop mythtv-backend
> systemctl stop mysql
> Then go to where the mythconverg database is stored:
> cd /var/lib/mysql
> Find out how big your database is:
> du -h mythconverg
> Check that you have enough free space on your system drive for a
> second copy of the database:
> df -h .
> If so, then just copy the entire database directory:
> cp -a mythconverg mythconverg-bak
> If there is not enough free space, you will need to do the copy to
> another drive.
> Then restart MySQL/MariaDB and try to repair the mythconverg database:
> systemctl start mysql
> The first thing to try when you have any problems with database tables
> is to run the database check and repair script:
> If you originally installed MythTV with the old Mythbuntu Control
> Centre, that script should be set up to be automatically run daily
> If it is not in cron.daily, put it there. MySQL/MariaDB databases
> seem to be unstable unless a check and repair is done regularly, and
> it should also be done after any bad shutdown, such as a power failure
> or use of the reset button, as tables can be damaged by that.
> If optimize_mythtb.pl does not work, try using the mysqlrepair command
> on the bad table(s). It has various options you can try. The backup
> stopped when it found the first bad table, so there may be others
> after that also in need of repairs.
> If that does not work, then the other thing that you can do is to
> uninstall the mythmusic package. Installing mythmusic creates all the
> music_* tables in the database, but I am not sure if removing it will
> also delete all those tables. If it does not, once the package is
> removed, you can manually delete those tables with a DROP command for
> each table, if necessary. This is almost certain to fix any problems
> in the music_* tables, but it is still possible that there are
> problems in the tables that were never processed due to the backup
> script stopping.
> Once all appears working again, check that the backup script works,
> before deleting the backup copy of the database files:
> rm -r /var/lib/mysql/mythconverg-bak
> WARNING: Copy and paste the rm command - mistyping an rm -r command
> can cause a disaster!
>> Great information Stephen.
> I will be keeping a copy of this email in the hope that I will never
> it :-)
Thanks very much - finally found some time to do this. The
optimize_mythdb.pl and mysqlrepair scripts did not work - even after
doing a remove/purge of mythmusic.
I wound up going into mysql and dropping mythmusic-generated tables
(about 10 of them). After doing that, all is well.
I did add the optimize_mythdb.pl script to /etc/cron.daily, also - hope
that will keep the database in good shape for here on in.
More information about the mythtv-users