Restoring mythtv database from backup

My mythtv database got corrupted and mysqlcheck repair and check commands

mysqlcheck --repair -u mythtv -pmythtv mythconverg
mysqlcheck --check -u mythtv -pmythtv mythconverg

were unable to fix it.

Ironically database got corrupted when my backend server crashed and rebooted while defragmenting xfs file system that stores my recordings..

Error message from log files:

root@hippo:/var/log# grep recorded syslog
syslog:Dec 23 20:12:42 hippo mysqld[3432]: 081223 20:12:42 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './mythconverg/recorded'; try to repair it

root@hippo:/var/log/mythtv# grep Incorrect mythbackend.log
mythbackend.log:Incorrect key file for table './mythconverg/recorded'; try to repair it

Also myisamchk was unable to fix recorded.MYI file:

root@hippo: cd /var/lib/mysql/mythconverg
root@hippo:/var/lib/mysql/mythconverg# myisamchk -f -r recorded.MYI
myisamchk: Unknown error 126
myisamchk: error: 'recorded.MYI' doesn't have a correct index definition. You need to recreate it before you can do a repair

So mythtv backup file to rescue…

I’m using this perl script to backup my mythtv database daily.

My crontab:

root@hippo:/var/log/mythtv# crontab -l
# m h  dom mon dow   command
00 4 * * * nice -19 /usr/local/bin/ --rotate 3

Backup files are here:

root@hippo:~/mytti# ls -ls
total 165152
19284 -rw-r--r-- 1 root root 19721887 2008-12-21 04:00 mythconverg-20081221040003.sql.gz
18736 -rw-r--r-- 1 root root 19160852 2008-12-22 04:00 mythconverg-20081222040003.sql.gz
18180 -rw-r--r-- 1 root root 18589893 2008-12-23 04:00 mythconverg-20081223040002.sql.gz

So restore full myth database per these instructions

Database itself must be created first:

root@hippo:~/mytti# cat /usr/share/mythtv/sql/mc.sql
CREATE DATABASE if not exists mythconverg;
GRANT ALL ON mythconverg.* TO mythtv@localhost IDENTIFIED BY "mythtv";
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@localhost IDENTIFIED BY "mythtv";

root@hippo:~/mytti# mysql -uroot -p < /usr/share/mythtv/sql/mc.sql
Enter password:

Next restore command:

root@hippo:~/mytti# --filename mythconverg-20081223040002.sql.gz

ERROR:  Unable to do a full restore. The database contains data.

It fails since database contains data. So we drop database:

root@hippo:~/mytti# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.0.51a-3ubuntu5.4 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysq> DROP DATABASE IF EXISTS mythconverg;
Query OK, 96 rows affected (0.05 sec)

mysql> quit;

This fails since no database exists:

root@hippo:~/mytti# --filename mythconverg-20081223040002.sql.gz

ERROR:  The database does not exist.

Need to recreate database:

root@hippo:~/mytti# mysql -uroot -p< /usr/share/mythtv/sql/mc.sql
Enter password:

Now restore works:

root@hippo:~/mytti# --filename mythconverg-20081223040002.sql.gz

Successfully restored backup.
[ OK ]
root@hippo:~/mytti# /etc/init.d/mythtv-backend start
* Starting MythTV server: mythbackend                                                             [ OK ]

And all of my recordings and recording schedule are backup again.


Tags: ,

4 Responses to “Restoring mythtv database from backup”

  1. Aaron Says:

    Thank you so much. You should summit this for the mythtv website sinc eit is a TON clearer than what they have there. Great work!

  2. fayk naym Says:

    Thank you! That was exactly what I needed.

  3. MythTV Help, I screwed up my database... MythTV Says:

    […] the database try myisamchk…ing-myisamchk/ Recreating the DB…e-from-backup/ Next lesson after "do backups" (ha ha ha reminds me I should do one!) is "when […]

  4. whizbo Says:

    Awesome. The step by step including “database contains data” and “database does not exist” errors is perfect for the mysql illiterate (read me)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: