Originally Posted by Joeforsale
Anyone have experience setting up a MySQL server with XBMC that can point me to a good guide? I'm having some issues.
I used a guide from contentwhores (who names these sites?), but the site went down a couple weeks ago and it's gone from the Google cache too. I did save the full text and will repost it here. If anyone has an issue with me posting the full text, I'll take it down.
-----Configuring XBMC to use a MySQL library
The release of XBMC Dharma (currently beta 3) has introduced a very cool new feature: the ability to use a MySQL database. Whereas it has been possible to share content over network shares, the library was always stored locally in the built-in SQLite database. Dharma adds the option to use MySQL which can be hosted on the same machine as the XBMC instance or on a remote system on the network. This article explains how to install MySQL and configure both it and XBMC to use this capability.
It assumes a fair level of familiarity with XBMC, with editing XML files, and installing software. It does not assume you're an expert either in the MySQL product or in SQL but some basic knowledge here would certainly be useful - for a great tutorial on SQL see webdevelopersnotes.com . I also assume that a high level of security on the LAN is not required so the xbmc system will have full access to the MySQL databases.My scenario
The PCs and servers being used for this upgrade are as follows:
MACMINI: 3rd generation Apple MacMini hardware running Windows 7 with XBMC Dharma beta 3. Content is accessed from network shared volumes mapped to drive letters, local SQLite database, Thumbnails on a network share volume. IP address 192.168.1.64/24.
RACK: Server running MySQL on Windows 7, directly connected to 2x DroboPros with the content, sharing content to the LAN over 5x SMB shares. Runs an Intel Atom processor with 2Gb RAM, 128Gb SSD, 4x GigE network ports and Windows 7. IP address on the LAN is 192.168.1.16/24, the DroboPros are each connected directly to one of the other LAN ports for maximum performance. In my setup the MySQL database will be installed on RACK with XBMC and the MySQL Workbench running on MACMINI.Installation in 7 steps
1. Install MySQL and verify that it works using MySQL Workbench
2. Manually configure MySQL to prepare it for XBMC
3. Configure XBMC to use MySQL rather than SQLite
4. Start XBMC and verify that the tables have been correctly configured
5. Edit the paths to the sources to use SMB shares and not local references
6. Scan the sources to populate the library on MySQL
7. Optimise the MySQL installationStep 1. Install MySQL and verify that it works using MySQL Workbench
Full tutorials on how to install MySQL for your platform can be found elsewhere but the short answer is to download MySQL Community Edition and the MySQL Workbench (GUI Tool) from the MySQL downloads page and install with the defaults. You can install the MySQL Workbench on the same system as the MySQL database or on a different system, no issue either way. Personally I prefer different systems as this more closely mimics how the XBMC machines will communicate with the server - over the LAN and not through a loopback (localhost or 127.0.0.1).
When they're all installed launch MySQL Workbench and create a New Connection under the SQL Development section. This will prompt you for a name for the connection (root on RACK' in my case), the IP address of the server (192.168.1.16), port (default 3306), username (default root) and password (as entered during the installation process for the MySQL database). You can then open the connection to start querying the MySQL server instance; if it doesn't connect then you've probably got an issue with the setup or perhaps your firewalling.Step 2: Manually configure MySQL for XBMC
Assuming the MySQL and the MySQL Workbench are running, our first step is to create a user that will access the two databases we'll be setting up. Use the SQL Editor in MySQL Workbench to send commands to and view responses from the MySQL server. Here the username (USER) will be xbmc and the password (IDENTIFIED BY) will also be xbmc.
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
Note: If you get an error 1396 when trying this then you've probably hit a bug ; you'll have to DROP the user manually in MySQL Workbench before re-creating them.
Next we have to create two empty databases which XBMC will populate with tables and columns the first time it starts and successfully access the MySQL server:
CREATE DATABASE xbmc_video CHARACTER SET latin1 COLLATE latin1_general_ci;
CREATE DATABASE xbmc_music CHARACTER SET latin1 COLLATE latin1_general_ci;
Lastly, we need to give the user xbmc' access to the databases:
Step 3: Configure XBMC to use MySQL rather than SQLite
GRANT ALL ON *.* to 'xbmc'
If it's still running, quit XBMC. Open the advancedsettings.xml file from the userdata folder in a text editor like Notepad. Add the following lines at the end, above the :
Where is the name or explicit IP address of the MySQL server (I have RACK mapped to 192.168.1.16 in my hosts file, so I could have used the name RACK in place of the IP address), and and are the username and password specified in step 2.Step 4: Start XBMC and verify that the tables have been correctly configured
When you start XBMC after completing step 3 XBMC will connect to the MySQL server, find the empty databases and populate them with all the correct tables and columns. As soon as XBMC displays the menu from the skin this will have been completed. To verify that it has worked as expected open the xbmc.log file and check to make sure there are no ERROR: SQL: ' messages. Assuming none, then use MySQL Workbench to check the databases and see that all the tables are there.Step 5: Edit the paths to the sources to use SMB shares and not local references
I'm sure you don't want to have to add all your content to the new MySQL-based library through a re-scan or library update as this could take a long time due to re-scraping. For sure the faster approach would be to export the library from within XBMC when still using the SQLite database, switch to MySQL as explained above, then re-import.
Now, this approach does work if your content is already stored on network shares and referenced by the UNC path in XBMC: e.g., as smb://RACK/480p movies/ and not to a drive letter S:\\480p movies\\. But most people don't have this, their existing libraries map to local drive letters.
OK you say, so I won't change to SMB network paths, I'll just map those shares to the same drive letters that I was accessing locally. So S: that was attached locally becomes S: that now maps to the share. Good idea. But it doesn't work. The reason is that the backslash character is reserved in MySQL so when you try and import the library you'll get an error ERROR: SQL: Undefined MySQL error: Code (1062) and the database won't be updated. UNC paths use forward slashes so they work fine.
As the sources I was using in XBMC pointed to locally-mapped network drives (e.g. S: pointed to \\\\RACK\\480p movies\\) I need to edit each and every one of my sources to pull data from a SMB reference as opposed to a local drive reference. That means selecting Videos' from the XBMC menu, turning off library mode, then editing each source to change the location.
Step 6: Scan the sources to populate the library on MySQLWith all the sources now referencing SMB network paths I can re-scan and XBMC will find the new' content, scrape the metadata as needed from IMDb, thetvdb.com etc. and update the library on MySQL. If you use a SQL Editor window in MySQL Workbench you can run queries as it's happening to see the new rows:
SELECT * from xbmc_video.movie;
This will display all the movies that XBMC has found. If you just want to see how many rows (separate movies) XBMC has in the MySQL database then use:
SELECT COUNT(*) from xbmc_video.movie;
In the above queries replace movie with tvshow and instead you'll see all the TV shows.Step 7: Optimise the MySQL installation
Certainly MySQL can be tuned for better performance. If you want to do that then the MySQL Performance Blog would be a good place to start.
But aside from that, what we've found is that the xbmc_video database is fine, but the xbmc_music database performs slowly as defined by XBMC. To improve this we can add three indices to the database which improve things dramatically:
ALTER TABLE xbmc_music.song ADD INDEX idx_idArtist(idArtist);
ALTER TABLE xbmc_music.song ADD INDEX idx_idGenre(idGenre);
ALTER TABLE xbmc_music.song ADD INDEX idx_idAlbum(idAlbum);