Freenas_mysql_kodi.docx

8.1 About FreeNAS http://www.freenas.org/

FreeNAS is a FreeBSD based NAS system that can be run on almost any hardware - it uses ZFS for disk and file management. It is used at home and in enterprise businesses. 99% of tasks can be done via the web interface.

Example below is based on FreeNAS 9.1.1 on 12/12/13, and assumes you have already configured your nas with drives, datasets etc

8.2 How to 8.2.1 Make a jail create a Jail via the FreeNAS webui - if unsure see here http://doc.freenas.org/index.php/Adding_Jails make a note of the jail IP address shown - you will need this to put in the advancedsettings.xml file on the Kodi machine later

8.2.2 Prepare the jail 

ssh into FreeNAS o If you have a windows PC use putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html) o (easiest) click the "shell" button in the left of the FreeNAS webui

type the following commands 

jls

Take note of the JID of the jail you created, use it in the command below and replace n with the number of the jail you just created 

jexec n csh

8.2.3 Install mysql You are now inside your jail 

pkg install mysql55-server



pkg install nano o (nano is a text

editor you will need shortly) 1

We now need to enable mysql in rc.conf 

nano /etc/rc.conf

insert the line below into the rc.conf file 

mysql_enable="YES"

save the file and exit (see bottom of nano edit page for keystrokes info) 

service mysql-server start

Optional step below - this is an automated script that will secure you mysql instance - it will prompt you for answers to questions. Its up to you if you do this. 

mysql_secure_installation

8.2.4 Configure mysql ready for Kodi to use type in 

mysql -u root -p

You are now in mysql administration

   

CREATE USER 'kodi' IDENTIFIED BY 'kodi'; GRANT ALL ON *.* TO 'kodi'; flush privileges; quit;

1 Make files accessible over the network If you haven't already done so, you will need to make your media files accessible to all your Kodi devices by sharing them on the network through file sharing. Kodi itself or the MySQL server will not share the actual files for this setup. Most operating systems have built-in methods for sharing files to the network, or a NAS device can be used.

Note:  

If you use passwords on your network shares then you will need to copy (or sync) your passwords.xml file from the userdata folder to each Kodi device. Do not use mapped drives on the OS-level, as Kodi will see those as local drives. 2

 

If you use smb:// paths, you may want to use static IP addresses rather than NetBIOS names, as not all your devices may be able to resolve the latter. Even if the media is on the same computer as one of the Kodi instances, you MUST use a network share path. You cannot use a local file path with MySQL.

See Category:File Sharing for some of the file sharing methods that work with Kodi.

2 Exporting See also: HOW-TO:Backup the video library and Import-export library Note: You only need to do the exporting steps if you are migrating an existing local library to a MySQL database. The MySQL setup creates an entirely new library on the MySQL server, so this step allows you to preserve your old library and restore it once MySQL is used in Kodi. Warning: This will create individual ".nfo" and image files along side your video files. This is considered the safest way to backup and/or migrate a library (MySQL or otherwise), but some users might not want the file clutter. For an alternative method see here.

1. Open Kodi on the computer that has the library you want to share 1. Export the Video Library by doing the following: 1. Go to Settings/Media/Library/Video Library and select Export library 2. Select Multiple files

1. 1. Export the Music Library by doing the following: 1. Go to 'Settings/Media/Library/Music Library and select Export library 2. Select Multiple files

3 MySQL and advancedsettings.xml advancedsettings.xml: mysql ***.***.***.*** <port>3306 <user>kodi <pass>kodi

3

<musicdatabase> mysql ***.***.***.*** <port>3306 <user>kodi <pass>kodi true true See also this note about using the tag: #Name tag is only needed when you're importing a previous library from exported files.

1. Create (or add to, if you already have one) an advancedsettings.xml file: 1. Open up a plain text editor 2. Copy and paste the text from the right-hand box into a new text document 3. Replace the two instances of ***.***.***.*** with local network IP address of your MySQL server. If you installed MySQL on a Windows machine, do not use its NetBIOS name, as not all devices may be able to resolve them. 4. Save the file as advancedsettings.xml 2. Copy this advancedsettings.xml file you just created to the userdata folder of every Kodi install you want to sync with.

Note: Sometimes it takes your dhcp too long before an ip-address is assigned to the local machine and the mysql driver are therefore not loaded. In this case the library will appear empty and will not show resumepoints and watchedstates. In that case run sudo systemctl enable connman-wait-for-network.service on the shell or look for the option "wait for a network connection before loading" in the network options

4 Importing At this point Kodi is using the MySQL server for the database, which means it has a new blank library. Now we can either import an old library (see below) or simply start a new library (see adding videos to the library). From this point on, Kodi behaves exactly like it would as if it had a local database, except that database is on the MySQL server and multiple installs can access that single database.

