Difference between revisions of "MySQL/Advanced notes"
(add section to clean stale entries, inspiration from forum post: https://forum.kodi.tv/showthread.php?tid=294011&pid=3010780#pid3010780)
(→Removing stale entries from the database: refine wording a bit)
|Line 17:||Line 17:|
==Removing stale entries from the database==
==Removing stale entries from the database==
Occasionally, it can be beneficial to remove database entries
Occasionally, it can be beneficial to remove database entries files that are no longer on available on the file-system. that database entries are from the library status which managed by Kodi in..
To database entries, connect to the box serving up the SQL database and log into the SQL database:
mysql -u root -p
mysql -u root -p
Revision as of 12:00, 1 February 2021
Share libraries w/MySQL, guide:
For a full listing of the advancedsettings.xml parameters, see advancedsettings.xml#musicdatabase/videodatabase
2 Other databases (PVR and ADSP)
|Kodi also supports using MySQL for PVR (tvdatabase and epgdatabase) as well as the ADSP database. However, there is little to no practical benefit to using MySQL for those databases, and there has been very little testing with them (expect bugs!). Unless you simply want to experiment, or have a very specific need, you should avoid using MySQL for PVR and ADSP.|
3 MySQL speed-up tweaks
3.1 In advancedsettings.xml:
Use the IP address and NOT the hostname of your MySQL server
eg.: 192.168.1.105 <<IP address>> instead of \\SERVER
4 Removing stale entries from the database
Occasionally, it can be beneficial to remove database entries that correspond to files that are no longer on available on the file-system. Note that these orphaned database entries are independent from the Kodi library status which is managed by Kodi [built ins] such as CleanLibrary().
|The database should ALWAYS be backed-up before attempting these commands.|
To locate and delete orphaned database entries, connect to the box serving up the SQL database and log into the SQL database:
mysql -u root -p
Once authenticated, the following can be used to identify active databases on which to work:
For example, Matrix will generate a video database called, "MyVideos119."
Execute the following to identify possible rows for deleting:
USE MyVideos119; SELECT * FROM files WHERE idfile not in (select idfile from episode) and idfile not in (select idfile from movie) and idfile not in (select idfile from musicvideo) ORDER BY `idFile` DESC;
If the command returns rows, these should be stale entries that can be safely removed by substituting DELETE for SELECT * in the last command:
DELETE FROM files WHERE idfile not in (select idfile from episode) and idfile not in (select idfile from movie) and idfile not in (select idfile from musicvideo) ORDER BY `idFile` DESC;
4.1 In my.cnf (my.ini for older versions):
The named file is located at /etc/mysql/my.cnf
1. $sudo nano /etc/mysql/my.cnf
2. Scroll down to [mysqld] section and add:
3. Press Ctrl+X, type y and hit Enter to validate changes and exit nano
4. Restart your MySQL server. $sudo restart mysql
5 Automated Backup
This will allow you to make a regular backup of your MySQL database using the schedule of your choosing and saving the backups for however many days you choose. Recommend performing daily backups and saving for a minimum of 7 days. This will give you enough time to discover a problem with the DB and restore to a previous version that did not have problems.
To begin, go ahead and download the command line version of 7zip. We will use this to compress our MySQL dumps. Create a new folder in your C directory named KODISQLBACKUP. So you should have a C:\KODISQLBACKUP\ location now. In this location, create another new folder and name it zip. So you should now have C:\KODISQLBACKUP\zip. Extract the contents of the 7zip download to this location. Once complete you should have:
C:\KODISQLBACKUP\zip\7za.exe C:\KODISQLBACKUP\zip\7-zip.chm C:\KODISQLBACKUP\zip\license.txt C:\KODISQLBACKUP\zip\readme.txt
We're going to create a down and dirty batch file to perform the actual backup, then we'll execute the batch script with the task scheduler built into Windows. For the purpose of these instructions, it is assumed that you are running MySQL 5.5 installed to the default location. If not, be sure to adjust your path(s) accordingly. It's also a good idea to save your backups to a network location to protect against local drive failure, so the paths here will reflect this. So here are the batch file contents:
:: All lines that start with 2 colons are comments, they do not affect the script itself. These comments are explanations of each command, as well as instructions for the things that you will need to edit. Pay close attention to all comments. :: These lines do not NEED to be edited set year=%DATE:~10,4% set day=%DATE:~7,2% set mnt=%DATE:~4,2% set hr=%TIME:~0,2% set min=%TIME:~3,2% IF %day% LSS 10 SET day=0%day:~1,1% IF %mnt% LSS 10 SET mnt=0%mnt:~1,1% IF %hr% LSS 10 SET hr=0%hr:~1,1% IF %min% LSS 10 SET min=0%min:~1,1% set backuptime=%mnt%-%day%-%year%-%hr%-%min% :: User name for DB - NOTE that root credentials are needed for this script. set dbuser=root :: User password - NOTE that the root credentials are needed for this script. set dbpass=password :: Path to location where you would like to save the errors log file. For simplicity, I keep mine in the same location as the backups. set errorLogPath="\\REMOTE\KODI_Database\backups\dumperrors.txt" :: We need to switch to the data directory to enumerate the folders pushd "C:\ProgramData\MySQL\MySQL Server 5.5\data" :: We will dump each database to it's own .sql so you can easily restore ONLY what is needed in the future. We're also going to skip the performance_schema db as it is not necessary. FOR /D %%F IN (*) DO ( IF NOT [%%F]==[performance_schema] ( SET %%F=!%%F:@002d=-! "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "\\REMOTE\KODI_Database\backups\%%F.%backuptime%.sql" ) ELSE ( echo Skipping DB backup for performance_schema ) ) ::Now to zip all of the .sql files in this folder and move the resulting .zip files to our network location. "c:\KODISQLBACKUP\zip\7za.exe" a -tzip "\\REMOTE\KODI_Database\backups\FullBackup.%backuptime%.zip" "\\REMOTE\KODI_Database\backups\*.sql" ::Now we'll delete the unzipped .sql files del "\\REMOTE\KODI_Database\backups\*.sql" ::Now we'll delete all zip files older than 30 days. You can adjust the number of days to suit your needs, simply change the -30 to whatever number of days you prefer. Be sure you enter the path to your backup location. Forfiles -p \\REMOTE\KODI_Database\backups\ -s -m *.* -d -30 -c "cmd /c del /q @path" popd
Save this as Kodibackup.bat at C:\KODISQLBACKUP Make sure you include the .bat at the end.
After you have edited the above batch file to reflect your paths and saved it as a batch file, all that is left is scheduling it to run. Daily backups are best, but you can use whatever frequency you are comfortable with. To do this, open up the Windows Task Scheduler.
In the Task Scheduler click on "Create Task...", NOT "Create Basic Task...". In the new task window, in the Name box put Kodi MySQL backup (or whatever name you want).
Then click on the Triggers tab, followed by the New button. Select Daily (or whatever you want) and adjust the time you would like it to run. Click OK.
Click on the Actions tab and click New. In the Program/Script box enter C:\KODISQLBACKUP\Kodibackup.bat (or whatever you named your batch file) and in the start in box enter C:\KODISQLBACKUP
Click ok, then click ok in the task window. In the main task scheduler window, right click on the task you just created and select run. If everything works as it should, you should now have a zipped backup of your database(s) in your backup location. It will now do this for you automatically.
AutoMySQLBackup is a script that is designed to keep daily, weekly and monthly backups of your databases. It is very customizable and reliable. There is useful information on installing and configuring the script here: http://www.linux.com/learn/tutorials/659107-how-to-do-painless-mysql-server-backups-with-automysqlbackup.
6 Name tag
<name> tag can be used for both the
<musicdatabase> entries in advancedsettings.xml file, if you want to change the name of the database. The
<name> tag is not required. Kodi will use "MyVideos" and "MyMusic" as database names if the
<name> 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)
7 Multiple profiles with one MySQL database
The idea is to share information on files in the library (only scrape once) but have individual watch status for every profile. For information see the forum thread [MYSQL] HOW-TO: 5 User XBMC and for a setup for Kodi v16 see this post. An older setup can be found at: http://forum.kodi.tv/showthread.php?tid=81095&pid=840499#pid840499