Easy method to backup MySQL MyVideos121 to local SQLite3

Help and Support
1

Sorry if this is a duplicate; I’ve been searching and haven’t found what I’m looking for so far.

I have multiple OSMC Vero boxes, and have successfully started using a MariaDB server to use a shared MySQL database from all my OSMC Vero boxes, and I could not be happier!

I have one box that is going to be leaving my home soon, and I wish to keep providing my loved one access to the media they’re used to here at home. The shared MySQL database over a VPN ends up being slow to start and update, so I’ve been kicking around having a way to have a scripted solution that does the following:

Take a dump of the current shared MySQL(MariaDB) database, convert that to the SQLite3 format, and then run another script against the new local copy that clears any watched data as well as resume points on any movies/episodes.

I have successfully used mysql2sqlite pip script to almost satisfy the dump & convert portion:

mysql2sqlite -f /mnt/TMPworking/$sqlite3_dbname -h $mysql_host -P 3306 -d $mysql_dbname -u $mysql_user --mysql-password $mysql_password -V --use-buffered-cursors

It looks great overall (All media and corrections from initial scrapes are there), but I am unable to mark anything as watched, nor track current resume points for movies/episodes. So my guess is with a couple of tweaks to my method I could have this portion working (currently this takes ~30min to complete).

As far as the watched data clearing, in a database that allows me to manually set/unset per file via GUI, I have been successful in using the following script to clear all watched states:

kodi-send --action=“CleanLibrary(video)”
sleep 60
kodi-send --action=“UpdateLibrary(video)”

However, I can’t seem to find a way to also clear any resume info for movies/episodes in-progress.

I have also tried using the Export/Import method, Exporting from a OSMC Kodi box with Export Library (of the MySQL shared library), then starting with a blank local database, letting Kodi start, and then doing an import (settings>Media>Library>Import library). This has been running (and crashing) for over 12 hours now, where after each crash I must manually restart.

I would really like to see my dump/convert option realized, as it is so much faster & would allow for an easy backup of the MySQL shared db that can be fallen back to if the MySQL server dies or has some other mishap.

Any help is appreciated. Thank you.

2

It sounds like it would have been a lot less effort to just scrape the library in from scratch.

Personally, I run a Plex server on my file server to give access to my library outside of my house. I prefer Kodi so that Is what I use in my home with a MySQL database linking my various clients. But outside my home it is preferable to me to allow more freedom of clients as well as on the fly transcoding to deal with bandwidth and client restrictions.

3

Thanks darwindesign, but I’ve never really liked Plex, and rescraping takes well over a day, even with the new OSMC Vero V. After rescraping, then I’ve spent hours fixing items that scraped incorrectly or that I wanted in a particular collection, etc… The proposed solution takes all the work I’ve done & work I will do & leverages it for a quick 30 min fix. I’ve got to get this perfected or my parents will never use it. They are used to Kodi from living with us, and it is likely they will eventually break everything by messing around with it, and I need a way to “refresh” everything without hours of rescraping, etc…

I am not a dba guy, so I am turning to those that might be. I am also not aware of the right command(s) to accomplish the watch/resume clearing, so I am asking for help. Thanks.

4

I’m not a dba guy either so I can’t help you with that. If you export your library out to individual files then it will place nfo and artwork files next to your media preserving all your current library tweaks. When you scrape a new library with those files in place you get a fateful reproduction of the library. If you add in the options to an advancedsettings.xml you can have it not import watched state if that is the preference as the export contains watched state in the nfo.

If your not a fan of Plex there are a few alternatives that are very similar. I would say though that you can add a Plex library to Kodi via PKC such that it is transparent to a regular Kodi library.

You can lock Kodi down enough that this shouldn’t be an issue.

I’m not sure about resume, but an easy way to reset watched status in bulk inside Kodi would be to go into the movie or TV library then click on the ellipsis at the top of the list and then context menu over the series or titles option than then you will see an option to set as unwatched.