1. Open any of your installs and re-import your library data, or simply set up a new library if you are starting fresh.

4

2. Add (or just "set content" on) the networked video source to Kodi and scan it in as if you were setting up your library for the first time (see adding videos to the library). However, Kodi will read the exported nfo files and images and use those instead of rebuilding the library from scratch. The end result will be an identical library, and your watched status for videos will be preserved. Note: You must add a network source using Kodi's standard formatting. For example, use "smb://192.168.1.20/Videos/" and not "\\BOBPC\Videos\". Try to use static IP addresses over NetBIOS names if using SMB. 3. Import the Music Library by doing the following: Same idea as videos, but using the steps for adding music to the library. You'll end up with an identical library. You can now add files and update the library from any of your devices and the library for all of them will stay in sync.

5 Adding new Kodi devices to the MySQL setup Since the database has now been set up on the MySQL server, all you need to do for any additional Kodi devices, is to copy a small number of files/folders. 



Copy the MySQL connections section for video and/or music databases into the advancedsettings.xml file on the other Kodi client(s). If you have identical Kodi devices, simply copy the whole advancedsettings.xml file from the initially setup Kodi device. Copy from the same userdata root folder the files, such as "favourites.xml", "sources.xml", "mediasources.xml", "passwords.xml" (if present).

To have the other Kodi client(s) also benefit from the already downloaded/cached art thumbnails:  

Copy the Textures13.db file from the userdata/Database folder to the other Kodi client(s). Copy the full Thumbnails subfolder from the userdata folder to other Kodi client(s). (This subfolder may contain several megabytes of small files when dealing with large video/music collections)

You don't need to do any of the other steps, such as rescanning or reimporting. Any fanart not already cached will be downloaded shortly after. If this method works on one device, it should work on all of them, as they are basically sharing the same "brain" for the video/music metadata. If you however used a metadata collector add-on (scraper) that does not ship by default with new installations, such as the Universal Movie Scraper you will not be able to display the media information screen on the new Kodi device. To fix that, you need to install the same metadata collector add-on on the new device. 5

6 Name tag An additional tag can be used for both the and <musicdatabase> entries in advancedsettings.xml file, if you want to change the name of the database. The tag is not required. Kodi will use "MyVideos" and "MyMusic" as database names if the tag is not specified. You may want to do this if you want to have multiple separate libraries (with different content) on the same MySQL server. For example, if you want to create multiple profiles, each with their own shared library, so that each Kodi device can "log-into" that library. (a kids library, a guest library, etc) Note: Don't try to merge the video and music databases! You need to use different values in the tag or you'll end up confusing Kodi leading to the library functionality not working at all. In other words, don't use Kids for both music and videos, but instead use something like Kids-music and Kids-video.

Kodi is still one of the most powerful media center applications around, and it works on everything from powerful media PCs to small Raspberry Pis. But if you have multiple TVs in your house, wouldn’t it be nice if they all stayed in sync? By default, if you have multiple Kodi machines, they won’t recognize each other. Episodes you watched on one TV won’t show as “watched” on another. Wouldn’t it be nice, though, if your bedroom Kodi box knew what you watched in the living room, and vice-versa? Would it be nice if you could stop watching a movie in the living room, and resume watching right where you left off somewhere else in the house? 6

Well, it’s possible—it just takes a bit of setup. Here’s how to do it.

What You’ll Need The core of the synchronization magic we’re about to undertake is a MySQL database. Don’t panic if you’ve never used one before! It does require a little technical knowhow, but we’re here to guide you every step of the way. If you follow along closely, you shouldn’t have any problems. What we’re going to do is install a free version of MySQL server, then instruct all your Kodi machines to use a database on that server as its library (instead of a separate database on each individual computer). From that point forward, when Kodi checks to see if you’ve seen a specific TV show episode or movie, paused media, or set a bookmark, it won’t just be answering for the specific media center you’re standing in front of, but for all media centers in the house. For this project, you’ll need the following:  



More than one media center with Kodi installed (they’ll all need to be the same base version of Kodi—we’ll be using v17 “Krypton” in this guide). A free copy of MySQL Community Server—the Kodi wiki recommends grabbing version 5.5 instead of the newer 5.7, so that’s what we’ll be using for this tutorial. An always-on or nearly-always-on computer to run the MySQL server on.

You can install the MySQL server on any computer that will be consistently on while you’re using the media centers. In our case, we’re going to install MySQL on the same always-on home server that we store our movies and TV shows on—that way, any time the media is available to Kodi, so is the database.

Step One: Install the MySQL Server For this tutorial, we’ll be installing MySQL on a media server running Windows 10. Our installation instructions should match for any version of Windows. For other operating systems, please consult the MySQL 5.5 Manual. The installation of MySQL is straightforward. Simply download the server installation app and run it. Accept the license agreement and the “Typical” installation. When it’s finished, make sure “Launch the MySQL Instance Configuration Wizard” is checked, and click Finish.

