HOW-TO:Share libraries using MySQL/Advanced notes
Share libraries w/MySQL, guide:
For a full listing of the advancedsettings.xml parameters, see advancedsettings.xml#<musicdatabase>/<videodatabase>
If your operating system supports symlinks, you can instead use a symlink which does not require editing the advancedsettings.xml. Here's how:
Assuming you have all of the share permissions correct, you can just REPLACE your thumbnails folder on all of the machines except the HOST machine with a symlink.
Here is an example for XBMC users on Windows 7:
Open an elevated command prompt (run as administrator) and then type in:
CD C:\Users\User_name\AppData\Roaming\XBMC\userdata\ mklink /D Thumbnails \\HTPC\Users\User_name\AppData\Roaming\XBMC\userdata\Thumbnails
Obviously, you need to adjust the paths for YOUR specific machine(s) or this won't work. Make sure you can reach the shared thumbnail directory through Explorer on each machine. If you can't, you have a permissions problem.
No further settings need to be adjusted, every machine that you do this on will essentially think it is using it's own thumbnails directory, but will in fact be using the HOST machines thumbnail directory. You do not HAVE to use the actual thumbnail directory of an existing XBMC installation as in the example code above. You could, for instance, use a neutral share and then create the symlink on the thumbnails directory for ALL of your machines.
If you were previously using the path substitution method, make sure you remove the path substitution from all machines that you use the symlink on.
3 MySQL speed-up tweaks
From: MySQL speed-up tweaks for XBMC. For v11 (Eden) only the first two tweaks are necessary. See Comments for more information.
3.1 In advancedsettings.xml:
Use the IP address and NOT the hostname of your MySQL server
3.2 In my.ini:
(add to mysqld section) skip-name-resolve
4 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 XBMCSQLBACKUP. So you should have a C:\XBMCSQLBACKUP\ location now. In this location, create another new folder and name it zip. So you should now have C:\XBMCSQLBACKUP\zip. Extract the contents of the 7zip download to this location. Once complete you should have:
C:\XBMCSQLBACKUP\zip\7za.exe C:\XBMCSQLBACKUP\zip\7-zip.chm C:\XBMCSQLBACKUP\zip\license.txt C:\XBMCSQLBACKUP\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:
:: These lines do not NEED to be edited
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%
:: User name for DB - NOTE that root credentials are needed for this script.
:: User password - NOTE that the root credentials are needed for this script.
:: 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.
:: 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] (
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "\\REMOTE\XBMC_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:\XBMCSQLBACKUP\zip\7za.exe" a -tzip "\\REMOTE\XBMC_Database\backups\FullBackup.%backuptime%.zip" "\\REMOTE\XBMC_Database\backups\*.sql"
::Now we'll delete the unzipped .sql files
::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\XBMC_Database\backups\ -s -m *.* -d -30 -c "cmd /c del /q @path"
Save this as XBMCbackup.bat at C:\XBMCSQLBACKUP 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 XBMC 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:\XBMCSQLBACKUP\xbmcdump.bat (or whatever you named your batch file) and in the start in box enter C:\XBMCSQLBACKUP
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.
5 Name tag
<name> tag can be used for both the video and music entries in advancedsettings.xml if you want to change the name of the database.
The name tag is not required (XBMC will use a default "MyVideos" and "MyMusic" DB name) and it is recommended to not include it if you only need one library, as some users have reported problems/bugs when it was included.
The tag is only needed if you want to have separate databases on the same MySQL server, like for more than one shared library. For example, if you want to create multiple profiles, each with their own shared library, so that each XBMC device can "log-into" that library.