Posts Tagged ‘mysql database restore’

Restoring mythtv database from backup

December 24, 2008

My mythtv database got corrupted and mysqlcheck repair and check commands

mysqlcheck --repair -u mythtv -pmythtv mythconverg
and
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/mythconverg_backup.pl --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
root@hippo:~/mytti#

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";
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@localhost IDENTIFIED BY "mythtv";
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET latin1;

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

Next restore command:

root@hippo:~/mytti# mythconverg_restore.pl --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;
Bye

This fails since no database exists:

root@hippo:~/mytti# mythconverg_restore.pl --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# mythconverg_restore.pl --filename mythconverg-20081223040002.sql.gz

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

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