7

The MySQL configuration wizard will launch and present you with the option to select between Detailed and Standard Configuration. Select Standard Configuration and click Next.

8

On the next screen, check “Install As Windows Service”, name it MySQL—or, if you’re running multiple MySQL servers for some purpose, give it a unique name—and check “Launch the MySQL Server Automatically” to ensure the MySQL server is always on when you need it.

9

On the next screen, check Modify Security Settings, plug in a new root password, and check Enable root access from remote machines.

10

Click through to the final screen and press Execute to let the wizard set everything up with the parameters you’ve specified. When it’s finished, move on to Step Two.

Step Two: Set Up Your MySQL User Next, it’s time to create a user account on the MySQL server for your media centers. We’ll need a bit of command line work for this. To start, run the MySQL Command Line Client—you should have an entry for it in your Start Menu.

When the console opens, enter the password you created in the previous step. You’ll then find yourself at the MySQL server prompt. 11

At the prompt, type the following commands, pressing Enter after each one, to create a user on the database server: CREATE USER 'kodi' IDENTIFIED BY 'kodi'; GRANT ALL ON *.* TO 'kodi'; flush privileges;

The first portion of the first command creates the user, the second portion creates the password. While identical login/passwords are generally a huge security no-no in this case we’re comfortable using a matching pair for the sake of simplicity. A MySQL database, on a private server, that tracks which episodes of Dexter you’ve watched is hardly a high risk installation.

That’s all you need to do in the command line for now—though we recommend keep the command prompt open for the MySQL server, however, as we’re going to check in later and take a peek at the databases once Kodi has created them for us. We have one final task before going to configure Kodi. Make sure that Port 3306 (the MySQL server port) is open on the firewall of the machine you’ve installed MySQL onto. By default, the Windows installer should open the port automatically, but we’ve seen situations in which it didn’t. The easiest way to open the port is with a PowerShell command. Search for PowerShell in your Start menu, then right-click on it and choose “Run as Administrator”.

12

Then, run the following command and press Enter: New-NetFirewallRule -DisplayName "Allow inbound TCP Port 3306 for MySQL" -Direction inbound –LocalPort 3306 -Protocol TCP -Action Allow

If the command was successful, as shown below, you should be good to continue.

Step Three: Back Up Your Current Kodi Library (Optional) RELATED: How to Store Your Kodi Artwork in the Same Folder as Your Videos

13

By default, Kodi uses an internal SQLite database. In order for Kodi to communicate effectively across your home network, we need to instruct it to use an external MySQL database. Before we get to that step, however, you’ll need to make an executive decision: you can either back up your current library and restore it later (which can sometimes be finicky), or you can start fresh with a new library (which is easy but will require you to re-set the watched state on your shows, and possibly re-choose your artwork if you don’t store it locally). If you want to back up your current library, you can do so from within Kodi. Only do this from one machine—choose the machine with the most up to date libraries. Open Kodi and head to Settings > Media Settings > Export Library. (If you don’t see these options, make sure your menus are set to “Advanced” or “Expert” in Kodi.)

You can export your library as a single file or as separate files. A single file will allow you to put your backup in one place, while multiple files will scatter extra JPG and NFO files into your media folders—this is more reliable, but quite cluttered. Choose whichever option you want.

14

Once your library is backed up, continue to the next step.

Step Four: Configure Kodi to Use Your New MySQL Server Once you’ve backed up the library (or opted to not worry about it and start from scratch), you’re ready to point Kodi to your MySQL server. You’ll need to perform this step on every machine running Kodi, but we recommend setting it up on one machine first—probably the same machine you backed up your library from, if you chose to do so. In order to point Kodi to MySQL, we need to edit Kodi’s advancedsettings.xml file. By default this file does not exist (although it is possible that, during the installation process, Kodi created one for you to deal with specific configuration issues). If the advancedsettings.xml file exists, it will be in the following location, based on your OS:   

Windows: C:\Users\[username]\AppData\Roaming\Kodi\userdata Linux and other Live versions of Kodi: $HOME/.kodi/userdata macOS: /Users/[username]/Library/Application Support/Kodi/userdata

Check in that folder. Is there an advancedsettings.xml file there? Yes? Open it up. No? You’ll need to open a text editor and create one. Regardless of whether you’re editing the existing one or create a new one, cut and paste the following text into the file (note: if there are already some entries in your advancedsettings.xml file, leave those in place and put these values within the correct sections):

15

mysql 192.168.1.10 <port>3306 <user>kodi <pass>kodi <musicdatabase> mysql 192.168.1.10 <port>3306 <user>kodi <pass>kodi Edit the above text to reflect the IP address of your server on your LAN and the username/password of your MySQL database (in our example, it was just kodi/kodi). This basic setup should get your video and music libraries synced, but you can also sync other portions of Kodi, as well as sync multiple profiles with the name tag if you use them.

