HOWTO Setup MySQL for MythTV

From Gentoo Linux Wiki

[edit] MySQL Setup

MythTV uses MySQL to store program listings, recording schedules, and other information. To get the database up and running, follow the HOWTO MySQL guide.

To see if you have problems because of UTF8, run mythbackend in a terminal. If you get an error about the primary key being too long try:
$mysql -u root
DROP DATABASE mythconverg;
CREATE DATABASE mythconverg CHARACTER SET latin1;
exit;

[edit] Install MythTV Database

Once MySQL is setup, install the MythTV Database. This is done by running:

mysql -u root -p < /usr/share/mythtv/database/mc.sql

If you plan to have your frontend and backend on separate machines, you need to allow MySQL to accept connections from other machines. To do this, comment out the bind-address setting:

...
set-variable    = key_buffer=16M
set-variable    = max_allowed_packet=1M
set-variable    = thread_stack=128K
# keep secure by default!
#bind-address    = 127.0.0.1
port            = 3306
# this can make it even more secure:
#skip-networking
...

[edit] Backing Up Your Database

It is advisable to have a cron job that backs up your database on a regular (weekly, monthly) basis. A script like this will do what you need.

#!/bin/sh
exec /bin/nice su myth -c 'cd ~; mysqldump \
  -uroot --password=password \
  -hlocalhost --all-databases \
  --opt --allow-keywords \
  --flush-logs --hex-blob \
  --master-data --max_allowed_packet=16M \
  --quote-names --result-file=MYTH-DATABASE.SQL'

[edit] Log Setup

MySQL keeps binary logs of all its transactions and by default places no limit on how large they may grow. To stop them from growing forever, add a line like

...
log-bin
server-id        = 1
expire_logs_days = 8
...

This will only keep 8 days of logs. Combined with the weekly backups, you will always be able to restore.

[edit] Troubleshooting

There are a few things to check in order to make sure you have a fully functioning Myth database. Mostly to make sure it can be accessed my a remote front end. It may be helpful to know how to connect to your database directly too:

Run this command from the machine the database is on to check it can connect to it.

# mysql -h localhost -u mysql -p mythconverg

Run this command from the machine the database in on to check it can connect to it via its ip address (leave out the quotes)

# mysql -h "localhost's ip" -u mysql -p mythconverg

And then run this from a remote machine to check that a remote machine can connect (again leavening out the quotes)

# mysql -h "localhost's ip" -u mysql -p mythconverg

If you can connect each time then all should be well, if not, read on.

• Edit your '/etc/mysql/my.cnf' file and comment out the line:

#bind-address                           = 127.0.0.1

• To grant access to the database from the localhost and from separate, remote front-ends run these commands on the master back-end server:

# mysql -u root -p

mysql> grant all on mythconverg.* to mythtv@'localhost' identified by 'mythtv';
mysql> grant all on mythconverg.* to mythtv@'%' identified by 'mythtv';
mysql> flush privileges;
mysql> quit

The '%' is a wild card you can replace it with '192.168.1.%' for example.