Re: MySQL maintenance

On 28/06/14 14:45, James Linder wrote:
>
> I use mysql for (amongst others) mythtv. I note that on a power fail 3 times out of 4 mysql is FOOBAR.
>
> This sorts corrupt tables, but how why ? etc on ext4 drives
>
> mysqlcheck --auto-repair --check -u mythtv -pmythtv mythconverg
>
> or
>
> mysqlcheck --auto-repair -A -u mythtv -pmythtv mythconverg
>
> perhaps (match to your distro) in rc.local
>
> James
It is for this particular reason that I convert all mythtv's myisam 
tables to innodb.
It is ACID compliant, so that a powerfail doesn't leave you with a 
screwed table.
I suggested to the myth devs that they change to innodb by default and 
was basically laughed at, as if you don't run a UPS on your media 
machine, also inno isn't available by default on some ancient releases 
of some distros.

With recent mysql (5.5) the default engine has changed to innodb so odds 
are a recent clean install will already be in innodb, if you are running 
from an older database you will want to do the conversion.

I haven't tested this since 2008 but this is the script I used to do the 
conversion
(take a backup first)

for t in $(mysql --batch --column-names=false -e "show tables" 
mythconverg |grep -v "exclude_this");
do
mysql -e "alter table $t type=InnoDB" mythconverg;
done

This will take a while depending on how big your database is and the 
spec on your system.
I'd suggest shutting mythbackend down first.

Oh to fix broken recordings in the table (when you jump around you skip 
random times not 30 seconds or whatever)

find /var/lib/mythtv/recordings -iname "*.mpg" -mtime -4 -print0 | xargs 
-0 -n1 mythcommflag --rebuild --file

That will re-build the seektable for any file modified in the last 4 days.
It'll take a while to run but its pretty quick, quicker than standard 
commflag.

-- 
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html