User:Klojum/Testkees/MySQL

Share libraries w/MySQL, guide: Introduction Setting up MySQL Setting up Kodi Sharing artwork Sync other parts of Kodi Upgrading XBMC/Kodi Portable devices and profiles Advanced notes Backup and restore databases Update paths in MySQL

MySQL

MySQL

Preamble

Why do we need this

This guide is to help you move your media to a new location without losing all of your current watched status, date added, last played, resume time (for shows/movies that you started watching and did not finish), etc. when using a MySQL database. The goal is to be able to relocate your media without disrupting your current library or being forced to scrape all of the media over again.

There are basically two types of Kodi users when it comes to MySQL usage: the complete novice and the veteran computer users with SQL knowledge. Those with sufficient SQL query experience can go to the Methode 1 - TL;DR section below, and start right of the bat.

The target audience here is the complete novice to all of this, so veteran users (particularly those well versed in SQL and SQL queries) will be able to cut out the majority of these instructions and simply follow the queries. With this in mind, I will be doing all of this using a program that will help make novice users more comfortable (hopefully) navigating this process.

These instructions are written from a Windows environment. They should be pretty easy to adapt, however I will be covering this using a Windows based MySQL editor so you will need to adapt for your environment.

When is this necessary or helpful?

• Your primary media hard drive is full and you need to migrate to a larger hard drive
• You are using external hard drives connected to your PC and need to move to a new PC
• You are restoring your media from a backup to a new location/machine
• You are replacing a hard drive that is dying but cannot or do not want to replicate the path exactly as before
• You want to clean up your file paths
• You want to move from local sources to network sources

There are other times when this is necessary, but these examples will give you an idea of the purpose of these instructions. To put it very simply, we are going to keep the same media but move it to a new file path.

Before We Begin

You will need to grab and install a copy of HeidiSQL or similar SQL editing program (note that instructions will be based on HeidiSQL).

Be sure that all of your Kodi clients using your MySQL database are shut down and will not be started again until we are completely finished.

Open HeidiSQL and configure a connection to your database. The only settings that need to be changed are the hostname, username, and password. For your username and password, you can use the same ones you use for Kodi.

Leave HeidiSQL open, we'll come back to it shortly.

Warning: Back up your Database

Before continuing, it is very important that you backup your database before you even begin. It's also important that you backup your database when instructed throughout the guide to expedite recovery and resume the process with as few steps as possible if something goes wrong. It is very easy to completely wreck your library database during this process.

This backup will be your control. This is the most important backup of all. If things go sideways, you can simply drop your database and import this backup in order to return to exactly where you are now. There are several ways to backup your database, but in the interest of keeping this as efficient as possible for the complete novice, we're going to do it with HeidiSQL.

Creating a backup using HeidiSQL

• Connect to your database by clicking the Open button in HeidiSQL.
• Double click on the "Unnamed" node to expand it.
• Right click on the MyVideos** database (note that if there are multiple MyVideos** databases, we are going to be working with the highest numbered one).
• Select "Export database as SQL".
• In the popup window, select both "Create" tick boxes. In the "Data" dropdown, select INSERT.
• Next to the "Filename" input box, click on the button with the little disk icon. Navigate to the location where you would like to save your backup. Name it Original and click "Save".
• Next click on the "Export" button after being sure you have selected all of the options above.
• When the export finishes, click the "Close" button.

Restore a database using HeidiSQL

If during the transfer process you manage to break something or something doesn't work for some reason, you can simply import your backup. To import, we will first drop the modified database. Right click on the MyVideos107 database that has been modified and select drop. Confirm the drop when prompted.

Next, we will import the backup. On the tool bar near the top of the HeidiSQL window, click on the icon with the folder and little magnifying glass. Navigate to and select your backup file and click "Open". Depending on the size of your database, you will likely see a dialogue box popup asking if you want to run the file(s) directly or load them into the editor. Select file(s) directly. (If your database is very small, you will not see this. Disregard this step in that case).

The import will begin at this point. Depending on your system and the size of your database, this may take a few minutes. Your machine may appear to be locked up and HeidiSQL may say it is not responding. Just leave it alone and let it run. The larger your library, the longer this will take. Leave the program alone until the import completes. When it is finished, you will be able to click on the "Close" button.

Once the import is finished and you have clicked the "Close" button, you will need to refresh using the button on the toolbar with the 2 green arrows. Expand the "Unnamed" node once again and voila, your original database is back in place and just as it was before we began.

Creating a backup via the command line

On the machine running the MySQL server, start a terminal session or use an external SSH connection, so you can use the mysqldump command to create a single export SQL file of all databases for the kodi database user ("kodi-backup.sql" in this example).

$ mysqldump --user kodi -p --all-databases > /path/to/kodi-backup.sql

It's also wise to use a different filename each time for your backup/export file, so that previous versions are not overwritten. Sometimes a previous backup comes in very handy.

