Archive for the ‘mysql’ Category

HOWTO to restore mythtv database

May 12, 2011

Mythtv database got mangled. This is how I restored it from backup database:

root@hippo:/home/iso$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 10.10
Release:        10.10
Codename:       maverick
You have new mail in /var/mail/root
root@hippo:/home/iso$

root@hippo:/home/iso$ mythbackend –version
Please attach all output as a file in bug reports.
MythTV Version   : 26437
MythTV Branch    : branches/release-0-23-fixes
Network Protocol : 23056
Library API      : 0.23.1.201000710-1
QT Version       : 4.7.0
Options compiled in:
linux debug using_oss using_alsa using_pulse using_jack using_pulseoutput using_backend using_dvb using_firewire using_frontend using_glx_proc_addr_arb using_hdhomerun using_hdpvr using_iptv using_ivtv using_joystick_menu using_libudev using_lirc using_mheg using_opengl_video using_opengl_vsync using_qtdbus using_qtwebkit using_v4l using_x11 using_xrandr using_xv using_xvmc using_xvmc_vld using_xvmcw using_bindings_perl using_bindings_python using_opengl using_vdpau using_ffmpeg_threads using_libavc_5_3 using_live using_mheg
root@hippo:/home/iso$

root@hippo:~/.mythtv#
root@hippo:~/.mythtv# cat /root/.mythtv/config.xml

<!–
Set the hostname override below only if you want to use
something other than the machine’s real hostname for identifying settings
in the database. This is useful if your hostname changes often, as
otherwise you’ll need to reconfigure mythtv every time.

NO TWO HOSTS MAY USE THE SAME VALUE
–>
localhost
mythtv
mythtv
mythconverg
0

root@hippo:~/.mythtv# cat /root/.mythtv/backuprc
DBBackupDirectory=/home/root/mytti
BUsername=mythtv
DBPassword=mythtv
root@hippo:~/.mythtv#

root@hippo:/home/iso$  /usr/share/mythtv/mythconverg_restore.pl –version
MythTV Database Restore Script
mythconverg_restore.pl
version: 1.0.13
root@hippo:/home/iso$

Note usage of these options:

–drop_database

If specified, and if the database already exists, the script will attempt
to drop the database. This argument may only be used when the
–create_database argument is also specified (see below).

–create_database

If specified, and if the database does not exist or the –drop_database
argument is specified, the script will attempt to create the initial
database. Note that database creation requires a properly configured MySQL
user and permissions.  See, also, the MythTV HOWTO (
http://www.mythtv.org/docs/ ) for details on “Setting up the initial
database.”

root@hippo:~/.mythtv# /usr/share/mythtv/mythconverg_restore.pl –verbose –drop_database –create_database  –username mythtv –filename  mythconverg-1254-20110510040002.sql.gz

Configuring environment:
–    username: root
–        HOME: /root
– MYTHCONFDIR: /root/.mythtv

Parsing configuration files:
– checking: /root/.mythtv/config.xml
parsing: /root/.mythtv/config.xml
– checking: /root/.mythtv/backuprc
parsing: /root/.mythtv/backuprc

Applying command-line arguments.

Checking configuration.

Database Information:
DBHostName: localhost
DBPort: 0
DBUserName: mythtv
DBPassword: XXX
DBName: mythconverg
DBSchemaVer:
DBBackupDirectory: /home/root/mytti
DBBackupFilename: mythconverg-1254-20110510040002.sql.gz
drop_database: yes
create_database: yes

Executables:
mysql_client: mysql
uncompress: gzip -d

Miscellaneous:
partial_restore: no
restore_xmltvids: no
change_hostname: no

Checking database.

Preparing initial database.
Dropping database.
Creating database.
Setting database character set.

Found 0 tables in the database.

Backup file is compressed.
– Uncompressing backup file with IO::Uncompress::Gunzip.

Attempting to use supplied password for mysql command-line client.
Any [client] or [mysql] password specified in the MySQL options file will
take precedence.

Executing command:
‘mysql’ –defaults-extra-file=’/tmp/rdrnijWlZM’ –host=’localhost’ –user=’mythtv’ ‘mythconverg’

mysql exited with status: 0

Restored 3599 of 3599 lines.

Successfully restored backup.
root@hippo:~/.mythtv#

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.