Once your advancedsettings.xml file is ready to go, open Kodi on that machine. You’ll need to either import your library (from Settings > Media Settings > Import Library), or rescan your sources to begin populating the MySQL database from scratch. Do that now. 16

When that’s done and your library is back in place, you can hop over to your MySQL command prompt and check to make sure Kodi created and populated the databases. At the mySQL comment prompt, run: SHOW DATABASES;

It will output all the databases currently on the MySQL server. You should see, at minimum, at least the following databases: information_schema , mysql , and performance_scheme , as these are part of the MySQL installation itself. The default database names for Kodi are myvideos107 and mymusic60 (we’re not using a database for music in our example, so only our video database is appearing in the list).

If you ever need to remove a database from your MySQL server, you can use the following command: DROP DATABASE databasename;

Empty databases take up hardly any space, and won’t negatively impact the performance of your syncing system, but it’s nice to keep things tidy. If your databases are there, that’s a good start, but it’s worth performing a simple check to see if Kodi is properly populating the databases. From the MySQL command prompt run the following commands (replacing databasename with the name of your video database): SELECT COUNT(*) from databasename.movie; SELECT COUNT(*) from databasename.tvshow;

Each query will return the total number of movies and television shows, respectively, contained in your library (according to the the MySQL database). As you can see, in our case, it is recognizing our library with 182 movies and 43 TV shows:

17

If the number of entries is zero, there is a problem somewhere along the line. Here’s is a quick troubleshooting checklist of common mistakes:    

Did you copy the advancedsettings.xml file to your machine before you started Kodi and re-populated your library? Did you use the GRANT ALL command to give the Kodi account access to the MySQL server? Did you open port 3306 on the MySQL host machine’s firewall? Are your sources valid and scannable when you remove the advancedsettings.xml file and revert to the local database? If not, you’ll need to troubleshoot your sources independently of your MySQL problems.

If everything looks good and your SELECT COUNT query pans out, that means you’re ready to start taking advantage of the cross-media-center syncing.

Step Five: Repeat Step Four for Your Other Kodi Machines The hard part is over! Now you just need to go to each of your other Kodi machines and place the same text in the advancedsettings.xml file that you did in step four. Once you do so (and restart Kodi on that machine), it should immediately grab your library information from the MySQL server (instead of you needing to re-populate the library yourself). On some devices, like Raspberry Pis running LibreELEC, you’ll need to go into the Network settings and make sure “Wait for network before starting Kodi” is turned on for this to work properly. In addition, if your videos are on a share that requires a password, and you get an error after setting up your advancedsettings.xml on a new machine, you may have to go to the “Files” view, click “Add Videos”, and access a folder on the share so Kodi prompts you for your credentials. You can then click “Cancel” or add the source as containing “None” type of media.

18

From there, try watching a video on one box. You should find that when you’re finished, it shows as “watched” on your other Kodi devices as well! You can even stop a video on one machine, then pick up where you left off just by selecting it to play on another machine. Enjoy your new whole-house library syncing!

Configure seu roteador TP-Link com a VPN Acesse o painel de controle do seu roteador digitando o URL para o painel admin do roteador na barra de endereços do seu navegador. (Você pode encontrar este URL na documentação que acompanha seu roteador.) Faça o login com o nome de usuário e senha do seu roteador. No topo da página, clique na guia Advanced.

Na barra lateral, vá para Network > Internet.

19

Configure Internet Connection Type para L2TP. Insira as seguintes informações:   

Username: cole o nome de usuário da configuração manual que você encontrou anteriormente. Password: cole a senha da da configuração manual que você encontrou anteriormente. VPN Server IP/Domain Name: cole o endereço de servidor da configuração manual que você encontrou anteriormente.

Certifique-se de que Secondary Connection está configurada para Dynamic IP.

20

Deixe MTU Size em 1460 (o padrão). Configure Connection Mode para Manually. Configure Max Idle Time para 0 minutos. Clique em Connect.

Você pode clicar em Save para salvar todas as suas configurações. IMPORTANTE: Você vai precisar esperar um tempo para que a conexão seja concluída. Depois de pressionar “Connect”, role para cima e certifique-se de que IP Address e Primary Address NÃO exiba 0.0.0.0. Apenas quando esses dois campos não exibirem 0.0.0.0 você será capaz de acessar a internet. Precisa de ajuda? Fale com a equipe de suporte da ExpressVPN para obter ajuda imediata.. Voltar ao início

Desconecte-se do seu servidor VPN Para se desconectar do servidor VPN, clique na guia Advanced. Na barra lateral, vá para Network > Internet. Próximo à parte inferior da página, clique em Disconnect.

21

22