Restore a database via the command line

HeidiSQL and other SQL tools like MySQL Workbench have the option of adding commands to their export file so database tables can auto-recreated. If this was not activated during your export, you will have to delete the video database manually before you start the database restore. Log into the MySQL server and request the list of databases:

 mysql> show databases;

It will result in a list that also contains both databases of Kodi 17:

MyMusic60;
MyVideos107;

Since this wiki page is limited (for now) to the video section, we will only remove the MyVideos107 database from the MySQL server:

 mysql> drop MyVideos107;

Mind you, MySQL is case-sensitive so use upper and lower case letters.

Now you can start the restore of the backup file you created earlier. Make sure you select the correct export file in case you made several backups.

$ mysql -u kodi -p < /path/to/kodi-backup.sql

Method 1 - TL;DR

This section is only directed at those who are familiar and comfortable with executing direct MySQL queries to the MySQL server.

Situation example

Old Samba Server	New NFS server
Server URL : //smb://192.168.0.1/	Server URL : nfs://192.168.2.2/
Movies Share: //smb://192.168.0.1/films/	Movies Share: nfs://192.168.2.2/films/
TVshow Share: //smb://192.168.0.1/tv/	TVshow Share: nfs://192.168.2.2/tv/

Create a full backup of your Kodi database(s) using your MySQL tool of choice. MySQL Workbench, HeidiSQL, PHPMyAdmin, etcetera. Make sure to include the CREATE commands for actually dropping and (re)creating the databases.

Type carefully...

As a example, this is MySQL's version of a search-and-replace in-text function. It will only replace what is looked for:

UPDATE art SET url = replace(url,'smb://192.168.0.1/','nfs://192.168.2.2/');

Below are the tables that need to be altered in the MyVideos107 database for Kodi 17:

UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');
UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');

UPDATE movie SET c22 = replace(c22,'smb://192.168.0.1/',' nfs://192.168.2.2/');
UPDATE tvshow set c16 = replace(c16,'smb://192.168.0.1/',' nfs://192.168.2.2/');
UPDATE episode SET c18 = replace(c18,'smb://192.168.0.1/',' nfs://192.168.2.2/');

WORK IN PROGRESS!

The music database tables can have another trick up their sleeves, as URLs are stored in different formats. Compare the two URLs in this example from the 'art' table:

nfs://192.168.1.141/MUSIC/ARTISTS/Gipsy Kings/fanart.jpg
image://music@nfs%3a%2f%2f192.168.1.141%2fMUSIC%2fARTISTS%2fGipsy%20Kings%2fGipsy%20Kings%2fGipsy%20Kings%20-%20Bamboleo.mp3/

Below are the tables that need to be altered in the MyMusic60 database for Kodi 17:

UPDATE art SET url = replace(url,'smb://192.168.0.1/',' nfs://192.168.2.2/');
UPDATE art SET url = replace(url,'@smb%3a%2f%2f192.168.0.1%2f',' @nfs%3a%2f%2f192.168.2.2%2f');
UPDATE path SET strPath = replace(strPath,'smb://192.168.0.1/',' nfs://192.168.2.2/');

Once the database conversion has been finalized, do not forget to also update your sources.xml file with the new paths. Kodi does not auto-update those.

Method 2 - The long way home

Modifying Sources

Now that we have safely backed up everything and we know how to restore, we are ready to start making our changes. First we want to change our sources in Kodi, so we will modify our sources.xml directly. Simple replace your old paths with your new ones in the sources.xml, then save your changes and close your editor. Be absolutely sure your paths are correct.

Once you have your paths properly configured, be sure you copy the new sources.xml to each of your Kodi clients that share your MySQL database.

Copy/paste each of my source paths into a text file with one path per line. Multiple paths may be changed, and it is much faster and easier to copy/paste than try to remember each path and potentially typo one or more of them.

Modifying MySQL Paths

This is where things will start getting complicated and attention to detail is absolutely crucial. Remember, you have a safety net in the form of your backup from earlier so if things go horribly wrong you can simply revert to the original database and start again. We're going to be editing multiple tables so we're going to break it down to a table by table scenario to reduce the risk of mistakes and to be sure you change all of your paths.

First, select your myvideos** database in HeidiSQL. Scroll down to the "path" table and double click it.

On the row of tabs just below the toolbar, select the data tab. Make note of the strPATH column. As you can see, the full path to every directory within your source is shown in this column. This is the first thing we need to edit.

To do so, select the tab with the blue arrow that says "Query". We need to replace OLD/PATH/ and NEW/PATH with your paths. In the query box you need to input the following

update path set strPath = replace(strPath,'OLD/PATH/','NEW/PATH/');

Example

You stored all of your movies on one hard drive, and all of your tv shows on another hard drive. Now you are upgrading to bigger drives, but still keeping movies on one drive and tv shows on another. So we are going to change our source paths from:

smb://movie_nas/share/movies/
smb://tv_nas/tv/share/tv_shows/

