What seems like a no brainer I know. If I want to move files from one place to another all I do is copy them right? Wrong… what about all your videometadata… directors, screenshots, plot synopsis…. or just the nice titles you had all put in place before you moved the files. I purchased a shiny new 500 gigger today to bring my mythbox up to 1 Terabyte (wow… I still remember when I upgraded to my first 100 meg drive…. wow…) and my plan was to split my movies folder off from the rest of my videos directory. The problem is, moving them would cause me to loose my metadata. I tried the usually places, the MythTV Forums, which are very useful for many ways but no one there could help me.

MythTV holds all of its data in a MySQL database, usually called mythconverg. While mythtv has a spot for you to specify the directory of your videos, it will only LOOK there for them. It will not change the locations of all of your existing videos. The file name including the directory is located in videometadata in a table called filename. I popped onto IRC and joined a MySQL room and asked the question. Within 15 minutes a very helpful person who asked not to be named came to my rescue. I tried it, and it worked. Here is what you NEED to do to move your files in mythvideo and KEEP your videometadata intact.

Please note, this is for moving mythvideo stuff, NOT mythtv recordings. I am sure it would be similar… but since i don’t use my box to record off of cable, I would not know for sure. If you know…. please leave it in a comment and I will put it into the HOWTO.

FIRST and foremost, please backup your mythtv MySQL database.

$ mysqldump -u <myth_user> -p --extended-insert --databases <myth_db_name> > mythdatabase.bak
Password: <myth_password>

Taken from: Backup Your Database

Be sure to replace <myth_user>, <myth_db_name>, and <myth_password> with the appropriate values.

In my case the root_user is mythtv and the password (if you don’t know it) is located in the general setup of your mythtv program. Enter the program and go Utilities / Setup > Setup > General. The password and username will be there. Please note, it IS case sensitive.

This will backup your mythtv database. It will backit up to a text file so you will be able to compress it down to a much smaller file. I actually back mine up once a week. Once this is done you can make the change to the MySQL database.

$ echo 'set @newdir="/the/new/directory/";set @olddir = "/the/old/directory/";update
videometadata set filename = concat(@newdir,substr(filename,char_length(@olddir)+1))
where left (filename,char_length(@olddir)) = @olddir;' | mysql -umythtv -p
mythconverg

This statement assumes the directory names, change those with yours. DON’T FORGET THE LEADING SLASHES. I did that and had to do a restore to get my database back again. It also assumes the username and database name. Change those with whatever yours is. This is ONE statement. I had to format it a little to get it to display on the blog, so please make note of that when you are copying and pasting, it should be ONE long continuous statement

To see if it worked

echo ’select filename from videometadata;’ | mysql -umythtv -p mythconverg

This will give you an output of the filename column of the videometadata table. A quick glance will tell you if you did it right.

If you skrew everything up, and need to restore your original database, you can do this.

$ mysql -u <root_user> -p <mythdatabase.bak
Password: <root_password>

If you get errors with this try using..

 $mysql -u root -p mythconverg < myth_bakup.sql Password: <password>

Taken from: Backup Your Database

Once this is done you should be able to kick back and enjoy your stuff again, with all your lovely videometadata intact.

Now, in my case I did not have to move my posters & screenshots. These are located in the videometadata table under the column of coverfile. If you have moved these as well, you can use the same SQL statement (substituting the column name and directory names to move them) If you are looking for your coverfiles…. mine were located in /home/malocite/.mythtv/MythVideo/

I hope this helps a lot of people. I am not sure why this wasnt’t written about sooner, sometimes I think every HOWTO has already been written, but then I find something like this and think, ahh… what the hell. I’ll do ‘er up.

This worked on a system running mythbuntu 7.04. Mythbuntu is an Ubuntu variant that has myth installed along with it. Its really easy to setup and to run. My other system (the newer one) runs Mythbuntu 8.04.

Please let me know how it works for you, if it was helpful, a shoutout in the comments or a DIGG would be super nice! Thanks!

Add to: | Technorati | Digg | del.icio.us | Yahoo | BlinkList | Spurl | reddit | Furl |

Technorati Tags:
MythTV, Mythbuntu, Ubuntu, MythVideo, MySQL, SQL Statements, Backup your MythTV, Move your Mythvideo files