to our new source paths of:

smb://bigger_movies/movies/
smb://bigger_tv/tv_shows/

The query to do this would be:

UPDATE path SET strPath = replace(strPath,'smb://movie_nas/share/','smb://bigger_movies/');
UPDATE path SET strPath = replace(strPath,'smb://tv_nas/tv/share/','smb://bigger_tv/');

Take notice that I only included the parts of the paths that I want changed. You can define how much of the path is changed by what you put inside the 'brackets'. The ONLY parts that will be changed will be the parts defined within the 'brackets' and all the rest will be ignored. Be very careful that you correctly change all of your paths, and watch out for double /'s at the end of your modified paths.

For each path that needs to be changed, you need to add one line, as above. After each path, press enter and add your next path. Continue in this fashion until you have ALL of your changed paths listed. Be sure your punctuation matches that in my examples, or else the queries will simply fail.

Executing the query

Now that you have crafted all of your queries, copy everything in the query window and save it to a text file for future reference. This will save you a lot of time crafting our next series of queries.

Once you've done that, we're ready to execute our queries. Understand that once we do this, you can't undo it without restoring your database backup from earlier-- so triple check that everything is correct before we execute.

To execute, press the button with the blue arrow on the toolbar above the tabs. If you do not have boxes pop up telling you there are errors, you've successfully executed your queries. Look in the log window near the bottom of the HeidiSQL window. If the last line says something like this:

/* 0 rows affected, 0 rows found. Duration for 1 query: 0.093 sec. */

Then you probably borked your old path. In short, that result means that there were no paths found matching your description. Double check your queries in the Query tab.

To check the result

Click on the Data tab again, and scroll down while watching the strPath column. You should see references to your new paths now, and not your old paths. Make sure you didn't forget to change any paths and make any corrections necessary at this point.

Backup again

If everything went smoothly, then backup your database again and name it Step1

Moving on to the next table

• Stuff to do ....*

Once you've crafted and triple checked your queries, go ahead and execute them just as we did for the path table.

Example

You had 2 hard drives with movies on them, and now you are upgrading to 1 much larger drive and combining all of your movies to this single drive. So we're going to change our paths from:

smb://movies1/share/movies/
smb://movies2/movies/share/

to

smb://big_movie_nas/movies/

The queries to accomplish this would be:

UPDATE movie ** blablah

One more table

Now we need to check/update one more table, and we're finished.

On the Database: myvideos** tab, double click on the movie table.

Go to the data tab and scroll right. Take note of columns c19 and c22. They should already reflect the changes made in the previous step, however we need to verify this.

If they are already correct, then move on. If they are not correct then go back to your query tab and simply edit our previous queries and reuse most of what we've already done.

Example

So for this table, we would simply take the queries from the previous example and change them to the following:

update movie set c19 = replace(c19,'smb://movies1/share/','smb://big_movie_nas/');
update movie set c19 = replace(c19,'smb://movies2/movies/share/','smb://big_movie_nas/movies/');
update movie set c22 = replace(c22,'smb://movies1/share/','smb://big_movie_nas/');
update movie set c22 = replace(c22,'smb://movies2/movies/share/','smb://big_movie_nas/movies/');

Testing

Assuming that everything went well, your library should now be fully functional with your new paths (except for thumbnails --see below). From within Kodi, you should not be able to tell anything even changed, and everything should appear exactly the same as before.

To test, open up a single instance of Kodi on only one of your machines. Go start a file of each type that you updated paths for and be sure they play properly. If that works like it should, the next test is to do a library update. If none of your existing media is added again, you've been successful.

If you want to go a step further, add some new content and be sure it shows up as expected. Make sure you have placed your new sources.xml in your userdata folder for each Kodi client before starting them up, or you are going to have problems.

Thumbnails

The last remaining obstacle is your thumbnail cache. You will need to disable your MySQL settings in one client (just temporarily remove the settings from your advancedsettings.xml). Then you need to delete your Thumbnail folders from each client, as well as the local Textures13.db file in the userdata/Database folder.

Now restart Kodi on the client that has MySQL disabled and let it scrape all of your sources again. It will take quite some time, depending on the size of your library and the CPU power of that Kodi client computer.

Once that is finished and you have all of your thumbnails back the way you like them, exit Kodi. Copy your thumbnails folder to each of your Kodi clients and the Texture13.db into the userdata/Database folder, and restore your MySQL settings to all clients. Now start Kodi again, and everything should be back to normal.

Final notes

It cannot be stressed enough-- backup often. If you backed up after each step as instructed, then you could revert to the previous step if you messed up the next step, which could save you a lot of time. If you messed up something major and you're not sure when or where, you have your original to fall back on and start over.

If you didn't backup as instructed, and you messed something up, then you have just landed yourself in a smelly creek with no paddle. Ask in the Kodi forum if you still have questions on the whole data migration in Kodi.